Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Run a SQL Execution Plan Report

Run the plan9i.sql script and view the distribution of data access methods in your Oracle database. This script is the Oracle 10g equivalent of the access.sql script in the Burleson book, and it will interrogate the library cache and produce a report showing all internal tables access methods.

In your Oracle database, you might want to “exercise” your personal database by running numerous queries against the sample database.

Your output should look something like this:

SQL> @plan9i

Owner        # of SQL selects
------------ ----------------
SYS                       504                                                   
AURORA$JIS$U                8                                                  
TILITY$                                                                        

SYSTEM                      6
PUBS                        4                                                  

Sat May 18                                                             page    1
                          Index range scans and counts
 

OWNER     TABLE_NAME           INDEX_NAME             TBL_BLOCKS    NBR_SCANS  
--------- -------------------- -------------------- ------------ ------------  
SYS       OBJ$                 I_OBJ2                        778          873  
SYS       ACCESS$              I_ACCESS1                     478          748  
SYS       DEPENDENCY$          I_DEPENDENCY1                 878          748  
SYS       IDL_SB4$             I_IDL_SB41                  1,103          413  
SYS       IDL_UB1$             I_IDL_UB11                 26,653          391  
SYS       IDL_CHAR$            I_IDL_CHAR1                   653          390  
SYS       IDL_UB2$             I_IDL_UB21                  2,503          390   SYS       ARGUMENT$            I_ARGUMENT2                   828           90  
SYS       OBJAUTH$             I_OBJAUTH1                    131           36  
SYS       SYSAUTH$             I_SYSAUTH1                      6           21  
SYS       PROFILE$             I_PROFILE                       3           17    

11 rows selected.

Sat May 18                                                             page    1
                         Index unique scans and counts
 
OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS               
--------- -------------------- -------------------- ------------               
SYS       OBJ$                 I_OBJ1                        857
SYS       C_OBJ#               I_OBJ#                        282               
SYS       JAVASNM$             I_JAVASNM1                    127               
SYS       PROCEDUREJAVA$       I_PROCEDUREJAVA$               74               
SYS       C_COBJ#              I_COBJ#                        72               
SYS       C_USER#              I_USER#                        64               
SYS       C_FILE#_BLOCK#       I_FILE#_BLOCK#                 56               
SYS       SMON_SCN_TO_TIME     SMON_SCN_TO_TIME_IDX           28               
SYS       SYN$                 I_SYN1                         17               
SYS       C_TS#                I_TS#                          13               
SYS       PROCEDURE$           I_PROCEDURE1                   13               
SYS       VIEW$                I_VIEW1                        12               
SYS       USER$                I_USER1                        11               
 

Script source:

Plan9i.sql

--**************************************************************
-- Object Access script report
--
-- © 2002 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; 

column object_owner heading "Owner"            format a12;
column ct           heading "# of SQL selects" format 999,999; 

select
   object_owner,
   count(*)   ct
from
   v$sql_plan
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|  |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','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions) nbr_FTS
from
   dba_tables   t,
   dba_segments s,
   v$sqlarea    a,
   (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
   a.address = p.address
   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')
having
   sum(a.executions) > 9
group by
   p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   sum(a.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,
   v$sqlarea s,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'TABLE ACCESS'
      and
      options = 'BY ROWID') p
where
   s.address = p.address
   and
   t.table_name = p.name
   and
   t.owner = p.owner
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,
   v$sqlarea s,
   dba_indexes d,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'INDEX'
      and
      options = 'FULL SCAN') p
where
   d.index_name = p.name
   and
   s.address = p.address
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
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,
   v$sqlarea s,
   dba_indexes d,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan

   where
      operation = 'INDEX'
      and
      options = 'RANGE SCAN') p
where
   d.index_name = p.name
   and
   s.address = p.address
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
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
   v$sqlarea s,
   dba_indexes d,
  (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'INDEX'
      and
      options = 'UNIQUE SCAN') p
where
   d.index_name = p.name
   and
   s.address = p.address
having
   sum(s.executions) > 9
group by
   p.owner, d.table_name, p.name
order by
   sum(s.executions) desc;



 
For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.