Hi Govind, Just a thought: The value of _small_table_threshold - which is currently 2% of the DB_BLOCK_BUFFERS. Any table undergoing FTS will be placed at the MRU end rather than the LRU end and would thus live longer (and either cause problems or alleviate it as the case may be!). Keep this in mind if you are looking at FTS... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002
What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -----Original Message----- Sent: Monday, January 13, 2003 2:24 PM To: Multiple recipients of list ORACLE-L 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----- <mailto:[EMAIL PROTECTED]> ] 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 <http://www.orafaq.net> -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com <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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
