13.5 EXPLAIN PLAN

---

Transactional activity in a system comprises SELECT, INSERT, UPDATE, and DELETE operations. In order to perform these operations, Oracle generates an execution plan for the statement. The execution plan is generated either based on a set of predefined rules (RBO) or based on the statistics available during the time of creating an execution plan (CBO). Since these execution plans are generated based on the information available in the SQL statement (attributes in the WHERE clause) these plans need to be examined in detail to ensure that they are correct. If this is not the case, appropriate information can be provided either by modifying the statement in such a manner to provide the appropriate selection criteria, or by providing hints to inform the optimizer of an alternative approach to executing the query (such as providing hints).

Hints are like clues to a detective investigating a case. They provide information to help the detective to complete the task more efficiently and quickly.

One such method of looking at the execution plan generated by Oracle is by creating an EXPLAIN PLAN.An EXPLAIN PLAN for an SQL statement is generated using certain environmental settings such as enabling the AUTOTRACE feature and then executing the statement. The syntax to enable AUTOTRACE is

SET AUTOT[RACE] {OFF | ON TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] 

To set the AUTOTRACE feature the user is required to have the PLUSTRACE role. In order to create the PLUSTRACE role the plustr ce.sql script needs to be executed as user sys. This script is located in ORACLE_HOME/sqlplus/admin directory.

The command used to generate the EXPLAIN PLAN is

SQL> SET AUTOTRACE TRACEONLY; SQL> SET TIMING ON;

This command sets the environment conditions and required variables. Subsequently, if the SQL statement is executed, the execution plan or EXPLAIN PLAN for the statement is generated.

SELECT UP.USPRL_ID,     UP.USPRL_FIRST_NAME,     UP.USPRL_LAST_NAME,     UP.USPRL_CITY,     UP.USPRL_STATE_ID,     UP.USPRL_LOGIN_NAME,     UP.USPRL_EMAIL,     UP.USPRL_PHONE,     UP.USPRL_ROLE_CD,     CMP.COMP_NAME,     CMP.COMP_SCAC_CODE,     US.USEC_TOTAL_LOGINS,     UL.USRLI_ID FROM USER_PROFILE UP,     COMPANY CMP,     USER_LOGIN UL,     USER_SECURITY US WHERE UL.USRLI_ACTIVE_STATUS_CD = 'Active'     AND UL.USRLI_LOGGED_IN_USPRL_ID = UP.USPRL_ID     AND UP.USPRL_COMP_ID = CMP.COMP_ID     AND UP.USPRL_ID = US.USEC_USPRL_ID ORDER BY CMP.COMP_TYPE_CD,     CMP.COMP_NAME,     UP.USPRL_LAST_NAME Elapsed: 00:00:09.80 Execution Plan ------------------------------------------------------ 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=19 Bytes=3078) 1 0 SORT (ORDER BY) (Cost=11 Card=19 Bytes=3078) 2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'USER_SECURITY' (Cost=2       Card=1 Bytes=8) 3 2     NESTED LOOPS (Cost=8 Card=19 Bytes=3078) 4 3      NESTED LOOPS (Cost=7 Card=19 Bytes=2926) 5 4       NESTED LOOPS (Cost=6 Card=19 Bytes=2204) 6 5        VIEW OF 'index$_join$_003' (Cost=4 Card=19 Bytes=342) 7 6          HASH JOIN 8 7           INDEX (FAST FULL SCAN) OF 'PK_USRLI' (UNIQUE) (Cost=3                     Card=19 Bytes=342)             9 7 INDEX (FAST FULL SCAN) OF 'USRLI_INDX1' (NON-UNIQUE)                (Cost=3 Card=19 Bytes=342) 10 5         TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'USER_PROFILE'                  (Cost=2 Card=1 Bytes=98) 11 10         INDEX (UNIQUE SCAN) OF 'PK_USPRL' (UNIQUE) 12 4         TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'COMPANY' (Cost=2                 Card=1425 Bytes=54150) 13 12         INDEX (UNIQUE SCAN) OF 'PK_COMP' (UNIQUE) 14 3         INDEX (RANGE SCAN) OF 'USEC_INDX1' (NON-UNIQUE)     Statistics ------------------------------------------------------ 0 recursive calls 394 db block gets 433218 consistent gets 8366 physical reads 0 redo size 27787 bytes sent via SQL*Net to client 1007 bytes received via SQL*Net from client 34 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 487 rows processed 

Note 

For a detailed explanation on how to interpret the EXPLAIN PLAN for an SQL query please refer to Metalink note number 46234.1.

---