EXPLAIN PLAN Usage

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. Once you’ve highlighted a problem query the first thing you should do is EXPLAIN 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.

Plan Table

The explain plan process stores data in the PLAN_TABLE. 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 PLAN_TABLE is created by default, but you can still create a local version of the table using the “utlxplan.sql” script.

AUTOTRACE – The Easy Option?

Switching on the AUTOTRACE 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.ename  = ‘SMITH’;

 

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

———- ———- ——— ———- ——— ———- ———-

DEPTNO     DEPTNO DNAME          LOC

———- ———- ————– ————-

7369 SMITH      CLERK           7902 17-DEC-80        800

20         20 RESEARCH       DALLAS

 

 

 

Execution Plan

———————————————————-

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   NESTED LOOPS

2    1     TABLE ACCESS (FULL) OF ‘EMP’

3    1     TABLE ACCESS (BY INDEX ROWID) OF ‘DEPT’

4    3       INDEX (UNIQUE SCAN) OF ‘PK_DEPT’ (UNIQUE)

 

 

 

 

Statistics

———————————————————-

81  recursive calls

4  db block gets

27  consistent gets

0  physical reads

0  redo size

941  bytes sent via SQL*Net to client

425  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

 

SQL>

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 TRACEONLY option of AUTOTRACE 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.28

 

Execution Plan

———————————————————-

Plan hash value: 1550022268

 

————————————————————————-

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————-

|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |

————————————————————————-

 

 

Statistics

———————————————————-

189  recursive calls

0  db block gets

102  consistent gets

0  physical reads

0  redo size

331  bytes sent via SQL*Net to client

332  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

13  sorts (memory)

0  sorts (disk)

1  rows processed

 

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT pause_for_secs(10) FROM DUAL;

 

1 row selected.

 

Elapsed: 00:00:10.26

 

Execution Plan

———————————————————-

Plan hash value: 1550022268

 

————————————————————————-

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————-

|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01 |

|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |

————————————————————————-

 

 

Statistics

———————————————————-

0  recursive calls

0  db block gets

0  consistent gets

0  physical reads

0  redo size

331  bytes sent via SQL*Net to client

332  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

 

SQL>

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

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

EXPLAIN PLAN

The EXPLAIN PLAN 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 AUTOTRACE. 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 STATEMENT_ID. 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>