RSS Feed

Method R Discussion Group

Hi,
    does the explain plan gives the correct output, in many cases plan gives the rough estimation,
    to know more about the pain points in my query what will be the best option


thanks
~As

To answer the first part of your question, I'll quote Cary Millsap: "EXPLAIN PLAN is an Oracle command that allows you to ask the Oracle kernel what sequence of steps it "believes" it would use to execute a given SQL statement. EXPLAIN PLAN produces a *predicted execution plan*.

An execution plan is the actual sequence of steps that the Oracle kernel has used (past tense) to execute a given SQL statement.

EXPLAIN PLAN is a pretty good predictor of execution plans, but it's not perfect. Sometimes it tells you one plan, but when you actually run a query, Oracle uses a different plan. The cases in which this occurs are well documented in our SQL Optimization courses."
(see:  http://support.hotsos.com/default.asp?hotsos.11.156.1 )

As for the second part of your question, the best option is to run a sql trace on your query, that will tell you exactly what's going on.

(in 9i and below: alter session set events '10046 trace name context forever, level 12';
10g: DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE);)

As Jan included in his response, the explain plan is the sequence of operations that Oracle believes it will follow to gather the data that will satisfy your query.  One more thing to note is that the estimates for rows, bytes, cost and time are just that - estimates.  The optimizer uses object statistics, instance parameter settings and system statistics to compute its "best guess" at how many rows, etc, each plan operation will involve.  The more accurate the statistics are, the closer the estimates will be to the real thing (which as Jan said, you can know by tracing the SQL's execution). 

But, in the end, explain plan shows only estimates.  While it is a good tool and a great way to see how the optimizer is estimating what "should" happen, the only way to KNOW is to execute the SQL and trace it and review the actual results.

If you really want to get into the deep internal workings of the optimizer, study the output of a 10053 trace. Or Take a class from Karen!