1. Documentation - > Oracle Database 11g Release 2 ( 11.2) -> Masters Book List - > Performance Tuning Guide -> 11 The Query Optimizer - > Reading and Understanding Execution Plans
Documentation - > Oracle Database 11g Release 2 ( 11.2) -> Masters Book List - > Performance Tuning Guide - > 12 Using EXPLAIN PLAN
2. Although we have already seen how to obtain an execution plan for a query ( with EXPLAIN PLAN or DBMS_XPLAN package) , it is worth reviewing the acquired knowledge and deepen their interpretation. An implementation plan is the output produced by the optimizer for the runtime of the DB know how to proceed to access the data from our database. It is represented as a tree, where you can get the order in which they are to perform operations (which is not the order in which the plan shown) , various types of data access , types of JOIN and data operations such as sorts , aggregations, ... Furthermore, the implementation plan gives us information about the cost and cardinality of each operation, access to information on partitions and parallel execution ( PX - Parallel execution ) . We can access execution plans from many different places : · PLAN_TABLE ( from SQL Developer or SQL * Plus ) · V$SQL_PLAN (stored in the Library Cache) V$SQL_PLAN_MONITOR ( versions 11g ) · DBA_HIST_SQL_PLAN ( plans stored in AWR ) · STATS$SQL_PLAN ( Statspack ) SQL Management Base ( SQL Plan Baselines ) SQL Tuning Set Files generadors traces by DBMS_MONITOR Trace files generated by the event 10053 Process state dumps (from 10gR2 )
To view the execution plan and have the DISPLAY DBMS_XPLAN method. But this package has more procedures for the plan from other locations : · DISPLAY : Formats and displays the contents of the plan table (default ) · DISPLAY_AWR : Access implementation plans contained in the AWR repository · DISPLAY_CURSOR : Get the cursors stored in memory · DISPLAY_SQL_PLAN_BASELINE : Access plans SQL Plan Baseline functionality · DISPLAY_SQLSET : Use the plans stored in a SQL Tuning Set (STS )
We use as the first exercise DBMS_XPLAN.DISPLAY procedimimiento we've seen but expanded with several arguments that have never been used.
Generate an execution plan with the EXPLAIN PLAN command with more parameters than usual - · We set an " example ' then specifically identifier to access this plan - · With " INTO" we can specify the target table for the implementation plan (default PLAN_TABLE ) EXPLAIN PLAN SET STATEMENT_ID = 'example' FOR INTO PLAN_TABLE SELECT e.LAST_NAME , D.DEPARTMENT_NAME Hr.employees FROM E , D HR.DEPARTMENTS WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID ;
- Show the execution plan that has been introduced in the table PLAN_TABLE - · You can also use the script @/rdbms/admin/utlxpls.sql to queries without PX ? - · To querys with PX must use @/rdbms/admin/utlxplp.sql ? - · The third argument is the format ( "Typical " is the default) SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ( ' PLAN_TABLE ' , 'example' , ' TYPICAL '));
- We are launching the consultation but this time we show all possible information from the plan SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY ( 'PLAN_TABLE' , 'example' , 'ALL')); - We are able to display the set of hints that accurately reproduce our plan - · We see this in the "Outline Data" section
3. A question we all should know is that the implementation plan we get to " explain plan " may differ from the real plan that is executed when we launched the consultation. And this is due to the famous " Bind Variables " . The variables are entered at runtime can modify the plan based on the values that have histograms and you have the columns of the predicate we use. In this case it is advisable to get the plan view V$SQL_PLAN through DBMS_XPLAN.DISPLAY_CURSOR . It is also advisable to use this method when the environment in which they are launching sessions is different from ours. For example, when we want to optimize a query that is launching an application that uses a different parameter to ours ( eg . OPTIMIZER_MODE , ... ) , then it is also advisable to obtain the memory implementation plans .
- We launched a consultation with Bind Variables - Declare a variable sample ' bv ' VAR bv VARCHAR2 ( 10); EXEC :bv : = 'Gerald' ; - Launched the query using that variable SELECT COUNT (*) FROM hr.employees WHERE FIRST_NAME = :bv ; - We get the actual execution plan that used the engine with the values of the Bind Variables - · The values listed in the " peeked Binds " ( Ex. 1 - :BV ( VARCHAR2 (30) ,CSID = 873 ):'Gerald') section SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ( format = > 'PEEKED_BINDS BASIC + '));
4. Another "fast" way to get the execution plan for a query is the AUTOTRACE parameter. With this parameter we can get the plan and execution statistics of our consultation. If we use the TRACEONLY argument , execution does not return any results , only the execution plan and statistics.
- We see the different options we have - Activate AUTOTRACE SET AUTOTRACE ON - We filter the results of the judgment run SET AUTOTRACE TRACEONLY - Show only the execution plan SET AUTOTRACE TRACEONLY EXPLAIN - Statistics show only SET AUTOTRACE TRACEONLY STATISTICS - Descactivamos AUTOTRACE SET AUTOTRACE OFF - Generate statistics generated by the query in the previous year utlizamos - Activate only statistics SET AUTOTRACE TRACEONLY STATISTICS - Consultation launched SELECT COUNT ( *) FROM WHERE FIRST_NAME = hr.employees : bv ;
5. The AWR repository is very useful for diagnosing problems in the past . We can see the judgments produced greater burden on the systems based on certain criteria ( run time, CPU time, buffer gets , ... ) . - We get all execution plans AWR repository consult the table SH.TEST_SALES SELECT TF . * FROM DBA_HIST_SQLTEXT HT TABLE ( DBMS_XPLAN.DISPLAY_AWR ( HT.SQL_ID , null , null, 'ALL' ) ) TF WHERE HT.SQL_TEXT LIKE '%SH.TEST_SALES%' ;
6. Oracle 11g has introduced the possibility of monitoring SQL statements in real time. You must have the STATISTICS_LEVEL parameter to "typical " at a minimum , and also the control_management_pack_access parameter must be "DIAGNOSTIC_TUNING" (default in both cases). All inquiries have monitoring enabled by default , but can force the MONITOR hint. Once we launched monitoring the V$views and V$SQL_PLAN_MONITOR , V$SQL_MONITOR are populated . - First we execute the following statements in another session - · This script allows Tanel Poder work load of the machine CPU CREATE TABLE KILL_CPU (N PRIMARY KEY) INDEX AS ORGANIZATION SELECT FROM ALL_OBJECTS WHERE ROWNUM ROWNUM < = 50; ALTER SESSION SET " _old_connect_by_enabled " = true; SELECT COUNT (*) X FROM KILL_CPU CONNECT BY N> N PRIOR START WITH N = 1;
- Going back to the first session we get the monitoring report SQLs - We will see the implementation plan of our judgment but also with actual execution statistics - · The "Activity (% ) " column gives us very valuable , because it tells us what operation is more active - · In the section " Global Stats" we see all the time is going on and there are very few CPU waits SET LONG 10000000 SET LONGCHUNKSIZE 10000000 SET LINES 512 512 PAGES SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR FROM DUAL ;
This report can be generated in HTML and open it with a browser as follows - Once generated the report , you must delete the file header generated SET ON TRIMSPOOL TRIM SET ON SET PAGES 0 SET LINESIZE 1000 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SPOOL /tmp/test_sql_monitoring.sql SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR ( TYPE = > ' ACTIVE ') FROM DUAL ; SPOOL OFF - When we have finished reviewing the example you can delete the table DROP TABLE KILL_CPU ;
7. Having seen several methods to generate an execution plan we get fully into the interpretation. The next image is taken from the course "Oracle Database 11g : SQL Tuning Workshop" in which all possible operations that can go in the plan itself delves not only in the implementation plans , but also practically .
The query shown in the picture uses the RULE hint that forces the optimizer to use the most current old system instead of the recommended rules and based on costs. The advantage of the rules-based system is that the generation of execution plans is much more deterministic. This means that it is much more likely that the cost-based system. This can be advantageous on occasion, but you can tell that the CBO (cost-based optimizer) is the default and recommended. If you look at the plan, the execution order is not the same as shown in the table (sorted by ID column), but the order seen in the tree on the right is performed. The first operation performed is the operation "road" (Id = 3). If the parent have more children, then the next operation performed is the next child hanging from the father. In this case we're going to operation 5. If the father of this child has no father operation is executed. Applying this traversal algorithm, we need the order of operations is ejcución 3-5-4-2-6-1.
- Consider an implementation plan to explain the information contained - Activate the statistics to "ALL" ALTER SESSION SET STATISTICS_LEVEL = ALL; - We launched a consultation of example but not force them to use Result Cache SELECT / * + RULE NO_RESULT_CACHE * / ENAME, JOB, SAL, DNAME FROM SCOTT.EMP, scott.dept WHERE DEPT.DEPTNO = EMP.DEPTNO AND NOT EXISTS (SELECT * FROM WHERE SCOTT.PROJ EMP.EMPNO = PROJ.EMPNO); - We get the execution plan that should go something like this - - ------------------------------------------------ ------------------------------ - | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | - ------------------------------------------------ ------------------------------ - | 0 | SELECT STATEMENT | | 1 | 3 | 00: 00: 00.01 | 35 | - | * 1 | FILTER | | 1 | 3 | 00: 00: 00.01 | 35 | - | 2 | MERGE JOIN | | 1 | 3 | 00: 00: 00.01 | 14 | - | 3 | SORT JOIN | | 1 | 7 | 00: 00: 00.01 | 7 | - | 4 | TABLE ACCESS FULL | DEPT | 1 | 7 | 00: 00: 00.01 | 7 | - | * 5 | SORT JOIN | | 7 | 3 | 00: 00: 00.01 | 7 | - | 6 | TABLE ACCESS FULL | EMP | 1 | 7 | 00: 00: 00.01 | 7 | - | * 7 | TABLE ACCESS FULL | PROJ | 3 | 0 | 00: 00: 00.01 | 21 | - ------------------------------------------------ ------------------------------ - SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (null, null, 'TYPICAL IOSTATS LAST'));
We explained that each column means : · A- Rows are the rows produced by each source row ( operation that produces rows) · Buffers corresponds with consistent readings at each operation enhanced · Starts specifies how many times the operation has been processed
8. Implementation plans can also display information about partitions. The functionality "Partition Pruning " discards those partitions to which access is not necessary to minimize the amount of read operations on the database. - We launched a consultation on a table partitioned by date ( one partition per quarter) - Configure AUTOTRACE to show only the execution plan SET EXPLAIN AUTOTRACE TRACEONLY - In the implementation plan we can see the " Pstart " columns and " PSTOP " - These columns show the partitions to be accessed - · It corresponds to the view column PARTITION_POSITION DBA_TAB_PARTITIONS - More information http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_warehouse.htm#i1007993 SELECT SUM ( AMOUNT_SOLD ) FROM SH.SALES WHERE TIME_ID BETWEEN TO_DATE ( '01 -JAN - 2001 ' , ' DD- MON- YYYY ' ) and TO_DATE ( '01 -APR - 2001 ' , 'DD- MON- YYYY ');
9. In the initial documentation are available possible operations that can appear in the execution plan with an explanation of each. Then I attached a document to understand Oracle comprehensive implementation plans including Parallel Execution -> http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf 10. 11gR2 versions can see the following phrase in the implementation plans "cardinality feedback used for esta statement" . It is a feature that adjusts the cardinality of operations based on statistics obtained by executing judgments on the DB . Attached a page which explains a very descriptive example -> https://blogs.oracle.com/optimizer/entry/cardinality_feedback .
- Let's take the example of the page that I have attached so you can see how it works " Cardinality Feedback" - First clean the Shared Pool ALTER SYSTEM FLUSH SHARED_POOL ; - Launched the sample query SELECT PRODUCT_NAME FROM OE.ORDER_ITEMS O , P OE.PRODUCT_INFORMATION WHERE AND QUANTITY O.UNIT_PRICE = 15 > 1 AND P.PRODUCT_ID = O.PRODUCT_ID ; - We get the execution plan - Most operations are destacadaos TABLES FULL ACCESS and INDEX UNIQUE SCAN SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ());
- We return to launch the consultation to demonstrate this functionality SELECT PRODUCT_NAME FROM OE.ORDER_ITEMS O, P OE.PRODUCT_INFORMATION WHERE AND QUANTITY O.UNIT_PRICE = 15> 1 AND P.PRODUCT_ID = O.PRODUCT_ID; - If we take now the execution plan used check that is different - · The operations are the two prominent TABLE FULL ACCESS to ORDER_ITEMS tables and PRODUCT_INFORMATION - · Optimized after executing the query has found that the statistics are not correct - · In the second run he observed estimates used to generate a better plan - · We can also check the folowing message "cardinality feedback used for esta statement" SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ()); - You can get the cursors used Cardinality Feedback through the view V$SQL_SHARED_CURSOR - The following query shows that there are two arrows to the consultation we have launched (SQL_ID = 'g8mza363us2mg') - But one uses Cardinality Feedback (USE_FEEDBACK_STATS = 'Y') SELECT SQL_ID, CHILD_NUMBER, USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR WHERE SQL_ID = 'g8mza363us2mg';