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
