How to identify full table scans?

From: <Govind.Arumugam_at_alltel.com>
Date: Mon, 13 Jan 2003 10:54:46 -0800
Message-ID: <F001.0052E0E5.20030113105446@fatcity.com>

List,

We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed.

Our intention is to run this query against X$BH every hour and gather this data. Do you have any suggestions or scripts to accomplish the same? Are there any issues in trying to do this every hour?

Thanks,
Govind

/* Recent full table scan */
/* Should be run as user SYS */

set serverout on size 1000000
set verify off
set pagesiz 300
set lin 120

col object_name form a30
col owner form a10

PROMPT Column flag in x$bh table is set to value 0x80000, when PROMPT block was read by a sequential scan.

spool recentfulltablescan.lst

SELECT count(o.object_name) "COUNT", o.object_name, o.object_type, o.owner, t.num_rows FROM dba_objects o,x$bh x, dba_tables t
WHERE x.obj=o.object_id
and o.object_name=t.table_name
-- AND o.object_type='TABLE'

AND standard.bitand(x.flag,524288)>0
AND o.owner<>'SYS'
group by o.object_name, o.object_type, o.owner, t.num_rows order by 1 ;

spool off

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <Govind.Arumugam_at_alltel.com
  INET: Govind.Arumugam_at_alltel.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jan 13 2003 - 12:54:46 CST