使用SQLPLUS实时监控ORACLE性能指标的一种方法

使用示例

SQL> set ARRAY 1
SQL> select * from table(system.fn_stat_diff('leaf node splits,leaf node 90-10 splits'));

COLUMN_VALUE
-------------------------------------------------------------------
16:08:44 leaf node splits/s ----------- 625.9
16:08:44 leaf node 90-10 splits/s ----- 0
------------------------------------
16:08:55 leaf node splits/s ----------- 419.91
16:08:55 leaf node 90-10 splits/s ----- 0
------------------------------------
16:09:05 leaf node splits/s ----------- 455
16:09:05 leaf node 90-10 splits/s ----- 0
------------------------------------
16:09:15 leaf node splits/s ----------- 341.2
16:09:15 leaf node 90-10 splits/s ----- 0
------------------------------------
16:09:25 leaf node splits/s ----------- 466.5
16:09:25 leaf node 90-10 splits/s ----- .2
------------------------------------
16:09:35 leaf node splits/s ----------- 439.4
16:09:35 leaf node 90-10 splits/s ----- 0
------------------------------------
16:09:46 leaf node splits/s ----------- 437.91
16:09:46 leaf node 90-10 splits/s ----- 0
------------------------------------
16:09:56 leaf node splits/s ----------- 443.6
16:09:56 leaf node 90-10 splits/s ----- 0
------------------------------------
16:10:06 leaf node splits/s ----------- 569
16:10:06 leaf node 90-10 splits/s ----- .1
------------------------------------
16:10:16 leaf node splits/s ----------- 554.5
16:10:16 leaf node 90-10 splits/s ----- 0
------------------------------------
16:10:27 leaf node splits/s ----------- 575.55
16:10:27 leaf node 90-10 splits/s ----- 0

SQL> set ARRAY 1
SQL> select * from table(system.fn_stat_diff('redo size',1));

COLUMN_VALUE
-------------------------------------------------------------------
16:09:31 redo size/s ------------------ 30793046
------------------------------------
16:09:32 redo size/s ------------------ 49260288
------------------------------------
16:09:33 redo size/s ------------------ 48100644
------------------------------------
16:09:34 redo size/s ------------------ 45237164
------------------------------------
16:09:35 redo size/s ------------------ 84104732
------------------------------------
16:09:37 redo size/s ------------------ 16269388
------------------------------------
16:09:38 redo size/s ------------------ 73549684
------------------------------------
16:09:39 redo size/s ------------------ 100642400

使用方法

–默认每10秒刷新，停止CTRL+C即可
set ARRAY 1
select * from table(system.fn_stat_diff(‘v$sysstat name逗号分割’,刷新间隔默认10秒));

—示例
set ARRAY 1
select * from table(system.fn_stat_diff(‘leaf node splits,leaf node 90-10 splits’));

set ARRAY 1
select * from table(system.fn_stat_diff(‘redo size’,1));

set ARRAY 1
select * from table(system.fn_stat_diff(‘user commits,user rollbacks,user calls,recursive calls,session connect time,consistent gets,physical reads,redo size,leaf node splits,gc cr blocks received,gc current blocks received,gc cr blocks served,gc current blocks served’));

创建所需类型，对函数

-----以下是创建函数system.fn_stat_diff

create or replace type system.type_str is table of varchar2(300);
/

create or replace function system.split(p_str varchar2,p_delimiter varchar2 default ',') return type_str
is
  rs type_str:=type_str();
  l_str varchar2(4000):='';
  l_len number:=0;
begin
  l_str:=p_str;
  l_len:=length(p_delimiter);
  while length(l_str)>0 loop
     if instr(l_str,p_delimiter)>0 then
       rs.extend;
       rs(rs.count):=substr(l_str,1,instr(l_str,p_delimiter)-1);
       l_str:=substr(l_str,instr(l_str,p_delimiter)+l_len);
     else
       rs.extend;
       rs(rs.count):=l_str;
       exit;
     end if;
  end loop;
  return rs;
end;
/

grant select on v_$sysstat to system;
grant execute on DBMS_LOCK to system;

CREATE OR REPLACE FUNCTION system.fn_stat_diff (stat_str varchar2,interval_sec number default 10)
RETURN system.type_str PIPELINED
IS
TYPE stattype IS TABLE OF NUMBER  INDEX BY VARCHAR2(64);
stat1 stattype;
v_value number;
v_date date;
v_interval_sec number:=1;
v_date_new date;
ret_str VARCHAR2 (200);
BEGIN
for r in (select /*+use_hash(s) leading(l,s)*/s.name,s.value,sysdate etime from table(system.split(stat_str))l, v$sysstat s where l.column_value=s.name)
loop
stat1(r.name):=r.value;
v_date:=r.etime;
end loop;
for i in 1..10000 
loop
dbms_lock.sleep(interval_sec);
PIPE ROW ('------------------------------------');
for r in (select /*+use_hash(s) leading(l,s)*/s.name,s.value,sysdate etime from table(system.split(stat_str))l, v$sysstat s where l.column_value=s.name)
loop
v_interval_sec:=(r.etime-v_date)*24*3600;
ret_str:=to_char(r.etime,'hh24:mi:ss')||' '||rpad(r.name||'/s ',30,'-')||' '|| round((r.value-stat1(r.name))/v_interval_sec,2);
PIPE ROW (ret_str);
stat1(r.name):=r.value;
v_date_new:=r.etime;
end loop;
v_date:=v_date_new;
end loop;
RETURN;
END fn_stat_diff ;
/