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 numf 99999999999
set pagesize 400
col "space allocated" for 9999999999999999999999
select * from
(
select
DECODE
(GROUPING (a.object_name), 1, 'All Objects', a.object_name)
AS "Object",
sum (case when a.statistic_name = 'ITL waits' then a.value else null end) "ITL Waits",
sum(case when a.statistic_name = 'buffer busy waits' then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'row lock waits' then a.value else null end) "Row Lock Waits",
sum(case when a.statistic_name = 'physical reads' then a.value else null end) "Physical Reads",
sum(case when a.statistic_name = 'logical reads' then a.value else null end) "Logical Reads",
sum(case when a.statistic_name = 'db block changes' then a.value else null end) "db block changes",
sum(case when a.statistic_name = 'optimized physical reads' then a.value else null end) "optimized physical reads",
sum(case when a.statistic_name = 'segment scans' then a.value else null end) "segment scans",
sum(case when a.statistic_name = 'gc current blocks received' then a.value else null end) "gc current blocks received",
sum(case when a.statistic_name = 'physical reads direct' then a.value else null end) "physical reads direct",
sum(case when a.statistic_name = 'physical writes' then a.value else null end) "physical writes",
sum(case when a.statistic_name = 'physical write requests' then a.value else null end) "physical write requests",
sum(case when a.statistic_name = 'space allocated' then a.value else null end) "space allocated"
from v$segment_statistics a
where 1=1
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' )
-- a.owner like upper('xxxx')
group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0 or b."Row Lock Waits">0 or "Physical Reads">0 or "Logical Reads">0 or "db block changes" >0 or "optimized physical reads" >0 or "segment scans" >0 or "gc current blocks received">0
or "segment scans" >0 or "gc current blocks received" >0 or "physical reads direct" >0 or "physical reads direct">0 or "physical writes">0 or "physical write requests" >0 or "space allocated" >0
)
;
===
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;
======================
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'
col p1 format a10
col p2 format a10
col p3 format a10
col p1text format a25
col p2text format a30
col p3text format a25
set linesize 200 trimspool on
set pagesize 100
with waits as (
select
sh.instance_number
, sh.snap_id
, sh.sample_id
, sh.blocking_session
, sh.sql_id
, decode(bitand(sh.p1,65535),4,'ITL',6,'ROWLOCK','UNKNOWN') lockmode
, count(*) * 10 waitcount -- only sampled every 10 seconds from gv$active_session_history
from DBA_HIST_ACTIVE_SESS_HISTORY sh
join dba_hist_snapshot s on s.snap_id = sh.snap_id
and s.snap_id = sh.snap_id
and s.instance_number = sh.instance_number
where sh.blocking_session is not null
--and sh.event_id = ( select event_id from v$event_name where name = 'enq: TX - row lock contention')
and sh.event_id in ( select event_id from v$event_name where name like 'enq:%')
--and s.begin_interval_time between to_date('&&d_begin_date','&&d_date_format') and to_date('&&d_end_date','&&d_date_format')
and s.BEGIN_INTERVAL_TIME > sysdate -1/24
group by
sh.instance_number
, sh.snap_id
, sh.sample_id
, sh.blocking_session
, sh.sql_id
, decode(bitand(sh.p1,65535),4,'ITL',6,'ROWLOCK','UNKNOWN')
) ,
blockers as (
select distinct
w.sql_id
, w.snap_id
, w.sample_id
, w.blocking_session
, lockmode
--, waitcount
from waits w
where lockmode = 'ITL'
order by waitcount
)
select
h.sql_id
, to_char(h.p1,'0XXXXXXX') p1
, h.p1text
, to_char(h.p2,'0XXXXXXX') p2
, h.p2text
, to_char(h.p3,'0XXXXXXX') p3
, h.p3text
, h.CURRENT_OBJ#
, h.current_block#
from blockers b
join dba_hist_active_sess_history h
on h.snap_id = b.snap_id
--and h.sample_id = b.sample_id
and h.session_id = b.blocking_session
and h.sql_id = b.sql_id
order by h.sql_id, h.current_block#
/
