oracle 连接数sessionn,会话统计信息session_pkg

目的:

Oracle会话上的统计和事件以累计方式记录,为了定量分析某个操作上的统计信息/等待事件,创建此工具包.

局限:

*由于没有会话上的latch统计信息,没有列出latch统计(Tom的runstats假设系统只有当前用户,进而获取当前会话的latch信息)

*获取统计信息的操作本身会影响该会话的统计信息

--1,所需权限

--作为sysdba用户，授予以下权限

grant select on v_$session_event to usera;

grant select on v_$statname to usera;

grant select on v_$mystat to usera;

grant select on v_$active_session_history to usera;

--2,创建临时表和package

create global temporary table session_stats

(statistic# int,

value int)

on commit preserve rows;

create global temporary table session_event

(wait_class varchar2(64),

event varchar2(64),

total_waits int,

time_waited int)

on commit preserve rows;

--

create or replace package session_pkg

as

procedure get_snap;

procedure rpt_stat_event(p_stats_threshold in int default 0);

end;

/

CREATE OR REPLACE PACKAGE BODY session_pkg AS

mysid INT;

myserial int;

sampleid int;

PROCEDURE get_snap IS

BEGIN

SELECT s.sid, s.serial# INTO mysid,myserial

FROM v_$session s, v_$mystat m

WHERE s.sid = m.sid

AND rownum = 1;

select max(sample_id) into sampleid from v_$active_session_history;

DELETE FROM session_event;

INSERT INTO session_event

SELECT wait_class, event, total_waits, time_waited

FROM sys.v_$session_event

WHERE sid = mysid;

DELETE FROM session_stats;

INSERT INTO session_stats

SELECT statistic#, VALUE FROM sys.v_$mystat;

COMMIT;

END;

PROCEDURE rpt_stat_event(p_stats_threshold in int default 0) IS

BEGIN

dbms_output.enable(NULL);

dbms_output.put_line('Session id:'||mysid ||' Serial#:' || myserial||' Last sampid:'||sampleid);

dbms_output.put_line(chr(9));

dbms_output.put_line('----session stats---');

dbms_output.put_line(rpad('Name', 30) || lpad('Value', 12));

FOR x IN (SELECT rpad(n.NAME, 30) ||

to_char(b.VALUE - a.VALUE, '999,999,999') data

FROM session_stats a, sys.v_$mystat b, sys.v_$statname n

WHERE a.statistic# = b.statistic#

AND a.statistic# = n.statistic#

AND n.name not in('session uga memory max','session pga memory max','session uga memory','session pga memory')

AND (b.VALUE - a.VALUE) > p_stats_threshold

ORDER BY (b.VALUE - a.VALUE) desc) LOOP

dbms_output.put_line(x.data);

END LOOP;

dbms_output.put_line(chr(9));

dbms_output.put_line('----session stats-pga/uga <<>>----');

dbms_output.put_line(rpad('Name', 30) || lpad('Value1', 16) || '->' || lpad('Value2', 16));

FOR x IN (SELECT rpad(n.NAME, 30) ||

to_char(a.VALUE, '999,999,999,999') || '->' ||

to_char(b.VALUE, '999,999,999,999') data

FROM session_stats a, sys.v_$mystat b, sys.v_$statname n

WHERE a.statistic# = b.statistic#

AND a.statistic# = n.statistic#

AND n.name in('session uga memory max','session pga memory max','session uga memory','session pga memory')

ORDER BY n.NAME) LOOP

dbms_output.put_line(x.data);

END LOOP;

dbms_output.put_line(chr(9));

dbms_output.put_line('----session events---');

dbms_output.put_line(rpad('Wait_Class', 15) || rpad('Event', 30) ||

rpad('Total_waits', 12) ||

rpad('Time_waited', 12));

FOR x IN (SELECT rpad(wait_classb, 15) || rpad(eventb, 30) ||

to_char(total_waitsb - nvl(total_waitsa, 0),

'999,999,999') ||

to_char(time_waitedb - nvl(time_waiteda, 0),

'999,999,999') data

FROM (SELECT a.wait_class  wait_classa,

a.event       eventa,

a.total_waits total_waitsa,

a.time_waited time_waiteda,

b.wait_class  wait_classb,

b.event       eventb,

b.total_waits total_waitsb,

b.time_waited time_waitedb

FROM session_event a,

(SELECT wait_class,

event,

total_waits,

time_waited

FROM sys.v_$session_event

WHERE sid = mysid) b

WHERE a.event(+) = b.event)

WHERE total_waitsb - nvl(total_waitsa, 0) > 0

ORDER BY wait_classb,

(total_waitsb - nvl(total_waitsa, 0)),

(time_waitedb - nvl(time_waiteda, 0))) LOOP

dbms_output.put_line(x.data);

END LOOP;

dbms_output.put_line(chr(9));

dbms_output.put_line('----v$active_session_history---');

dbms_output.put_line(rpad('Wait_class', 15) || rpad('Event', 30) ||

rpad('count(*)', 16) || rpad('min_samp', 12) ||rpad('max_samp', 12));

FOR x IN (SELECT rpad(wait_class, 15, ' ') || rpad(event, 30, ' ') ||

rpad(count(*), 16, ' ') || rpad(min(sample_id), 12, ' ') ||

rpad(max(sample_id), 12, ' ')  data

FROM sys.v_$active_session_history

WHERE session_id = mysid

AND session_serial# = myserial

AND sample_id > sampleid

AND event is not null

GROUP BY wait_class, event

ORDER BY wait_class,event) LOOP

dbms_output.put_line(x.data);

END LOOP;

END;

END;

/

--3使用方法：

set serveroutput on

exec session_pkg.get_snap;

--do something

exec session_pkg.rpt_stat_event(0);