Interpret Execution Plan

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';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.