((Re)): Really Wierd Query tuning issue
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Aug 2004 07:00:46 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
