An explain plan predicts how Oracle will process your query, where as an execution plan describes the steps it actually took.
The main difference between the AUTOTRACE and EXPLAIN PLAN commands in Oracle is that AUTOTRACE actually executes the query (in the way TRACE does) and automatically queries the plan table, whereas EXPLAIN PLAN does neither. The AUTOTRACE command generates similar information, as shown in the next listing. To use AUTOTRACE, the user must possess the PLUSTRACE role (by running plustrce.sql, which is usually located in the ORACLE_HOME/sqlplus/admin directory).
the autotrace statistics reported however are FACTS, after executing the statement, not guesses, not part of optimization.
So, autotrace used explain plan to show the plan that probably, likely will be used and autotrace shows the ACTUAL number of resources expended to execute the query.
According to Tom's suggestion:
If you understand the small number of times it could be misleading, you can use it to judge what will probably happen. It shows the GUESS, we compare the guess to reality to understand why a bad plan is generated.
We always want the reality, and the plan - reality(Execution Plan/ Auto trace) shows us what actually happened, the plan is the guess and as long as the explain plan steps match the actual - we can use that to help understand what the optimizer was thinking. As a person tuning sql, that is very useful. But by itself, the plan is "interesting" but must be considered for what it is - "probably what might happen"
Top tip: If someone asks for “the plan” they’re usually looking for one of the execution variety. I.e. what really happened. We should provide the execution plan in that case.
The main difference between the AUTOTRACE and EXPLAIN PLAN commands in Oracle is that AUTOTRACE actually executes the query (in the way TRACE does) and automatically queries the plan table, whereas EXPLAIN PLAN does neither. The AUTOTRACE command generates similar information, as shown in the next listing. To use AUTOTRACE, the user must possess the PLUSTRACE role (by running plustrce.sql, which is usually located in the ORACLE_HOME/sqlplus/admin directory).
the autotrace statistics reported however are FACTS, after executing the statement, not guesses, not part of optimization.
So, autotrace used explain plan to show the plan that probably, likely will be used and autotrace shows the ACTUAL number of resources expended to execute the query.
According to Tom's suggestion:
If you understand the small number of times it could be misleading, you can use it to judge what will probably happen. It shows the GUESS, we compare the guess to reality to understand why a bad plan is generated.
We always want the reality, and the plan - reality(Execution Plan/ Auto trace) shows us what actually happened, the plan is the guess and as long as the explain plan steps match the actual - we can use that to help understand what the optimizer was thinking. As a person tuning sql, that is very useful. But by itself, the plan is "interesting" but must be considered for what it is - "probably what might happen"
Top tip: If someone asks for “the plan” they’re usually looking for one of the execution variety. I.e. what really happened. We should provide the execution plan in that case.
No comments:
Post a Comment