Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Really Wierd Query tuning issue

Re: Really Wierd Query tuning issue

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Thu, 12 Aug 2004 13:41:44 -0600
Message-id: <411BC7F8.6090600@sun.com>


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
> -----------------------------------------------------------------



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
-----------------------------------------------------------------
Received on Thu Aug 12 2004 - 14:37:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US