Join order: CHANNELS#0 TIMES#1 CUSTOMERS#2 SALES#3īest so far: Table#: 0 cost: 3.0015 card: 2.0000 bytes: 42 Note that these are permutations, where order is important, not combinations, where order is not important. You can clearly see how the optimizer tries different join orders, giving way to the idea that the order in which you list tables in the FROM clause is not necessarily the join order seen within the execution plan.įor this example, the CBO tries 21 permutations. The trace file (for the above statement) has nearly 6,000 lines in it, and much of the output is similar blocks. This location corresponds to your background_dump_dest parameter. Using Oracle 11 g, the output file can be found in the trace folder under this path (ORACLE_BASE for me is c:apporacle): $ORACLE_BASEdiagrdbmsora11gora11gtrace GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc Let’s take a fairly simple statement (as seen in various places throughout Oracle’s documentation) and examine its 10053 output. Having the PLAN_TABLE in your schema (assuming this is done via ALTER SESSION by someone other than SYS) is needed as that is where, as always, the execution plan output is stored and 10053 tracing includes the output of the execution plan for your statement. Unlike “normal” tracing using SQL_TRACE, the trace file generated here is already formatted. Parameters used by the optimizer (level 1 only) What is collected in the trace file includes:ġ. Level 1 is more comprehensive than level 2. You have a choice of two levels with the 10053 trace event. This built-in doesn’t have the best documentation in the world (or public support for that matter). Yet another method includes DBMS_SYSTEM and its SET_EV subprogram. Oradebug event 10053 trace name context off Oradebug event 10053 trace name context forever, level 1 Using the ALTER SESSION option is more flexible as pretty much any user can do this. ALTER SESSION SET EVENTS='10053 trace name context forever, level 1' ĪLTER SESSION SET EVENTS '10053 trace name context off' Īnother method involves using ORADEBUG, which overall, has the advantage of outputting the name of the trace file, but has the disadvantage of having to connect as SYS. A simple alter session command turns it on (and off). There are a couple of ways to start this trace event. Let’s run a 10053 trace event and examine the contents of the trace file. Other tools or settings show us WHAT the CBO comes up with the 10053 setting tells us HOW the CBO came to its decision (the final execution plan).įor a relatively simple query, you might be amazed at all the work Oracle, via the CBO, goes through. So, behind the scenes, what is the CBO doing when it comes to how it comes up with an execution plan? This is where the 10053 trace event comes into play. Oracle internally comes up with join methods and what we code indirectly influences how the two result sets are joined. Looked at another way, Oracle naturally considers indexes once we put them in place. What influences the join method is the size of rowsets, and the CBO gets that from statistics. There’s nothing we’re coding that has much to do with which join method (typical methods being nested loop, hash, and merge sort) will be used. Aside from hints, we don’t generally change what Oracle (the CBO) does. For example, if you were expecting an index to be used but the access method against that table reflects a full table scan, you know have some work to do regarding the index and perhaps some initialization parameters.Īnother key area has to do with join methods. Access method(s), or how Oracle intends on going about the task of getting rows is one key element within a plan. The execution plan generated by the CBO is used to confirm several aspects of our statement of interest. Common to both ends is the Cost Based Optimizer. On the other end is where we get to practice our “Oracle CSI” skills and choose a tool which helps us solve the “why is this statement so bad?” issue. The end result is a statement which is optimal, or nearly so, and for the most part, we’ve done our due diligence in terms of avoiding introducing a problem child SQL statement into the fray. On the “before there is a problem” end, we (anyone who writes SQL) have the opportunity to write good or efficient SQL. When it comes to performance tuning, we can spend time on one or both ends of the problem. Ever wonder what the Oracle Database Cost Based Optimizer (CBO) was doing behind the scenes or how it comes up with an execution plan? While other tools or settings show us WHAT the CBO comes up with, the 10053 trace event setting tells us HOW the CBO came to its decision (the final execution plan).
0 Comments
Leave a Reply. |