11g以前，用strmmon,11g提供了dbms_streams_advisor_adm

EXEC dbms_streams_advisor_adm.analyze_current_performance;
进行一次性能数据收集和报告

收集的系统信息在
DBA_STREAMS_TP_DATABASE
DBA_STREAMS_TP_COMPONENT
DBA_STREAMS_TP_COMPONENT_LINK

性能数据在
DBA_STREAMS_TP_PATH_STAT
DBA_STREAMS_TP_COMPONENT_STAT
DBA_STREAMS_TP_PATH_BOTTLENECK

DBA_STREAMS_TP_PATH_STAT，一个复制链路的情况
主要3个指标：
1.每秒消息量
2.每秒处理的事务数
3.复制延迟
select advisor_run_id,to_char(advisor_run_time,'yyyy-mm-dd hh24:mi:ss') advisor_run_time,
sum(decode(statistic_name,'MESSAGE RATE',statistic_value,0)) MESSAGE_RATE,
sum(decode(statistic_name,'TRANSACTION RATE',statistic_value,0)) TRANSACTION_RATE,
sum(decode(statistic_name,'LATENCY',statistic_value,0)) LATENCY
from DBA_STREAMS_TP_PATH_STAT
group by advisor_run_id,to_char(advisor_run_time,'yyyy-mm-dd hh24:mi:ss')
order by 1;

DBA_STREAMS_TP_COMPONENT_STAT，各个组键的统计
select advisor_run_id,to_char(advisor_run_time,'yyyy-mm-dd hh24:mi:ss') advisor_run_time,
component_name,component_db,component_type,sub_component_type,
max(decode(statistic_name,'BANDWIDTH',statistic_value,0)) BANDWIDTH,
max(decode(statistic_name,'CURRENT QUEUE SIZE',statistic_value,0)) CURR_QUE_SIZE,
max(decode(statistic_name,'LATENCY',statistic_value,0)) LATENCY,
max(decode(statistic_name,'SEND_RATE',statistic_value,0)) SEND_RATE,
max(decode(statistic_name,'STATUS',statistic_value,0)) STATUS,
max(decode(statistic_name,'MESSAGE APPLY RATE',statistic_value,0)) MES_APP_RATE,
max(decode(statistic_name,'CAPTURE RATE',statistic_value,0)) CAPT_RATE,
max(decode(statistic_name,'IDLE',statistic_value,0)) IDLE,
max(decode(statistic_name,'EVENT: CPU + Wait for CPU',statistic_value,0)) CPU,
max(decode(statistic_name,'ENQUEUE RATE',statistic_value,0)) ENQ_RATE,
max(decode(statistic_name,'SPILL RATE',statistic_value,0)) SPILL_RATE,
max(decode(statistic_name,'TRANSACTION APPLY RATE',statistic_value,0)) TRAN_APP_RATE,
max(decode(statistic_name,'STATE',statistic_value,0)) STATE,
max(decode(statistic_name,'FLOW CONTROL',statistic_value,0)) FLOW_CTL
from DBA_STREAMS_TP_COMPONENT_STAT
group by advisor_run_id,to_char(advisor_run_time,'yyyy-mm-dd hh24:mi:ss'),
component_name,component_db,component_type,sub_component_type
order by 3,4,5,6,1,2;

DBA_STREAMS_TP_PATH_BOTTLENECK,发现的瓶颈

上面的这些表都是temp表，数据只在当前会话

还提供了一个包UTL_SPADV，提供自动执行性能数据收集以及格式化输出

执行2次，每次间隔30s
exec UTL_SPADV.COLLECT_STATS( interval => 30, num_runs => 2);

自动给执行，间隔60s
exec UTL_SPADV.START_MONITORING
exec UTL_SPADV.STOP_MONITORING

格式化输出
exec UTL_SPADV.SHOW_STATS();

最简单的性能监控，就是看latency
还有就是创建一个heartbeat table，检查延迟
Example of a Streams Heartbeat Table [ID 461278.1]

ITPub博客

STREAMS笔记(12) 性能监控

最新文章