((Re)): Really Wierd Query tuning issue

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 13 Aug 2004 07:00:46 -0600
Message-Id: <6.1.0.6.2.20040813064456.0309cc38@pop.centrexcc.com>

Autotrace uses explain, while in the sql trace (10046 event trace) Oracle records the actual plan used for the query (Since Oracle 8). That's the same as what you'll see in v$sql_plan as of 9. I practically never use autotrace. I can do an explain myself. If I want the execution stats (cpu used, consistent reads, ...) I use a variation of Tom Kyte's test harness which gives me much more information than autotrace. And If I want/need to be sure about the plan or want the details by rowsource in the plan I use sqltrace and tkprof, or, increasingly, when using Oracle9, v$sql_plan and v$sql_plan_statistics.

At 06:31 AM 8/13/2004, you wrote:

>did a 10046 trace on each query. autotrace output was wrong. Said it was
>doing an index unique scan and it was doing a full table scan. The output
>for the hinted query and the query with the order by said 'index unique
>scan', 10046 trace said 'index full scan'.
>What does the 10046 trace use to get the explain plan? I thought it used
>the same stuff as autotrace? I had thought that only 'explain plan for'
>could be inaccurate?
>not sure i can post the 10053 trace. I would have to change table names,
>etc...

How many blocks does the table occupy? I'd have to assume it's 6 or less. If that is correct, and given that you stated the index clustering factor as 53, that would mean that the rows are distributed across the blocks in a kind-of round robin fashion. Practically no two consecutive ( PK order wise ) rows are in the same block. Of course you can achieve that with as little as two blocks.

>----------- Original message --------------
>
> > What's your db_file_multiblock_read_count set at, or are you using system
> > stats? From the absence of cpu costs in the explain plan I'd say no.
> >
>--------------------
>no system stats. db_file_multiblock_read_count is set to the default of 8.
>we have defaults for optimizer_index_caching and optimizer_index_cost_adj
>also unfortunately... out of my control.

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 Fri Aug 13 2004 - 07:56:31 CDT