| 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: Really Wierd Query tuning issue
Ryan,
Did you run the second query shortly after the first? What happens if you run the first query over and over? Are the logical I/Os of the table/index or could they be related to parsing the statement?
As for clustering factor, IIRC, that comes into play only when you are dealing with index range or full scans. For a unique scan, it will return 1 index entry that points to 1 table row. For other scans, the CBO uses it to calculate how many table blocks will I have to visit if I read a certain number of index entries. For example, a high clustering factor tells the CBO that the index entries returned by the range scan (say 10% of the total) will require visiting a high number of table blocks (say 80%), so a full table scan may be more efficient.
Regards,
Daniel
ryan.gaffuri_at_comcast.net wrote:
> I have the following query:
> select col1, col2
> from table
> where primary_key = :bind_variable
> Table has 56 rows, clustering factor on the PK_INDEX is 53. So not good. I get the following plan:
> -------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> -------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 33 | 2 (50)|
> | 1 | TABLE ACCESS BY INDEX ROWID| table| 1 | 33 | 2 (50)|
> |* 2 | INDEX UNIQUE SCAN | table_PK | 56 | | |
> -------------------------------------------------------------------------------------
> standard unique index scan retrieving 1 record. This uses 63 Logical IOs. I assume its high because of my high clustering factor. Though I am not sure why so many LIOs if I am getting just 1 row with a unique scan even with a bad clustering factor.
> If I change the query as follows:
> select col1, col2
> from table
> where primary_key = :bind_variable
> order by primary_key
>
> I get the example same query plan, but just 4 logical IOs. Any idea why logical IOs drops so much even though the plan does not change?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Aug 12 2004 - 14:37:57 CDT
 |
 |