((Re)): Really Wierd Query tuning issue
- From: ryan.gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Aug 2004 12:31:35 +0000
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@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:
