An execution plan is the sequence of operations Oracle performs to run the statement. The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements.

What's an Explain plan?

  • The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
  • An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.
  • It shows the following information in a statement:
      1. Ordering of the tables
      2. Access method
      3. Join method for tables
      4. Data operations ( filter, sort, or aggregation)
      5. Optimization ( cost and cardinality of each operation)
      6. Partitioning (set of accessed partitions)
      7. Parallel execution (distribution method of join inputs)
    • Main advantage is that it does not actually run the query - just parses the sql. This means that it executes quickly. In the early stages of tuning explain plan gives you an idea of the potential performance of your query without actually running it. You can then make a judgement as to any modifications you may choose to make.
    • When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data
    • Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables
    • Explain Plan is not as useful when used in conjunction with tkprof since the trace file contains the actual execution path of the SQL statement. Use Explain Plan when anticipated execution statistics are desired without actually executing the statement.

    Plan Table

    • The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users.
    • A table that Oracle fills when you issue “Explain plan” command for an SQL statement.
    • UTLXPLAN.SQL script if plan table not already exists and creates table Named PLAN_TABLE
    • Most important fields within the plan table are (operation,   option,   object_name,   id,   parent_id.)
    • This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:
    SQL> CONN sys/password AS SYSDBA 
    SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
    SQL> GRANT ALL ON sys.plan_table TO public;
    SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

    Running Explain Plan

    EXPLAIN PLAN FOR 
    SELECT last_name FROM employees;

    --Using EXPLAIN PLAN with the STATEMENT ID Clause
    EXPLAIN PLAN SET STATEMENT_ID = 'st1'
    FOR SELECT last_name FROM employees;

    --Using EXPLAIN PLAN with the INTO Clause
    EXPLAIN PLAN INTO my_plan_table
    FOR SELECT last_name FROM employees;

    Displaying Plan_Table Output

    The execution plan can be display by using following methods

    • Using simple query
    • Using DBMS_XPLAN (As of 9i)
    • Utlxpls.sql or utlxplp.sql scripts (for serial or parllel queries)
    • Using V$SQL_PLAN Views
    • Using Toad

    By Using simple query

    The query will display execuation plan for the the last "EXPLAIN PLAN" command.

    SELECT substr (lpad(' ', level-1)||operation ||' ('||options || ')',1,30 ) 
    "Operation", object_name "Object"
    FROM plan_table
    start WITH id = 0
    connect BY prior id=parent_id;
    Operation                      Object
    ------------------------------ ------------------------------
    SELECT STATEMENT ()
    TABLE ACCESS (FULL) DUAL

    By Using DBMS_XPLAN

    DBMS_XPLAN.DISPLAY is a table valued function that displays the contents of a PLAN_TABLE. Introduced in Oracle 9i and expanded in 10g, makes it much easier to format and display execution plans.

    • DBMS_XPLAN.DISPLAY     : A table valued function
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

     

    • DBMS_XPLAN.DISPLAY_CURSOR
      Can also display "live" execution plans from cursors stored within the System Global Area (SGA).
    • DBMS_XPLAN.DISPLAY_AWR

    Function can be used to look up an historical SQL statement captured in Oracle 10g's Automatic Workload Repository (AWR), and display its execution plan. This gives you a seven-day rolling window of history that you can access.

    Utlxpls.sql or utlxplp.sql scripts (for serial or parllel queries)

    These are utility scripts and used to display the explain plan of the last explain plan command. and can be used for serial and parllel quieries. Assume that the PLAN_TABLE table has been created.

    @ORACLE_HOME\RDBMS\ADMIN\Utlxpls.sql          :FOR serial quieries
    @ORACLE_HOME\RDBMS\ADMIN\utlxplp.sql :FOR parallel quieries

    Note: Executing individual scripts or using DBMS_XPLAN is same.

    Using V$SQL_PLAN Views

    After the statement has executed V$SQL_PLAN views can be used to display the execution plan of a SQL statement. Its definition is similar to the PLAN_TABLE. it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.

    The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time

    The V$SQL_PLAN_STATISTICS_ALL view enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor

    Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.

    Using Toad

    Toad is the ability to click on any SQL statement in the shared pool and instantly view its execution plan by using a following path.

    TOAD’s execution plan viewer is that you can collapse and expand the individual operations that make up the execution plan.

    The vertical and horizontal lines connecting different steps help you keep track of the nesting and which child operations go with which parent operations in the hierarchy

    TOOLS > SGA Trace / Optimization

    Interpreting Explain Plan

    Query Plan
    -----------------------------------------
    SELECT STATEMENT [CHOOSE] Cost=1234
    TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

    Step-1:   TABLE ACCESS FULL LARGE (Means we are doing a full table scan of table LARGE)
    Step-2:   SELECT STATEMENT [CHOOSE] Cost=1234

    [CHOOSE] = indicates about optimizer_goal is used because there is a cost in the cost field

    [COST] = Used internally to determine the best cost for particular plans.

    [:Q65001] = indicates that this particular part of the query is being executed in parallel.

    [ANALYZED] = object has been analyzed with currently available statistics for the CBO to use.

    Remote Queries

    Only shows remote in the OPERATION column OTHER column shows query executed on remote node OTHER_NODE shows where it is executed Different operational characteristics for RBO & CBO.

    RBO - Drags everything across the link and joins locally
    CBO - Uses cost estimates to determine whether to execute remotely or locally

    SQL> EXPLAIN plan FOR
    SELECT * FROM dept@loop_link;

    Bind Variables

    Bind variables are recommended in most cases because they promote sharing of sql code
    At parse time the parser has NO IDEA what the bind variable contains. With RBO this makes no difference but with CBO, which relies on accurate statistics to produce plans, this can be a problem.

    Defining bind variables in sqlplus:

    variable x varchar2(18);
    assigning VALUES:

    begin
    :x := 'hello';
    end; /
    SQL> EXPLAIN plan FOR SELECT * FROM dept WHERE rowid = ':x';

    Query Plan
    ------------------------------------
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID DEPT [ANALYZED]

    Explain Plan command examples

    SELECT customer_id, customer_number, customer_name
    FROM customers
    WHERE UPPER (customer_name) LIKE 'ACME%'
    ORDER BY customer_name;
    ID PARENT OPERATION OBJECT_NAME
    ---- ------ ---------------------------------- -------
    0 SELECT STATEMENT
    1 0 SORT ORDER BY
    2 1 TABLE ACCESS FULL CUSTOMERS
    0 Root operation is a SELECT statement.
    1 output of the statement will be the results of a sort operation (for the purposes of satisfying the ORDER BY clause).
    2 The input to the sort will be the results of a full table scan of the customers table.

    Stated more clearly, the database server will execute this query by checking every row in the customers table for a criteria match and sorting the results.

    Perhaps the developer expected Oracle to use an index on the customer_name column to avoid a full table scan, but the use of the UPPER function defeated the index. (A function-based index could be deployed to make this query more efficient.)

    SELECT a.customer_name, b.invoice_number, b.invoice_date
    FROM customers a, invoices b
    WHERE b.invoice_date > TRUNC (SYSDATE - 1)
    AND a.customer_id = b.customer_id;
    ID PARENT OPERATION OBJECT_NAME
    ---- ------ ---------------------------------- -------------
    0 SELECT STATEMENT
    1 0 NESTED LOOPS
    2 1 TABLE ACCESS BY INDEX ROWID INVOICES
    3 2 INDEX RANGE SCAN INVOICES_DATE
    4 1 TABLE ACCESS BY INDEX ROWID CUSTOMERS
    5 4 INDEX UNIQUE SCAN CUSTOMERS_PK

     

    Example-2

    SELECT a.customer_name, COUNT (DISTINCT b.invoice_id) "Open Invoices",
    COUNT (c.invoice_id) "Open Invoice Items"
    FROM customers a, invoices b, invoice_items c
    WHERE b.invoice_status = 'OPEN'
    AND a.customer_id = b.customer_id
    AND c.invoice_id (+) = b.invoice_id
    GROUP BY a.customer_name;
    ID PARENT OPERATION OBJECT_NAME
    --------- ----------------------------------- ------------------------------
    0 SELECT STATEMENT
    1 0 SORT GROUP BY
    2 1 NESTED LOOPS OUTER
    3 2 HASH JOIN
    4 3 TABLE ACCESS BY INDEX ROWID INVOICES
    5 4 INDEX RANGE SCAN INVOICES_STATUS
    6 3 TABLE ACCESS FULL CUSTOMERS
    7 2 INDEX RANGE SCAN INVOICE_ITEMS_PK

    First Oracle will perform a range scan on the invoices_status index to get the ROWIDs of all rows in the invoices table with the desired status. For each ROWID found, the record from the invoices table will be fetched.

    This set of invoice records will be set aside for a moment while the focus turns to the customers table. Here, Oracle will fetch all customers records with a full table scan. To perform a hash join between the invoices and customers tables, Oracle will build a hash from the customer records and use the invoice records to probe the customer hash.

    Next, a nested loops join will be performed between the results of the hash join and the invoice_items_pk index. For each row resulting from the hash join, Oracle will perform a unique scan of the invoice_items_pk index to find index entries for matching invoice items. Note that Oracle gets everything it needs from the index and doesn’t even need to access the invoice_items table at all. Also note that the nested loops operation is an outer join. A sort operation for the purposes of grouping is performed on the results of the nested loops operation in order to complete the SELECT statement.

    It is interesting to note that Oracle chose to use a hash join and a full table scan on the customers table instead of the more traditional nested loops join. In this database there are many invoices and a relatively small number of customers, making a full table scan of the customers table less expensive than repeated index lookups on the customers_pk index. But suppose the customers table was enormous and the relative number of invoices was quite small. In that scenario a nested loops join might be better than a hash join. Examining the execution plan allows you to see which join method Oracle is using. You could then apply optimizer hints to coerce Oracle to use alternate methods and compare the performance. You may wonder how I got that whole detailed explanation out of the eight line

    execution plan listing shown above. Did I read anything into the execution plan? No! It’s all there! Understanding the standard inputs and outputs of each type of operation and coupling this with the indenting is key to reading an execution plan.

    A nested loops join operation always takes two inputs: For every row coming from the first input, the second input is executed once to find matching rows. A hash join operation also takes two inputs: The second input is read completely once and used to build a hash. For each row coming from the first input, one probe is performed against this hash. Sorting operations, meanwhile, take in one input. When the entire input has been read, the rows are sorted and output in the desired order.

    SELECT customer_name
    FROM customers a
    WHERE EXISTS
    (
    SELECT 1
    FROM invoices_view b
    WHERE b.customer_id = a.customer_id
    AND number_of_lines > 100
    )
    ORDER BY customer_name;
    ID PARENT OPERATION OBJECT_NAME
    -- ------ ----------------------------------- ------------------------------
    0 SELECT STATEMENT
    1 0 SORT ORDER BY
    2 1 FILTER
    3 2 TABLE ACCESS FULL CUSTOMERS
    4 2 VIEW INVOICES_VIEW
    5 4 FILTER
    6 5 SORT GROUP BY
    7 6 NESTED LOOPS
    8 7 TABLE ACCESS BY INDEX ROWID INVOICES
    9 8 INDEX RANGE SCAN INVOICES_CUSTOMER_ID
    10 7 INDEX RANGE SCAN INVOICE_ITEMS_PK

    This execution plan is somewhat complex because the query includes a subquery that the optimizer could not rewrite as a simple join, and a view whose definition could not be merged into the query. The definition of the invoices_view view is as follows:

    CREATE OR REPLACE VIEW invoices_view
    AS
    SELECT a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
    a.invoice_number, a.invoice_type, a.total_amount,
    COUNT(*) number_of_lines
    FROM invoices a, invoice_items b
    WHERE b.invoice_id = a.invoice_id
    GROUP BY a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
    a.invoice_number, a.invoice_type, a.total_amount;

    Here is what this execution plan says: Oracle will execute this query by reading all rows from the customers table with a full table scan. For each customer record, the invoices_view view will be assembled as a filter and the relevant contents of the view will be examined to determine whether the customer should be part of the result set or not.

    Oracle will assemble the view by performing an index range scan on the invoices_customer_id index and fetching the rows from the invoices table containing one specific customer_id. For each invoice record found, the invoice_items_pk index will be range scanned to get a nested loops join of invoices to their invoice_items records.

    The results of the join are sorted for grouping, and then groups with 100 or fewer invoice_items records are filtered out. What is left at the step with ID 4 is a list of invoices for one specific customer that have more than 100 invoice_items records associated. If at least one such invoice exists, then the customer passes the filter at the step with ID 2.

    Finally, all customer records passing this filter are sorted for correct ordering and the results are complete. Note that queries involving simple views will not result in a “view” operation in the execution plan. This is because Oracle can often merge a view definition into the query referencing the view so that the table accesses required to implement the view just become part of the regular execution plan. In this example, the GROUP BY clause embedded in the view foiled Oracle’s ability to merge the view into the query, making a separate “view” operation necessary in order to execute the query.

    Also note that the filter operation can take on a few different forms. In general, a filter operation is where Oracle looks at a set of candidate rows and eliminates some based on certain criteria. This criteria could involve a simple test such as number_of_lines > 100 or it could be an elaborate subquery. In this example, the filter at step ID 5 takes only one input. Here Oracle evaluates each row from the input one at a time and either adds the row to the output or discards it as appropriate. Meanwhile, the filter at step ID 2 takes two inputs.

    When a filter takes two inputs, Oracle reads the rows from the first input one at a time and executes the second input once for each row. Based on the results of the second input, the row from the first input is either added to the output or discarded.

    Oracle is able to perform simple filtering operations while performing a full table scan. Therefore, a separate filter operation will not appear in the execution plan when Oracle performs a full table scan and throws out rows that don’t satisfy a WHERE clause. Filter operations with one input commonly appear in queries with view operations or HAVING clauses, while filter operations with multiple inputs will appear in queries with EXISTS clauses.

    An important note about execution plans and subqueries: When a SQL statement involves subqueries, Oracle tries to merge the subquery into the main statement by using a join. If this is not feasible and the subquery does not have any dependencies or references to the main query, then Oracle will treat the subquery as a completely separate statement from the standpoint of developing an execution plan—almost as if two separate SQL statements were sent to the database server. When you generate an execution plan for a statement that includes a
    fully autonomous subquery, the execution plan may not include the operations for the subquery. In this situation, you need to generate an execution plan for the subquery separately.

    Go to top
    JSN Boot template designed by JoomlaShine.com