Sql explain plan

Sql explain plan DEFAULT

What's an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

Query processing can be divided into 7 phases:

[1] SyntacticChecks the syntax of the query
[2] SemanticChecks that all objects exist and are accessible
[3] View MergingRewrites query as join on base tables as opposed to using views
[4] Statement
     Transformation
Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] OptimizationDetermines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP GenerationQEP = Query Evaluation Plan
[7] QEP ExecutionQEP = Query Evaluation Plan

Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed.

Terminology

Row SourceA set of rows used in a query may be a select from a base object or the result set returned by joining 2 earlier row sources
Predicatewhere clause of a query
Tuplesrows
Driving TableThis is the row source that we use to seed the query. If this returns a lot of rows then this can have a negative affect on all subsequent operations
Probed TableThis is the object we lookup data in after we have retrieved relevant key data from the driving table.

How does Oracle access data?

At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:

  • Full Table Scan (FTS)

  • Index Lookup (unique & non-unique)

  • Rowid

Explain plan Hierarchy

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

The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the
next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query.

[CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the
cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:

SELECT STATEMENT     [CHOOSE] Cost=1234

However the explain plan below indicates the use of the RBO because the cost field is blank:

SELECT STATEMENT     [CHOOSE] Cost=

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

[:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially.

[ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the 'level' of analysis done.

Access Methods in detail

In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. Multiblock i/o is controlled by the parameter <PARAM:db_block_multi_block_read_count>.

This defaults to:

db_block_buffers / ( (PROCESSES+3) / 4 )

Maximum values are OS dependant

Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel.

Example FTS explain plan:

SQL> explain plan for select * from dual;

Query Plan
-----------------------------------------
SELECT STATEMENT     [CHOOSE] Cost=
  TABLE ACCESS FULL DUAL

Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.

In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index):

SQL> explain plan for
select empno,ename from emp where empno=10;
Query Plan

------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1

Notice the 'TABLE ACCESS BY ROWID' section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index is being accessed by an 'INDEX UNIQUE SCAN' operation. This is explained below. The index name in this case is EMP_I1. If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.

In the following example all the columns (empno) are in the index. Notice that no table access takes place:

SQL> explain plan for
select empno from emp where empno=10;

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
  INDEX UNIQUE SCAN EMP_I1

Indexes are presorted so sorting may be unecessary if the sort order required is the same as the index.

SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;

Query Plan
-------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]

In this case the index is sorted so ther rows will be returned in the order of the index hence a sort is unecessary.

SQL> explain plan for
select /*+ Full(emp) */ empno,ename from emp
where empno> 7876 order by empno;

Query Plan
-------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=9
  SORT ORDER BY
    TABLE ACCESS FULL EMP [ANALYZED]  Cost=1 Card=2 Bytes=66

Because we have forced a FTS the data is unsorted and so we must sort the data
after it has been retrieved.

There are 4 methods of index lookup:

  • index unique scan

  • index range scan

  • index full scan

  • index fast full scan

Method for looking up a single key value via a unique index. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index, However this may return > 1 row as the uniqueness will not be guaranteed.

SQL> explain plan for
select empno,ename from emp where empno=10;

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1

Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between)

SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;

Query Plan
-------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
  INDEX RANGE SCAN EMP_I1 [ANALYZED]

A non-unique index may return multiple values for the predicate col1 = 5 and will use an index range scan

SQL> explain plan for select mgr from emp where mgr = 5

Query plan
--------------------
SELECT STATEMENT [CHOOSE] Cost=1
  INDEX RANGE SCAN EMP_I2 [ANALYZED]

In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are  only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

An Index full scan will perform single block i/o's and so it may prove to be inefficient. Index BE_IX is a concatenated index on big_emp (empno,ename)

SQL> explain plan for select empno,ename
     from big_emp order by empno,ename;

Query Plan
------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=26
  INDEX FULL SCAN BE_IX [ANALYZED]

Scans all the block in the index Rows are not returned in sorted order Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO may be hinted using INDEX_FFS hint uses multiblock i/o can be executed in parallel can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate. Index BE_IX is a concatenated index on big_emp (empno,ename)

SQL> explain plan for select empno,ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
  INDEX FAST FULL SCAN BE_IX [ANALYZED]

Selecting the 2nd column of concatenated index:

SQL> explain plan for select ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=1
  INDEX FAST FULL SCAN BE_IX [ANALYZED]

This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid

SQL> explain plan for select * from dept where rowid = ':x';

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

Table is accessed by rowid following index lookup:

SQL> explain plan for
select empno,ename from emp where empno=10;

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
    INDEX UNIQUE SCAN EMP_I1

A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order - order in which joins are performed

The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken.

Suppose there is a concatenated index on A(a.col1,a.col2). Note that a.col1 is the leading column. Consider the following query:

select A.col4
from   A,B,C
where  B.col3 = 10
and    A.col1 = B.col1
and    A.col2 = C.col2
and    C.col3 = 5

We could represent the joins present in the query using the following schematic:

  B     <---> A <--->    C
col3=10                col3=5

There are really only 2 ways we can drive the query: via B.col3 or C.col3. We would have to do a Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables;

If we drive off table B, using predicate B.col3=10 (as a filter or lookup key) then we will retrieve the value for B.col1 and join to A.col1. Because we have now filled the leading column of the concatenated index on table A we can use this index to give us values for A.col2 and join to A.

However if we drive of table c, then we only get a value for a.col2 and since this is a trailing column of a concatenated index and the leading column has not been supplied at this point, we cannot use the index on a to lookup the data.

So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish whether the individual access paths are a good idea or not.

If the CBO does not choose this join order then we can hint it by changing the from
clause to read:

from B,A,C

and using the /*+ ordered */ hint. The resultant query would be:

select /*+ ordered */ A.col4
from   B,A,C
where  B.col3 = 10
and    A.col1 = B.col1
and    A.col2 = C.col2
and    C.col3 = 5

Join Types

  • Sort Merge Join (SMJ)

  • Nested Loops (NL)

  • Hash Join

Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently Sorted rows from both sides are then merged together (joined)

                   MERGE
                 /      \
            SORT        SORT
             |             |
        Row Source 1  Row Source 2

If the row sources are already (known to be) sorted then the sort operation is unecessary as long as both 'sides' are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel.

SQL> explain plan for
select /*+ ordered */ e.deptno,d.deptno
from emp e,dept d
where e.deptno = d.deptno
order by e.deptno,d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
  MERGE JOIN
    SORT JOIN
      TABLE ACCESS FULL EMP [ANALYZED]
    SORT JOIN
      TABLE ACCESS FULL DEPT [ANALYZED]

Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

First we return all the rows from row source 1 Then we probe row source 2 once for each row returned from row source 1

Row source 1
~~~~~~~~~~~~
Row 1 --------------       -- Probe ->       Row source 2
Row 2 --------------       -- Probe ->       Row source 2
Row 3 --------------       -- Probe ->       Row source 2

Row source 1 is known as the outer table
Row source 2 is known as the inner table

Accessing row source 2 is known a probing the inner table For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;

Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
  NESTED LOOPS
    TABLE ACCESS FULL DEPT [ANALYZED]
    TABLE ACCESS FULL EMP [ANALYZED]

New join type introduced in 7.3 More efficient in theory than NL & SMJ Only accessible via the CBO Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp,dept
where emp.deptno = dept.deptno;

Query Plan
----------------------------
SELECT STATEMENT  [CHOOSE] Cost=3
  HASH JOIN
    TABLE ACCESS FULL DEPT
    TABLE ACCESS FULL EMP

Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3

A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances - Star joins uses cartesian products.

Notice that there is no join between the 2 tables:

SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept

Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
  MERGE JOIN CARTESIAN
    TABLE ACCESS FULL DEPT
    SORT JOIN
      TABLE ACCESS FULL EMP

The CARTESIAN keyword indicate that we are doing a cartesian product.

Operations

Operations that show up in explain plans

There are a number of different operations that promote sorts

  • order by clauses

  • group by

  • sort merge join

Note that if the row source is already appropriately sorted then no sorting is required. This is now indicated in 7.3:

SORT GROUP BY NOSORT
     INDEX FULL SCAN .....

In this case the group by operation simply groups the rows it does not do the sort operation as this has already been completed.

Sorts are expensive operations especially on large tables where the rows do not fit in memory and spill to disk. By default sort blocks are placed into the buffer cache. This may result in aging out of other blocks that may be reread by other processes. To avoid this you can use the parameter <Parameter:SORT_DIRECT_WRITES> which does not place sort blocks into the buffer cache.

Has a number of different meanings used to indicate partition elimination may also indicate an actual filter step where one row source is filtering another functions such as min may introduce filter steps into query plans

In this example there are 2 filter steps. The first is effectively like a NL except that it stops when it gets something that it doesn't like (i.e. a bounded NL). This is there because of the not in. The second is filtering out the min value:

SQL> explain plan for select * from emp
     where empno not in (select min(empno)
     from big_emp group by empno);

Query Plan
------------------
SELECT STATEMENT [CHOOSE]  Cost=1
  FILTER     **** This is like a bounded nested loops
    TABLE ACCESS FULL EMP [ANALYZED]
     FILTER   **** This filter is introduced by the min
        SORT GROUP BY NOSORT
          INDEX FULL SCAN BE_IX

This example is also interesting in that it has a NOSORT function. The group by does not need to sort because the index row source is already pre sorted.

When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.

In the following example the select contains an inline view which cannot be merged:

SQL> explain plan for
select ename,tot
from emp,
    (select empno,sum(empno) tot from big_emp group by empno) tmp
where emp.empno = tmp.empno;

Query Plan
------------------------
SELECT STATEMENT [CHOOSE]
  HASH JOIN
    TABLE ACCESS FULL EMP [ANALYZED]
    VIEW
      SORT GROUP BY
        INDEX FULL SCAN BE_IX

In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step.

Allows a large table to be broken up into a number of smaller partitions which can be queried much more quickly than the table as a whole a union all view is built over the top to provide the original functionality Check constraints or where clauses provide partition elimination capabilities

SQL> explain plan for
select /*+ use_nl(p1,kbwyv1) ordered */  sum(prc_pd)
from parent1 p1,  kbwyv1
where p1.class = 22
and   kbwyv1.bitm_numb = p1.bitm_numb
and   kbwyv1.year = 1997
and   kbwyv1.week between 32 and 33 ;

Query Plan
-----------------------------------------
SELECT STATEMENT   [FIRST_ROWS] Cost=1780
  SORT AGGREGATE
    NESTED LOOPS   [:Q65001] Ct=1780 Cd=40 Bt=3120
      TABLE ACCESS FULL PARENT1 [:Q65000] [AN] Ct=20 Cd=40 Bt=1040
      VIEW  KBWYV1 [:Q65001]
        UNION-ALL PARTITION  [:Q65001]
          FILTER   [:Q64000]
            TABLE ACCESS FULL KBWYT1 [AN] Ct=11 Cd=2000 Bt=104000
          TABLE ACCESS FULL KBWYT2 [AN] Ct=11 Cd=2000 Bt=104000
          TABLE ACCESS FULL KBWYT3 [AN] Ct=11 Cd=2000 Bt=104000
          FILTER   [:Q61000]
            TABLE ACCESS FULL KBWYT4 [AN] Ct=11 Cd=2000 Bt=104000

KBWYV1 is a view on 4 tables KBWYT1-4. KBWYT1-4 contain rows for week 31-34 respectively and are maintained by check constraints. This query should only return rows from partions 2 & 3. The filter operation indicates this. Partitions 1 & 4 are eliminated at execution time. The view line indicates that the view is not merged. The union-all partion information indicates that we have recognised this as a partition view. Note that the tables can be accessed in parallel.

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 [email protected]_link;

Query Plan
-------------------------------------------------------
SELECT STATEMENT REMOTE  [CHOOSE] Cost=1
  TABLE ACCESS FULL DEPT [SJD.WORLD] [ANALYZED]

In this case the whole query has been sent to the remote site. The other column shows nothing.

SQL> explain plan for
select a.dname,avg(b.sal),max(b.sal)
from [email protected]_link a, emp b
where a.deptno=b.deptno
group by a.dname
order by max(b.sal),avg(b.sal) desc;

Query Plan
-----------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=20
  SORT ORDER BY  [:Q137003] [PARALLEL_TO_SERIAL]
    SORT GROUP BY  [:Q137002] [PARALLEL_TO_PARALLEL]
      NESTED LOOPS   [:Q137001] [PARALLEL_TO_PARALLEL]
        REMOTE   [:Q137000] [PARALLEL_FROM_SERIAL]
        TABLE ACCESS FULL EMP [:Q137001] [ANALYZED]
        [PARALLEL_COMBINED_WITH_PARENT]

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]

Parallel Query

Main indicators that a query is using PQO:

  • [:Q1000004] entries in the explain plan

  • Checkout the other column for details of what the slaves are executing

  • v$pq_slave will show any parallel activity

Columns to look in for information

  • other - contains the query passed to the slaves

  • other_tag - describes the contents of other

  • object_node - indicates order of pqo slaves

Parallel Query operates on a producer/consumer basis. When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers. If there are only 2 slaves available then we use these. If there is only 1 slave available then we go serial If there are none available then we use serial. If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial

Consumer processes typically perform a sorting function. If there is no requirement for the data to be sorted then the consumer slaves are not produced and we end up with the number of slaves used matching the degree of parallelism as opposed to being 2x the degree.

Parallel Terms

PARALLEL_FROM_SERIALThis means that source of the data is serial but it is passed to a parallel consumer
PARALLEL_TO_PARALLELBoth the consumer and the producer are  parallel
PARALLEL_COMBINED_WITH_PARENTThis operation has been combined with the parent operator. For example in a sort merge join the sort operations would be shown as PARALLEL_COMBINED_WITH_PARENT because the sort and the merge are handled as 1 operation.
PARALELL_TO_SERIALThe source of the data is parallel but it is passed to a serial consumer. This typically will happen at the top of the explain plan but could occur anywhere

Assumptions

OPTIMIZER_MODE = CHOOSE
DEPT is small compared to EMP
DEPT has an index (DEPT_INDX) on deptno column

Three examples are presented

Query #1:  Serial
Query #2:  Parallel
Query #3:  Parallel, with forced optimization to RULE and forced usage of DEPT_INDX

Sample Query #1 (Serial)

select A.dname, avg(B.sal), max(B.sal)
from  dept A, emp B
where A.deptno = B.deptno
group by A.dname
order by max(B.sal), avg(B.sal) desc;

Execution Plan #1 (Serial)

OBJECT_NAME                      OBJECT_NODE OTHER
-------------------------------  ----------- -------
SELECT STATEMENT
 SORT ORDER BY
   SORT GROUP BY
     MERGE JOIN
       SORT JOIN
         TABLE ACCESS FULL emp
       SORT JOIN
         TABLE ACCESS FULL dept

Notice that the object_node and other columns are empty

Sample Query #2 (Query #1 with parallel hints)

select /*+ parallel(B,4) parallel(A,4) */
A.dname, avg(B.sal), max(B.sal)
from  dept A, emp B
where A.deptno = B.deptno
group by A.dname
order by max(B.sal), avg(B.sal) desc;

Execution Plan #2  (Parallel)

OBJECT_NAME                      OBJECT_NODE OTHER
-------------------------------  ----------- -------
SELECT STATEMENT      Cost = ??
 SORT ORDER BY                   :Q55004     **[7]**
   SORT GROUP BY                 :Q55003     **[6]**
     MERGE JOIN                  :Q55002     **[5]**
       SORT JOIN                 :Q55002     **[4]**
         TABLE ACCESS FULL emp   :Q55001     **[2]**
       SORT JOIN                 :Q55002     **[3]**
         TABLE ACCESS FULL dept  :Q55000     **[1]**

Execution Plan #2  -- OTHER column

**[1]**  (:Q55000) "PARALLEL_FROM_SERIAL"

Serial execution of SELECT DEPTNO, DNAME FROM DEPT

**[2]**  (:Q55001) "PARALLEL_TO_PARALLEL"

        SELECT /*+ ROWID(A1)*/
        A1."DEPTNO" C0, A1."SAL" C1
        FROM "EMP" A1
        WHERE ROWID BETWEEN :1 AND :2

**[3]**  (:Q55002) "PARALLEL_COMBINED_WITH_PARENT"
**[4]**  (:Q55002) "PARALLEL_COMBINED_WITH_PARENT"
**[5]**  (:Q55002) "PARALLEL_TO_PARALLEL"

        SELECT /*+ ORDERED USE_MERGE(A2)*/
        A2.C1 C0, A1.C1 C1
        FROM :Q55001 A1,:Q55000 A2
        WHERE A1.C0=A2.C0

**[6]**  (:Q55003) "PARALLEL_TO_PARALLEL"

        SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
        FROM :Q55002 A1
        GROUP BY A1.C0

**[7]**  (:Q55004) "PARALLEL_FROM_SERIAL"

        SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
        FROM :Q55003 A1
        ORDER BY A1.CO, A1.C1 DESC

Sample Query #3 (Query #2 with fudged hints)

select /*+ index(A dept_indx) parallel(B,4) parallel(A,4) */
      A.dname, avg(B.sal), max(B.sal)
from  dept A, emp B
where A.deptno = B.deptno
group by A.dname
order by max(B.sal), avg(B.sal) desc;

Execution Plan #3  (Parallel)

OBJECT_NAME                         OBJECT_NODE OTHER
----------------------------------- ----------- -------
SELECT STATEMENT          Cost = ??
 SORT ORDER BY                      :Q58002     **[6]**
   SORT GROUP BY                    :Q58001     **[5]**
     NESTED LOOPS JOIN              :Q58000     **[4]**
       TABLE ACCESS FULL emp        :Q58000     **[3]**
       TABLE ACCESS BY ROWID dept   :Q58000     **[2]**
         INDEX RANGE SCAN dept_indx :Q58000     **[1]**

Execution Plan #3  -- OTHER column

**[1]**  (:Q58000) "PARALLEL_COMBINED_WITH_PARENT"
**[2]**  (:Q58000) "PARALLEL_COMBINED_WITH_PARENT"
**[3]**  (:Q58000) "PARALLEL_COMBINED_WITH_PARENT"
**[4]**  (:Q58000) "PARALLEL_TO_PARALLEL"

        SELECT /*+ ORDERED USE_NL(A2) INDEX(A2) */
        A2."DNAME" C0, A1.C0 C1
        FROM
          (SELECT /*+ ROWID(A3) */
           A3."SAL" CO, A3."DEPTNO" C1
           FROM "EMP" A3
           WHERE ROWID BETWEEN :1 AND :2) A1,
          "DEPT" A2
        WHERE A2."DEPTNO" = A1.C1

**[5]**  (:Q58001) "PARALLEL_TO_PARALLEL"

        SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
        FROM :Q58000 A1
        GROUP BY A1.C0

**[6]**  (:Q58002) "PARALLEL_TO_SERIAL"

        SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
        FROM :Q58001 A1
        ORDER BY A1.C0, A1.C1 DESC

How to obtain explain plans

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.

Autotrace can be configured to run the sql & gives a plan  and statistics afterwards or just give you an explain plan without executing the query.

Analyzes trace file

Sours: https://www.akadia.com/services/ora_interpreting_explain_plan.html

Understanding Oracle Explain Plan

Question:  I need some tips for understanding the Oracle explain plan.  I have been told that reading the Oracle explain plan will help me identify tuning opportunities, but the explain plan and how to use it are a mystery to me.

Answer:  In the most basic terms, the Oracle explain plan is a tool that can be used to get Oracle to tell you how it plans on executing a query.  Clearly, this makes the explain plan a valuable tool for tuning.

Once the Oracle explain plan reveals how it plans on executing the query in question, the environment can be adjusted to run the query faster.

The Oracle explain plan is a statement that returns execution plans for the requested SELECT, UPDATE, INSERT and DELETE statements.  The execution plan for any given statement shows the operations in sequence used by Oracle to run that statement.

Information returned by the Oracle explain plan includes:

  • the tables referenced in the statement
  • the ordering and method with which each table is accessed
  • for join operations, the join method for the tables involved
  • data operations such as filter or sort included in the statement.

In addition to the information listed above, the Oracle explain plan table will also contain information on:

The explain plan information is written to an explain plan table.  It is important to note that the explain plan table must be created before running the explain plan statement.

Once the Oracle explain plan table has been created, suspect SQL statements can be evaulated and tuned accordingly.

It is also possible to derive an Oracle explain plan using the SQL*Plus autotrace function as shown here:

SQL> SET AUTOTRACE ON
SQL> SELECT *
  2  FROM   emp e, dept d
  3  WHERE  e.deptno = d.deptno
  4  AND    e.ename  = 'SMITH';

The disadvantage of this method is that the statement has to complete its run in order to be evaluated.  If the statement is truly bad, this could involve a considerable amount of wasted time.

The preferred method would be the Oracle explain plan route.

The explain plan statement generates the execution plan for a query without executing the query itself, allowing the execution plan for poorly performing queries to be displayed without impacting the database. 

The following example shows how the explain plan statement is used to generate an execution plan:

SQL> EXPLAIN PLAN FOR  
2  SELECT *  
3  FROM   emp e, dept d  
4  WHERE  e.deptno = d.deptno  
5  AND    e.ename  = 'SMITH';

Explained.

If multiple people are accessing the same Oracle explain plan table, or a history of the Oracle execution plans is to be saved, the statement_id clause of the Oracle explain plan statement should be used.  This associates a user specified identifier with each explain plan, which can be used when retrieving the data. 

The following example shows how the statement_id is set using the Oracle explain plan statement.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR  
2  SELECT *  
3  FROM   emp e, dept d  
4  WHERE  e.deptno = d.deptno  
5  AND    e.ename  = 'SMITH'; Explained.

The goals of tuning the SQL statements returned by the Oracle explain plan are fairly simple:

  • Eliminate sub-optimal large-table full-table scans: Ensure that the fastest access path to the data is chosen.  The execution plan "FULL" is the key.
  • Use fastest table join method: The optimizer must choose intelligently between nested loops joins, hash joins and star transformation join methods and these are displayed in the execution plan.  
  • Ensure optimal table-joining order: SQL will run fastest when the first table joins deliver the smallest result set.

With Oracle 11g, SQL Plan Management evolves more fully.

Sours: http://www.dba-oracle.com/t_understanding_oracle_explain_plan.htm
  1. Led buggy whip
  2. Where to stream tosh.o
  3. 8 fiberglass columns
  4. Premium crepe paper
  5. Synpase x

Introduction

In this article, I’m going to explain what the Execution Plans in SQL Server are and how to understand the details of an execution plan by reading the various metrics available once we hover over the components in the plan.

Being a Data Professional, it is very essential that we must understand how to write efficient queries that return faster results and how to tune the slow performing queries to achieve a boost in performance. In order to understand how the database engine works behind the scenes, we need to know the logical operations performed by the query processor. This can be done by investigating the execution plans generated by the query processor. An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to calculate the most efficient way to return a set of results.

What is an Execution Plan?

As already mentioned earlier, an execution plan in SQL Server Management Studio is a graphical representation of the various steps that are involved in fetching results from the database tables. Once a query is executed, the query processing engine quickly generates multiple execution plans and selects the one which returns the results with the best performance. There are two types of execution plans to be specific –

  1. Estimated Execution Plan – As the name suggests, this type of execution plan is just a guess by the query processor about how the specific steps that are to be involved while returning the results. It is often generated before the query has been executed
  2. Actual Execution Plan – The Actual Execution Plan is generated after the query has been executed. It shows the actual operations and steps involved while executing the query. This may or may not differ from the Estimated Execution Plan

How to generate Execution Plans?

As we have learned, the Execution Plans in SQL Server can be generated before and after the query has been executed, I’m going to mention the various steps on how you can get the estimated and actual execution plans.

Estimated Execution Plans in SQL Server Management Studio

There are several ways to get the estimated execution plan in SQL Server.

  1. Once the query is written completely, you can hit “Ctrl + L” and it will generate the estimated execution plan
  2. You can also right-click on the query window and select “Display Estimated Execution Plan” from the context menu that appears

    Display Estimated Execution Plan in SQL Server Management Studio

    Figure 1 – Display Estimated Execution Plan Context

  3. Alternatively, you can directly click the “Display Estimated Execution Plan” icon which is available on the toolbar in SQL Server Management Studio

    Display Estimated Execution Plan Icon

    Figure 2 – Display Estimated Execution Plan Icon

Actual Execution Plans in SQL Server Management Studio

Similarly, you can follow the steps below to get the actual execution plan in SQL Server.

  1. Hit “Ctrl + M” and it will generate the actual execution plan after the query has been executed successfully
  2. Right-click on the query window and select “Display Actual Execution Plan” from the context menu

    Display Actual Execution Plan in SQL Server

    Figure 3 – Display Actual Execution Plan Context

  3. Alternatively, you can directly click the “Display Actual Execution Plan” icon which is available on the toolbar in SQL Server Management Studio

    Display Actual Execution Plan Icon

    Figure 4 – Display Actual Execution Plan Icon

Interpreting Execution Plans in SQL Server Management Studio

Once you generate the execution plans as mentioned in the steps above, you’ll see something like the diagram below as in Figure 5. For estimated plans, it will generate as soon as you perform the step whereas for the actual execution plan it will be displayed only after the query has been executed.

Execution Plan in SQL Server

Figure 5 – Execution Plan in SQL Server

For such simple queries, the estimated execution plans are usually like the actual execution plans. For the purpose of this tutorial, we will try to understand one of the operators of the Actual Execution Plan only.

In the execution plan depicted in the above Figure 5, if you hover the cursor over the components, you can view the detailed stats for each of the operations and components being displayed in the execution plan. The plan is interpreted from right-to-left and top-to-bottom. Since our plan consists of only one single row, there is no need for the top-to-bottom approach.

Fetch Execution Plan Details in SQL Server

Figure 6 – Fetch Execution Plan Details

We can distinguish the execution plan into the following five steps:

  1. Clustered Index Scan
  2. Data Flow from Clustered Index Scan (Arrow)
  3. Sort Operator
  4. Data Flow from Sort Operator (Arrow)
  5. Select Operator

For the sake of this tutorial, let’s only understand in detail the various metrics that are being involved for the Clustered Index Scan operator. The details of the other operators can also be viewed similarly; however, the detailed explanation of the metrics of the other operators is beyond the scope of this article.

Clustered Index Scan

The first component when we traverse from right-to-left is the Clustered Index Scan component. This Index Scan is performed on the Primary Key of the table i.e. “PK_Dimension_Customer“. Let’s understand each of the metrics that are being displayed while we hover over the clustered index scan operator. You can refer to the Figure 7, for understanding more.

Clustered Index Scan Operator - Execution Plan in SQL Server

Figure 7 – Clustered Index Scan Operator

  • Physical Operation: These are the operators that implement the operation as directed by the logical operators. All the physical operators are usually object which perform an operation. Some examples are Clustered Index Scan, Index Seek etc.
  • Logical Operation: These operators describe the actual algebraic operation that is used to process the query. Examples are Right Anti Semi Join, Hash Join etc.
  • Actual Execution Mode: This is the actual execution mode that is used by the processing engine to execute the query. Examples – Row and Batch
  • Estimated Execution Mode: This is similar to Actual Execution Mode but shows the estimated value
  • Storage: This tells us how the query optimizer will store the results that are being extracted by the query
  • Number of Rows Read: This returns the total number of records that are being read by the operator from the table index
  • Actual Number of Rows: This tells us the total number of records that have been returned based on the condition in the WHERE clause
  • Actual Number of Batches: If the execution mode for the query is a batch, then it will list the number of batches being executed to fetch the results
  • Estimated I/O Cost: This tells us the cost of the input/output operations of the result set
  • Estimated Operator Cost: This is not an actual cost but relative information with respect to the other operators in the execution plan
  • Estimated CPU Cost: The cost that the CPU will incur in order to process the operation
  • Estimated Subtree Cost: The cost of the execution tree that is being currently read from right-to-left and top-to-bottom
  • Number of Executions: This tells us about the number of executions that the optimizer can handle in a single batch
  • Estimated Number of Executions: This is also similar to the Number of Executions just the estimated value
  • Estimated Number of Rows: The number of rows that the optimizer thinks will be returned by the operator
  • Estimated Number of Rows to be Read: The number of rows that the optimizer thinks will be read by the operator
  • Estimated Row Size: The storage size each row in the operator
  • Actual Rebinds: This tells us about how many times the reevaluation of the object must be done in order to process the operator
  • Actual Rewinds: This property tells us if there were any changes in the correlated values for the object that is being processed
  • Ordered: This property determines if the dataset on which the operation is to be performed in a sorted state or not
  • Node ID: It is the automatic assignment of a number in the order in which the operator is called in the execution plan reading from right-to-left and top-to-bottom

Additionally, if you see below, there are these three properties that tell us more about the query and the object on which the plan is generated.

  • Predicate: This is the value retrieved from the WHERE clause in the SQL statement
  • Object: The table on which the operation is being performed
  • Output: The selected columns are being displayed in the result set

You can also right-click any operator or arrow in the plan and select Properties to learn the more detailed explanation of the metrics displayed in the tooltip.

Saving an Execution Plan in SQL Server Management Studio

Sometimes, it is essential that after interpreting the plan generated by the query, you might want to save if for future references. SSMS provides an option to save the plan in the file system with an extension of “.sqlplan“.

In order to save an execution plan, please follow the steps below.

  1. Right-click on the plan window
  2. Select Save Execution Plan As…

    Save Execution Plan in SQL Server

    Figure 8 – Save the Plan As

  3. Select a location and provide a name for the execution plan and click on Save

    Saving the Execution Plan in SQL Server Management Studio

    Figure 9 – Saving the Plan in SQL Server Management Studio

You can open this plan as and when required.

Conclusion

In this article, we have learned what execution plans in SQL Server are and how to generate one. We also walked through various metrics that are being considered in the operators used in the plan. Finally, we have seen how to save an execution plan in SQL Server Management Studio to the file system and use it for future references.

Aveek Das

Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure.

He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.

In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn

View all posts by Aveek Das

Aveek Das

Latest posts by Aveek Das (see all)

Execution plans, SQL Server Management Studio (SSMS)

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure. He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums. In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedInView all posts by Aveek Das

View all posts by Aveek Das →

Sours: https://www.sqlshack.com/execution-plans-in-sql-server/
SQL: Explain Plan for knowing the Query performance

Explain Plan Usage

Home » Articles » 8i » Here

When a 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. Once you've highlighted a problem query the first thing you should do is the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing. Interpretation of the execution plan is beyond the scope of this article.

Related articles.

Plan Table

The explain plan process stores data in the . This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows.

-- Creating a shared PLAN_TABLE prior to 11g SQL> CONN sys/password AS SYSDBA Connected 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;

In Oracle 11g a shared is created by default, but you can still create a local version of the table using the "utlxplan.sql" script.

Some operations will likely need the role to be granted to the user performing the operation. This role is created using the following script.

SQL> CONN sys/password AS SYSDBA Connected SQL> $ORACLE_HOME/sqlplus/admin/plustrce.sql SQL> GRANT plustrace TO my_test_user;

AUTOTRACE - The Easy Option?

Switching on the parameter in SQL*Plus causes an explain to be performed on every query.

SQL> SET AUTOTRACE ON SQL> SELECT * 2 FROM emp e, dept d 3 WHERE e.deptno = d.deptno 4 AND e.

This is a relatively easy way to get the execution plan but there is an issue. In order to get the execution plan the statement must be run to completion. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. At first glance, using the option of seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. As such, long running queries will still take a long time to complete, but they will not present their data. The following example show this in practice.

CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A BEGIN DBMS_LOCK.sleep(p_seconds); RETURN p_seconds; END; / Function created. SQL> SET TIMING ON SQL> SET AUTOTRACE ON SQL> SELECT pause_for_secs(10) FROM DUAL; PAUSE_FOR_SECS(10) ------------------ 10 1 row selected. Elapsed: 00:00:10.Elapsed: 00:00:10.

The query takes the same time to return (about 10 seconds) whether the option is used or not. If the option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.

The solution to this is to use the option, which only performs the EXPLAIN PLAN, rather than running the statement.

EXPLAIN PLAN

The method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to . First the query must be explained.

SQL> EXPLAIN PLAN FOR 2 SELECT * 3 FROM emp e, dept d 4 WHERE e.deptno = d.deptno 5 AND e.ename = 'SMITH'; Explained. SQL>

Then the execution plan displayed.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql Plan Table -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | | | | | | | NESTED LOOPS | | | | | | | | TABLE ACCESS FULL |EMP | | | | | | | TABLE ACCESS BY INDEX RO|DEPT | | | | | | | INDEX UNIQUE SCAN |PK_DEPT | | | | | | -------------------------------------------------------------------------------- 8 rows selected. SQL>

For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".

From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.

Statement ID

If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the . This associates a user specified ID with each plan which can be used when retrieving the data.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR 2 SELECT * 3 FROM emp e, dept d 4 WHERE e.deptno = d.deptno 5 AND e.ename = 'SMITH'; Explained. SQL> @explain.sql TIM PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP -------------------------------------- --------------- --------------- ----- ----- --------------- --------------- Select Statement 57 4 1.1 Nested Loops 57 4 2.1 Table Access (Full) EMP TABLE 37 3 2.2 Table Access (By Index Rowid) DEPT TABLE 20 1 3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0 5 rows selected. SQL>

By default the Oracle scripts do not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.

Reading Execution Plans

There is an explanation of how to read execution plans here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

 

Sours: https://oracle-base.com/articles/8i/explain-plan-usage

Plan sql explain

How to use Explain Plan to optimize queries?

I also assume you are using Oracle. And I also recommend that you check out the explain plan web page, for starters. There is a lot to optimization, but it can be learned.

A few tips follow:

First, when somebody tasks you to optimize, they are almost always looking for acceptable performance rather than ultimate performance. If you can reduce a query's running time from 3 minutes down to 3 seconds, don't sweat reducing it down to 2 seconds, until you are asked to.

Second, do a quick check to make sure the queries you are optimizing are logically correct. It sounds absurd, but I can't tell you the number of times I've been asked for advice on a slow running query, only to find out that it was occasionally giving wrong answers! And as it turns out, debugging the query often turned out to speed it up as well.

In particular, look for the phrase "Cartesian Join" in the explain plan. If you see it there, the chances are awfully good that you've found an unintentional cartesian join. The usual pattern for an unintentional cartesian join is that the FROM clause lists tables separated by comma, and the join conditions are in the WHERE clause. Except that one of the join conditions is missing, so that Oracle has no choice but to perform a cartesian join. With large tables, this is a performance disaster.

It is possible to see a Cartesian Join in the explain plan where the query is logically correct, but I associate this with older versions of Oracle.

Also look for the unused compound index. If the first column of a compound index is not used in the query, Oracle may use the index inefficiently, or not at all. Let me give an example:

The query was:

(The DBMS was not Oracle, so the syntax was different, and I've forgotten the original syntax).

A quick peek at the indexes revealed an index on Customers with the columns (Country, State, ZipCode) in that order. I changed the query to read

and now it ran in about 6 seconds instead of about 6 minutes, because the optimizer was able to use the index to good advantage. I asked the application programmers why they had omitted the country from the criteria, and this was their answer: they knew that all the addresses had country equal to 'USA' so they figured they could speed up the query by leaving that criterion out!

Unfortunately, optimizing database retrieval is not really the same as shaving microseconds off of computing time. It involves understanding the database design, especially indexes, and at least an overview of how the optimizer does its job.

You generally get better results from the optimizer when you learn to collaborate with it instead of trying to outsmart it.

Good luck coming up to speed at optimization!

Sours: https://stackoverflow.com/questions/234622/how-to-use-explain-plan-to-optimize-queries
Explaining Explain Plans

19 Using EXPLAIN PLAN

19.9 PLAN_TABLE Columns

The used by the statement contains the columns listed in Table 19-1.

Table 19-1 PLAN_TABLE Columns

ColumnTypeDescription

Value of the optional parameter specified in the statement.

Unique identifier of a plan in the database.

Date and time when the statement was generated.

Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. This column is used to indicate whether an outline or SQL Profile was used for the query.

If you need to add or change a remark on any row of the , then use the statement to modify the rows of the .

Name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values:

  • DELETE STATEMENT

  • INSERT STATEMENT

  • SELECT STATEMENT

  • UPDATE STATEMENT

See Table 19-3 for more information on values for this column.

A variation on the operation described in the column.

See Table 19-3 for more information on values for this column.

Name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which output from operations is consumed.

Name of the user who owns the schema containing the table or index.

Name of the table or index.

Unique alias of a table or view in a SQL statement. For indexes, it is the object alias of the underlying table.

Number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner with respect to the original statement text. View expansion results in unpredictable numbers.

Modifier that provides descriptive information about the object; for example, - for indexes.

Current mode of the optimizer.

Not currently used.

A number assigned to each step in the execution plan.

The ID of the next execution step that operates on the output of the step.

Depth of the operation in the row source tree that the plan represents. The value can be used for indenting the rows in a plan table report.

For the first row of output, this indicates the optimizer's estimated cost of executing the statement. For the other rows, it indicates the position relative to the other children of the same parent.

Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the and columns.

Estimate by the query optimization approach of the number of rows accessed by the operation.

Estimate by the query optimization approach of the number of bytes accessed by the operation.

Describes the contents of the column. Values are:

  • SERIAL (blank) - Serial execution. Currently, SQL is not loaded in the column for this case.

  • SERIAL_FROM_REMOTE (S -> R) - Serial execution at a remote site.

  • PARALLEL_FROM_SERIAL (S -> P) - Serial execution. Output of step is partitioned or broadcast to parallel execution servers.

  • PARALLEL_TO_SERIAL (P -> S) - Parallel execution. Output of step is returned to serial query coordinator (QC) process.

  • PARALLEL_TO_PARALLEL (P -> P) - Parallel execution. Output of step is repartitioned to second set of parallel execution servers.

  • PARALLEL_COMBINED_WITH_PARENT (PWP) - Parallel execution; Output of step goes to next step in same parallel process. No interprocess communication to parent.

  • PARALLEL_COMBINED_WITH_CHILD (PWC) - Parallel execution. Input of step comes from prior step in same parallel process. No interprocess communication from child.

Start partition of a range of accessed partitions. It can take one of the following values:

n indicates that the start partition has been identified by the SQL compiler, and its partition number is given by n.

indicates that the start partition will be identified at run time from partitioning key values.

indicates that the start partition (same as the stop partition) will be computed at run time from the location of each record being retrieved. The record location is obtained by a user or from a global index.

indicates that the range of accessed partitions is empty.

Stop partition of a range of accessed partitions. It can take one of the following values:

n indicates that the stop partition has been identified by the SQL compiler, and its partition number is given by n.

indicates that the stop partition will be identified at run time from partitioning key values.

indicates that the stop partition (same as the start partition) will be computed at run time from the location of each record being retrieved. The record location is obtained by a user or from a global index.

indicates that the range of accessed partitions is empty.

Step that has computed the pair of values of the and columns.

Other information that is specific to the execution step that a user might find useful. See the column.

Method used to distribute rows from producer query servers to consumer query servers.

See Table 19-2 for more information on the possible values for this column. For more information about consumer and producer query servers, see Oracle Database Data Warehousing Guide.

CPU cost of the operation as estimated by the query optimizer's approach. The value of this column is proportional to the number of system cycles required for the operation. For statements that use the rule-based approach, this column is null.

I/O cost of the operation as estimated by the query optimizer's approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.

Temporary space, in bytes, used by the operation as estimated by the query optimizer's approach. For statements that use the rule-based approach, or for operations that do not use any temporary space, this column is null.

Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.

Predicates used to filter rows before producing them.

Expressions produced by the operation.

Elapsed time in seconds of the operation as estimated by query optimization. For statements that use the rule-based approach, this column is null.

Name of the query block, either system-generated or defined by the user with the hint.


Table 19-2 describes the values that can appear in the column:

Table 19-2 Values of DISTRIBUTION Column of the PLAN_TABLE

DISTRIBUTION TextInterpretation

Maps rows to query servers based on the partitioning of a table or index using the rowid of the row to /.

Maps rows to query servers based on the partitioning of a table or index using a set of columns. Used for partial partition-wise join, , of a partitioned table, and .

Maps rows to query servers using a hash function on the join key. Used for or .

Maps rows to query servers using ranges of the sort key. Used when the statement contains an clause.

Randomly maps rows to query servers.

Broadcasts the rows of the entire table to each query server. Used for a parallel join when one table is very small compared to the other.

The query coordinator (QC) consumes the input in order, from the first to the last query server. Used when the statement contains an clause.

The query coordinator (QC) consumes the input randomly. Used when the statement does not have an clause.


Table 19-3 lists each combination of and produced by the statement and its meaning within an execution plan.

Table 19-3 OPERATION and OPTIONS Values Produced by EXPLAIN PLAN

OperationOptionDescription

Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path.

converts bitmap representations to actual rowids that can be used to access the table.

converts the rowids to a bitmap representation.

returns the number of rowids if the actual values are not needed.

looks up the bitmap for a single key value in the index.

retrieves bitmaps for a key value range.

performs a full scan of a bitmap index if there is no start or stop key.

Merges several bitmaps resulting from a range scan into one bitmap.

Subtracts bits of one bitmap from another. Row source is used for negated predicates. Can be used only if there are nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Viewing Bitmap Indexes with EXPLAIN PLAN".

Computes the bitwise of two bitmaps.

Computes the bitwise of two bitmaps.

Takes each row from a table row source and finds the corresponding bitmap from a bitmap index. This set of bitmaps are then merged into one bitmap in a following operation.

Retrieves rows in hierarchical order for a query containing a clause.

Operation accepting multiple sets of rows returning the union-all of the sets.

.

Operation counting the number of rows selected from a table.

Count operation where the number of rows returned is limited by the expression in the clause.

Retrieval of one or more rowids from a domain index. The options column contain information supplied by a user-defined domain index cost function, if any.

Operation accepting a set of rows, eliminates some of them, and returns the rest.

Retrieval of only the first row selected by a query.

Operation retrieving and locking the rows selected by a query containing a clause.

Operation hashing a set of rows into groups for a query with a clause.

(These are join operations.)

Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table.

Query optimizer uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows.

Hash (left) antijoin

Hash (left) semijoin

Hash right antijoin

Hash right semijoin

Hash (left) outer join

Hash right outer join

(These are access methods.)

Retrieval of a single rowid from an index.

Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order.

Retrieval of one or more rowids from an index. Indexed values are scanned in descending order.

Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in ascending order.

Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in descending order.

Retrieval of all rowids (and column values) using multiblock reads. No sorting order can be defined. Compares to a full table scan on only the indexed columns. Only available with the cost based optimizer.

Retrieval of rowids from a concatenated index without using the leading column(s) in the index. Introduced in Oracle9i. Only available with the cost based optimizer.

Iterates over the next operation in the plan for each value in the -list predicate.

Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates.

(These are join operations.)

Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.

Merge join operation to perform an outer join statement.

Merge antijoin.

Merge semijoin.

Can result from 1 or more of the tables not having any join conditions to any other tables in the statement. Can occur even with a join and it may not be flagged as in the plan.

Retrieval of rows in hierarchical order for a query containing a clause.

(These are access methods.)

Retrieval of all rows from a materialized view.

Retrieval of sampled rows from a materialized view.

Retrieval of rows from a materialized view based on a value of an indexed cluster key.

Retrieval of rows from materialized view based on hash cluster key value.

Retrieval of rows from a materialized view based on a rowid range.

Retrieval of sampled rows from a materialized view based on a rowid range.

MAT_VIEW REWITE ACCESS

If the materialized view rows are located using user-supplied rowids.

If the materialized view is nonpartitioned and rows are located using index(es).

If the materialized view is partitioned and rows are located using only global indexes.

If the materialized view is partitioned and rows are located using one or more local indexes and possibly some global indexes.

Partition Boundaries:

The partition boundaries might have been computed by:

A previous step, in which case the and column values replicate the values present in the step, and the contains the ID of the step. Possible values for and are (n), , .

The or step itself, in which case the contains the of the step. Possible values for and are (n), , ( only), and .

Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates.

(These are join operations.)

Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table.

Nested loops operation to perform an outer join statement.

.

Iterates over the next operation in the plan for each partition in the range given by the and columns. describes partition boundaries applicable to a single partitioned object (table or index) or to a set of equi-partitioned objects (a partitioned table and its local indexes). The partition boundaries are provided by the values of and of the . Refer to Table 19-1 for valid values of partition start/stop.

Access one partition.

Access many partitions (a subset).

Access all partitions.

Similar to iterator, but based on an -list predicate.

Indicates that the partition set to be accessed is empty.

,

Implements the division of an object into block or chunk ranges among a set of parallel slaves

.

Implements the Query Coordinator which controls, schedules, and executes the parallel plan below it using parallel query slaves. It also represents a serialization point, as the end of the part of the plan executed in parallel and always has a operation below it.

.

Same semantics as the regular operation except that it appears in a parallel plan

.

Shows the consumer/receiver slave node reading repartitioned data from a send/producer (QC or slave) executing on a PX SEND node. This information was formerly displayed into the column. See Table 19-2.

,

Implements the distribution method taking place between two parallel set of slaves. Shows the boundary between two slave sets and how data is repartitioned on the send/producer side (QC or side. This information was formerly displayed into the column. See Table 19-2.

Retrieval of data from a remote database.

Operation involving accessing values of a sequence.

Retrieval of a single row that is the result of applying a group function to a group of selected rows.

Operation sorting a set of rows to eliminate duplicates.

Operation sorting a set of rows into groups for a query with a clause.

Operation sorting a set of rows before a merge-join.

Operation sorting a set of rows for a query with an clause.

(These are access methods.)

Retrieval of all rows from a table.

Retrieval of sampled rows from a table.

Retrieval of rows from a table based on a value of an indexed cluster key.

Retrieval of rows from table based on hash cluster key value.

Retrieval of rows from a table based on a rowid range.

Retrieval of sampled rows from a table based on a rowid range.

If the table rows are located using user-supplied rowids.

If the table is nonpartitioned and rows are located using index(es).

If the table is partitioned and rows are located using only global indexes.

If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes.

Partition Boundaries:

The partition boundaries might have been computed by:

A previous step, in which case the and column values replicate the values present in the step, and the contains the ID of the step. Possible values for and are (n), , .

The or step itself, in which case the contains the of the step. Possible values for and are (n), , ( only), and .

Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates.

Operation performing a view's query and then returning the resulting rows to another operation.


Sours: https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm

Similar news:

Getting an Execution Plan

Viewing an execution plan in the Oracle database involves two steps:

  1. — saves the execution plan in the .

  2. Format and display the execution plan.

Creating and Saving an Execution Plan

To create an execution plan, you just have to prefix the respective SQL statement with :

You can execute the command in any development environment or SQL*Plus. It will, however, not show the plan but save it into a table named . Starting with release 10g, this table is automatically available as a global temporary table. With previous releases, you have to create it in each schema as needed. Ask your database administrator to create it for you or to provide the statement from the Oracle database installation:

You can execute this statement in any schema you like to create the in this schema.

Warning

The command does not necessarily create the same execution plan as though it would when executing the statement.

Showing Execution Plans

The package was introduced with release 9iR2 and can format and display execution plans from the . The following example shows how to display the last execution plan that was explained in the current database session:

Once again, if that statement doesn’t work out of the box, you should ask your DBA for assistance.

The query will display the execution plan as shown in the book:

Some of the columns shown in this execution plan were removed in the book for a better fit on the page.

Previous pageNext page

Sours: https://use-the-index-luke.com/sql/explain-plan/oracle/getting-an-execution-plan


622 623 624 625 626