This is the third of an article series on advanced Oracle explain plan techniques (read Part 2 here), continuing where we left off: a discussion of the Oracle DBMS_XPLAN format options. This article will illustrate the ‘ALL STATS LAST’ as well as the ‘ADVANCED’ formatting options.
Notice above that the SQL text has a hint: /+* GATHER_PLAN_STATISTICS */ . I suppose this hint could be in with any other hint present. This hint works in conjunction with the DBMS_XPLAN format option ‘ALLSTATS LAST’ to show some additional information.
This feature showed up in Oracle 10. The next article in this series will discuss what Oracle is doing with the information in Oracle 11R2 and in Oracle 12.
This simple query will show us the SQL_ID and enough of the SQL statement so we can find the correct one. I could have put a comment into the SQL text and searched for that as well.
Above is the original SQL statement.
As you can see, the undocumented ‘Advanced’ format output has considerably more information than the traditional explain plan and predicate information.
Let’s review the content starting from the top. First I show the original SQL statement. You can see the DBMX_XPLAN executed with the correct (and using positional notation) information. The first thing in the output is the sql_id, child cursor, original SQL (unparsed SQL) and the hash value.
Next thing down is the explain plan. When in SQL*Plus, if you enter ‘set HEADING 50’, you won’t get so many headings in the middle of things. I should experiment with turning headings off when using this syntax.
After the explain plan is the query block output assignments. Another Logical Read will show how to set these and exactly what this information means.
After the query block info is the outline, exactly as previously discussed. These are hints that can be placed in the SQL to produce an identical explain plan.
The standard output Predicate Information then follows. If you have been following along with these articles, I discussed Query Transformation. Notice line 7 of the Predicate Information is a where clause item ‘A.STATUS = ‘OPEN’…but if you review the original SQL statement, this syntax is not there! This is Query Transformation and its transitive evaluation where B.STATUS = OPEN and C.STATUS = OPEN and there happens to be an A.STATUS = B.STATUS…therefore A.STATUS should be ‘OPEN’ as well. This might allow for this SQL to use an available index. This is why Oracle does query transformations.
The Column Projection Information is quite useful really. If we had a datatype mismatch, or for some reason Oracle wasn’t using an index we thought it should be using, we can check the information here, see the data type and how and where the predicate information was being evaluated in the explain plan.
The final bit of information here is probably completely useless. It is the explain plan for the DBMS_XPLAN statement itself.
In this series of articles (read Parts 1 and 2 here) will focus on various DBMS_XPLAN syntax to illustrate various examples and uses and how it has evolved thus far thru Oracle 12. The next article in this series will follow up on some loose ends left from this article, and will also show a useful tool that presents this syntax for you.
Leave a Reply