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).
The database administrator has a choice when it comes to database performance tuning -- solve the problem before it happens or after. On the "before there is a problem" end, we (anyone who writes SQL) have the opportunity to write good or efficient SQL. 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.
The other end of the spectrum 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. Common to both ends is the Cost Based Optimizer. The execution plan generated by the CBO is used to confirm several aspects of our statement of interest. Access method(s), or how Oracle intends on going about the task of getting rows is one key element within a plan. 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.
Another key area has to do with join methods. Aside from hints, we don't generally change what Oracle (the CBO) does. 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. What influences the join method is the size of rowsets, and the CBO gets that from statistics. Looked at another way, Oracle naturally considers indexes once we put them in place. Oracle internally comes up with join methods and what we code indirectly influences how the two result sets are joined.
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. 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).
For a relatively simple query, you might be amazed at all the work Oracle, via the CBO, goes through. Let's run a 10053 trace event and examine the contents of the trace file.
View article
Back to DBAsupport.com
