Listen Software Solutions' "How To" Series:
SQL Tuning - The Explain Plan
By David Nishimoto
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
|
