RE: How to identify full table scans?

[Govind.Arumugam]

Title: RE: How to identify full table scans?

This helps to identify the queries that could be tuned for LIO and/or PIO from a SQL Tuning perspective.  We can give this list to the development or application teams so that they could independently work off this list (hopefully!).

-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 2:22 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to identify full table scans?

Govind,

Just curious why you are attacking the full table scans.  I implemented something like this in the past utilizing Steve Adams' script expensive_sql.sql.  It was very telling and very very useful.

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 13, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Subject: How to identify full table scans?

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: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).