Object waits with event ..
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 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' ) ;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;
