Explain Plan in Oracle

Explain plan in Oracle shows execution plan in Oracle which is choose by oracle optimizer for select statements, insert, update and delete statements. Informations we can get from explain plan in Oracle are :

• Table used for sql statement we want to explain
• Access method in tables we access.
• Steps for executing the sql
• join method
  
• sorting and agregating

Steps using explain plan in Oracle :

• Create table plan by execute script utlxplan.sql locates in $ORACLE_HOME/rdbms/admin/utlxplan.sql
• Use explain plan by using this sql : EXPLAIN PLAN
  SET STATEMENT_ID = 'some identifier'
  INTO plan_table
  FOR sql_statement;
• Check explain plan result : Select * from PLAN_TABLE
  where statement_id = 'some identifier';
• If we use cost based optimizer we can use this sql to check explain plan result : SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '
  ||object_name||' '||
  DECODE(id,0,'Cost = '|| position) AS "Query Plan"
  FROM plan_table
  START WITH id = 0
  AND statement_id='SQL Pak Hari'
  CONNECT BY PRIOR ID = PARENT_ID
  AND statement_id = 'SQL Pak Hari';
• Thats all