STRMMON – Streams Monitoring Program

Overview

 

STRMMON is a monitoring tool focused on Oracle Streams.  Using this tool, database administrators get a quick overview of the Streams activity occurring within a database. In a single line display, strmmon reports information    The output format comes in two formats:  default and long.  The default format reports the rate of activity occuring for Streams processes.  The long format provides the detailed information that was available in previous releases of STRMMON.  The reporting interval and number of iterations to display are configurable. 

 

STRMMON can also be used to report Streams activity on two databases  at a time within the same strmmon session. 

Usage

There are 7 command line input parameters for STRMMON:  interval, count, user, passw, dbname, sysdba and long. The first 2 parameters (interval and count) control the sampling rate and the amount of output.  The next 4 parameters specify the connect information to the particular Streams database. Use multiple occurrences of these 4 parameters to monitor multiple databases within the same strmmon command.  Specifying the last parameter (long) displays more detailed
information about each process.

 

When the command strmmon is issued without any parameters, a usage message is displayed:

 

% strmmon

Usage: strmmon -interval <seconds> -count <number> [-user <user name>]

               [-passw <password>] [-dbname <database name>] [-sysdba] -long

 

Parameters

 

Parameter Name	Value Units	Description
-interval	seconds	The interval at which STRMMON will monitor the database.  To specify that the sampling rate to be every 3 seconds:  -interval 3  This is a required parameter for strmmon.
-count	Number	The number of iterations to monitor the Streams environment.  To specify 5 iterations, use the following:  -count 5 This is a required parameter for strmmon.
-user	Username	The schema name for logging into the database.  Any schema name can be specified.  If  the SYS schema is specified, additional information is displayed.  To specify the SYSTEM schema,  use -user SYSTEM This parameter should not be specified if logging in as “/ as sysdba” is desired. -user  is an optional parameter for strmmon.
-passw	password	The login password for the schema identified with the –user clause.  To specify the password for the SYSTEM schema, use -passw  oracle This parameter should not be specified if logging in as “/ as sysdba” is desired -passw is an optional parameter for strmmon.
-dbname	service_name	The connection information or service name from tnsnames.ora for the specific database to be monitored.  To specify the connect information for the monitored database, use -dbname ORCL.WORLD This is an optional parameter for strmmon.
-sysdba		This flag indicates that the login role is SYSDBA.  This optional parameter is ypically used with the SYS schema.  To specify the login role SYSDBA, use -sysdba When logging in as “/ as sysdba”,  the –user and –passw parameters are not required.
-long		This flag indicates that the more detailed report is desired.  This is an  optional parameter for STRMMON.  By default, only the capture, apply and propagation rates are displayed.

 

 

Output

 

The strmmon output begins with a banner line identifying the program parameters and database.  This information is followed with a brief description of the major components of the output display. The Streams Pool Size line is displayed  for database versions 10g and above. An example of this identifying output  for the default report is shown below:

 

Example 1:  Single Database  Default output (Streams rates)

% strmmon -interval 3 -count 5 -sysdba

STREAMS Monitor, v 2.2 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5 

Logon= @ ORACLE 10.2.0.2.0

Streams Pool Size = 152M

LOG : <redo generated per sec>
NET: <client bytes per sec> <dblink bytes per sec>
Cxxx: <lcrs captured per sec> <lcrs enqueued per sec> <capture latency>
MEM : <percent of memory used> % <streams pool size>
PRxx: <messages received per sec>
Qx : <msgs enqueued per sec> <msgs spilled per sec>
PSxx: <lcrs propagated per sec> <bytes propaged per sec>
Axxx: <lcrs applied per sec> <txns applied per sec> <dequeue latency>
<F>: flow control in effect
<B>: potential bottleneck
<x%I x%F x%xx>: <idle wait events percentage> <flow control wait events percentage> <other wait event percentage and name>
xx->: database instance name


2006-02-21 14:31:47 || lnxmain-> | | | | MEM 7 % 152M
2006-02-21 14:31:47 || lnxmain-> | LOG 170 | NET 5K 0 | - C001 0 0 0sec <100%I 0%F> | Q44727 0 0 | PS01 0 0 <97%I 0%F> | Q44678 0 0 | - A001 0 0 2sec <0%I 0%F> | - A002 0 0 0sec <0%I 0%F> | MEM 7 % 152M
2006-02-21 14:31:50 || lnxmain-> | LOG 0 | NET 5K 0 | - C001 0 0 0sec <97%I 0%F> | Q44727 0 0 | PS01 0 0 <97%I 0%F> | Q44678 0 0 | - A001 0 0 2sec <0%I 0%F> | - A002 0 0 0sec <0%I 0%F> | MEM 7 % 152M
2006-02-21 14:31:53 || lnxmain-> | LOG 341 | NET 5K 0 | - C001 1 0 0sec <94%I 0%F> | Q44727 0 0 | PS01 0 0 <97%I 0%F> | Q44678 0 0 | - A001 0 0 2sec <0%I 0%F> | - A002 0 0 0sec <0%I 0%F> | MEM 7 % 152M
2006-02-21 14:31:56 || lnxmain-> | LOG 0 | NET 5K 0 | - C001 0 0 0sec <97%I 0%F> | Q44727 0 0 | PS01 0 0 <97%I 0%F> | Q44678 0 0 | - A001 0 0 2sec <0%I 0%F> | - A002 0 0 0sec <0%I 0%F> | MEM 7 % 152M
ade:[ pmcelroy_lnxmain ] [pmcelroy@stadf18 ~/strmmon]$ | LOG 0 | NET 5K 0 | - C001 0 0 0sec <97%I 0%F> | Q44727 0 0 | PS01 0 0 <97%I 0%F> | Q44678 0 0 | - A001 0 0 2sec <0%I 0%F> | - A002 0 0 0sec <0%I 0%F> | MEM 7 % 152M

Example 2:  Single Database  Long output (Reports statistic values)

 

The following example demonstrates connecting to multiple databases and generating the detailed output (-long parameter):

 

% strmmon -interval 3 -count 5 -sysdba -long

 

STREAMS Monitor, v 2.2 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5 

Logon= @ ORACLE 10.2.0.2.0

Streams Pool Size = 152M

LOG : <last write scn> <redo blocks written>
NET: <client bytes> <dblink bytes>
Cxxx: <read scn> <msgs captured> <capture scn> <msgs enqueued> <enqueue scn> <capture latency>
MEM : <percent of memory used> % <streams pool size>
PRxx: <number received>/<total time>
Qx : <outstanding mesgs>/<cumulative mesgs> <cumulative spilled>
PSxx: <number propagated> <total bytes>/<total time>
Axxx: <msgs deq'd> <dequeue scn> <dequeue latency> <txns recv'd> <txns assigned> <txns applied> <hwm scn> <hwm latency>
<F>: flow control in effect
<B>: potential bottleneck
<x%I x%F x%xx>: <idle wait events percentage> <flow control wait events percentage> <other wait event percentage and name>
xx->: database instance name

2006-02-21 14:31:28 || lnxmain-> | LOG 339067 199137 | NET 1834923 299067 | C001 339066 23887 339056 118 338885 0sec | Q44727 0/97 0 | PS01 97 71483/554 | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec | - A002 1 1 0sec 1 1 1 1 0sec | MEM 7 % 152M
2006-02-21 14:31:28 || lnxmain-> | LOG 339067 199137 | NET 1854885 299067 | - C001 339066 23890 339066 118 338885 0sec <100%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M
2006-02-21 14:31:31 || lnxmain-> | LOG 339074 199138 | NET 1874867 299067 | - C001 339070 23890 339066 118 338885 0sec <68%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M
2006-02-21 14:31:34 || lnxmain-> | LOG 339082 199140 | NET 1894849 299067 | - C001 339081 23893 339081 118 338885 0sec <100%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M
2006-02-21 14:31:37 || lnxmain-> | LOG 339082 199140 | NET 1914811 299067 | - C001 339081 23893 339081 118 338885 0sec <97%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M

 

Example 3:  Connect to Two Databases  Long output (Reports statistic values)

 

The following command demonstrates connecting to multiple database and generating the long output:

 

% strmmon -interval 3 -count 5 -user sys -passw change_on_install -dbname inst1 -sysdba -user sys -passw change_on_install -dbname inst2 -sysdba –long

 

 

STREAMS Monitor, v 2.2 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5 

Logon=sys@inst1 ORACLE 10.2.0.2.0

Streams Pool Size = 152M

LOG : <last write scn> <redo blocks written>
NET: <client bytes> <dblink bytes>
Cxxx: <read scn> <msgs captured> <capture scn> <msgs enqueued> <enqueue scn> <capture latency>
MEM : <percent of memory used> % <streams pool size>
PRxx: <number received>/<total time>
Qx : <outstanding mesgs>/<cumulative mesgs> <cumulative spilled>
PSxx: <number propagated> <total bytes>/<total time>
Axxx: <msgs deq'd> <dequeue scn> <dequeue latency> <txns recv'd> <txns assigned> <txns applied> <hwm scn> <hwm latency>
<F>: flow control in effect
<B>: potential bottleneck
<x%I x%F x%xx>: <idle wait events percentage> <flow control wait events percentage> <other wait event percentage and name>
xx->: database instance name

 

 

STREAMS Monitor, v 2.2 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5 

Logon=sys@inst2 ORACLE 10.2.0.2.0

Streams Pool Size = 152M

LOG : <last write scn> <redo blocks written>
NET: <client bytes> <dblink bytes>
Cxxx: <read scn> <msgs captured> <capture scn> <msgs enqueued> <enqueue scn> <capture latency>
MEM : <percent of memory used> % <streams pool size>
PRxx: <number received>/<total time>
Qx : <outstanding mesgs>/<cumulative mesgs> <cumulative spilled>
PSxx: <number propagated> <total bytes>/<total time>
Axxx: <msgs deq'd> <dequeue scn> <dequeue latency> <txns recv'd> <txns assigned> <txns applied> <hwm scn> <hwm latency>
<F>: flow control in effect
<B>: potential bottleneck
<x%I x%F x%xx>: <idle wait events percentage> <flow control wait events percentage> <other wait event percentage and name>
xx->: database instance name

 

2006-02-21 18:55:39 || lnxmain-> | LOG 368319 219734 | NET 2309572 605728 | C001 368318 36098 368318 118 338885 0sec | Q44727 0/97 0 | PS01 97 71483/554 | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec | - A002 1 1 0sec 1 1 1 1 0sec | MEM 7 % 152M || lnxmain2-> | LOG 368301 211346 | NET 1005612 1252294 | C001 368300 24693 368300 103 338666 0sec | Q44783 0/78 0 | PS01 78 53187/112 | Q44734 0/97 0 | - A001 97 338885 - 15 15 15 338797 2sec | MEM 7 % 152M
2006-02-21 18:55:40 || lnxmain-> | LOG 368333 219737 | NET 2329405 605728 | - C001 368332 36098 368318 118 338885 0sec <97%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M || lnxmain2-> | LOG 368302 211347 | NET 1023918 1252294 | - C001 368301 24693 368301 103 338666 0sec <100%I 0%F> | Q44783 0/78 0 | PS01 78 53187/112 <100%I 0%F> | Q44734 0/97 0 | - A001 97 338885 - 15 15 15 338797 2sec <97%I 0%F> | MEM 7 % 152M
2006-02-21 18:55:43 || lnxmain-> | LOG 368333 219737 | NET 2349238 605728 | - C001 368332 36101 368332 118 338885 3sec <97%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M || lnxmain2-> | LOG 368306 211349 | NET 1042226 1252294 | - C001 368305 24696 368305 103 338666 0sec <100%I 0%F> | Q44783 0/78 0 | PS01 78 53187/112 <97%I 0%F> | Q44734 0/97 0 | - A001 97 338885 - 15 15 15 338797 2sec <97%I 0%F> | MEM 7 % 152M
2006-02-21 18:55:46 || lnxmain-> | LOG 368340 219738 | NET 2369052 605728 | - C001 368336 36101 368332 118 338885 3sec <97%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <97%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <97%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M || lnxmain2-> | LOG 368306 211349 | NET 1060534 1252294 | - C001 368305 24696 368305 103 338666 0sec <97%I 0%F> | Q44783 0/78 0 | PS01 78 53187/112 <97%I 0%F> | Q44734 0/97 0 | - A001 97 338885 - 15 15 15 338797 2sec <97%I 0%F> | MEM 7 % 152M
2006-02-21 18:55:49 || lnxmain-> | LOG 368340 219738 | NET 2388866 605728 | - C001 368336 36101 368336 118 338885 3sec <97%I 0%F> | Q44727 0/97 0 | PS01 97 71483/554 <100%I 0%F> | Q44678 0/78 0 | - A001 78 338666 - 1 1 1 338642 2sec <100%I 0%F> | - A002 1 1 0sec 1 1 1 1 0sec <97%I 0%F> | MEM 7 % 152M || lnxmain2-> | LOG 368308 211350 | NET 1078842 1252294 | - C001 368307 24696 368305 103 338666 0sec <97%I 0%F> | Q44783 0/78 0 | PS01 78 53187/112 <97%I 0%F> | Q44734 0/97 0 | - A001 97 338885 - 15 15 15 338797 2sec <97%I 0%F> | MEM 7 % 152M

 

 

 

Output Description

After this initial  information about the program, Strmmon produces a single line of output representing the current status of Oracle Streams after the requested interval for each iteration . For example, if strmmon is invoked with “–interval 3  -count 5” ,  a line of output will be displayed every 3 seconds.  After 5 lines have been displayed (15 seconds), the monitoring will end. .

 

Each line is composed of multiple blocks of information dependent on the streams processes configured within the database.   These blocks are displayed by a keyword to identify the component followed by the statistics for that particular component.  The separator between the components is the “|” symbol. 

 

There are 6 components for Streams:  LOG,  CPxx, Qx, PRxx, PSxx, APxx.   Except for the LOG and NET components, multiple occurrences of each component are possible dependent on the streams processes configured at database. 

 

LOG

Information about the redo log activity is written in this block.  The first statistic following the LOG: keyword is the current SCN that has been written to the redo log.  This number represents the current activity within the database.  If this number does not increase, no activity is occurring on the database.  The second statistic is the last block number written in the redo log.  Redo blocks are always 512 bytes, so this statistic can be used to calculate the amount of redo generated between intervals.   The output for the LOG component under the -long option is always the first entry after the timestamp and the database name on the display and appears as follows:

2005-08-13 18:05:23 || lnxmain-> | LOG 2952151 812574 | 
2005-08-13 18:05:23 || lnxmain-> | LOG 2952151 812574 | 
2005-08-13 18:05:27 || lnxmain-> | LOG 2952155 812576 | 
2005-08-13 18:05:30 || lnxmain-> | LOG 2952156 812577 | 
2005-08-13 18:05:33 || lnxmain-> | LOG 2952156 812577 |

 

In the above example, the current scn that written to the redo log is 2952151 and the last block number is 812574.  Since the strmmon command was issued with –interval 3 –count 5,  5 lines of output are displayed with a 3 second interval between them.  The activity on this database is very low, as seen by the fact that the current scn increases infrequently (note change between 18:05:23 and 18:05:27 and 18:05:30). 

 

For the default output, the LOG component entry shows the rate at which redo information is generated:

2005-08-13 18:08:51 || lnxmain-> | LOG 1K | 
2005-08-13 18:08:54 || lnxmain-> | LOG 341 | 
2005-08-13 18:08:57 || lnxmain-> | LOG 170 | 
2005-08-13 18:09:00 || lnxmain-> | LOG 0 | 

The printed value is the average number of bytes of redo generated per second over the specified interval.

 

NET

For the default output, the average number of bytes per second generated over the network by clients and dblinks is reported.  For the -long output, the current values for  the statistics of client and dblink network activity are reported.

 

Cxxx

For each capture process configured in the database, a separate block will be displayed. Each block displays the number of lcrs captured per sec, number of lcrs enqueued per sec and the capture latency. If "-long" option is specified, then in each block, the Logminer read scn, the total number of messages captured from the redo log and the most recent scn captured from the redo log are shown. In addition, the number of messages that match the rules specified for the capture process including the most recent message scn enqueued are shown along with the capture latency. 

One can also use the difference between successive capture "messages captured" statistics to determine the rate at which capture is mining the redo log. The enqueue scn of capture is an indicator of where the capture process will restart, if capture is stopped and restarted while the database is running. This statistic can also be used for comparison with the appropriate database Apply process high-water mark scn. If these statistics match, the capture and apply are caught up and the data is synchronized.

MEM%

If strmmon is run from the SYS schema connected as SYSDBA, and the database version is 10gR1 or higher, then this displays the percentage of Streams Pool memory currently in use and the size of the Streams Pool.   These statistics are reported in both the default and –long output formats.

Qx

For each streams queue in the database, a separate block displays the queue identifier, the cumulative message rate and the spill rate. If "-long" option is specified, then for each streams queue in the database, a separate block will display the queue identifier as well as the number of outstanding messages in the buffered queue, the cumulative number of messages that have been in the queue and the number of messages spilled from memory to disk. In version 9iR2, the number of messages currently spilled is displayed. In 10gR1, the cumulative number of messages spilled is displayed. For 9iR2, the default and the "-long" option display the same data.

The queue identifier (QID) can be used to identify the name of the queue. Use the QID in queries against the DBA_QUEUES view to identify the particular queue in the database. In Oracle Database 9i Release 2, the number of outstanding messages in the buffered queue is only displayed if strmmon is run from the SYS schema as SYSDBA. In 9iR2, if the number of spilled messages becomes non-zero, consider stopping capture temporarily to slow down the flow of data.  See Metalink  article 259609.1 for an example flow control script.

 

PSxx

 

For each propagation sender, the number of lcrs propagated per sec is displayed. If "-long" option is specified, the for each propagation, the total number of messages and the total number of bytes propagated to the destination site and the total time needed to propagate those messages is displayed.

PRxx

For each propagation receiver, the number of lcrs received per sec is displayed.  If "-long" option is specified, the for each propagation, the total number of messages received and the total time to receive  those messages is displayed

Axxx

For each apply process, the number of lcrs applied per sec, the number of transactions applied per second, and the apply latency are displayed. If the "-long" option is specified, then for each apply process in the database,  the total messages dequeued and the most recent scn dequeued by the apply reader, the dequeue latency, the total number of transactions received, assigned and applied by the coordinator are displayed. The apply high water mark scn along with the apply high water mark latency is also shown. This statistic records the most recent scn from the source site that has been applied at the destination site.

Flags

<F>

This indicates that the capture process is blocked due to flow control.

<B>

This indicates that the capture or apply process is currently a bottleneck.

<x%I x%F x%xx>

 

When displayed for a component,  x%I indicates the percentage(x%) of idle(I) time for the component.  x%F indicates the percentage(x%) of time waiting on flow control events(F). The event with the highest percent (x%) of time waited is indicated(xx)the percentage of time(x%) spent waiting on t<other wait event percentage and name>

xx->

This indicates the name of the database instance for which the data follows.

Installation

STRMMON is delivered as a tar file that includes an OCI program and make file for use with Oracle releases prior to 10g Release 2.  Click here to download the STRMMON zip file. Change directory to a new directory.  Extract the files from the zip file.

 

For releases prior to Oracle Database 10g Release 2, use the following instructions. Create a directory for the STRMMON files.  Extracted the files from the downloaded STRMMON.zip file.  Before compiling and linking the strmmon program, make sure that the ORACLE_HOME and LD_LIBRARY_PATH environmental variables are set up appropriately.  To compile and link, use the command:

 

    make –f strmmon.mk strmmon

 

After the program has been compiled and linked, strmmon can be used for monitoring a Streams environment.  STRMMON is an OCI program.  If you have difficulty using the supplied make file,  use the recommended procedure for compiling and linking OCI programs for your platform.