Some nice things coming in 9.2... V$SEGMENT_STATISTICS V$SEGSTAT V$SEGSTAT_NAME
hth connor --- Richard Huntley <[EMAIL PROTECTED]> wrote: > FROM METALINK: > > some quick starters: > > select disk_reads, sql_text > from v$sqlarea > where disk_reads > 10000 > order by disk_reads desc; > > select buffer_gets, sql_text > from v$sqlarea > where buffer_gets > 200000 > order by buffer_gets desc; > > Also, look at the following links: > > http://www.jlcomp.demon.co.uk/ > http://www.jlcomp.demon.co.uk/sqlarea.html > <http://www.jlcomp.demon.co.uk/sqlarea.html> > > HTH > > > -----Original Message----- > Sent: Thursday, July 25, 2002 8:48 AM > To: Multiple recipients of list ORACLE-L > > > How is everyone identifying hot tables? In my tool > box I have the following > two scripts but I am looking for other options. How > do you track frequently > accessed tables? > > Script #1 - Author unknown: > select obj_name, > sum(decode(action_name,'SELECT',1,0)) sel > ,sum(decode(action_name,'INSERT',1,0)) inserts, > > sum(decode(action_name,'UPDATE',1,0)) updates, > > sum(decode(action_name,'DELETE',1,0)) deletes > from dba_audit_trail > having sum(decode(action_name,'SELECT' , 1, > 'INSERT',1, > 'DELETE',1,'UPDATE',1,0)) > 0 > group by obj_name; > > Script #2 - Dave Ensor (BMC): > set verify off > col CTYP heading 'Command Type' > col OBJ format a32 wrap heading 'Table' > col EXES format 999,990 heading 'Execs' > col GETS format 99,999,990 heading 'Buff Gets' > col ROWP format 99,999,990 heading 'Rows Proc' > > select CTYP > , OBJ > , 0 - EXEM EXES > , GETS > , ROWP > from (select distinct EXEM, CTYP, OBJ, GETS, ROWP > from ( select decode (S.COMMAND_TYPE > , 2, 'Insert into > ' > , 3, 'Select from > ' > , 6, 'Update of > ' > , 7, 'Delete from > ' > , 26, 'Lock of > ') CTYP > , O.OWNER || '.' || O.NAME > OBJ > , sum(0 - S.EXECUTIONS) > EXEM > , sum(S.BUFFER_GETS) > GETS > , sum(S.ROWS_PROCESSED) > ROWP > from V$SQL S > , V$OBJECT_DEPENDENCY D > , V$DB_OBJECT_CACHE O > where S.COMMAND_TYPE in > (2,3,6,7,26) > and D.FROM_ADDRESS = S.ADDRESS > and D.TO_OWNER = O.OWNER > and D.TO_NAME = O.NAME > and O.TYPE = 'TABLE' > group by S.COMMAND_TYPE > , O.OWNER > , O.NAME ) ) > where ROWNUM <= &1 > / > > > > > > ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
