Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Large # Items in IN () Very Slow

Re: Large # Items in IN () Very Slow

From: Jonathan D. Trumbull <Jonathan.Trumbull_at_abbott.com>
Date: 30 Oct 2002 04:45:21 -0800
Message-ID: <266c2da3.0210300445.1cc075da@posting.google.com>


Stan,

> >
> seems like timed_statictics is turned off. could you post the explain
> plan and tkprof output with timed_statictics set to TRUE.

Opps! I am new at this. Here is the explain plan (from TOAD) and the TKPROF output with TIMED_STATISTICS = TRUE:

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out
SELECT STATEMENT Hint=CHOOSE		1  	 	134  	 	        SORT AGGREGATE		1 
	5
    INDEX FAST FULL SCAN	SPS_R_ALL_IDX	24 K	121 K	134
call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------
Parse        1      0.17       0.18          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        1     60.27      66.49        897        896         14  
        1

------- ------ -------- ---------- ---------- ---------- ----------
total        3     60.44      66.67        897        896         14  
        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

I can't see why the fetch is so long. All the data is in the cache after a few seconds and I stop seeing disk activity.

Thanks for the help!

--Jonathan Received on Wed Oct 30 2002 - 06:45:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US