Improving fetch times

From: Yechiel Adar <adar666_at_inter.net.il>
Date: Fri, 20 Jul 2007 13:41:08 +0300
Message-ID: <46A09144.2030403@inter.net.il>

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 20 2007 - 05:41:08 CDT