Oracle Analyzing and Reading the Execution Plan

Analyzing the Execution Plan

When examining an execution plan, look for inefficiencies and opportunities to improve performance. When you see any of the following, you have an opportunity to tune the query:

• Full index or table scans
• Unselective range scans
• Late predicate filters
• Incorrect or inefficient join order
• Late filter operations

The key is to look for operations where the percentage of “throw-away” is very high: when the number of rows needed by the operation is a very small percentage of the number of rows read. Reduce the number of reads, and you’ll most likely improve the performance of the operation. The execution plan may show that a statement uses an index, but that does not necessarily mean that the statement runs efficiently. Sometimes indexes can be extremely inefficient because of the columns of the index being used and their selectivity.

When developing SQL, use explain plan to determine the execution plan. Then test the statement’s actual performance and resource consumption using AUTOTRACE.

Reading the Execution Plan

Reading an execution plan is fairly easy. Note that each statement is indented a certain level when autotrace or when displaying it from the PLAN_TABLE. Take this rather basic execution plan as an example:

Execution Plan
----------------------------------------------------------
   0         SELECT STATEMENT Optimizer=CHOOSE
               (Cost=1 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
               (Cost=1 Card=1 Bytes=62)
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE)

In this example, each operation is assigned an identifying operation number, as seen in the numbers to the far left. The next set of numbers defines the parent operation for the operation listed. In this case the line INDEX (UNIQUE SCAN) line is indented the farthest. This implies that the index scan will be executed first.

The result from the index scan, the ROWID of the row in the table will be passed to the next command, which is a TABLE ACCESS (BY INDEX ROWID). Here, we are getting a row from the table based on the ROWID passed from the previous operation. This row will be returned to the issuing query.

You may have noticed the information returned in the execution plan that reads "(Cost=1 Card=1 Bytes=62)". The Cost is the cost of that operation as estimated by the Cost Based Optimizer (CBO). This cost does not have any specific unit of measurement. A cost of 20 is not necessarily twice the cost of 10. However, a cost of 20 is a higher cost than a cost of 10. The CBO uses these relative cost values to determine the execution plan. The Card value is the cardinality of the data involved in the query. The cardinality in the explain plan results is the estimate by the CBO of the number of rows accessed by this operation. Ideally, the CBO will use the execution plan step with the lowest cardinality. The Bytes value is the CBO's estimate of the number of bytes accessed by this operation. Ideally, the CBO will use the execution plan step with the lowest number of bytes accessed.

Let’s look at another example of an explain plan. There may be several operations that occur at the same level. In this case, the last one at the same level would be the first operation to execute. Subsequent operations at the same level will execute from top to bottom. Once those operations have completed, they will pass their row sources to the parent operation.

SQL> explain plan set statement_id='QUEST2' for
2 select /*+ INDEX(a, ix_emp_01) */ a.empno, b.dname
3 from emp a, dept b
4 where a.deptno=b.deptno
5 and a.empno=21334;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  |    Operation                |   Name    | Rows  |  Bytes  |  Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    31 |     837 |     24 |
| * 1 | HASH JOIN                   |           |    31 |     837 |     24 |
| * 2 | TABLE ACCESS BY INDEX ROWID | EMP       |     1 |       5 |     21 |
| * 3 | INDEX RANGE SCAN            | IX_EMP_01 |     1 |         |      1 |
|   4 | TABLE ACCESS FULL           | DEPT      |   409 |    8998 |      2 |
----------------------------------------------------------------------------

In this case, we have a join that involves two tables, the EMP table and the DEPT table. We also have an index access on an index of the EMP table. The first operation in this case is the one that is indented the farthest. That would be the INDEX RANGE SCAN. It will pass its results to its parent operation, which will be a ROWID lookup of the EMP table. These results will be passed to the hash join operation, and the full table scan of the DEPT table will occur. Those results will be passed to the hash join operation and a final row source will be returned to the user.