用数据库注销触发器方法收集历史性能数据还缺少SQL语句的存储。如果能够以规则的间隔对每一个连接到实例的前台进程进行采样,并将数据写入存储表中,则将拥有每一个进程自开始至完成期间在数据库中所做事情的历史记录。我们称这种性能数据收集器为DC。该任务的最佳数据源是V$SESSION_WAIT视图。DBA有选择高采样频率的倾向,但通常一分钟比较合适。若将该采样与前面讨论的数据库注销触发器相补充,则既可获得汇总等待事件,又可获得所有会话的详细的一分钟接一分钟的等待事件和SQL语句。
创建表的脚本:
create table session_snap_history
(
sid NUMBER,
serial# NUMBER,
seq# NUMBER,
event VARCHAR2(64),
logon_time DATE,
sql_hash_value NUMBER,
sql_id VARCHAR2(13),
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),
username VARCHAR2(30),
osuser VARCHAR2(30),
paddr RAW(4),
process VARCHAR2(24),
saddr RAW(4),
module VARCHAR2(48),
row_wait_obj# NUMBER,
row_wait_file# NUMBER,
row_wait_block# NUMBER,
row_wait_row# NUMBER,
target_obj VARCHAR2(2000),
sql_text BLOB,
disk_reads NUMBER,
buffer_gets NUMBER
) tablespace users;
采样存过:
/*
* Remark:This is an incomplete procedure.
*/
create or replace procedure DC is
cursor current_event is
select s.SID,
s.SERIAL#,
s.SEQ#,
s.EVENT,
s.LOGON_TIME,
s.SQL_HASH_VALUE,
s.SQL_ID,
P1TEXT,
s.P1,
s.P1RAW,
s.P2TEXT,
s.P2,
s.P2RAW,
s.P3TEXT,
s.P3,
s.P3RAW,
s.WAIT_TIME,
s.SECONDS_IN_WAIT,
s.STATE,
s.USERNAME,
s.OSUSER,
s.PADDR,
s.PROCESS,
s.SADDR,
s.MODULE,
s.ROW_WAIT_OBJ#,
s.ROW_WAIT_FILE#,
s.ROW_WAIT_BLOCK#,
s.ROW_WAIT_ROW#
from sys.v$session s
where s.USERNAME is not null
and s.TYPE <> 'BACKGROUND'
and s.EVENT in ('db file sequential read', --想要采样的event可以添加
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits');
current_event_record current_event%rowtype;
v_target_obj varchar2(2000); --属于等待事件的数据库对象名称
v_sql_text blob; --SQL语句
v_disk_reads number; --所有子游标运行这条语句导致的读磁盘次数
v_buffer_gets number; --所有子游标运行这条语句导致的读内存次数
v_sql_hash_value varchar2(200);
v_sql_id varchar2(200);
begin
open current_event;
loop
fetch current_event
into current_event_record;
exit when current_event%notfound;
/*if后面加elsif来分别判断每个event,取出v_target_obj。*/
if current_event_record.event = 'db file sequential read' or
current_event_record.event = 'db file scattered read' then
v_sql_hash_value := current_event_record.sql_hash_value;
v_sql_id := current_event_record.sql_id;
select 'segment_name:' || t.segment_name || ';' || 'partition_name:' ||
t.partition_name
into v_target_obj
from dba_extents t
where current_event_record.p2 between t.BLOCK_ID and
(t.BLOCK_ID + t.BLOCKS - 1)
and t.FILE_ID = current_event_record.p1;
end if;
select t.SQL_TEXT --取出SQL语句
into v_sql_text
from v$sqltext t
where t.HASH_VALUE = v_sql_hash_value;
select v_disk_reads, v_buffer_gets --取出读磁盘次数和读内存次数
into v_disk_reads, v_buffer_gets
from (select t.SQL_ID,
sum(t.DISK_READS) DISK_READS,
sum(t.BUFFER_GETS) BUFFER_GETS
from v$sqlarea t
group by t.SQL_ID)
where SQL_ID = v_sql_id;
insert into u1.session_snap_history --save
(sid,
serial#,
seq#,
event,
logon_time,
sql_hash_value,
sql_id,
p1text,
p1,
p1raw,
p2text,
p2,
p2raw,
p3text,
p3,
p3raw,
wait_time,
seconds_in_wait,
state,
username,
osuser,
paddr,
process,
saddr,
module,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#,
target_obj,
sql_text,
disk_reads,
buffer_gets)
values
(current_event_record.sid,
current_event_record.serial#,
current_event_record.seq#,
current_event_record.event,
current_event_record.logon_time,
current_event_record.sql_hash_value,
current_event_record.sql_id,
current_event_record.p1text,
current_event_record.p1,
current_event_record.p1raw,
current_event_record.p2text,
current_event_record.p2,
current_event_record.p2raw,
current_event_record.p3text,
current_event_record.p3,
current_event_record.p3raw,
current_event_record.wait_time,
current_event_record.seconds_in_wait,
current_event_record.state,
current_event_record.username,
current_event_record.osuser,
current_event_record.paddr,
current_event_record.process,
current_event_record.saddr,
current_event_record.module,
current_event_record.row_wait_obj#,
current_event_record.row_wait_file#,
current_event_record.row_wait_block#,
current_event_record.row_wait_row#,
v_target_obj,
v_sql_text,
v_disk_reads,
v_buffer_gets);
commit;
end loop;
close current_event;
end;
/
用操作系统的crontab 或者 数据库的job、scheduler来调用这个存过就可以采样数据了。