Object waits with event ..
define IF_COMMENT=' ' --define IF_COMMENT='--'set linesize 300 pagesize 300 numf 9999999999999999999999 col object_name for a50 col statistic_name for a35 col owner for a25col 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 a25
col 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;
No comments:
Post a Comment