1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Spool taking time to execute

Discussion in 'SQL PL/SQL' started by yabhi_22, Jul 24, 2009.

  1. yabhi_22

    yabhi_22 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I have a master table which is huge and I am trying to select all rows and spool a corresponding file.
    The query uses a order by clause. The primary key is used in it.

    I tried using oracle hints like index,all rows, rowid etc..but unfortunately there is no performance difference.

    Can you guys help on this?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Oracle is not clueless, and it knows that 10,000,000 rows fetched by full table scan is far less costly than using an index and doubling that I/O load. You've asked for every row in the table, and you've asked for that result set to be sorted; depending upon your sort_area_size and the size of that result set you could be dumping sorts to disk rather than performing them in memory. You can alter your session and increase the sort_area_size you're using; you COULD also simply spool the unordered output to a file then use an operating system utility like sort (on UNIX/Linux) to process the file and order the contents. Convenience comes at a price, and normally that price is performance. You've asked for a dump truck full of data; why unload that truck with a soup spoon?



    David Fitzjarrell
     
  3. yabhi_22

    yabhi_22 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Ok.
    So I need to do a simple select from the table and spool the data in a file.
    But I tried this, unfortunately this did not make any difference.
    I was suprised as a select without order by was taking same time as a select with order by.
    Did I miss something?
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Does spooling or SELECT stratement takes time ? I belilieve the Select statement actually takes time (even without where clause) !!!!

    If thats the case ..
    You have to find. .
    How big is the Table ?
    How big is the block size?
    Is the block size too bigger than what it is supposed to be ?

    If so , I think the problem is beacuase of Higher water mark because of
    Frequent parallel/Direct DML statements in the table.

    In this case you take the help of a DBA.
    (This is just One possibility)
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is true.

    This has nothing, really, to do with query speed and I cannot understand why you think it does.

    What does that sentence mean? How can the block size be 'bigger' than the block size configured for the tablespace?

    Simply moving the highwater mark to the end of the populated data blocks does not cause a problem. What could create problems is large volumes of data deleted from the table, leaving the highwater mark well above the last populated data block, causing Oracle to scan empty blocks beyond those actually populated, wasting resources and needlessly increasing query time. There is no evidence of this as reported by the original poster.

    Yes, I'd agree a qualified DBA needs to be consulted.