# S231STM.def: Collects Streams Monitoring Information # $Id: S231STM.def,v 1.12 2008/02/25 17:32:55 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/S231STM.def,v 1.12 2008/02/25 17:32:55 mschenke Exp $ # # Change History # 20080225 MSC Change the copyright notice. =head1 NAME S231STM - Collects Streams Monitoring Information =head1 DESCRIPTION This module collects Streams activity information occurring within an Oracle database. The following reports can be generated and are regrouped under C: =cut if !getSetting('DATABASE_INSTALLED') return echo tput('bold'),'Processing STM module ...',tput('off') # Initialization var $COUNT = getSetting('STM_COUNT',5) var $DEBUG = getSetting('STM_DEBUG') var $LONG = getSetting('STM_LONG') var $SYSDBA = getSetting('SQL_SYSDBA') var $INTERVAL = getSetting('STM_INTERVAL',3) var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' toc '1:Streams Monitoring' # Load the common macros run DBinfo() # Obtain the database version var $VERSION = get_db_version() =head2 no_setup - No Streams Monitor Setup All versions earlier than Oracle 9i Release 2 do not have the Streams Monitoring option. =cut if !match($VERSION,'^(92$|10|11)') {report no_setup write '**Error:** Oracle version ',nvl($VERSION,'?'),' did not have Streams \ or the database is not running.' toc '2:[[',getFile(),'][rda_report][No Streams Monitor Setup]]' return } var $VERSION10 = match($VERSION,'^(10|11)') var $VERSION102 = match($VERSION,'^(102|11)') # Define the formating macros macro fmt_bytes {var ($val) = @arg if expr('<',$val,1024) return sprintf(' %d B',$val) if expr('<',$val,1048576) return sprintf(' %1.3f KiB',expr('/',$val,1024)) if expr('<',$val,1073741824) return sprintf(' %1.3f MiB',expr('/',$val,1048576)) return sprintf(' %1.3f GiB',expr('/',$val,1073741824)) } macro fmt_latency {var ($val) = @arg if expr('<',$val,0) return ' -' if expr('<',$val,60) return sprintf(' %d sec',$val) if expr('<',$val,3600) return sprintf(' %1.3f min',expr('/',$val,60)) if expr('<',$val,86400) return sprintf(' %1.3f hr',expr('/',$val,3600)) if expr('<',$val,2592000) return sprintf(' %1.3f day',expr('/',$val,86400)) if expr('<',$val,31557600) return sprintf(' %1.3f mon',expr('/',$val,2592000)) return sprintf(' %1.3f yr',expr('/',$val,31557600)) } macro fmt_row {var (\%tbl,@key) = @arg var @tbl = () loop $key (@key) {if match($key,'^\*') var @tbl = (@tbl,nvl($tbl{substr($key,1)},' - ')) else var @tbl = (@tbl,nvl($tbl{$key},' -')) } return @tbl } macro push_value {var (\%tbl,$key,$num,$fmt) = @arg if $fmt var $num = sprintf($fmt,$num) else var $num = concat(' ',$num) var $tbl{$key} = join('%BR%',$tbl{$key},$num) } # --- Macro to get database information sample -------------------------------- macro get_ref_data {import $sql,$LONG,$SYSDBA,$VERSION10 # Get a database time stamp set $sql {SET serverout on size 100000 "BEGIN " dbms_output.put_line('tps=''' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || " ''''); " dbms_output.put_line('dif=' || " TO_CHAR(86400 * NVL(SYSDATE - TO_DATE(:1,'DD-Mon-YYYY HH24:MI:SS'),0))); "END; "/ } # Get the log stats if $SYSDBA {append $sql {SELECT 'log=' || " TO_CHAR(last_write_scn_bas+4294967296*last_write_scn_wrp) || '|' || " TO_CHAR(last_blk) " FROM x$kcrfws; } } # Print the capture stats if $VERSION10 {append $sql {SELECT 'cp=' || " c.capture# || '|' || " c.capture_name || '|' || " NVL(c.total_messages_captured,0) || '|' || " NVL(c.capture_message_number,0) || '|' || " NVL(c.total_messages_enqueued,0) || '|' || " NVL(c.enqueue_message_number,0) || '|' || " NVL((c.capture_time - " c.capture_message_create_time) * 86400,-1) || '|' || " l.read_scn || '|' || " c.elapsed_pause_time " FROM v$streams_capture c,v$logmnr_session l " WHERE c.capture_name = l.session_name " ORDER BY 1; } } else {append $sql {SELECT 'cp=' || " c.capture# || '|' || " c.capture_name|| '|' || " NVL(c.total_messages_captured,0) || '|' || " NVL(c.capture_message_number,0) || '|' || " NVL(c.total_messages_enqueued,0) || '|' || " NVL(c.enqueue_message_number,0) || '|' || " NVL((c.capture_time - " c.capture_message_create_time) * 86400,-1) " FROM v$streams_capture c " ORDER BY 1; } } # Get the queue stats if $VERSION10 {append $sql {SELECT 'q=' || " queue_id || '|' || " NVL(cnum_msgs,0) || '|' || " NVL(cspill_msgs,0) || '|' || " NVL(num_msgs,0) " FROM v$buffered_queues; } } else {append $sql {SELECT 'q=' || " dq.qid || '|' || " dq.owner || '|' || " dq.queue_table " FROM dba_queues dq,dba_queue_tables dqt " WHERE dq.owner not in ('SYS','SYSTEM') " AND dq.QUEUE_TYPE = 'NORMAL_QUEUE' " AND dq.owner = dqt.owner " AND dq.queue_table = dqt.queue_table " AND dqt.object_type = 'SYS.ANYDATA'; } } # Get the apply stats if $LONG {append $sql {SELECT 'ap=' || " ac.apply# || '|' || " ar.total_messages_dequeued || '|' || " ac.total_received || '|' || " ac.total_assigned || '|' || " ac.total_applied || '|' || " NVL(ac.hwm_message_number,0) || '|' || " NVL((ac.hwm_time - hwm_message_create_time) * 86400,-1) || '|' || " NVL(ar.dequeued_message_number,0) || '|' || " NVL((ar.dequeue_time - " ar.dequeued_message_create_time) * 86400,-1) " FROM v$streams_apply_coordinator ac,v$streams_apply_reader ar " WHERE ac.apply# = ar.apply# " ORDER BY 1; } } else {append $sql {SELECT 'ap=' || " ac.apply# || '|' || " ac.apply_name || '|' || " ac.total_received || '|' || " ac.total_assigned || '|' || " ac.total_applied || '|' || " NVL((ac.hwm_time - ac.hwm_message_create_time) * 86400,-1) || '|' || " SUM(aps.total_messages_applied) " FROM v$streams_apply_coordinator ac,v$streams_apply_server aps " WHERE ac.apply# = aps.apply# " GROUP BY ac.apply#, " ac.apply_name, " ac.hwm_time, " ac.hwm_message_create_time, " ac.total_assigned, " ac.total_received, " ac.total_applied " ORDER BY 1; } } # Get the memory stats if and($SYSDBA,$VERSION10) {append $sql {SELECT 'mem=' || frused_kwqbpmt " FROM x$kwqbpmt; "SELECT 'siz=' || TO_CHAR(ROUND(current_size / 1048576,2)) " FROM v$sga_dynamic_components " WHERE component = 'streams pool'; } } # Execute the SQL statements return loadSql(bindSql($sql,'NULL')) } macro get_smp_data return loadSql(bindSql(@arg)) macro add_sql_prop {var ($qid) = @arg import $sql,%tb_gpr keep $sql,%tb_gpr if $VERSION102 {set $req {SELECT 'pr_:1=' || " dqs.total_number || '|' || " dqs.total_time || '|' || " dqs.total_bytes || '|' || " dqs.destination " FROM dba_queue_schedules dqs, dba_queues dq " WHERE dqs.schema = dq.owner " AND dqs.qname = dq.name " AND dq.qid = :1 " AND dqs.message_delivery_mode = ''BUFFERED'' " ORDER BY dqs.destination'; } } else {set $req {SELECT 'pr_:1=' || " dqs.total_number || '|' || " dqs.total_time || '|' || " dqs.total_bytes || '|' || " dqs.destination " FROM dba_queue_schedules dqs, dba_queues dq " WHERE dqs.schema = dq.owner " AND dqs.qname = dq.name " AND dq.qid = :1 " ORDER BY dqs.destination; } } var $req = bindSql($req,$qid) var $sql = join("\012",$sql,$req) var $tb_gpr{$qid} = 1 return $req } macro add_sql_qn {var ($qid) = @arg import $sql,%tb_gqn keep $sql,%tb_gqn set $req {SELECT 'qn_:1=' || bufqm_nmsg " FROM x$bufqm " WHERE bufqm_qid = :1; } var $req = bindSql($req,$qid) var $sql = join("\012",$sql,$req) var $tb_gqn{$qid} = 1 return $req } macro add_sql_qs {var ($obj) = @arg import $sql,%tb_gqs keep $sql,%tb_gqs set $req {SELECT 'qs_:1=' || COUNT(*) " FROM :1; } var $req = bindSql($req,$obj) var $sql = join("\012",$sql,$req) var $tb_gqs{$obj} = 1 return $req } # --- Macro to generate the table heading ------------------------------------- macro write_heading {import $LONG,$SYSDBA,$VERSION10,%ref var @top =('* *') var @hdr =('*Time Stamp*') # Add log contribution if $SYSDBA {if $LONG {var @top = (@top,' *Log* ','') var @hdr = (@hdr,' *Last Write SCN*',' *Last Block*') } else {var @top = (@top,' *Log* ') var @hdr = (@hdr,' *Redo Generated per sec*') } } # Add capture contribution var @top = (@top,' *Capture* ','','','') var @hdr = (@hdr,' *C#*') if $LONG {if $VERSION10 {var @top = (@top,'') var @hdr = (@hdr,' *Read SCN*') } var @top = (@top,'','') var @hdr = (@hdr,' *Msgs Captured*',' *Capture SCN*',\ ' *Msgs Enqueued*',' *Enqueue SCN*') } else var @hdr = (@hdr,' *LCRs Captured per sec*',' *LCRs Enqueued per sec*') var @hdr = (@hdr,' *Capture Latency*') if $VERSION10 {var @top = (@top,'') var @hdr = (@hdr,' *F/B* ') } # Add queue contribution if $VERSION10 {if $LONG {var @top = (@top,' *Queue* ','','','') var @hdr = (@hdr,' *Id*',' *Outstanding Msgs*',' *Cumulative Msgs*',\ ' *Cumulative Spilled*') } else {var @top = (@top,' *Queue* ','','') var @hdr = (@hdr,' *Id*',\ ' *Msgs Enqueued per sec*',' *Msgs Spilled per sec*') } } elsif $LONG {var @top = (@top,' *Queue* ','') var @hdr = (@hdr,' *Id*') if $SYSDBA {var @top = (@top,'') var @hdr = (@hdr,' *Outstanding Msgs*') } var @hdr = (@hdr,' *Currently Spilled*') } # Add progagation contribution if $LONG {var @top = (@top,' *Propagation* ','','','') var @hdr = (@hdr,' *Id/P#* ',' *Number Propagated*',' *Total Bytes*',\ ' *Total Time*') } else {var @top = (@top,' *Propagation* ','') var @hdr = (@hdr,' *Id/P#* ',' *LCRs Propagated per sec*') } # Add apply contribution if $LONG {var @top = (@top,' *Apply* ','','','','','','','','','') var @hdr = (@hdr,' *A#*',' *Msgs Dequeued*',' *Dequeue SCN*',\ ' *Dequeue Latency*',' *Txns Received*',' *Txns Assigned*',\ ' *Txns Applied*',' *HWm SCN*',' *HWm Latency*',' *B*') } else {var @top = (@top,' *Apply* ','','','','') var @hdr = (@hdr,' *A#*',' *LCRs Applied per sec*',' *Txns Applied per sec*',\ ' *Dequeue Latency*',' *B* ') } # Add memory contribution if and($SYSDBA,$VERSION10) {var @top = (@top,' *Memory* ','') var @hdr = (@hdr,' *Used (%)*',' *Pool Size*') } # Insert the heading in the report write '|',join('|',@top),'|' write '|',join('|',@hdr),'|' } # --- Macro to extract the reference information ------------------------------ macro get_ref_info {import $sql,%ref,%tb_app,%tb_cap,%tb_prp,%tb_que,$LONG,$SYSDBA,$VERSION10 var $ref{'tps'} = value(grepLastSql('^tps=','f')) # Prepare log stats if $SYSDBA var ($ref{'scn'},$ref{'blk'}) = split('\|',value(grepLastSql('^log=','f'))) # Prepare capture stats loop $lin (grepLastSql('^cp=')) {var ($cap{'num'},$nam,$cap{'ctot'},$cap{'cscn'},$cap{'etot'},$cap{'escn'},\ $cap{'clat'},$cap{'rscn'},$cap{'tim'}) = split('\|',value($lin)) var $tb_cap{concat('flw_',$nam)} = false var $tb_cap{concat('ctot_',$nam)} = $cap{'ctot'} # total_captured var $tb_cap{concat('etot_',$nam)} = $cap{'etot'} # total_enqueued if defined($cap{'rscn'}) var $tb_cap{concat('rscn_',$nam)} = $cap{'rscn'} # read_scn if defined($cap{'tim'}) var $tb_cap{concat('tim_',$nam)} = $cap{'tim'} # elapsed_pause_time } # Prepare queue stats loop $lin (grepLastSql('^q=')) {if !$VERSION10 {var ($nam,$que{'own'},$que{'tbl'}) = split('\|',value($lin)) if $SYSDBA call add_sql_qn($nam) call add_sql_qs(concat($que{'own'},'.AQ$_',$que{'tbl'},'_P')) } elsif !$LONG {var ($nam,$que{'cnmsg'},$que{'csmsg'}) = split('\|',value($lin)) var $tb_que{concat('cnmsg_',$nam)} = $que{'cnmsg'} # cnummsgs var $tb_que{concat('csmsg_',$nam)} = $que{'csmsg'} # cspillmsgs } # Prepare the propagation stats if $LONG call add_sql_prop($nam) else {loop $lin (grepSql(add_sql_prop($nam),concat('^pr_',quote($nam)))) {var ($prp{'prp'},$prp{'$tim'},$prp{'vol'},$prp{'$dst'}) = \ split('\|',value($lin)) var $tb_prp{concat($nam,'|',$prp{'$dst'})} = $prp{'prp'} } } } # Prepare apply stats if !$LONG {loop $lin (grepLastSql('^ap=')) {var ($app{'num'},$nam,$app{'rec'},$app{'ass'},$app{'app'},$app{'hlat'},\ $app{'tot'}) = split('\|',value($lin)) var $tb_app{concat('tot_',$nam)} = $app{'tot'} # total_applied var $tb_app{concat('app_',$nam)} = $app{'app'} # txns_applied } } } # --- Macros to treat a sample ------------------------------------------------ macro get_smp_info {import %ref,%smp,%tb_app,%tb_cap,%tb_gpr,%tb_gqn,%tb_gqs,%tb_prp,%tb_que,\ $LONG,$SYSDBA,$VERSION10 keep %ref,%smp,%tb_app,%tb_cap,%tb_gpr,%tb_gqn,%tb_gqs,%tb_prp,%tb_que,\ $LONG,$SYSDBA,$VERSION10 var $smp{'tps'} = value(grepLastSql('^tps=','f')) var $smp{'dif'} = value(grepLastSql('^dif=','f')) var @tbl = (replace(trim($smp{'tps'},"'"),' ',' ',true)) # Perform log stats if $SYSDBA {var ($smp{'scn'},$smp{'blk'}) = split('\|',value(grepLastSql('^log=','f'))) if $LONG var @tbl = (@tbl,$smp{'scn'},$smp{'blk'}) elsif expr('<',$smp{'blk'},$ref{'blk'}) var @tbl = (@tbl,' -') else {var $rat = expr('/',expr('*',512,\ expr('-',$smp{'blk'},$ref{'blk'})),\ $smp{'dif'}) var @tbl = (@tbl,concat(fmt_bytes($rat),'ps')) } } # Perform capture stats var %res = () loop $lin (grepLastSql('^cp=')) {var $cap{'flw'} = false var ($cap{'num'},$nam,$cap{'ctot'},$cap{'cscn'},$cap{'etot'},$cap{'escn'},\ $cap{'clat'},$cap{'rscn'},$cap{'tim'}) = split('\|',value($lin)) if exists($tb_cap{concat('flw_',$nam)}) {call push_value(\%res,'num',$cap{'num'},' %02d') call push_value(\%res,'clat',fmt_latency($cap{'clat'})) # capture_latency if $LONG {call push_value(\%res,'ctot',$cap{'ctot'}) # total_captured call push_value(\%res,'cscn',$cap{'cscn'}) # captured_scn call push_value(\%res,'etot',$cap{'etot'}) # total_enqueued call push_value(\%res,'escn',$cap{'escn'}) # enqueued_scn if $VERSION10 call push_value(\%res,'rscn',$cap{'rscn'}) # read_scn } else {if expr('>=',$cap{'ctot'},$tb_cap{concat('ctot_',$nam)}) {call push_value(\%res,'crat',\ expr('/',expr('-',$cap{'ctot'},\ $tb_cap{concat('ctot_',$nam)}),\ $smp{'dif'}),' %1.4f') # total_captured per sec call push_value(\%res,'erat',\ expr('/',expr('-',$cap{'etot'},\ $tb_cap{concat('etot_',$nam)}),\ $smp{'dif'}),' %1.4f') # total_enqueued per sec } else {call push_value(\%res,'crat','-') # total_captured per sec call push_value(\%res,'erat','-') # total_enqueued per sec } } if $VERSION10 {var $typ = '- ' if expr('>',$cap{'tim'},$tb_cap{concat('tim_',$nam)}) var ($typ,$cap{'flw'}) = ('F ',true) elsif and(expr('<',$cap{'cscn'},$tb_cap{concat('rscn_',$nam)}),\ or(not($tb_cap{concat('flw_',$nam)}),\ and($tb_cap{concat('flw_',$nam)},expr('>',$smp{'dif'},60)))) var $typ = 'B ' call push_value(\%res,'typ',$typ) } } # Update the capture record var $tb_cap{concat('flw_',$nam)} = $cap{'flw'} var $tb_cap{concat('ctot_',$nam)} = $cap{'ctot'} # total_captured var $tb_cap{concat('etot_',$nam)} = $cap{'etot'} # total_enqueued if defined($cap{'rscn'}) var $tb_cap{concat('rscn_',$nam)} = $cap{'rscn'} # read_scn if defined($cap{'tim'}) var $tb_cap{concat('ptim_',$nam)} = $cap{'tim'} # elapsed_pause_time } if $LONG {if $VERSION10 var @tbl = (@tbl,fmt_row(\%res,'num','rscn','ctot','cscn','etot','escn',\ 'clat','*typ')) else var @tbl = (@tbl,fmt_row(\%res,'num','ctot','cscn','etot','escn','clat')) } else {if $VERSION10 var @tbl = (@tbl,fmt_row(\%res,'num','crat','erat','clat','*typ')) else var @tbl = (@tbl,fmt_row(\%res,'num','crat','erat','clat')) } # Perform queue stats var %res = () loop $lin (grepLastSql('^q=')) {if $VERSION10 {var ($nam,$que{'cnmsg'},$que{'csmsg'},$que{'nmsg'}) = split('\|',value($lin)) if $LONG {call push_value(\%res,'num',$nam) call push_value(\%res,'nmsg',$que{'nmsg'}) # nummsgs call push_value(\%res,'cnmsg',$que{'cnmsg'}) # cnummsgs call push_value(\%res,'csmsg',$que{'csmsg'}) # cspillmsgs } else {if exists($tb_que{concat('cnmsg_',$nam)}) {call push_value(\%res,'num',$nam) call push_value(\%res,'cnrat',\ expr('/',expr('-',$que{'cnmsg'},\ $tb_que{concat('cnmsg_',$nam)}),\ $smp{'dif'}),' %1.4f') # cnummsgs per sec call push_value(\%res,'csrat',\ expr('/',expr('-',$que{'csmsg'},\ $tb_que{concat('csmsg_',$nam)}),\ $smp{'dif'}),' %1.4f') # cspillmsgs per sec } var $tb_que{concat('cnmsg_',$nam)} = $que{'cnmsg'} # cnummsgs var $tb_que{concat('csmsg_',$nam)} = $que{'csmsg'} # cspillmsgs } } else {var ($nam,$que{'own'},$que{'tbl'}) = split('\|',value($lin)) if $LONG {call push_value(\%res,'num',$nam) if $SYSDBA {var $val = '-' if exists($tb_gqn{$nam}) {loop $lin (grepLastSql(concat('^qn_',quote($nam),'='))) var $val = value($lin) } else {loop $lin (grepSql(add_sql_qn($nam),concat('^qn_',quote($nam),'='))) var $val = value($lin) } call push_value(\%res,'nmsg',$val) } var $key = concat($que{'own'},'.AQ$_',$que{'tbl'},'_P') var $val = '-' if exists($tb_gqs{$key}) {loop $lin (grepLastSql(concat('^qs_',quote($key),'='))) var $val = value($lin) } else {loop $lin (grepSql(add_sql_qs($key),concat('^qs_',quote($key),'='))) var $val = value($lin) } call push_value(\%res,'smsg',$val) } } # Get the corresponding propagation information var $cnt = 0 if exists($tb_gpr{$nam}) var @lin = grepLastSql(concat('^pr_',quote($nam))) else var @lin = grepSql(add_sql_prop($nam),concat('^pr_',quote($nam))) loop $lin (@lin) {var ($prp{'prp'},$prp{'$tim'},$prp{'vol'},$prp{'$dst'}) = \ split('\|',value($lin)) incr $cnt if $LONG {call push_value(\%res,'pid',sprintf('%s/%02d ',$nam,$cnt)) call push_value(\%res,'prp',$prp{'prp'}) call push_value(\%res,'vol',$prp{'vol'}) call push_value(\%res,'tim',$prp{'tim'}) } else {var $key = concat($nam,'|',$prp{'$dst'}) if expr('>=',$prp{'prp'},$tb_prp{$key}) {call push_value(\%res,'pid',sprintf('%s/%02d ',$nam,$cnt)) call push_value(\%res,'prat',\ expr('/',expr('-',$prp{'prp'},$tb_prp{$key}),\ $smp{'dif'}),' %1.4f') } var $tb_prp{$key} = $prp{'prp'} } } } if $LONG {if $VERSION10 var @tbl = (@tbl,fmt_row(\%res,'num','nmsg','cnmsg','csmsg')) elsif $SYSDBA var @tbl = (@tbl,fmt_row(\%res,'num','nmsg','smsg')) else var @tbl = (@tbl,fmt_row(\%res,'num','smsg')) var @tbl = (@tbl,fmt_row(\%res,'*pid','prp','vol','tim')) } else {if $VERSION10 var @tbl = (@tbl,fmt_row(\%res,'num','cnrat','csrat')) var @tbl = (@tbl,fmt_row(\%res,'*pid','prat')) } # Perform apply stats var %res = () loop $lin (grepLastSql('^ap=')) {if $LONG {var ($app{'num'},$app{'dmsg'},$app{'rec'},$app{'ass'},$app{'app'},\ $app{'hscn'},$app{'hlat'},$app{'dscn'},$app{'dlat'}) = \ split('\|',value($lin)) call push_value(\%res,'num',$app{'num'},' %02d') call push_value(\%res,'dmsg',$app{'dmsg'}) # msgs_deqd call push_value(\%res,'dscn',$app{'dscn'}) # dequeue_scn call push_value(\%res,'dlat',fmt_latency($app{'dlat'})) # dequeue_latency call push_value(\%res,'rec',$app{'rec'}) # txns_recvd call push_value(\%res,'ass',$app{'ass'}) # txns_assigned call push_value(\%res,'app',$app{'app'}) # txns_applied call push_value(\%res,'hscn',$app{'hscn'}) # hwm_scn call push_value(\%res,'hlat',fmt_latency($app{'hlat'})) # hwm_latency if expr('>',expr('-',$app{'rec'},$app{'ass'}),10) var $typ = 'B ' else var $typ = '- ' call push_value(\%res,'typ',$typ) } else {var ($app{'num'},$nam,$app{'rec'},$app{'ass'},$app{'app'},$app{'hlat'},\ $app{'tot'}) = split('\|',value($lin)) if expr('>=',$app{'app'},$tb_app{concat('app_',$nam)}) {call push_value(\%res,'num',$app{'num'},' %02d') call push_value(\%res,'tot',\ expr('/',expr('-',$app{'tot'},\ $tb_app{concat('tot_',$nam)}),\ $smp{'dif'}),' %1.4f') # total_applied per sec call push_value(\%res,'app',\ expr('/',expr('-',$app{'app'},\ $tb_app{concat('app_',$nam)}),\ $smp{'dif'}),' %1.3f') # txns_applied per sec call push_value(\%res,'hlat',fmt_latency($app{'hlat'})) # hwm_latency if expr('>',expr('-',$app{'rec'},$app{'ass'}),10) var $typ = 'B ' else var $typ = '- ' call push_value(\%res,'typ',$typ) } } # Update the apply record var $tb_app{concat('tot_',$nam)} = $app{'tot'} # total_applied var $tb_app{concat('app_',$nam)} = $app{'app'} # txns_applied } if $LONG var @tbl = (@tbl,fmt_row(\%res,'num','dmsg','dscn','dlat','rec','ass',\ 'app','hscn','hlat','*typ')) else var @tbl = (@tbl,fmt_row(\%res,'num','tot','app','hlat','*typ')) # Perform memory stats if and($SYSDBA,$VERSION10) {var $smp{'mem'} = value(grepLastSql('^mem=','f')) var $smp{'siz'} = value(grepLastSql('^siz=','f')) var @tbl = (@tbl,concat(' ',$smp{'mem'}),fmt_bytes($smp{'siz'})) } # Generate the data raw write '|',join('|',@tbl),'|' # Save the sample as new reference var %ref = %smp } =head2 monitor - Monitor Results Monitors database streams. =cut report monitor write '---+!! Streams Monitor' write $TOC # Print the header debug ' Inside STM module, gathering initial data' write '---+ Parameters' write '|*Count*|',$COUNT,'|' write '|*Interval*|',$INTERVAL,' sec|' write '|*Logon User*|',getSetting('SQL_LOGIN'),'|' write '|*Database Instance Name*|',getSetting('ORACLE_SID'),'|' set $sql {SELECT 'version=' || version " FROM v$instance; } if $VERSION10 {append $sql {SELECT 'siz=' || TO_CHAR(ROUND(current_size / 1048576,2)) " FROM v$sga_dynamic_components " WHERE component = 'streams pool'; } } call loadSql($sql) if grepLastSql('version=') write '|*Database Version*|',value(last),' |' if grepLastSql('siz=') write '|*Streams Pool Size*|',value(last),' MiB|' write $TOP # Get the reference data macro debug_data {write '' write $arg[0] write '' write '---' write '' call writeLastSql() write '' } var ($sql,%ref,%tb_app,%tb_cap,%tb_gpr,%tb_gqn,%tb_gqs,%tb_prp,%tb_que) = () call get_ref_data() if $DEBUG call debug_data($sql) call get_ref_info() # Monitor the Streams write '---+ Streams Monitor Output' call write_heading() for $cnt (1,$COUNT) {# Sleep between iterations debug ' Inside STM module, sleeping ',$INTERVAL,' seconds' sleep $INTERVAL # Get the iteration data debug ' Inside STM module, gathering sample ',$cnt,' of ',$COUNT var %smp = () call get_smp_data($sql,$ref{'tps'}) if $DEBUG call debug_data($sql) call get_smp_info() } write $TOP # Print the legend write '---+ Legend' write '|*Abbreviation*|*Description*|' write '|A#|Apply process number|' write '|B|Potential bottleneck|' write '|C#|Capture process number|' write '|F|Flow control in effect|' if $LONG write '|HWm|High Watermark|' write '|Id|Queue Identifier|' write '|LCRs|Logical Change Records|' write '|Msgs|Messages|' write '|P#|Propagation process number|' if $LONG write '|SCN|System Change Number|' write '|Txns|Transactions|' write $TOP toc '2:[[',getFile(),'][rda_report][Monitor Results]]' =head1 SEE ALSO See also L. =begin credits =over 10 =item RDA 4.4: Malcolm Mckellar, James Mullaney. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut