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