Some nice things coming in 9.2...
V$SEGMENT_STATISTICS
V$SEGSTAT
V$SEGSTAT_NAME
hth
connor
- Richard Huntley <rhuntley_at_mindleaders.com> 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: hamcdc_at_yahoo.co.uk
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: 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 Thu Jul 25 2002 - 12:28:34 CDT
