It wouldn't really do me much good to send you the plan or the query, since
there is alot of business specific information in it and its a long query.
This is a rather odd case.

The problem is that I am joining on a column that has about 120 distinct
values out of about 3 million records. Oracle is choosing a full tablescan
on tableA and an index range scan on tableB (both tables have similiar data
distribution).

This results in about 170,000 Logical I/Os.

If I force an index scan on tableA, then I can get that down to 9000 logical
I/Os. No brainer right?

However, when I use runstats_pkg, overall resource usage is about 234,000
higher with the full tablescan an the 170,000 logical I/Os.

Anyone ever run into this situation? Where logical I/Os are radically
reduced, but overall stress on the system is radically increased?

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html---------------------------------------------------------------------------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org