Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

access.sql script to see execution plan details

Oracle Database Tips by Donald Burleson

QUESTION:  I have a pre Oracle9i database and I have no v$sql_plan view to see all of my SQL execution plans.  How do I get this report?

                  Full table scans and counts
          Note that "K" indicates in the table is in the KEEP pool.


OWNER          NAME                      NUM_ROWS  C K   BLOCKS  NBR_FTS
-------------- ------------------------  --------- - - -------- --------
SYS            DUAL                                N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PROFILE             N K        2   16,178
DONALD         PAGE                      3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                        434 N          8    7,355
DONALD         PAGE_IMAGE                   18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                    476 N K      192    2,087
DONALD         PRINT_PAGE_RANGE                 10 N K       32      874
ARM            JANET_BOOKS                      20 N          8       64

 

ANSWER: It's easier in Oracle9i using the script plan9i.sql which reads from v$sql_plan, but here is my original Oracle7script that creates a plan_table and populates it from the v$sql view.  It may need some tweaking for your environment, but I've always had great luck with it for finding summaries of all SQL executions from my library cache.  For Oracle9i and 10g, see Oracle Plan9i.sql script to display full-table scans.  For a time-series display of SQL execution plan summaries see the script plan10g.sql in my latest book "Oracle Tuning, The Definitive Reference".

Steps:

1.     Download the access.sql and access_report.sql scripts below.

2.     Issue the following statements for the schema owner of your tables:

grant select on v_$sqltext to schema_owner;
grant select on v_$sqlarea to schema_owner;
grant select on v_$session to schema_owner;
grant select on v_$mystat to schema_owner;

3.     Go into SQL*Plus, connect as the schema owner, and run access.sql.

 

--**************************************************************
-- Object Access script
--
-- 2001 by Donald K. Burleson
--
--   No part of this SQL script may be copied. Sold or
--   distributed
--   without the express consent of Donald K. Burleson
--**************************************************************
set echo on;
 
 
--**********************************************
--  We must run this script as the schema owner
--**********************************************
 
 
connect schema_owner/password;
 
 
set serveroutput on size 100000
 
set echo off;
--**************************************************************
prompt We first gather all SQL in the library cache and run EXPLAIN PLAN.
prompt This takes awhile, so be patient . . .
--**************************************************************
 
--set echo on
--set feedback on
 
set feedback off
set echo off
-- Drop and recreate PLAN_TABLE for EXPLAIN PLAN
drop table plan_table;
@c:\oracle\ora81\rdbms\admin\utlxplan
 
Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA
drop table sqltemp;
create table sqltemp
(
   ADDR           VARCHAR2 (16)
  ,HASHVAL        INTEGER
  ,SQL_TEXT       VARCHAR2(2000)
  ,DISK_READS     NUMBER
  ,EXECUTIONS     NUMBER
  ,PARSE_CALLS    NUMBER
  ,PARSE_USER     VARCHAR2(30)
  ,BUFFER_GETS    NUMBER
  ,SORTS          NUMBER
  ,ROWS_PROCESSED NUMBER
  ,STMT_ID        VARCHAR2(100)
);
 
--set echo on
set feedback on
 
CREATE OR REPLACE PROCEDURE do_explain
   (addr IN  VARCHAR2
   ,hash IN INTEGER
   ,sql_text_IN IN VARCHAR2
   ,parse_user_IN IN VARCHAR2
   ,stmt_id_IN IN VARCHAR2 )
AS
 
   dummy    VARCHAR2(32767);
 
   dummy1   VARCHAR2(100); 
 
   mycursor INTEGER;
   ret      INTEGER;
   my_sqlerrm VARCHAR2 (85);
   signed_hash  NUMBER;
 
   FUNCTION get_sql(addr_IN IN VARCHAR2, hash_IN IN INTEGER)
   RETURN VARCHAR2
   IS
      temp_return  VARCHAR2(32767);
      CURSOR sql_pieces_cur
      IS
      SELECT sql_text
        FROM v$sqltext
       WHERE address = HEXTORAW(addr_IN)
         AND hash_value = hash_IN
      ORDER BY piece ASC;
   BEGIN
      FOR sql_pieces_rec IN sql_pieces_cur
      LOOP
         temp_return := temp_return||sql_pieces_rec.sql_text;
      END LOOP;
 
      IF temp_return IS NULL
      THEN
         RAISE_APPLICATION_ERROR(-20000,'SQL Not Found');
      END IF;
 
      RETURN temp_return;
   END get_sql;
 
   FUNCTION current_schema RETURN VARCHAR2
   IS
      temp_schema   v$session.schemaname%TYPE;
   BEGIN
      SELECT schemaname
        INTO temp_schema
        FROM v$session
       WHERE sid = (SELECT MAX(sid) FROM v$mystat);
      --
      RETURN temp_schema;
   EXCEPTION
      WHEN OTHERS THEN RETURN NULL;
   END current_schema;
 
BEGIN
   -- adjust signed_hash if hash > 2**31
   -- (hash type mismatch between v$sqlarea and v$sqltext)
   IF hash > POWER(2,31)
   THEN
      signed_hash := hash - POWER(2,32);
   ELSE
      signed_hash := hash;
   END IF;
  
  
--   EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA='||parse_user_IN;
 
 
   dummy:='EXPLAIN PLAN SET STATEMENT_ID='''||stmt_id_IN||''' INTO plan_table FOR ' ;
   IF LENGTH(sql_text_IN) > 1900
   THEN
      BEGIN  -- try to get using hash first, and unsigned hash if not found
         dummy:=dummy||get_sql(addr,hash);
        
      EXCEPTION
         WHEN OTHERS THEN dummy:=dummy||get_sql(addr,signed_hash);
                        
      END;
   ELSE
      dummy := dummy||sql_text_IN;
   END IF;
 
   -- JB: optimization = only change schema if different from current
   --IF parse_user_IN != current_schema
   --THEN
   --   dbms_output.put_line(current_schema||' '||parse_user_IN);
   --   mycursor := DBMS_SQL.OPEN_CURSOR;
   --   DBMS_SQL.PARSE(mycursor,dummy1,DBMS_SQL.NATIVE);
   --   ret := DBMS_SQL.EXECUTE(mycursor);
   --   DBMS_SQL.CLOSE_CURSOR(mycursor);  
   --END IF;
 
   mycursor := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(mycursor,dummy,DBMS_SQL.NATIVE);
   ret := DBMS_SQL.EXECUTE(mycursor);
 
 
   DBMS_SQL.CLOSE_CURSOR(mycursor);
 
 
   COMMIT;
EXCEPTION -- Insert errors into PLAN_TABLE...
   WHEN OTHERS
   THEN
      my_sqlerrm := SUBSTR(sqlerrm,1,80);
      INSERT INTO plan_table(statement_id,remarks) -- change to plan_table (JB)
      VALUES (stmt_id_IN, my_sqlerrm);
      
      -- cleanup cursor id open
      IF DBMS_SQL.IS_OPEN(mycursor)
      THEN
         DBMS_SQL.CLOSE_CURSOR(mycursor);
      END IF;
 
-- EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYS';
 
END;
/
 
 
show errors
 
 
DECLARE
 
   CURSOR  c1
   IS
   SELECT
          RAWTOHEX(SA.address) addr
         ,SA.hash_value        hash
         ,SA.sql_text          sql_text
         ,SA.DISK_READS        diskrds
         ,SA.EXECUTIONS        execs
         ,SA.PARSE_CALLS       parses
         ,SA.BUFFER_GETS       buffer_gets
         ,SA.SORTS             sorts
         ,SA.ROWS_PROCESSED    rows_processed
         ,DU.username          username
         ,SUBSTR(RAWTOHEX(SA.address)||':'||TO_CHAR(SA.hash_value) , 1,30) stmt_id
     FROM
          v$sqlarea   SA
         ,DBA_USERS   DU
    WHERE
          command_type = 3
      AND
          SA.parsing_schema_id != 0
      AND SA.parsing_schema_id = DU.user_id;
 
   CURSOR c2
   IS
   SELECT
          addr
         ,hashval
         ,sql_text
         ,parse_user
         ,stmt_id
     FROM
          sqltemp
    ORDER BY parse_user;
 
BEGIN
-- EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYS';
 
   FOR c1_rec IN c1
   LOOP
      INSERT INTO
         sqltemp (ADDR
                 ,HASHVAL
                 ,SQL_TEXT
                 ,DISK_READS
                 ,EXECUTIONS
                 ,PARSE_CALLS
                 ,BUFFER_GETS
                 ,SORTS
                 ,ROWS_PROCESSED
                 ,PARSE_USER
                 ,STMT_ID
                 )
         VALUES (c1_rec.addr
                ,c1_rec.hash
                ,c1_rec.sql_text
                ,c1_rec.diskrds
                ,c1_rec.execs
                ,c1_rec.parses
                ,c1_rec.buffer_gets
                ,c1_rec.sorts
                ,c1_rec.rows_processed
                ,c1_rec.username
                ,c1_rec.stmt_id
                );
   END LOOP;
   --
   FOR c2_rec IN c2
   LOOP
      do_explain(c2_rec.addr
                ,c2_rec.hashval
                ,c2_rec.sql_text
                ,c2_rec.parse_user
                ,c2_rec.stmt_id);       
   END LOOP;
END;
/
 
--show errors
 
commit;
 
 
-- ********************************************************
-- Report section
-- ********************************************************
 
@access_report
--@/s001/app/oracle/home/sql/access_report
 
--drop procedure do_explain;
--drop table sqltemp;
--drop table plan_table;
 

Here is access_report.sql script to display the report:
 
--**************************************************************
-- Object Access script report
--
-- 2001 by Donald K. Burleson
--
--   No part of this SQL script may be copied. Sold or
--   distributed
--   without the express consent of Donald K. Burleson
--**************************************************************
 
-- ********************************************************
-- Report section
-- ********************************************************
 
set echo off;
set feedback on
 
set pages 999;
column nbr_FTS  format 999,999
column num_rows format 999,999,999
column blocks   format 999,999
column owner    format a14;
column name     format a24;
column ch       format a1;
 
ttitle 'Total SQL found in library cache'
select count(*) from plan_table;
 
ttitle 'Total SQL that could not be explained'
select count(*) from plan_table where remarks is not null;
 
column object_owner heading "Owner"            format a12;
column ct           heading "# of SQL selects" format 999,999;
 
select
   object_owner,
   count(*)   ct
from
   plan_table
where
   object_owner is not null
group by
   object_owner
order by
   ct desc
;
 
 
--spool access.lst;
 
set heading off;
set feedback off;
 
set heading on;
set feedback on;
ttitle 'full table scans and counts|  |Note that "C" indicates in the table is cached (Oracle7 only).|The "K" indicates that the table is in the KEEP Pool (Oracle8).'
select
   p.owner,
   p.name,
   t.num_rows,
   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','K','DEFAULT',' ') K,
   s.blocks blocks,
   sum(s.executions) nbr_FTS
from
   dba_tables t,
   dba_segments s,
    sqltemp s,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
   from
      plan_table
   where
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   and
   t.owner = s.owner
   and
   t.table_name = s.segment_name
   and
   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM','PERFSTAT')
having
   sum(s.executions) > 9
group by
   p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   sum(s.executions) desc;
 
column nbr_RID  format 999,999,999
column num_rows format 999,999,999
column owner    format a15;
column name     format a25;
 
ttitle 'Table access by ROWID and counts'
select
   p.owner,
   p.name,
   t.num_rows,
   sum(s.executions) nbr_RID
from
   dba_tables t,
    sqltemp s,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
   from
      plan_table
   where
      operation = 'TABLE ACCESS'
      and
      options = 'BY ROWID') p
where
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   and
   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM','PERFSTAT')
having
   sum(s.executions) > 9
group by
   p.owner, p.name, t.num_rows
order by
   sum(s.executions) desc;
 
--*************************************************
--  Index Report Section
--*************************************************
 
column nbr_scans  format 999,999,999
column num_rows   format 999,999,999
column tbl_blocks format 999,999,999
column owner      format a9;
column table_name format a20;
column index_name format a20;
 
ttitle 'Index full scans and counts'
select
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions) nbr_scans
from
   dba_segments seg,
   sqltemp s,
   dba_indexes d,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
   from
      plan_table
   where
      operation = 'INDEX'
      and
      options = 'FULL SCAN') p
where
   d.index_name = p.name
   and
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
   and
   p.owner not in ('SYS','SYSTEM','PERFSTAT')
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name, seg.blocks
order by
   sum(s.executions) desc;
 
 
ttitle 'Index range scans and counts'
select
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions) nbr_scans
from
   dba_segments seg,
    sqltemp s,
   dba_indexes d,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
   from
      plan_table
   where
      operation = 'INDEX'
      and
      options = 'RANGE SCAN') p
where
   d.index_name = p.name
   and
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
   and
   p.owner not in ('SYS','SYSTEM','PERFSTAT')
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name, seg.blocks
order by
   sum(s.executions) desc;
 
 
ttitle 'Index unique scans and counts'
select
   p.owner,
   d.table_name,
   p.name index_name,
   sum(s.executions) nbr_scans
from
   sqltemp s,
   dba_indexes d,
  (select distinct
     statement_id stid,
     object_owner owner,
     object_name name
   from
      plan_table
   where
      operation = 'INDEX'
      and
      options = 'UNIQUE SCAN') p
where
   d.index_name = p.name
   and
   s.addr||':'||TO_CHAR(s.hashval) = p.stid
   and
   p.owner not in ('SYS','SYSTEM','PERFSTAT')
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name
order by
   sum(s.executions) desc;
 
 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational