Jonathan D. Trumbull wrote:
>Hi,
>
>I have searched and searched but I can't find even a hint of a
>reference on this.
>
>Basically I have a query in the form:
>
>SELECT ColumnA,ColumnB
>FROM SomeTable
>WHERE ColumnA IN ('Item1','Item2',....'ItemN')
>
>If I have many items, say >1000 my query slows to a crawl. It takes
>~2 minutes on a very very lightly loaded server. I have an nonunique
>index on ColumnA. Looking at the explain plan, it is using that index
>(although a table scan should also be fast since it's now only 40,000
>rows long). For fun, I built the table as a IOT and it's still just
>as slow (the explain plan uses a range scan of the index?).
>
>There is very little disk activity it just nails the processor at 98%.
>
>My tests are being done on a single processor intel box with 512M of
>memory (lots free) with 8.1.7 SE and WIN2K. I am using this box as a
>test setup because I am seeing the same problem on a 4-processor
>production DB.
>
>Any ideas what could be slowing this down?
>
>Here is the TKPROF.
>
>call count cpu elapsed disk query current
> rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.00 0.00 0 0 0
> 0
>Execute 1 0.00 0.00 0 0 0
> 0
>Fetch 1 0.00 0.00 0 896 14
> 1
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 3 0.00 0.00 0 896 14
> 1
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 5
>
seems like timed_statictics is turned off. could you post the explain
plan and tkprof output with timed_statictics set to TRUE
--
hth,
Stan
Received on Tue Oct 29 2002 - 20:44:48 CST
