| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: deciding which query plan to use
I think a proper question to ask would be ...is the system i/o bound or cpu
bound? ....among other things.
-----Original Message-----
From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
Sent: Wednesday, April 14, 2004 7:05 AM
To: oracle-l_at_freelists.org
Subject: deciding which query plan to use
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?
The query it self is over the form:
select cols
from tab1, tab2
where tabl1.col = tab2.col
and tab2.col = <value>
the join column has very few distinct values. That is the whole bottleneck. Its just tough to decide what is better? Lower Logical I/Os or lower overall latch usage?
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |