Oracle Magazine, July/August 2019
Database Developer and DBA ETL ORACLE MAGAZINE JULY AUGUST 2019 144 Note this is an adaptive plan Note that the execution plan that was used in Listing 4 is not the same as the plan described in Listing 2 As previously mentioned there could be many reasons for this but the Note section in Listing 4 gives a hint about one possible cause Adaptive query optimization played a part in determining the execution plan For more information on adaptive plans consult the About Adaptive Query Optimization section in SQL Tuning Guide The cause of the difference between the EXPLAIN PLAN output in Listing 2 and the true execution plan in Listing 4 is less important than knowing that the true plan has now been discovered Armed with the true plan we can begin analysis of the problematic SQL statement for tuning ANALYZING THE PLAN Since the cost based optimizer became available in Oracle Database 7 there have been many articles books and blog posts published with best practices for evaluating an execution plan to tune the performance of a SQL statement Without naming and shaming sources common advisories were EXECUTION PLAN NOTES Here are a couple of additional notes about the contents of execution plans Do not confuse DBMS_ XPLAN DISPLAY_ CURSOR with DBMS_ XPLAN DISPLAY_ PLAN which by default is used to format the output of a plan saved to the PLAN_ TABLE dictionary table DBMS_ XPLAN DISPLAY_ PLAN reports on optimizer plans that have been generated via the EXPLAIN PLAN command and can give misleading results to the developer A call to DBMS_ XPLAN DISPLAY_ PLAN can be modified to source its data from V SQL_ PLAN but DBMS_ XPLAN DISPLAY_ CURSOR will do the job more easily and succinctly Querying a table function such as DBMS_ XPLAN DISPLAY_ CURSOR can be done directly as shown in Listing 4 in Oracle Database 122 and later In earlier database releases such queries need to be written with the TABLE operator to let the database know that a table function is being used select from TABLE dbms_ xplan display_ cursor
You must have JavaScript enabled to view digital editions.
