((Re)): Really Wierd Query tuning issue

From: <ryan.gaffuri_at_comcast.net>
Date: Fri, 13 Aug 2004 12:31:35 +0000
Message-Id: <081320041231.13148.411CB4A70002D7590000335C2200751150079D9A00000E09D2020E979D@comcast.net>

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

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.

> Without the index access, the order by requires a sort, which could put the
> cost of a "scan + sort" higher than the index access by unique index. A
> 10053 trace might be interesting and may help explain things, provided the
> two plans ARE actually different.
>

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:27:15 CDT