Explain Plan

Before creating EXPLAIN PLAN for any Query, you must identify the exact query which is consuming maximum database resource.

First Identify the query (Done by DBA)

How to identify SQL consuming maximum data base resources

• Go to V$SQLAREA

• SQL>Select sql_TEXT, DISK_READS, BUFFER_GETS FROM V$SQLAREA ORDER BY DISK_READS DESC;

• Get first 10 SQL where disk reads are highest and get first 10 SQL where buffer_gets are highest.

Below are the steps to create Explain Plan

For EXPLAIN PLAN we must have PLAN table created in our schema

Create this table PLAN_TABLE

• To create PLAN_TABLE there is SQL in

ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN
Execute this query.

• SQL> @$ORACLE_HOME/rdbms/admin/utlxpls

How to give query to PLAN_TABLE, for this write below written query this query should be from first 10 queries which were consuming more resources.

• Suppose in our Case if we need explain plan for query

SQL>Select * from emp where empno =7902

• SQL>Explain Plan Set statement_id = ’st1’For Select * from emp where =7902;
  
  In above query Statement_id = ‘st1’ is there we can have any thing in place of st1 it means st2 or xyz etc.
  
  This query will not give output on screen so in explain PLAN it explain execution PLAN
  
  SQL> col OPTIONS format a10
  col OPTIMIZER format a10
  col OBJECT_NAME format a10
  select id,options, operation,object_name, OPTIMIZER,CPU_COST, IO_COST,temp_space
  from plan_table
  where statement_id = '&Statement'
  order by id ;
  
  If the query don’t give the value of CPU_COST, IO_COST, TEMP_SPACE then oracle has used RBO in this it will do FTS but if we want to ignore FTS then we have to create index on column which is used in where clause.
  
  Explain plan will tells us how QUERY is executed

• Solution

If there is full table scan create index on table.