ORACLE WAIT INTERFACE

OWI-v7 script by Srinivas Maddali

(NOTE: This script is evolving continuously)

DROP TABLE system.session_event_history;

create table system.session_event_history
(
sid,
serial#,
username,
osuser,
paddr,
process,
logon_time,
type,
event,
total_waits,
total_timeouts,
time_waited,
average_wait,
max_wait,
logoff_timestamp
)
as
select a.sid,
b.serial#,
b.username,
b.osuser,
b.paddr,
b.process,
b.logon_time,
b.type,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
sysdate logoff_time
from v$session_event a,
v$session b
where a.sid=b.sid
and b.username=login_user
and b.sid=(select sid from v$mystat where rownum <2> and 1=2
/

TRUNCATE TABLE system.session_event_history;

DROP table system.session_sql_detail;

create table system.session_sql_detail
as
select a.*,
b.sql_text
from v$session a,
v$sql b
where a.sql_hash_value=b.hash_value
and a.sid=(select sid from v$mystat where rownum <2> and 1=2
/
TRUNCATE TABLE system.session_sql_detail;
DROP TABLE system.sess_stat_histroy;

create table system.sess_stat_histroy
as
select c.username username,
c.osuser osuser,
a.sid sid,
c.serial# serial#,
c.process process,
c.logon_time,
a.statistic#,
b.name,
a.value,
sysdate logoff_time
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and c.sid =(select sid from v$mystat where rownum <2> and c.username=user
and 1=2
/

TRUNCATE TABLE system.sess_stat_histroy;
DROP TABLE system.session_wait_history;

create table system.session_wait_history
as
select SID
,SEQ#
,EVENT
,P1TEXT
,P1
,P1RAW
,P2TEXT
,P2
,P2RAW
,P3TEXT
,P3
,P3RAW
,WAIT_TIME
,SECONDS_IN_WAIT
,STATE
,sysdate logoff_time
from v$session_wait
WHERE 1=2
/

TRUNCATE TABLE system.session_wait_history;
DROP TABLE SYSTEM.SESS_READS_TEMP_N_DATA_FILES;
CREATE table SYSTEM.SESS_READS_TEMP_N_DATA_FILES
AS
SELECT *
FROM
(SELECT a.event,
a.sid,
c.sql_hash_value,
DECODE(d.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) SEGMENT_TYPE,
b.tablespace_name,
b.file_name
FROM v$session_wait a,
dba_data_files b,
v$session c,
sys.x$ktsso d
WHERE c.saddr = d.ktssoses(+)
AND c.serial# = d.ktssosno(+)
AND d.inst_id(+)=userenv('instance')
AND a.sid=c.sid
AND a.p1=b.file_id
AND a.event='direct path read'
UNION ALL
SELECT a.event,
a.sid,
d.sql_hash_value,
DECODE(e.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) SEGMENT_TYPE,
b.tablespace_name,
b.file_name
FROM V$SESSION_WAIT A,
DBA_TEMP_FILES B,
V$PARAMETER C,
V$SESSION D,
SYS.x$ktsso e
WHERE D.SADDR = E.KTSSOSES(+)
AND d.serial#=E.ktssosno(+)
and e.inst_id(+)=userenv('instance')
AND a.sid=d.sid
AND b.file_id=a.p1 - c.value
AND a.event='direct path read'
ORDER BY 1,2)
WHERE 1=2
/

TRUNCATE TABLE SYSTEM.READS_TEMP_N_DATA_FILES;
GRANT SELECT ON SYS.v_$session TO SYSTEM;
GRANT SELECT ON SYS.v_$lock TO SYSTEM;
GRANT SELECT ON SYS.v_$resource TO SYSTEM;
GRANT SELECT ON SYS.v_$_lock TO SYSTEM;
GRANT SELECT ON SYS.obj$ TO SYSTEM;

DROP TABLE SYSTEM.SESS_BLOCKER_SESSIONS;

CREATE table SYSTEM.SESS_BLOCKER_SESSIONS
AS
SELECT /*+ ORDERED */
s.username,
s.SID,
s.serial#,
s.SID || ':' || s.serial# sessionid,
s.program,
s.osuser,
s.machine,
u.NAME || '.' || o.NAME USER_OBJECT,
DECODE (x.request,0, ' ',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6, 'Exclusive') request_mode,
DECODE (x.lmode,0, ' ',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6, 'Exclusive') held_mode,
l.TYPE,
l.ctime,
o.obj#,
DECODE (o.type#,0, 'NEXT OBJECT',1, 'INDEX',2, 'TABLE',3, 'CLUSTER',4, 'VIEW',5, 'SYNONYM',6, 'SEQUENCE',7, 'PROCEDURE',8, 'FUNCTION',9, 'PACKAGE', 11, 'PACKAGE BODY',12, 'TRIGGER',13, 'TYPE',14, 'TYPE BODY',19, 'TABLE PARTITION',20, 'INDEX PARTITION',21, 'LOB',22, 'LIBRARY',23, 'DIRECTORY','UNDEFINED') obj_type,
'Black' Black,
l.id1,
l.id2,
s.lockwait
FROM SYS.v_$session s,
SYS.v_$lock l,
SYS.v_$resource r,
SYS.v_$_lock x,
SYS.obj$ o,
SYS.user$ u
WHERE s.SID = l.SID
AND l.addr = x.laddr(+)
AND l.TYPE = 'TM'
AND x.raddr = r.addr(+)
AND o.obj#(+) = l.id1
AND u.user#(+) = o.owner#
ORDER BY 16, 17, 18 DESC
/

TRUNCATE TABLE SYSTEM.BLOCKER_SESSIONS
/
CREATE OR REPLACE TRIGGER SYS.LOGOFF_TRIG BEFORE
LOGOFF ON DATABASE declare
logoff_sid pls_integer;
logoff_time date := sysdate;
begin
select sid
into logoff_sid
from v$mystat
where rownum <2> dbms_output.put_line('This is the logoff sid '||logoff_sid);

insert into system.session_event_history
select a.sid,
b.serial#,
b.username,
b.osuser,
b.paddr,
b.process,
b.logon_time,
b.type,
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
sysdate logoff_time
from v$session_event a,
v$session b
where a.sid=b.sid
and b.username=login_user;

insert into system.sess_stat_histroy
select c.username username,
c.osuser osuser,
a.sid sid,
c.serial# serial#,
c.process process,
c.logon_time,
a.statistic#,
b.name,
a.value,
sysdate logoff_time
from v$sesstat a,
v$statname b,
v$session c
where a.statistic# = b.statistic#
and a.sid = c.sid
and c.sid =logoff_sid
and c.username=login_user
and a.sid=logoff_sid;

insert into system.session_sql_detail
select a.*,
b.sql_text
from v$session a,
v$sql b
where a.sql_hash_value=b.hash_value
and a.sid=logoff_sid;

insert into system.session_wait_history
select SID
,SEQ#
,EVENT
,P1TEXT
,P1
,P1RAW
,P2TEXT
,P2
,P2RAW
,P3TEXT
,P3
,P3RAW
,WAIT_TIME
,SECONDS_IN_WAIT
,STATE
,sysdate
from v$session_wait
where sid=logoff_sid;

insert into SYSTEM.SESS_READS_TEMP_N_DATA_FILES
SELECT a.event,
a.sid,
c.sql_hash_value,
DECODE(d.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) SEGMENT_TYPE,
b.tablespace_name,
b.file_name
FROM v$session_wait a,
dba_data_files b,
v$session c,
sys.x$ktsso d
WHERE c.saddr = d.ktssoses(+)
AND c.serial# = d.ktssosno(+)
AND d.inst_id(+)=userenv('instance')
AND a.sid=c.sid
AND a.p1=b.file_id
AND a.event='direct path read'
and a.sid=logoff_sid
UNION ALL
SELECT a.event,
a.sid,
d.sql_hash_value,
DECODE(e.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) SEGMENT_TYPE,
b.tablespace_name,
b.file_name
FROM V$SESSION_WAIT A,
DBA_TEMP_FILES B,
V$PARAMETER C,
V$SESSION D,
SYS.x$ktsso e
WHERE D.SADDR = E.KTSSOSES(+)
AND d.serial#=E.ktssosno(+)
and e.inst_id(+)=userenv('instance')
AND a.sid=d.sid
AND b.file_id=a.p1 - c.value
AND a.event='direct path read'
and a.sid=logoff_sid
ORDER BY 1,2
;

INSERT INTO SYSTEM.SESS_BLOCKER_SESSIONS
SELECT /*+ ORDERED */
s.username,
s.SID,
s.serial#,
s.SID || ':' || s.serial# sessionid,
s.program,
s.osuser,
s.machine,
u.NAME || '.' || o.NAME USER_OBJECT,
DECODE (x.request,0, ' ',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6, 'Exclusive') request_mode,
DECODE (x.lmode,0, ' ',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6, 'Exclusive') held_mode,
l.TYPE,
l.ctime,
o.obj#,
DECODE (o.type#,0, 'NEXT OBJECT',1, 'INDEX',2, 'TABLE',3, 'CLUSTER',4, 'VIEW',5, 'SYNONYM',6, 'SEQUENCE',7, 'PROCEDURE',8, 'FUNCTION',9, 'PACKAGE', 11, 'PACKAGE BODY',12, 'TRIGGER',13, 'TYPE',14, 'TYPE BODY',19, 'TABLE PARTITION',20, 'INDEX PARTITION',21, 'LOB',22, 'LIBRARY',23, 'DIRECTORY','UNDEFINED') obj_type,
'BLACK' Black,
l.id1,
l.id2,
s.lockwait
FROM SYS.v_$session s,
SYS.v_$lock l,
SYS.v_$resource r,
SYS.v_$_lock x,
SYS.obj$ o,
SYS.user$ u
WHERE s.SID = l.SID
AND l.addr = x.laddr(+)
AND l.TYPE = 'TM'
AND x.raddr = r.addr(+)
AND o.obj#(+) = l.id1
AND u.user#(+) = o.owner#
ORDER BY 16, 17, 18 DESC
;
end;
/
GRANT SELECT ON V_$SESSION TO SYSTEM;
GRANT SELECT ON V_$SESSTAT TO SYSTEM;
GRANT SELECT ON V_$STATNAME TO SYSTEM;
GRANT SELECT ON SYS.V_$SQL_PLAN TO SYSTEM;
GRANT SELECT ON SYS.DBA_EXTENTS TO SYSTEM;

CREATE OR REPLACE VIEW SYSTEM.VW_INDEX_ROWID_SCAN
AS
SELECT owner,
segment_name,
partition_name,
EVENT
FROM dba_extents,
system.session_wait_history
WHERE P2 between BLOCK_ID and (BLOCK_ID + blocks -1)
AND FILE_ID=P1
/

CREATE OR REPLACE VIEW SYSTEM.VW_SQL_EXEC_PLAN
AS
SELECT hash_value,
child_number,
lpad(' ',2*depth)||operation||' '||options||decode(id, 0,substr(optimizer,1,6)||' Cost='||to_char(cost)) operation,
object_name,
cost,
cardinality,
round(bytes/1024) KBytes
FROM SYS.V_$SQL_PLAN
WHERE hash_value in (select sql_hash_value from system.session_sql_detail)
/

CREATE OR REPLACE VIEW SYSTEM.VW_PHYSICAL_READS_DIRECT
AS
SELECT A.NAME,
B.SID,
B.VALUE,
ROUND((SYSDATE - C.LOGON_TIME)* 24)Hours_connected
FROM sys.V_$STATNAME A,
sys.V_$SESSTAT B,
sys.V_$SESSION C
WHERE B.SID=C.SID
AND A.STATISTIC#=B.STATISTIC#
AND B.VALUE > 0
AND A.NAME = 'physical reads direct'
order by b.value
/
create table dba_waiter_sess_history
as
select a.*
from dba_waiters
/

drop view SYSTEM.SQL_THAT_NEED_REVIST_VW
/

CREATE OR REPLACE VIEW SYSTEM.SQL_THAT_NEED_REVISIT_VW
AS
SELECT distinct a.sid
,a.username
,to_char(a.logon_time,'mm-dd-yyyy hh24:mi:ss') logon_time
,to_char(a.logoff_timestamp,'mm-dd-yyyy hh24:mi:ss') logoff_timestamp
,a.event
,a.time_waited
,round((a.logoff_timestamp - a.logon_time)*24*60,2) minutes_connected
,c.p1
,c.p2
,c.p3
FROM system.session_event_history a
,(
select sid
,event
,sum(time_waited) sum_time_waited
from system.session_event_history
where event = 'db file sequential read'
having sum(time_waited) > 0
group by sid
,event
) b
,system.session_wait_history c
WHERE a.sid=b.sid
AND b.sid=c.sid
AND a.event=b.event
AND b.event=c.event
AND a.logoff_timestamp =c.logoff_time
AND a.time_waited > 0
AND c.event = 'db file sequential read'
AND A.USERNAME not in ('SYSTEM','SYS')
AND USERNAME IS NOT NULL
ORDER BY minutes_connected
/