Re: Improving fetch times


Do you have an Extended (event 10046 ) Trace  that shows the
Wait events ?

Is the Row Source operations listing actually the same for the
query ?

At 06:41 PM Friday, Yechiel Adar wrote:
I have a problem with a query that takes a long time to fetch.
Each execution fetch about 6-7,000 rows.
How can I improve the fetch phase?
I will also open a TAR but I decided to ask also for your help.

Oracle 10.2.0.2 on windows.

SELECT R.DDATE, R.IINDEX, R.NRATE
FROM RATES R, RATESDATE R1
WHERE
(R1.DDATE = '7/31/2007')
AND (R.IINDEX= R1.IINDEX)
 AND (R1.DLASTUPDATE = R.DDATE)
ORDER BY R.DDATE,R.IINDEX

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2963 0.06 0.09 0 0 0 0 Execute 2963 3.89 380.16 0 174817 0 0 Fetch 23704 61.96 6987.59 0 0 0 17996747 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 29630 65.92 7367.85 0 174817 0 17996747

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 39

Rows     Row Source Operation
-------  ---------------------------------------------------
   6069  PX COORDINATOR  (cr=59 pr=0 pw=0 time=2698736 us)
      0   PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0    SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)
      0     PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0      PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
      0        PX BLOCK ITERATOR PARTITION: 1 24 (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FAST FULL SCAN I_P_DDATE_IINDEX_NRATE PARTITION: 1 24 (cr=0 pr=0 pw=0 time=0 us)(object id 73075) 0 INDEX RANGE SCAN I_DDATE_IINDEX_DLASTUPDATE (cr=0 pr=0 pw=0 time=0 us)(object id 73101)

TIA

--
Adar Yechiel
Rechovot, Israel


Hemant K Chitale
http://hemantoracledba.blogspot.com



--
http://www.freelists.org/webpage/oracle-l


Other related posts: