Database realtime monitoring
来源：互联网 发布：宏程序车螺纹编程实例 编辑：程序博客网 时间：2024/04/29 16:55
create or replace type MySysstat as object (
REDO_BLOCKS NUMBER,
LOGICAL_READS NUMBER,
BLOCK_CHANGES NUMBER,
PHYSICAL_READS NUMBER,
PHYSICAL_WRITES NUMBER,
USER_CALLS NUMBER,
PARSES NUMBER,
HARD_PARSES NUMBER,
SORTS_MEM NUMBER,
SORTS_DD NUMBER,
LOGONS NUMBER,
EXECUTES NUMBER,
TRANSACTIONS NUMBER,
CHANGES_PER_READ NUMBER,
RECURSIVE NUMBER,
ROLLBACKS NUMBER,
ROWS_PER_SORT NUMBER,
CPU_PER_ELAPSED NUMBER,
BUFFER_HIT NUMBER);
create or replace type MyTabSysstat as table of MySysstat;
create or replace function getstats_fnc(cnt number default -1, s_wait number default 30) return MyTabSysstat pipelined as
cursor cur is
select name, value
from v$sysstat
where name in (
'redo size','redo blocks written','session logical reads',
'db block changes','physical reads','physical writes',
'user calls','parse count (total)','parse count (hard)',
'sorts (memory)','sorts (disk)','logons cumulative',
'execute count','user rollbacks','user commits',
'recursive calls','sorts (rows)','CPU used by this session',
'consistent gets','db block gets', 'physical reads direct'
);
type enr_type is table of cur%rowtype index by varchar2(64) ;
enr enr_type;
enr_temp cur%rowtype;
enr_old enr_type;
i number:=0;
v_date_old date := sysdate;
v_date date := sysdate;
v_rows_per_sort number;
v_rollbacks number;
v_sqlcode number;
begin
loop
exit when i=cnt+1;
open cur;
loop
exit when cur%notfound;
fetch cur into enr_temp;
enr(enr_temp.name).name := enr_temp.name;
enr(enr_temp.name).value:= enr_temp.value;
end loop;
close cur;
if i!=0 then
if enr('sorts (memory)').value-enr_old('sorts (memory)').value+
enr('sorts (disk)').value-enr_old('sorts (disk)').value=0 then
v_rows_per_sort:=null;
else
v_rows_per_sort:= (enr('sorts (rows)').value-enr_old('sorts (rows)').value)/
(enr('sorts (memory)').value-enr_old('sorts (memory)').value+
enr('sorts (disk)').value-enr_old('sorts (disk)').value);
end if;
if enr('user rollbacks').value-enr_old('user rollbacks').value+
enr('user commits').value-enr_old('user commits').value=0 then
v_rollbacks:= null;
else
v_rollbacks:= (enr('user rollbacks').value-enr_old('user rollbacks').value)/
(enr('user rollbacks').value-enr_old('user rollbacks').value+
enr('user commits').value-enr_old('user commits').value);
end if;
pipe row (MySysstat(
-- REDO_BLOCKS
(enr('redo blocks written').value-enr_old('redo blocks written').value)/
((v_date-v_date_old)*86400),
-- LOGICAL_READS
(enr('session logical reads').value-enr_old('session logical reads').value)/
((v_date-v_date_old)*86400),
-- BLOCK_CHANGES
(enr('db block changes').value-enr_old('db block changes').value)/
((v_date-v_date_old)*86400),
-- PHYSICAL_READS
(enr('physical reads').value-enr_old('physical reads').value)/
((v_date-v_date_old)*86400),
-- PHYSICAL_WRITES
(enr('physical writes').value-enr_old('physical writes').value)/
((v_date-v_date_old)*86400),
-- USER_CALLS
(enr('user calls').value-enr_old('user calls').value)/
((v_date-v_date_old)*86400),
-- PARSES
(enr('parse count (total)').value-enr_old('parse count (total)').value)/
((v_date-v_date_old)*86400),
-- HARD_PARSES
(enr('parse count (hard)').value-enr_old('parse count (hard)').value)/
((v_date-v_date_old)*86400),
-- SORTS_MEM
(enr('sorts (memory)').value-enr_old('sorts (memory)').value)/
((v_date-v_date_old)*86400),
-- SORTS_DD
(enr('sorts (disk)').value-enr_old('sorts (disk)').value)/
((v_date-v_date_old)*86400),
-- LOGONS
(enr('logons cumulative').value-enr_old('logons cumulative').value)/
((v_date-v_date_old)*86400),
-- EXECUTES
(enr('execute count').value-enr_old('execute count').value)/
((v_date-v_date_old)*86400),
-- TRANSACTIONS
(enr('user rollbacks').value-enr_old('user rollbacks').value+
enr('user commits').value-enr_old('user commits').value)/
((v_date-v_date_old)*86400),
-- CHANGES_PER_READ
(enr('db block changes').value-enr_old('db block changes').value)/
(enr('session logical reads').value-enr_old('session logical reads').value),
-- RECURSIVE
(enr('recursive calls').value-enr_old('recursive calls').value)/
(enr('recursive calls').value-enr_old('recursive calls').value+
enr('user calls').value-enr_old('user calls').value),
-- ROLLBACKS
v_rollbacks,
-- ROWS_PER_SORT
v_rows_per_sort,
-- CPU_PER_ELAPSED
(enr('CPU used by this session').value-enr_old('CPU used by this session').value)/
((v_date-v_date_old)*86400),
-- BUFFER_HIT
(1 - ((enr('physical reads').value-enr_old('physical reads').value-
(enr('physical reads direct').value-enr_old('physical reads direct').value) )/
((enr('db block gets').value-enr_old('db block gets').value)+
(enr('consistent gets').value-enr_old('consistent gets').value)-
(enr('physical reads direct').value-enr_old('physical reads direct').value))))*100
));
end if;
dbms_lock.sleep(s_wait);
v_date_old:= v_date;
enr_old:= enr;
v_date:= sysdate;
i:=i+1;
end loop;
return;
exception
when others then
v_sqlcode:= sqlcode;
if v_sqlcode = -1013 then null;
else raise;
end if;
return;
end;
output::
SQL> connect perfstat/perfstatConnected.SQL> set linesize 200SQL> set array 1SQL> COL REDO_BLOCKS FOR 999.99 heading 'Redo/s'SQL> COL LOGICAL_READS FOR 99,999 heading 'L-Rd/s'SQL> COL BLOCK_CHANGES FOR 9,999 heading 'B-Chg/s'SQL> COL PHYSICAL_READS FOR 9,999 heading 'P-Rd/s'SQL> COL PHYSICAL_WRITES FOR 9,999 heading 'P-Wrt/s'SQL> COL USER_CALLS FOR 9,999 heading 'U-Cal/s'SQL> COL PARSES FOR 9,999 heading 'Parse/s'SQL> COL HARD_PARSES FOR 99.9 heading 'H-Par/s'SQL> COL SORTS_MEM FOR 999 heading 'M-Srt/s'SQL> COL SORTS_DD FOR 99.9 heading 'D-Srt/s'SQL> COL LOGONS FOR 99 heading 'Log/s'SQL> COL EXECUTES FOR 999 heading 'Ex/s'SQL> COL TRANSACTIONS FOR 999 heading 'TX/s'SQL> COL CHANGES_PER_READ FOR 9.99 heading 'Chge/Rd'SQL> COL RECURSIVE FOR 9.99 heading 'Rcr/Ex'SQL> COL ROLLBACKS FOR .99 heading 'RB/TX'SQL> COL ROWS_PER_SORT FOR 9,999 heading 'Rows/Sort'SQL> COL CPU_PER_ELAPSED FOR 99.99 heading 'CPU/s'SQL> COL BUFFER_HIT FOR 999 heading 'Buf-H%'SQL>SQL> select * from table(cast(getstats_fnc(100, 2) as MyTabSysstat)); Redo/s L-Rd/s B-Chg/s P-Rd/s P-Wrt/s U-Cal/s Parse/s H-Par/s M-Srt/s D-Srt/s Log/s Ex/s TX/s Chge/Rd Rcr/Ex RB/TX Rows/Sort CPU/s Buf-H%------- ------- ------- ------ ------- ------- ------- ------- ------- ------- ----- ---- ---- ------- ------ ----- --------- ------ ------ .00 3,733 0 3,728 6 1 0 .0 0 .0 0 1 0 .00 .96 .00 0 1.00 2,853 0 2,839 0 16 7 .0 3 .0 0 11 0 .00 .81 9 .50 1 .00 2,736 0 2,744 0 0 0 .0 0 .0 0 1 0 .00 1.00 .00 -0....
- Database realtime monitoring
- Database Monitoring
- Oracle database monitoring scripts
- Centralize Your Database Monitoring Process
- Android 集成FireBase Realtime DataBase实现聊天
- Rules of Thumb for Database Monitoring
- TaintDroid: An Information-Flow Tracking System for Realtime Privacy Monitoring on Smartphones
- TaintDroid: An Information-Flow Tracking System for Realtime Privacy Monitoring on Smartphones
- Firebase系列之---Realtime Database(实时数据库)的使用
- Monitoring Changes in Your Database Using DDL Triggers
- Top DBA Shell Scripts for Monitoring the Database
- 拥抱Firebase，Firebase之Realtime Database。(含github源码)，欢迎关注。
- two scripts - monitoring the usage of rollback segments of Oracle Database
- realtime workflow
- 137 While Monitoring the space usage in your database that is in ARCHIVELOG mode you observed that t
- Security Monitoring
- Keystroke Monitoring
- monitoring usage
- 经典BBS语录2007贺岁版
- 英文荟萃
- display a history load for a hash_value
- content pipeline without volume texture support???!!!
- JAVA和Tomcat安装完成后配置的环境变量
- Database realtime monitoring
- This script allows capturing and storing occured errors
- pdf reader in linux
- 社区英雄榜：谁是最有价值的技术博客
- checkform
- 你能为公司带来什么？
- .NET 开发人员应该下载的十个必备工具
- Model-View-Control
- c基础
