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 


 

 

 


 

 

 
 

Find full-table scans with AWR using plan10g.sql


Oracle Database Tips by Donald Burleson

If you are using the extra-cost AWR option, you can query the dba_hist_sql_plan table to quickly find all tables that experience full-table scans.  If you want to interrogate your current library cache, you can use the plan9i.sql script to see full-table scans from v$sql_plan.

While full-table scans are not "bad", per se, they are an indicator that you may have a missing index, and excessive full-table scans on large tables should be investigated.  High full-table scans on small tables could also be checked to ensure that they reside in your KEEP pool.  Here is a sample of the output, showing full-table scans between AWR snapshot intervals:

                     full table scans and counts                                     
Snapshot Time   OWNER      NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS    
-------------   ---------- ------------------------ ------------ - - -------- -----   
12/08/04 14     APPLSYS  FND_CONC_RELEASE_DISJS           39 N K        2   98,864   
                APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2   98,864   
                APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   98,864   
                SYS        DUAL                              N K        2   63,466   
                APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036   
                APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174   
 
12/08/04 15     APPLSYS    FND_CONC_RELEASE_DISJS         39 N K        2   33,811   
                APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2    2,864   
                APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   32,864   
                SYS        DUAL                              N K        2   63,466   
                APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   12,033   
                APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174   

The full arsenal of Oracle tuning scripts are in my book "Oracle Tuning: The Definitive Reference" and Mike Ault's Oracle script collection.

Here is my plan10g.sql script to display SQL access signatures over time:
 

-- *************************************************
-- Copyright 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
 
-- To use this script you must be licensed to access the AWR tables
-- by purchasing the Oracle tuning pack and the Oracle diagnostic pack
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
 
 
spool plan.lst
 
set echo off
set feedback on
 
set pages 999;
column nbr_FTS  format 99,999
column num_rows format 999,999
column blocks   format 9,999
column owner    format a10;
column name     format a30;
column ch       format a1;
column time     heading "Snapshot Time"        format a15
 
column object_owner heading "Owner"            format a12;
column ct           heading "# of SQL selects" format 999,999;
 
break on time
 
select
   object_owner,
   count(*)   ct
from
   dba_hist_sql_plan
where
   object_owner is not null
group by
   object_owner
order by
   ct desc
;
 
 
--spool access.lst;
 
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
   to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
   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_delta E "executions_delta" ) nbr_FTS
from
   dba_tables E "dba_tables"    t,
   dba_segments E "dba_segments"  s,
   dba_hist_sqlstat E "dba_hist_sqlstat"     a,
   dba_hist_snapshot sn,
   (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from
      dba_hist_sql_plan pl
   where
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
   a.snap_id = sn.snap_id
   and
   a.sql_id = p.sql_id
   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_delta E "executions_delta" ) > 1
group by
   to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   1 asc;
 
 
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
   to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
   p.owner,
   p.name,
   t.num_rows,
   sum(a.executions_delta E "executions_delta" ) nbr_RID
from
   dba_tables E "dba_tables"  t,
   dba_hist_sqlstat E "dba_hist_sqlstat"     a,
   dba_hist_snapshot sn,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from
      dba_hist_sql_plan pl
   where
      operation = 'TABLE ACCESS'
      and
      options = 'BY USER ROWID') p
where
   a.snap_id = sn.snap_id
   and
   a.sql_id = p.sql_id
   and
   t.table_name = p.name
   and
   t.owner = p.owner
having
   sum(a.executions_delta E "executions_delta" ) > 9
group by
   to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, p.name, t.num_rows
order by
   1 asc;
 
--*************************************************
--  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
   to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions_delta E "executions_delta" ) nbr_scans
from
   dba_segments E "dba_segments"  seg,
   dba_indexes E "dba_indexes"  d,
   dba_hist_sqlstat E "dba_hist_sqlstat"     s,
   dba_hist_snapshot sn,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from
      dba_hist_sql_plan pl
   where
      operation = 'INDEX'
      and
      options = 'FULL SCAN') p
where
   d.index_name = p.name
   and
   s.snap_id = sn.snap_id
   and
   s.sql_id = p.sql_id
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
having
   sum(s.executions_delta E "executions_delta" ) > 9
group by
   to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name, seg.blocks
order by
   1 asc;
 
 
ttitle 'Index range scans and counts'
select
   to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
   p.owner,
   d.table_name,
   p.name index_name,
   seg.blocks tbl_blocks,
   sum(s.executions_delta E "executions_delta" ) nbr_scans
from
   dba_segments E "dba_segments"  seg,
   dba_hist_sqlstat E "dba_hist_sqlstat"     s,
   dba_hist_snapshot sn,
   dba_indexes E "dba_indexes"  d,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from
      dba_hist_sql_plan pl
   where
      operation = 'INDEX'
      and
      options = 'RANGE SCAN') p
where
   d.index_name = p.name
   and
   s.snap_id = sn.snap_id
   and
   s.sql_id = p.sql_id
   and
   d.table_name = seg.segment_name
   and
   seg.owner = p.owner
having
   sum(s.executions_delta E "executions_delta" ) > 9
group by
   to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name, seg.blocks
order by
   1 asc;
 
ttitle 'Index unique scans and counts'
select
   to_char(sn.end_interval_time,'mm/dd/rr hh24') time,
   p.owner,
   d.table_name,
   p.name index_name,
   sum(s.executions_delta E "executions_delta" ) nbr_scans
from
   dba_hist_sqlstat E "dba_hist_sqlstat"     s,
   dba_hist_snapshot sn,
   dba_indexes E "dba_indexes"  d,
  (select distinct
     pl.sql_id,
     object_owner owner,
     object_name name
   from
      dba_hist_sql_plan pl
   where
      operation = 'INDEX'
      and
      options = 'UNIQUE SCAN') p
where
   d.index_name = p.name
   and
   s.snap_id = sn.snap_id
   and
   s.sql_id = p.sql_id
having
   sum(s.executions_delta E "executions_delta" ) > 9
group by
   to_char(sn.end_interval_time,'mm/dd/rr hh24'),p.owner, d.table_name, p.name
order by
   1 asc;
 
spool off



The output is shown below, and it is the same in 9i and 10g. A good way to start the review of the results is by looking at the counts of full-table scans for each AWR snapshot period. This report gives all the information needed to select candidate tables for the KEEP pool. The database will benefit from placing small tables, less than two percent of db_cache_size, that are subject to frequent full-table scans in the KEEP pool. The report from an Oracle Applications database below shows full-table scans on both large and small tables.

The goal is to use the RECYCLE pool for segregating large tables involved in frequent full-table scans. To locate these large-table full-table scans, the plan9i.sql full-table scan report for a 9i database:

 

                     full table scans and counts                                     

Snapshot Time   OWNER      NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS    

-------------   ---------- ------------------------ ------------ - - -------- -----    12/08/04 14     APPLSYS    FND_CONC_RELEASE_DISJS         39 N K        2   98,864   

                APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2   98,864   

                APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   98,864   

                SYS        DUAL                              N K        2   63,466   

                APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036   

                APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174   

 

12/08/04 15     APPLSYS    FND_CONC_RELEASE_DISJS         39 N K        2   33,811   

                APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2    2,864   

                APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   32,864   

                SYS        DUAL                              N K        2   63,466   

                APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   12,033   

                APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174   


 

 


 

 

��  
 
 
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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.