Re: Really Wierd Query tuning issue

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 12 Aug 2004 19:51:51 -0600
Message-Id: <6.1.0.6.2.20040812194726.02ff0548@pop.centrexcc.com>

Can you post the tkprof output from your sql trace or the trace itself. With bind variables you can not entirely trust the explain plan (even without you can not always). Could it be possible that the optimizer mistakenly chose a full scan for the query without the order by, which ended up using 63 LIO rather than the much fewer the optimizer expected. But with the order by, it DID use the index to save on the sort.

At 11:59 AM 8/12/2004, you 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:

>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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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 - 20:47:48 CDT