stored procedure 收集session wait 資訊

包括物件如下：

event_seq: 序列，用於唯一標識每一條記錄

dc_event_data: 表，用於儲存session wait資訊

dc_collect:儲存過程，用於定期收集各個session wait資訊，並儲存到dc_event_data表中。根據建議，頻率可保持在一分鐘一次，而不影響系統效能。

dc_analyze: stored procedure. 用於分析和具體化表dc_event_data中的記錄

--想法來源於<>

------------------------------------------
--SEQUENCE:
-- event_seq 這個序列其實不是很有必要，先留著，用於標識每一條記錄
--USAGE:
-- As a unique ID for each collection
------------------------------------------
drop sequence event_seq
/

create sequence event_seq
start with 1
increment by 1
nocache
nocycle
nomaxvalue;

------------------------------------------
--TABLE:
-- DC_EVENT_DATA 用於儲存各SESSION的效能資訊
--USAGE:
-- To keep data collected by
-- procedure DC
------------------------------------------
drop table dc_event_data
/

create table DC_EVENT_DATA
(
event_seq number,
curr_time date,
sid number,
seq# number,
event varchar2(64),
p1text varchar2(64),
p1 number,
p1raw raw(8),
p2text varchar2(64),
p2 number,
p2raw raw(8),
p3text varchar2(64),
p3 number,
p3raw raw(8),
wait_time number,
seconds_in_wait number,
state varchar2(19),
serial# number,
username varchar2(30),
osuser varchar2(30),
paddr raw(8),
logon_time date,
process varchar2(12),
sql_hash_value number,
saddr raw(8),
module varchar2(48),
row_wait_obj# number,
row_wait_file# number,
row_wait_block# number,
row_wait_row# number,
sql_text varchar2(64)
);

------------------------------------------
--PROCEDURE:
-- DC_COLLECT 在某個時間點，檢索V$SESSION_WAIT，收集需要關注的等待事件的session引數，將其儲存至表DC_EVENT_DATA
--USAGE:
-- Collect performance data and keep
-- them in the tables as history data
------------------------------------------
drop procedure dc_collect
/

create or replace procedure DC_COLLECT
as
cursor current_event is
select a.sid sid,
a.seq# seq#,
a.event event,
a.p1text p1text,
a.p1 p1,
a.p1raw p1raw,
a.p2text p2text,
a.p2 p2,
a.p2raw p2raw,
a.p3text p3text,
a.p3 p3,
a.p3raw p3raw,
a.wait_time wait_time,
a.seconds_in_wait seconds_in_wait,
a.state state,
b.serial# serial#,
b.username username,
b.osuser osuser,
b.paddr paddr,
b.logon_time logon_time,
b.process process,
b.sql_hash_value sql_hash_value,
b.saddr saddr,
b.module module,
b.row_wait_obj# row_wait_obj#,
b.row_wait_file# row_wait_file#,
b.row_wait_block# row_wait_block#,
b.row_wait_row# row_wait_row#,
c.sql_text sql_text
from v$session_wait a,
v$session b,
v$sqltext c
where a.sid = b.sid
and b.sql_hash_value=c.hash_value
and b.username is not null
and b.type <> 'BACKGROUND'
and a.event in (
'db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits');
begin
for c1 in current_event loop
insert into DC_EVENT_DATA
values(
event_seq.nextval,
sysdate,
c1.sid,
c1.seq#,
c1.event,
c1.p1text,
c1.p1,
c1.p1raw,
c1.p2text,
c1.p2,
c1.p2raw,
c1.p3text,
c1.p3,
c1.p3raw,
c1.wait_time,
c1.seconds_in_wait,
c1.state,
c1.serial#,
c1.username,
c1.osuser,
c1.paddr,
c1.logon_time,
c1.process,
c1.sql_hash_value,
c1.saddr,
c1.module,
c1.row_wait_obj#,
c1.row_wait_file#,
c1.row_wait_block#,
c1.row_wait_row#,
c1.sql_text);
commit;
end loop;
end;
/

------------------------------------------
--PROCEDURE:
-- DC_ANALYZE 對收集到的資訊，根據sid,開始和結束時間，選取相關紀錄，並將相關資訊具體化。本儲存過程對部分wait event的處理有待細化
--USAGE:
-- Analyze performance data
--ATTENTION:
-- input parameters start_time and
-- end_time must in format
-- 'yyyy-mm-dd hh:mi:ss am'
------------------------------------------
drop procedure dc_analyze
/

create or replace procedure DC_ANALYZE
(start_time varchar2, end_time varchar2, sid_no number)
as
cursor current_event is
select * from dc_event_data
where sid=sid_no
and curr_time>=to_date(start_time,'yyyy-mm-dd hh:mi:ss am')
and curr_time<=to_date(end_time,'yyyy-mm-dd hh:mi:ss am');

v_obj_name varchar2(128);
begin
dbms_output.put_line('USERNAME SID DATE_TIME EVENT OBJECT_NAME HASH_VALUE');
dbms_output.put_line('--------------- --- ---------------------- ------------------------------ --------------- ----------');

for c1 in current_event loop
if c1.event='db file sequential read' or c1.event='db file scattered read' then
select a.object_name into v_obj_name from dba_objects a, sys.x$bh b
where (a.object_id=b.obj or a.data_object_id=b.obj)
and b.file#=c1.p1
and b.dbablk=c1.p2;
elsif c1.event='direct path read' or c1.event='direct path write' or c1.event='buffer busy waits' then
select segment_name into v_obj_name from dba_extents
where c1.p2 between block_id and (block_id+blocks-1)
and file_id=c1.p1;
elsif c1.event='latch free' then
select name into v_obj_name from v$latchname where latch#=c1.p2;
elsif c1.event='' then
select object_name into v_obj_name from dba_objects where object_id=c1.row_wait_obj#;
else
v_obj_name := 'other events';
end if;

dbms_output.put_line(rpad(c1.username,15,' ')||' '||rpad(to_char(c1.sid),3,' ')||' '||to_char(c1.curr_time,'yyyymmdd hh:mi:ss am')||' '||rpad(c1.event,30,' ')||' '||rpad(v_obj_name,15,' ')||' '||to_char(c1.sql_hash_value));
end loop;
end;
/