Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 173

EXPLAIN PLAN
Transactional activity in a system consists of SELECT , INSERT , UPDATE , and DELETE operations. When such an operation
is performed, Oracle generates an execution plan for the statement. The execution plan is generated based on
the statistics available and the underlying objects such as indexes, number of rows, filtering rules specified in the
WHERE clause, etc. It is this execution plan that drives the manner in which data is retrieved from the database. If the
performance or response time from the query is high, then based on the explain plan the query should be optimized.
Such optimization may be by adding an index, or if the appropriate index is not being used, modifying the query to
use the index, or rewriting the query to make it efficient.
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 plustrce.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 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 OL_W_ID,
OL_D_ID,
OL_NUMBER,
SUM(OL_AMOUNT),
SUM(OL_QUANTITY)
FROM ORDER_LINE OL,
ORDERS ORD
WHERE OL.OL_O_ID = ORD.O_ID
AND OL.OL_W_ID = ORD.O_W_ID
AND OL.OL_D_ID = ORD.O_D_ID
GROUP BY OL_NUMBER,
OL_W_ID,
OL_D_ID;
300 rows selected.
Elapsed: 00:02:18.30
Execution Plan
-------------------------------
Plan hash value: 99144695