How large is your db block buffer? I was once asked to help with an
unsually slow database, and the first thing I noticed was the 3MB
buffer size!
Jonathan.Trumbull_at_abbott.com (Jonathan D. Trumbull) wrote in message news:<266c2da3.0210291539.4d11f1db_at_posting.google.com>...
> 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
Received on Wed Oct 30 2002 - 21:47:44 CST
