Listen Software’s How To: SQL Tuning – The Explain Plan

Purpose: The following three steps provide a method to get
information back on how the optimizer will evaluate your SQL.
Note, the optimizer uses statistics to determine an execution path.

Step 1–Create the PLAN_TABLE

create table PLAN_TABLE (
  statement_id    char(30),
  timestamp       date,
  remarks         char(80),
  operation       char(30),
  options         char(30),
  object_node     char(30),
  object_owner    char(30),
  object_name     char(30),
  object_instance numeric,
  object_type     char(30),
  search_columns  numeric,
  id              numeric,
  parent_id       numeric,
  position        numeric,
  other           long
)
STORAGE 
(
INITIAL 300k 
NEXT 1M 
MINEXTENTS 1 
MAXEXTENTS 121 
PCTINCREASE 0
)
PCTFREE  10
PCTUSED 70
TABLESPACE {TABLESPACE_NAME}

Step 2–Submit your SQL to the optimizer

DELETE FROM PLAN_TABLE;
COMMIT;

EXPLAIN PLAN
SET STATEMENT_ID = 'IDENTIFICATION STRING'
INTO PLAN_TABLE
FOR
SELECT field1,field2 from table where field1=criteria;

@PLAN1

Step 3–Display the Explain Plan (Plan1.SQL)

/*Run from SQL Plus*/

CLEAR SCREEN
PROMPT
ACCEPT STATE_ID 
PROMPT '  ENTER THE STATEMENT ID TO BE DISPLAYED: ';
SET VERIFY OFF
PROMPT
COLUMN OPERATION FORMAT A20
COLUMN OPTIONS FORMAT A15
COLUMN OBJECT_NAME FORMAT A20
COLUMN ID FORMAT 999 HEADING 'ID '
COLUMN PARENT_ID FORMAT 999 HEADING 'PARENT|ID  '
COLUMN POSITION FORMAT 999
SELECT OPERATION,OPTIONS,OBJECT_NAME,ID,PARENT_ID,POSITION
FROM PLAN_TABLE
WHERE STATEMENT_ID = '&STATE_ID'
ORDER BY ID;
UNDEFINE STATE_ID
SET VERIFY ON

Hints

CACHE

Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed.

select /*+ FULL(scott_emp) CACHE(scott_emp) */
ename from scott_emp

INDEX(table INDEX) Table

Specifies the name of the table or alias associated with the index. Index specifies the index on which to scan.

select /*+ INDEX(scott_emp emp_id_pk) */
field1 from scott_emp where id=1;

AND_EQUAL (table index index index)

Uses an access path that merges the scans on several single-column indexes. Table specifies the table associated with the indexes.

ORDERED

The ORDERED hint causes Oracle to join tables in the order in which they appear in the From clause.

SELECT /*+ ORDERED */ tab1.col1, tab2.col2
from tab1, tab2
where tab1.col1=tab2.col2

FIRST_ROWS

Returns a cursor after the first row is fetched.

Back to the LSS “How To” Series Main Page