Object waits with event ..
col VALUE for 999999999999999999 with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b, dba_objects a where a.object_id=b.obj# and b.statistic_name='db block changes' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='segment scans' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='row lock waits' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='buffer busy waits' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads direct' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes direct' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='logical reads' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='ITL waits' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; OWNER OBJECT_NAME STATISTIC_NAME VALUE ------------------------- ------------------------------ ----------------------------------- ---------- ANUJ TAB1_IND db block changes 576 ANUJ TAB2 db block changes 128 ANUJ TAB1 db block changes 32 set lines 500 pagesize 300 col file# for 99999 col block# for 9999999999 col obj for a40 col type for a9 col lm for 99 col bsid for 9999 col kill for a15 col event for a30 select count(*) cnt, ''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' kill, --session_id sid, substr(event,1,30) event, mod(p1,16) as lm, sql_id, CURRENT_OBJ# || ' ' || object_name obj , o.object_type type , CURRENT_FILE# file# , CURRENT_BLOCK# block# , blocking_session bsid ,con_id from gv$active_session_history ash,dba_objects o where 1=1 and event like 'enq: T%' --and sample_time between to_timestamp('19.12.2021 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND TO_TIMESTAMP('19.12.2021 23:59:59', 'dd.mm.yyyy hh24:mi:ss') -- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second -- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY --and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '15' minute and o.object_id (+)= ash.current_obj# group by event,''''||ash.SESSION_ID ||','|| ash.SESSION_SERIAL#||',@'||ash.inst_id ||'''' ,p1,sql_id,current_obj#,object_name,object_type,current_file#, current_block#, blocking_session,con_id order by count(*) / define IF_COMMENT=' ' --define IF_COMMENT='--' set linesize 300 pagesize 300 col object_name for a30 col statistic_name for a35 col owner for a25col VALUE for 999999999999999999with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b, dba_objects a where a.object_id=b.obj# and b.statistic_name='db block changes' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='segment scans' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='row lock waits' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='buffer busy waits' order by desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads direct' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes direct' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='logical reads' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ; with temp as (select a.owner,a.object_name,b.statistic_name,b.value from gv$segstat b,dba_objects a where a.object_id=b.obj# and b.statistic_name='ITL waits' order by 4 desc) select * from temp where rownum < 30 &IF_COMMENT and owner not in ('AUDSYS','DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ;
| col owner format a15 | |
| col object format a30 | |
| col object_type format a10 | |
| col index_name format a30 | |
| col table_name format a30 | |
| col lockmode format a20 | |
| col event_name format a40 head 'EVENT NAME' | |
| col sql_id format a13 | |
| col instance_number format 9999 head 'INST' | |
| set line 200 trimspool on | |
| set pagesize 60 | |
| -- d_date_format set by get_date_range.sql | |
| with waits as ( | |
| select | |
| sh.inst_id instance_number | |
| , sh.blocking_inst_id | |
| , sh.sql_id | |
| , n.name event_name | |
| , chr(bitand(sh.p1,-16777216)/16777215)|| | |
| chr(bitand(sh.p1, 16711680)/65535) LOCKNAME | |
| , bitand(sh.p1,65535) lockmode | |
| , sh.current_obj# | |
| from gv$active_session_history sh | |
| join v$event_name n on sh.event_id = n.event_id | |
| where sh.blocking_inst_id is not null | |
| --and sh.event_id = ( select event_id from v$event_name where name like 'enq: TX - row lock contention') | |
| and sh.event_id in ( select event_id from v$event_name where name like 'enq:%') | |
| and sh.current_obj# is not null | |
| and sh.current_obj# > 0 | |
| ), | |
| itlwaits as ( | |
| select distinct | |
| w.instance_number | |
| , w.event_name | |
| , w.sql_id | |
| --, w.lockname | |
| , current_obj# | |
| , count(*) itl_wait_count | |
| from waits w | |
| where w.lockname = 'TX' | |
| and lockmode = 4 -- ITL | |
| -- in this case just interested in indexes | |
| --and w.event_name = 'enq: TX - index contention' | |
| group by w.instance_number, w.event_name, w.sql_id, current_obj# | |
| ) | |
| select | |
| w.instance_number | |
| , w.sql_id | |
| , w.itl_wait_count | |
| , decode(i.owner,null,t.owner,i.owner) owner | |
| , o.object_type | |
| , decode(i.index_name,null,t.table_name,i.index_name) object | |
| , decode(i.ini_trans,null,t.ini_trans,i.ini_trans) ini_trans | |
| , decode(i.max_trans,null,t.max_trans,i.max_trans) max_trans | |
| from itlwaits w | |
| join dba_objects o on o.object_id = w.current_obj# | |
| left outer join dba_indexes i on i.owner = o.owner | |
| and i.index_name = o.object_name | |
| left outer join dba_tables t on t.owner = o.owner | |
| and t.table_name = o.object_name | |
| order by w.itl_wait_count | |
| / |
===========
col SQL_PROCESS for a13
col "event(waits:requests:blocks)" for a40
col "waits%" for a7
col "reqs%" for a7
col "blocks%" for a7
col inst_id for 9999 hea #i
col PROGRAMS for a60 HEADING 'PROGRAMS BY TYPES '
col t0 for 999
col OBJECT_NAME for a35
with ash as (select * from gv$active_session_history where sample_time > sysdate - 1/24
)
select /*+ rule*/ * from (
select
inst_id,
object_name,
tablespace_name,
SUM(WAIT_COUNT),
to_char(RATIO_TO_REPORT(SUM(WAIT_COUNT)) OVER() * 100, '990.99') AS "waits%",
SUM(REQUESTS),
to_char(RATIO_TO_REPORT(SUM(REQUESTS)) OVER() * 100, '990.99') AS "reqs%",
SUM(BLOCKS),
to_char(RATIO_TO_REPORT(SUM(BLOCKS)) OVER() * 100, '990.99') AS "blocks%",
decode(nvl(upper('&&1'), 'BLOCKS')
, 'WAITS' , rtrim(xmlagg(xmlelement(s, '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by WAIT_COUNT desc), '; ')
, 'REQS' , rtrim(xmlagg(xmlelement(s, '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by REQUESTS desc), '; ')
, 'BLOCKS', rtrim(xmlagg(xmlelement(s, '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by BLOCKS desc), '; ')
, rtrim(xmlagg(xmlelement(s, '(' || WAIT_COUNT||':'|| REQUESTS ||':'|| BLOCKS, '); ').extract('//text()') order by BLOCKS desc), '; '))
as "event(waits:requests:blocks)"
from (select
inst_id,
object_name,
tablespace_name,
sum(WAIT_COUNT) as WAIT_COUNT,
sum(WAIT_COUNT * REQS_PER_WAIT) as REQUESTS,
sum(WAIT_COUNT * BLOCKS_PER_WAIT) as BLOCKS
from (select count(*) as WAIT_COUNT,
o.object_type||' '||o.owner||'.'||o.object_name||'.'||o.subobject_name as object_name,
NVL(f.tablespace_name, l.tablespace_name) as tablespace_name,
case when p2text = 'blocks' then p2
when p3text in ('blocks','block cnt') then p3
when p1text = 'requests' then p1
else 1
end as BLOCKS_PER_WAIT,
case when p3text='requests' then p3 when p1text='requests' then p1 else 1 end as REQS_PER_WAIT
,inst_id
from Gv$active_session_history --
left join dba_objects o on current_obj# = object_id
left join dba_data_files f on current_file# = file_id
left join dba_lobs l on l.owner = o.owner and l.segment_name = o.object_name
where
wait_class in ('User I/O','System I/O') and
session_state = 'WAITING'
and current_obj# > 0
group by o.object_type||' '||o.owner||'.'||o.object_name||'.'||o.subobject_name,
NVL(f.tablespace_name, l.tablespace_name),
case when p2text = 'blocks' then p2
when p3text in ('blocks','block cnt') then p3
when p1text = 'requests' then p1
else 1
end,
case when p3text='requests' then p3 when p1text='requests' then p1 else 1 end
,inst_id )
group by inst_id, object_name, tablespace_name)
group by
inst_id, object_name,
tablespace_name
order by 1, decode(nvl(upper('&&1'),'BLOCKS'), 'WAITS', SUM(WAIT_COUNT), 'REQS', SUM(REQUESTS), 'BLOCKS', SUM(BLOCKS), SUM(BLOCKS)) desc
) where rownum <= nvl('&2', 10)
/ #i OBJECT_NAME TABLESPACE_NAME SUM(WAIT_COUNT) waits% SUM(REQUESTS) reqs% SUM(BLOCKS) blocks% event(waits:requests:blocks)
----- ----------------------------------- ------------------------------ --------------- ------- ------------- ------- ----------- ------- ----------------------------------------
1 .. 60138 98.45 133258 99.30 482330 99.44 (60138:133258:482330)
1 TABLE SYS.SMON_SCN_TIME. SYSAUX 201 0.33 201 0.15 1047 0.22 (201:201:1047)
1 INDEX SYS.SYS_IOT_TOP_19184. SYSAUX 109 0.18 109 0.08 657 0.14 (109:109:657)
1 TABLE SYS.MAP_OBJECT. 254 0.42 255 0.19 254 0.05 (254:255:254)
1 TABLE SYS.FGA$. SYSTEM 51 0.08 51 0.04 146 0.03 (51:51:146)
1 INDEX SYS.I_RADM_CD2. 139 0.23 139 0.10 141 0.03 (139:139:141)
1 INDEX SYS.I_HH_OBJ#_INTCOL#. 1 0.00 1 0.00 128 0.03 (1:1:128)
1 TABLE SYS.WRI$_ADV_DEF_EXEC_TYPES. 1 0.00 1 0.00 126 0.03 (1:1:126)
1 LOB SYS.SYS_LOB0000010634C00004$$. SYSAUX 101 0.17 101 0.08 112 0.02 (101:101:112)
1 TABLE SYSTEM.LOGMNR_UID$. SYSAUX 45 0.07 45 0.03 45 0.01 (45:45:45)
SQL>
====
select 'LOGICAL_READS_DELTA' as metric_name from dual
union all
select 'BUFFER_BUSY_WAITS_DELTA' as metric_name from dual
union all
select 'DB_BLOCK_CHANGES_DELTA' as metric_name from dual
union all
select 'PHYSICAL_READS_DELTA' as metric_name from dual
union all
select 'PHYSICAL_WRITES_DELTA' as metric_name from dual
union all
select 'PHYSICAL_READS_DIRECT_DELTA' as metric_name from dual
union all
select 'PHYSICAL_WRITES_DIRECT_DELTA' as metric_name from dual
union all
select 'ITL_WAITS_DELTA' as metric_name from dual
union all
select 'ROW_LOCK_WAITS_DELTA' as metric_name from dual
union all
select 'GC_CR_BLOCKS_SERVED_DELTA' as metric_name from dual
union all
select 'GC_CU_BLOCKS_SERVED_DELTA' as metric_name from dual
union all
select 'GC_BUFFER_BUSY_DELTA' as metric_name from dual
union all
select 'GC_CR_BLOCKS_RECEIVED_DELTA' as metric_name from dual
union all
select 'GC_CU_BLOCKS_RECEIVED_DELTA' as metric_name from dual
union all
select 'TABLE_SCANS_DELTA' as metric_name from dual
union all
select 'CHAIN_ROW_EXCESS_DELTA' as metric_name from dual
union all
select 'PHYSICAL_READ_REQUESTS_DELTA' as metric_name from dual
union all
select 'PHYSICAL_WRITE_REQUESTS_DELTA' as metric_name from dual
union all
select 'OPTIMIZED_PHYSICAL_READS_DELTA' as metric_name from dual;
LOGICAL_READS_DELTA
BUFFER_BUSY_WAITS_DELTA
DB_BLOCK_CHANGES_DELTA
PHYSICAL_READS_DELTA
PHYSICAL_WRITES_DELTA
PHYSICAL_READS_DIRECT_DELTA
PHYSICAL_WRITES_DIRECT_DELTA
ITL_WAITS_DELTA
ROW_LOCK_WAITS_DELTA
GC_CR_BLOCKS_SERVED_DELTA
GC_CU_BLOCKS_SERVED_DELTA
GC_BUFFER_BUSY_DELTA
GC_CR_BLOCKS_RECEIVED_DELTA
GC_CU_BLOCKS_RECEIVED_DELTA
TABLE_SCANS_DELTA
CHAIN_ROW_EXCESS_DELTA
PHYSICAL_READ_REQUESTS_DELTA
PHYSICAL_WRITE_REQUESTS_DELTA
OPTIMIZED_PHYSICAL_READS_DELTA
19 rows selected.
set linesize 700 pagesize 500
VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;
define topn=10
define inst_no=1
define metric='LOGICAL_READS_DELTA' --- change the value
column owner format a15
column object_name format a35
column subobject_name format a30
SELECT owner,
object_name,
sm AS &metric,
round(sm*100/tot,0) AS "% &metric"
FROM
(SELECT owner,
object_name,
SUM(metric) AS sm,
tot
FROM
(SELECT owner,
object_name,
metric,
tot
FROM
(SELECT seg.obj#,
SUM(&metric) AS metric
FROM dba_hist_seg_stat SEG,
dba_hist_snapshot s
WHERE s.snap_id = seg.snap_id
AND s.instance_number = seg.instance_number
-- AND s.instance_number = &inst_no
AND s.snap_id >=:bid
AND s.snap_id <= :eid
GROUP BY obj#
) s,
(SELECT SUM(&metric) AS tot
FROM dba_hist_seg_stat SEG,
dba_hist_snapshot s
WHERE s.snap_id = seg.snap_id
AND s.instance_number = seg.instance_number
--AND s.instance_number = &inst_no
AND s.snap_id >=:bid
AND s.snap_id <= :eid
) t,
dba_objects
WHERE object_id = s.obj#
ORDER BY 3 DESC
)
GROUP BY owner,object_name, tot
ORDER BY 3 DESC, 1,2
)
WHERE rownum<=&topn;
