Re: Finding full-table scans?

From: Don Burleson <don_at_burleson.cc>
Date: 5 Apr 2003 03:39:42 -0800
Message-ID: <998d28f7.0304050339.1d06c62a@posting.google.com>

Here is the script that I use to locate FTS.

A also have lot's more SQL scripts in my latest book, Creating a self-tuning Oracle Database.

http://www.dba-oracle.com/bp/bp_book3_oracle9i_sga.htm

Hope this helps . .

--**************************************************************

• Object Access script report
  --
  
• © 2003 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
;

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; Received on Sat Apr 05 2003 - 05:39:42 CST