Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Large # Items in IN () Very Slow
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