Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to identify full table scans?

RE: How to identify full table scans?

From: <Govind.Arumugam_at_alltel.com>
Date: Mon, 13 Jan 2003 14:24:15 -0800
Message-ID: <F001.0052E53D.20030113142415@fatcity.com>


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-----
Sent: Monday, January 13, 2003 2:22 PM
To: Multiple recipients of list ORACLE-L

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-----
Sent: Monday, January 13, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L

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).

--

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 - 16:24:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US