Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 24, 2002

Listen Software's How To: SQL Tuning - The Explain Plan

By David Nishimoto

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




Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM