You Asked
Dear tom,
A. your new book is illuminating.
B. could you please, post an 10053 trace file and explain in your. Word what is going on there?
C. try to hack it my self - didn't worked for me, too much numbers.
Regards,
Amiel
?
and we said...
Do you have access to my book "Effective Oracle by Design", in there I write (among lots of other stuff): Use the 10053 Event to Trace CBO Choices Have you ever wondered why the CBO is making the choices it is making? It is not because it is arbitrary or capricious, rather the opposite. It is a piece of predicable software that comes to conclusions based on inputs. Given the same inputs, it will steadfastly give the same outputs (well, until you upgrade, that is). It appears arbitrary and capricious at times because what it does is among the most complex things Oracle itself does. Cost-based query optimization is a hard thing to do-truly computer science. The myriad of facts, parameters, and choices the CBO is faced with make it difficult for a human to predict what will happen.

When I'm faced with a query plan I just cannot explain, I use the 10053 trace event. I use this after I've tried all of the normal paths, such as using hints to get the query plan I felt I should have gotten in the first place. Most of the time, the COST/CARD output resulting from that exercise is sufficient to see where the plan went wrong and what statistics I might be missing.

Note that this trace event is undocumented, unsupported, and works only in certain circumstances. However, it is widely known outside Oracle Corporation. A simple Google search for event 10053 will return more than 1,500 documents on the Web and hundreds of hits on the newsgroups. There are even Oracle support notes on this topic available on metalink.oracle.com, the Oracle support web site, if you search for 10053.? I do not make a regular practice of reading these trace files. In fact, I most often use them in the filing of a Technical Assistance Request (TAR) with Oracle support. They provide information to the support analysts that can be useful in diagnosing optimizer-related issues. However, an example will show you what you might expect to see in the trace file generated by this event. In order to generate the CBO trace file, we
need to set an event and then simply parse a query. One technique is as follows:

big_table@ORA920> ALTER SESSION SET EVENTS
? 2 '10053 trace name context forever, level 1';
Session altered.

big_table@ORA920> explain plan for
? 2 select * from big_table where object_id = 55;
Explained.

Now we are ready to inspect the trace file. You can use the same technique outlined in the "TKPROF" section of Chapter 2 to get a trace filename for your session (see that chapter for details if you do not know how to identify your session's trace file). Upon exiting SQL*Plus and editing the trace file, you will see something like this:
/usr/oracle/ora920/OraHome1/admin/ora920/udump/ora920_ora_23183.trc
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /usr/oracle/ora920/OraHome1
System name:??? Linux
Node name:? tkyte-pc-isdn.us.oracle.com
Release:??? 2.4.18-14
Version:??? #1 Wed Sep 4 13:35:50 EDT 2002
Machine:??? i686
Instance name: ora920
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 23183, image: oracle@tkyte-pc-isdn.us.oracle.com
This is just the standard trace file header. The interesting stuff comes next:
*** SESSION ID:(15.1158) 2003-01-26 16:54:53.834