autotrace and EXPLAIN PLAN may not create actual plans in some cases.
This is because autotrace and EXPLAIN plan do not peak binds and they also dont care about the data type of the columns while deciding whether an index on that columns can be used or not.
MOS note, EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plans (Doc ID 1268111.1) got the details. This note is for : Oracle Server - Enterprise Edition - Version 8.0.3.0 and later.
Kerry Osborne also showed that AUTOTRACE sometimes may produce wrong plans. (http://kerryosborne.oracle-guy.com/2010/02/autotrace-lies/), he also showed EXPLAIN plan sometimes may produce wrong plans: (http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies)
By the way, what these two tools have in common? -> Autotrace uses Explain Plan.
So, in conclusion, we can say that, the actual execution plan is what is prepared/created during the execution.
We should not always rely on the explain plans that we have created using autotrace or Explain plan.
We should rely on the explain plans that are prepared for the running queries or prepared and still avaiable through cursor info stored in data dictionary views for the queries executed earlier.
In conclusion, the best ways for getting the actual explain plans are;
1)Tracing the session; using SQL TRACE (10046 trace)
2)If we know sql_id of the query, using V$SQL_PLAN or DBMS_XPLAN.DISPLAY_CURSOR.
"Autotrace" and "EXPLAIN PLAN" are easy to use, but they may not generate the correct explain plans everytime.
So, DBMS_XPLAN.DISPLAY_CURSOR is recommend.
This method required sql stateament to be in shared pool once, so it means it requires an execution.
Suppose, we have written an sql statement and want to check its actual explain plan created by the optimizer; we just have to the following;
execute the statement; (we can cancel the execution of sql statement if it is a long one, just after we start its execution, it will be already parsed,so we still get what we need)
Find the sql statement in v$sql
execute DBMS_XPLAN.DISPLAY_CURSOR for that sql statement.
This is because autotrace and EXPLAIN plan do not peak binds and they also dont care about the data type of the columns while deciding whether an index on that columns can be used or not.
MOS note, EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plans (Doc ID 1268111.1) got the details. This note is for : Oracle Server - Enterprise Edition - Version 8.0.3.0 and later.
Kerry Osborne also showed that AUTOTRACE sometimes may produce wrong plans. (http://kerryosborne.oracle-guy.com/2010/02/autotrace-lies/), he also showed EXPLAIN plan sometimes may produce wrong plans: (http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies)
By the way, what these two tools have in common? -> Autotrace uses Explain Plan.
So, in conclusion, we can say that, the actual execution plan is what is prepared/created during the execution.
We should not always rely on the explain plans that we have created using autotrace or Explain plan.
We should rely on the explain plans that are prepared for the running queries or prepared and still avaiable through cursor info stored in data dictionary views for the queries executed earlier.
In conclusion, the best ways for getting the actual explain plans are;
1)Tracing the session; using SQL TRACE (10046 trace)
2)If we know sql_id of the query, using V$SQL_PLAN or DBMS_XPLAN.DISPLAY_CURSOR.
"Autotrace" and "EXPLAIN PLAN" are easy to use, but they may not generate the correct explain plans everytime.
So, DBMS_XPLAN.DISPLAY_CURSOR is recommend.
This method required sql stateament to be in shared pool once, so it means it requires an execution.
Suppose, we have written an sql statement and want to check its actual explain plan created by the optimizer; we just have to the following;
execute the statement; (we can cancel the execution of sql statement if it is a long one, just after we start its execution, it will be already parsed,so we still get what we need)
Find the sql statement in v$sql
execute DBMS_XPLAN.DISPLAY_CURSOR for that sql statement.
No comments :
Post a Comment
If you will ask a question, please don't comment here..
For your questions, please create an issue into my forum.
Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html
Register and create an issue in the related category.
I will support you from there.