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 a25
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
col OWNER for a15
col OBJECT_NAME for a25
col SUBOBJECT_NAME for a15
col OBJECT_TYPE for a15
col TABLESPACE_NAME for a15
col reads for a8
col logical_reads for a8
col physical_reads for a8
col physical_reads for a14
col segment_scans for a14
col writes for a8
select vss.owner,
vss.object_name,
vss.subobject_name,
vss.object_type ,
vss.tablespace_name ,
dbms_xplan.format_size(sum(case statistic_name when 'logical reads' then value else 0 end + case statistic_name when 'physical reads' then value else 0 end)) as reads ,
dbms_xplan.format_size(sum(case statistic_name when 'logical reads' then value else 0 end)) as logical_reads ,
dbms_xplan.format_size(sum(case statistic_name when 'physical reads' then value else 0 end)) as physical_reads ,
dbms_xplan.format_size(sum(case statistic_name when 'segment scans' then value else 0 end)) as segment_scans ,
dbms_xplan.format_size(sum(case statistic_name when 'physical writes' then value else 0 end)) as writes
from v$segment_statistics vss
where vss.owner not in ('SYS', 'SYSTEM')
--and vss.object_type in ('TABLE', 'INDEX')
and vss.owner in ('XXXX')
group by vss.owner,
vss.object_name ,
vss.object_type ,
vss.subobject_name ,
vss.tablespace_name
order by reads desc;
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(*)
/
==========
ALTER SESSION SET nls_timestamp_format='DD-MON-RR HH24:MI';
set linesize 300 pagesize 300
col OBJECT_NAME for a20
col BEGIN_INTERVAL_TIME for a27
col SUBOBJECT_NAME for a25
col OWNER for a15
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
SELECT P.snap_id,
P.begin_interval_time,
O.owner,
O.object_name,
O.subobject_name,
O.object_type,
S.row_lock_waits_delta
FROM dba_hist_seg_stat S, dba_hist_seg_stat_obj O, dba_hist_snapshot P
WHERE S.dbid =O.dbid
AND S.ts# =O.ts#
AND S.obj# =O.obj#
AND S.dataobj# =O.dataobj#
AND S.snap_id =P.snap_id
AND S.dbid =P.dbid
AND S.instance_number =P.instance_number
AND S.row_lock_waits_delta > 0
AND P.snap_id BETWEEN :BgnSnap AND :EndSnap
ORDER BY 1,3,4;
====
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 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 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' )
;
ALTER SESSION SET nls_timestamp_format='DD-MON-RR HH24:MI';
set linesize 300
col OBJECT_NAME for a25
col OWNER for a20
col BEGIN_INTERVAL_TIME for a27
col SUBOBJECT_NAME for a20
col snap_id new_value v_snap_id
select max(snap_id) as snap_id from dba_hist_snapshot;
SELECT P.snap_id,
P.begin_interval_time,
O.owner,
O.object_name,
O.subobject_name,
O.object_type,
S.row_lock_waits_delta
FROM dba_hist_seg_stat S,
dba_hist_seg_stat_obj O,
dba_hist_snapshot P
WHERE S.dbid =O.dbid
AND S.ts# =O.ts#
AND S.obj# =O.obj#
AND S.dataobj# =O.dataobj#
AND S.snap_id =P.snap_id
AND S.dbid =P.dbid
AND S.instance_number =P.instance_number
AND S.row_lock_waits_delta > 0
--AND P.snap_id BETWEEN <begin_snap> AND <end_snap>
and s.snap_id between &v_snap_id-2 and &v_snap_id
and OWNER='SYS'
ORDER BY 1,3,4;
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 linesize 300
col "space allocated" for 9999999999999999999999
col "Object" for a25
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 long 5000 pagesize 0
select DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner) from DBA_INDEXES u where table_name='LOGIN';
select DBMS_METADATA.GET_DDL('TABLE',u.TABLE_NAME,u.owner) from DBA_INDEXES u where table_name='LOGIN';
set pagesize 300 linesize 300
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
col INDEX_TYPE for a12
col OWNER for a12
col INDEX_NAME for a30
col TABLE_NAME for a25
select i.table_name,i.owner,i.INDEX_NAME,i.INDEX_TYPE,i.INI_TRANS,i.MAX_TRANS,i.PCT_FREE,i.LAST_ANALYZED,t.INI_TRANS tab_INI_TRANS,t.MAX_TRANS tab_MAX_TRANS ,t.PCT_FREE tab_PCT_FREE from dba_indexes i,dba_tables t
where 1=1
--and t.table_name in ('XXX')
-- and t.table_name in ('STOCK')
--and i.TABLE_NAME like '%VVVV%'
and i.owner ='XXX'
and i.table_name=t.table_name
--and i.INDEX_NAME='SYSCCCC'
--and i.INI_TRANS>2
;
=======================
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#
/
set linesize 200 trimspool on
set pagesize 100
col owner format a30 head 'Owner'
col segment_type format a7 head 'Segment|Type'
col ini_trans format 999 head 'ITL'
col ini_trans_count format 99,999 head 'ITL|Count'
break on owner skip 1 on segment_type
with data as (
select owner, 'Table' segment_type, nvl(ini_trans,0) ini_trans
from dba_tables
union all
select table_owner, 'TabPart' segment_type, nvl(ini_trans,0) ini_trans
from dba_tab_partitions
union all
select table_owner, 'TabSubPart' segment_type, nvl(ini_trans,0) ini_trans
from dba_tab_subpartitions
union all
select owner, 'Index' segment_type, nvl(ini_trans,0) ini_trans
from dba_indexes
union all
select index_owner, 'IndPart' segment_type, nvl(ini_trans,0) ini_trans
from dba_ind_partitions
union all
select index_owner, 'IndSubPart' segment_type, nvl(ini_trans,0) ini_trans
from dba_ind_subpartitions
)
select owner, segment_type, ini_trans, count(*) ini_trans_count
from data d
join dba_users u on u.username = d.owner
and u.oracle_maintained = 'N'
group by owner, segment_type, ini_trans
order by owner, segment_type, ini_trans;
====
from https://github.com/jkstill/oracle-script-lib/blob/master/sql/ash-itl-waits.sql
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
clear break
break on instance_number skip 1 on sql_id skip 1
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#
, sh.current_file#
, sh.current_block#
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#
, current_file#
, current_block#
, 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#, current_file#, current_block#
)
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
, current_file#
, current_block#
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.sql_id, w.itl_wait_count
/
define statistic_name='db block changes'
set lines 300 pages 100
col owner format A18
col object_name format A30
col statistic_name format A30
col object_type format A18
col value format 99999999999
col perc format 99.99
undef statistic_name
break on statistic_name
select * from (
with segstats as (
select * from (
select inst_id, owner, object_name, object_type , value ,
rank() over (partition by inst_id, statistic_name order by value desc ) rnk , statistic_name
from gv$segment_statistics
where value >0
--and statistic_name like '%'||'&&statistic_name' ||'%'
--and STATISTIC_NAME is not null
) where rnk <31
) ,
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id)
select a.inst_id, a.statistic_name, a.owner, a.object_name, a.object_type,a.value,(a.value/b.sum_value)*100 perc
from segstats a , sumstats b
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
--and a.STATISTIC_NAME is not null
order by a.statistic_name, a.value desc
)
where 1=1
and STATISTIC_NAME is not null
***************
--- cdb view **************************
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 pagesize 60
clear break
break on instance_number skip 1 on sql_id skip 1
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#
, sh.current_file#
, sh.current_block#
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#
, current_file#
, current_block#
, count(*) itl_wait_count
from waits w
where 1=1
-- and 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#, current_file#, current_block#
)
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
, current_file#
, current_block#
from itlwaits w
join cdb_objects o on o.object_id = w.current_obj#
left outer join cdb_indexes i on i.owner = o.owner
and i.index_name = o.object_name
left outer join cdb_tables t on t.owner = o.owner
and t.table_name = o.object_name
order by w.sql_id, w.itl_wait_count
/
