This is the fourth of an article series on advanced Oracle explain plan techniques (read Part 3 here). In this article, we will finish the exploration of DBMS_XPLAN format options with a focus on the ‘ALLSTATS LAST’ formatting option.
In the example above, notice that the SQL text has a hint: /+* GATHER_PLAN_STATISTICS */ . I have shown this in the previous article and will discuss the significance of this information here. Again, this DBMS_XPLAN feature works in conjunction with this hint and shows additional information, most importantly, the E-Rows (estimated row counts…matches up with the standard explain plan with the information the optimizer used to create the explain plan) and the A-Rows…the ACTUAL row counts from its first execution. I go into detail in my courses about how you need to see the actual row counts when looking at problem SQL. Why? Because the cost-based optimizer assembled the explain plan thinking the row counts would be like the E-Rows but in actuality, the real row counts encountered at execution time were maybe significantly different, so different that a different explain plan would have been more optimal. Prior to Oracle 10 and this newer syntax, you had to run a SQL Trace (the 10046 trace) to see the actual row counts from SQL execution. This method is much easier.
I created a free tool that contains this syntax (download from www.DanHotka.com) when there wasn’t a free explain plan tool. Now that SQL Developer exists, and is free, and does explain plans, I’ve enhanced this tool to include newer syntax such as the above-mentioned DBMS_XPLAN syntax combination.
Notice the above SQL does have the GATHER_PLAN_STATISTICS hint. For my free tool, this needs to be the very first hint. When I see this hint, I then run the appropriate DBMS_XPLAN syntax! Notice line 2 of the explain plan. If you are familiar with explain plans, you will know that Hash Joins should be the first table reference in the indentation should be the smaller and the second reference should be the larger. So, looking at the A-Rows, a Nested Loop would have performed better, or, the tables need to be reversed for the Hash Join. Also notice on line 2 that the E-Rows was 26,000 rows and the A-Rows was a third of a million! This is quite a difference.
Then, along comes Oracle 11r2. A little known feature they built into this release is ‘cardinatlity feedback.’ Oracle 11r2 LOOKS at this same runtime information, for this particular SQL statement, and will re-parse it when it sees it again.
Notice the note at the bottom of this execution. It says that it used information from the first execution when running this SQL. Also notice that the row counts in the E-Rows is much higher than in the first execution of this SQL. Notice that the row counts on the inner most steps (lines 4 and 5 of this explain plan) line up with the A-Rows now. They were quite off in the first execution. This is where the math goes bad for the optimizer when making explain plan decisions.
Oracle 12 expands upon this cardinality feedback and the V$SQL table where these SQL statements are stored for a bit (the library cache) has a new column called ‘SQL_IS_REOPTIMIZABLE’. Oracle noticed the difference and logged it here, and will recompile this SQL when it sees it again.
Oracle 12 refers to this as Adaptive Feedback. Oracle 12 will also save this runtime row count, along with the where clause predicates to be used with similar SQL when it is presented to the optimizer. This technology is called Adaptive Plan Directives. These plan directives are flushed to disk every 15 minutes and are saved for up to 30 days.
If Oracle 12 uses these plans, there will be a note in the DBMS_XPLAN output “SQL Plan Directive used for this statement.”
In this article series (read Parts 1, 2 and 3) we have explored various DBMS_XPLAN syntax to illustrate various examples and uses and how it has evolved through Oracle 12.
Leave a Reply