STREAMS筆記(12) 效能監控

westzq1984發表於2013-06-11
筆記
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部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-763738/，如需轉載，請註明出處，否則將追究法律責任。

相關文章