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