26 Monitoring Other Streams Components

This chapter provides sample queries that you can use to monitor various Streams components.

This chapter contains these topics:

Monitoring Streams Administrators and Other Streams Users
Monitoring the Streams Pool
Monitoring Compatibility in a Streams Environment
Monitoring Streams Performance Using AWR and Statspack

Note:

The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information.

See Also:

Oracle Database Reference for information about the data dictionary views described in this chapter
Oracle Streams Replication Administrator's Guide for information about monitoring a Streams replication environment

Monitoring Streams Administrators and Other Streams Users

The following sections contain queries that you can run to list Streams administrators and other users who allow access to remote Streams administrators:

Listing Local Streams Administrators
Listing Users Who Allow Access to Remote Streams Administrators

See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about configuring Streams administrators and other Streams users using the DBMS_STREAMS_AUTH package

Listing Local Streams Administrators

You optionally can grant privileges to a local Streams administrator by running the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. The DBA_STREAMS_ADMINISTRATOR data dictionary view contains only the local Streams administrators created with the grant_privileges parameter set to true when the GRANT_ADMIN_PRIVILEGE procedure was run for the user. If you created a Streams administrator using generated scripts and set the grant_privileges parameter to false when the GRANT_ADMIN_PRIVILEGE procedure was run for the user, then the DBA_STREAMS_ADMINISTRATOR data dictionary view does not list the user as a Streams administrator.

To list the local Streams administrators created with the grant_privileges parameter set to true when running the GRANT_ADMIN_PRIVILEGE procedure, run the following query:

COLUMN USERNAME HEADING 'Local Streams Administrator' FORMAT A30

SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR
  WHERE LOCAL_PRIVILEGES = 'YES';

Your output looks similar to the following:

Local Streams Administrator
------------------------------
STRMADMIN

The GRANT_ADMIN_PRIVILEGE might not have been run on a user who is a Streams administrator. Such administrators are not returned by the query in this section. Also, you can change the privileges for the users listed after the GRANT_ADMIN_PRIVILEGE procedure has been run for them. The DBA_STREAMS_ADMINISTRATOR view does not track these changes unless they are performed by the DBMS_STREAMS_AUTH package. For example, you can revoke the privileges granted by the GRANT_ADMIN_PRIVILEGE procedure for a particular user using the REVOKE SQL statement, but this user would be listed when you query the DBA_STREAMS_ADMINISTRATOR view.

Oracle recommends using the REVOKE_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package to revoke privileges from a user listed by the query in this section. When you revoke privileges from a user using this procedure, the user is removed from the DBA_STREAMS_ADMINISTRATOR view.

See Also:

"Configuring a Streams Administrator"

Listing Users Who Allow Access to Remote Streams Administrators

You can configure a user to allow access to remote Streams administrators by running the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_STREAMS_AUTH package. Such a user allows the remote Streams administrator to perform administrative actions in the local database using a database link.

Typically, you configure such a user at a local source database if a downstream capture process captures changes originating at the local source database. The Streams administrator at a downstream capture database administers the source database using this connection.

To list the users who allow to remote Streams administrators, run the following query:

COLUMN USERNAME HEADING 'Users Who Allow Remote Access' FORMAT A30

SELECT USERNAME FROM DBA_STREAMS_ADMINISTRATOR
  WHERE ACCESS_FROM_REMOTE = 'YES';

Your output looks similar to the following:

Users Who Allow Remote Access
------------------------------
STRMREMOTE

Monitoring the Streams Pool

The Streams pool is a portion of memory in the SGA that is used by Streams. The Streams pool stores enqueued messages in memory, and it provides memory for capture processes and apply processes. The Streams pool always stores LCRs captured by a capture process, and it can store user-enqueued messages.

The Streams pool size is managed by Automatic Shared Memory Management when the SGA_TARGET initialization parameter is set to a nonzero value. If this parameter is set to 0 (zero), then you can specify the size of the Streams pool in bytes using the STREAMS_POOL_SIZE initialization parameter. In this case, the V$STREAMS_POOL_ADVICE dynamic performance view provides information about an appropriate setting for the STREAMS_POOL_SIZE initialization parameter.

This section contains example queries that show when you should increase, retain, or decrease the size of the Streams pool. Each query shows the following information about the Streams pool:

STREAMS_POOL_SIZE_FOR_ESTIMATE shows the size, in megabytes, of the Streams pool for the estimate. The size ranges from values smaller than the current Streams pool size to values larger than the current Streams pool size, and there is a separate row for each increment. There always is an entry that shows the current Streams pool size, and there always are 20 increments. The range and the size of the increments depend on the current size of the Streams pool.
STREAMS_POOL_SIZE_FACTOR shows the size factor of an estimate as it relates to the current size of the Streams pool. For example, a size factor of .2 means that the estimate is for 20% of the current size of the Streams pool, while a size factor of 1.6 means that the estimate is for 160% of the current size of the Streams pool. The row with a size factor of 1.0 shows the current size of the Streams pool.
ESTD_SPILL_COUNT shows the estimated number messages that will spill from memory to the queue table for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.
ESTD_SPILL_TIME shows the estimated elapsed time, in seconds, spent spilling messages from memory to the queue table for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.
ESTD_UNSPILL_COUNT shows the estimated number messages that will unspill from the queue table back into memory for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.
ESTD_UNSPILL_TIME shows the estimated elapsed time, in seconds, spent unspilling messages from the queue table back into memory for each STREAMS_POOL_SIZE_FOR_ESTIMATE and STREAMS_POOL_SIZE_FACTOR returned by the query.

If any capture processes, propagations, or apply processes are disabled when you query the V$STREAMS_POOL_ADVICE view, and you plan to enable them in the future, then make sure you consider the memory resources required by these Streams clients before you decrease the size of the Streams pool.

Tips:

In general, the best size for the Streams pool is the smallest size for which spilled and unspilled messages and times are close to zero.
For the most accurate results, you should run a query on the V$STREAMS_POOL_ADVICE view when there is a normal amount of dequeue activity by propagations and apply processes in a database. If dequeue activity is far lower than normal, or far higher than normal, then the query results might not be a good guide for adjusting the size of the Streams pool.

See Also:

Streams Pool
"Setting Initialization Parameters Relevant to Streams" for more information about the STREAMS_POOL_SIZE initialization parameter

Query Result that Advises Increasing the Streams Pool Size

Consider the following results returned by the V$STREAMS_POOL_ADVICE view:

COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE HEADING 'Streams Pool Size|for Estimate(MB)'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99

SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

                  Streams Pool Estimated    Estimated Estimated    Estimated
Streams Pool Size         Size     Spill        Spill   Unspill      Unspill
 for Estimate(MB)       Factor     Count         Time     Count         Time
----------------- ------------ --------- ------------ --------- ------------
               24           .1       158        62.00         0          .00
               48           .2       145        59.00         0          .00
               72           .3       137        53.00         0          .00
               96           .4       122        50.00         0          .00
              120           .5       114        48.00         0          .00
              144           .6       103        45.00         0          .00
              168           .7        95        39.00         0          .00
              192           .8        87        32.00         0          .00
              216           .9        74        26.00         0          .00
              240          1.0        61        21.00         0          .00
              264          1.1        56        17.00         0          .00
              288          1.2        43        15.00         0          .00
              312          1.3        36        11.00         0          .00
              336          1.4        22         8.00         0          .00
              360          1.5         9         2.00         0          .00
              384          1.6         0          .00         0          .00
              408          1.7         0          .00         0          .00
              432          1.8         0          .00         0          .00
              456          1.9         0          .00         0          .00
              480          2.0         0          .00         0          .00

Based on these results, 384 megabytes, or 160% of the size of the current Streams pool, is the optimal size for the Streams pool. That is, this size is the smallest size for which the estimated number of spilled and unspilled messages is zero.

Note:

After you adjust the size of the Streams pool, it might take some time for the new size to result in new output for the V$STREAMS_POOL_ADVICE view.

Query Result that Advises Retaining the Current Streams Pool Size

Consider the following results returned by the V$STREAMS_POOL_ADVICE view:

COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE  HEADING 'Streams Pool|Size for Estimate'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99
 
SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

                  Streams Pool Estimated    Estimated Estimated    Estimated
Streams Pool Size         Size     Spill        Spill   Unspill      Unspill
 for Estimate(MB)       Factor     Count         Time     Count         Time
----------------- ------------ --------- ------------ --------- ------------
               24           .1        89        52.00         0          .00
               48           .2        78        48.00         0          .00
               72           .3        71        43.00         0          .00
               96           .4        66        37.00         0          .00
              120           .5        59        32.00         0          .00
              144           .6        52        26.00         0          .00
              168           .7        39        20.00         0          .00
              192           .8        27        12.00         0          .00
              216           .9        15         5.00         0          .00
              240          1.0         0          .00         0          .00
              264          1.1         0          .00         0          .00
              288          1.2         0          .00         0          .00
              312          1.3         0          .00         0          .00
              336          1.4         0          .00         0          .00
              360          1.5         0          .00         0          .00
              384          1.6         0          .00         0          .00
              408          1.7         0          .00         0          .00
              432          1.8         0          .00         0          .00
              456          1.9         0          .00         0          .00
              480          2.0         0          .00         0          .00

Based on these results, the current size of the Streams pool is the optimal size. That is, this size is the smallest size for which the estimated number of spilled and unspilled messages is zero.

Query Result that Advises Decreasing the Streams Pool Size

Consider the following results returned by the V$STREAMS_POOL_ADVICE view:

COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE  HEADING 'Streams Pool|Size for Estimate'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99
 
SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

                  Streams Pool Estimated    Estimated Estimated    Estimated
Streams Pool Size         Size     Spill        Spill   Unspill      Unspill
 for Estimate(MB)       Factor     Count         Time     Count         Time
----------------- ------------ --------- ------------ --------- ------------
               24           .1       158        62.00         0          .00
               48           .2       145        59.00         0          .00
               72           .3       137        53.00         0          .00
               96           .4       122        50.00         0          .00
              120           .5       114        48.00         0          .00
              144           .6       103        45.00         0          .00
              168           .7         0          .00         0          .00
              192           .8         0          .00         0          .00
              216           .9         0          .00         0          .00
              240          1.0         0          .00         0          .00
              264          1.1         0          .00         0          .00
              288          1.2         0          .00         0          .00
              312          1.3         0          .00         0          .00
              336          1.4         0          .00         0          .00
              360          1.5         0          .00         0          .00
              384          1.6         0          .00         0          .00
              408          1.7         0          .00         0          .00
              432          1.8         0          .00         0          .00
              456          1.9         0          .00         0          .00
              480          2.0         0          .00         0          .00

Based on these results, 168 megabytes, or 70% of the size of the current Streams pool, is the optimal size for the Streams pool. That is, this size is the smallest size for which the estimated number of spilled and unspilled messages is zero.

Note:

After you adjust the size of the Streams pool, it might take some time for the new size to result in new output for the V$STREAMS_POOL_ADVICE view.

Monitoring Compatibility in a Streams Environment

The queries in the following sections show Streams compatibility for tables in the local database:

Listing the Database Objects that Are Not Compatible with Streams
Listing the Database Objects that Have Become Compatible with Streams Recently

Listing the Database Objects that Are Not Compatible with Streams

A database object is not compatible with Streams if a capture process cannot capture changes to the object. The query in this section displays the following information about objects that are not compatible with Streams:

The object owner
The object name
The reason why the object is not compatible with Streams
Whether capture processes automatically filter out changes to the object (AUTO_FILTERED column)

If capture processes automatically filter out changes to an object, then the rules sets used by the capture processes do not need to filter them out explicitly. For example, capture processes automatically filter out changes to materialized view logs. However, if changes to incompatible objects are not filtered out automatically, then the rule sets used by each capture process must filter them out to avoid errors.

For example, suppose the rule sets for a capture process instruct the capture process to capture all of the changes made to a specific schema. The query in this section shows that one object in this schema is not compatible with Streams, and that changes to the object are not filtered out automatically. In this case, you can add a rule to the negative rule set for the capture process to filter out changes to the incompatible object.

The AUTO_FILTERED column pertains only to capture processes. Apply processes do not automatically filter out LCRs that encapsulate changes to objects that are not compatible with Streams, even if the AUTO_FILTERED column is YES for the object. Such changes can result in apply errors if they are dequeued by an apply process.

Run the following query to list the objects in the local database that are not compatible with Streams:

COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A30
COLUMN REASON HEADING 'Reason' FORMAT A30
COLUMN AUTO_FILTERED HEADING 'Auto|Filtered?' FORMAT A9

SELECT OWNER, TABLE_NAME, REASON, AUTO_FILTERED FROM DBA_STREAMS_UNSUPPORTED;

Your output looks similar to the following:

Object                                                                 Auto
Owner    Object Name                    Reason                         Filtered?
-------- ------------------------------ ------------------------------ ---------
HR       MLOG$_COUNTRIES                materialized view log          YES
HR       MLOG$_DEPARTMENTS              materialized view log          YES
HR       MLOG$_EMPLOYEES                materialized view log          YES
HR       MLOG$_JOBS                     materialized view log          YES
HR       MLOG$_JOB_HISTORY              materialized view log          YES
HR       MLOG$_LOCATIONS                materialized view log          YES
HR       MLOG$_REGIONS                  materialized view log          YES
IX       AQ$_ORDERS_QUEUETABLE_G        IOT with overflow              NO
IX       AQ$_ORDERS_QUEUETABLE_H        unsupported column exists      NO
IX       AQ$_ORDERS_QUEUETABLE_I        unsupported column exists      NO
IX       AQ$_ORDERS_QUEUETABLE_S        AQ queue table                 NO
IX       AQ$_ORDERS_QUEUETABLE_T        AQ queue table                 NO
IX       ORDERS_QUEUETABLE              column with user-defined type  NO
OE       CATEGORIES_TAB                 column with user-defined type  NO
OE       CUSTOMERS                      column with user-defined type  NO
OE       PRODUCT_REF_LIST_NESTEDTAB     column with user-defined type  NO
OE       SUBCATEGORY_REF_LIST_NESTEDTAB column with user-defined type  NO
OE       WAREHOUSES                     column with user-defined type  NO
PM       ONLINE_MEDIA                   column with user-defined type  NO
PM       PRINT_MEDIA                    column with user-defined type  NO
PM       TEXTDOCS_NESTEDTAB             column with user-defined type  NO
SH       MVIEW$_EXCEPTIONS              unsupported column exists      NO
SH       SALES_TRANSACTIONS_EXT         external table                 NO

Notice that the Auto Filtered? column is YES for the oe.mlog$_orders materialized view log. Each capture process automatically filters out changes to this object, even if the rules sets for a capture process instruct the capture process to capture changes to the object.

Because the Auto Filtered? column is NO for the other objects listed in the example output, capture processes do not filter out changes to these objects automatically. If a capture process attempts to process LCRs for these unsupported objects, then the capture process raises an error. However, you can avoid these errors by configuring rules sets that instruct the capture process not to capture changes to these unsupported objects.

Note:

The results of the query in this section depend on the compatibility level of the database. More database objects are incompatible with Streams at lower compatibility levels. The COMPATIBLE initialization parameter controls the compatibility level of the database.

See Also:

Chapter 6, "How Rules Are Used in Streams"
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter

Listing the Database Objects that Have Become Compatible with Streams Recently

The query in this section displays the following information about database objects that have become compatible with Streams in a recent release of Oracle:

The object owner
The object name
The reason why the object was not compatible with Streams in previous releases of Oracle
The Oracle release in which the object became compatible with Streams

Run the following query to display this information for the local database:

COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20
COLUMN REASON HEADING 'Reason' FORMAT A30
COLUMN COMPATIBLE HEADING 'Compatible' FORMAT A10

SELECT OWNER, TABLE_NAME, REASON, COMPATIBLE FROM DBA_STREAMS_NEWLY_SUPPORTED;

Your output looks similar to the following:

Owner      Object Name          Reason                         Compatible
---------- -------------------- ------------------------------ ----------
HR         COUNTRIES            IOT                            10.1
OUTLN      OL$                  unsupported column exists      10.1
SH         CAL_MONTH_SALES_MV   unsupported column exists      10.1
SH         FWEEK_PSCAT_SALES_MV unsupported column exists      10.1
SH         PLAN_TABLE           unsupported column exists      10.1
DBSNMP     MGMT_BSLN_RAWDATA    IOT                            10.1
HR         COUNTRIES            IOT                            10.1
IX         AQ$_ORDERS_QUEUETABL IOT with overflow              10.2
           E_G
OUTLN      OL$                  unsupported column exists      10.1
SH         CAL_MONTH_SALES_MV   unsupported column exists      10.1
SH         FWEEK_PSCAT_SALES_MV unsupported column exists      10.1
SH         PLAN_TABLE           unsupported column exists      10.1
STRMADMIN  AQ$_STREAMS_QUEUE_TA IOT with overflow              10.2
           BLE_D
STRMADMIN  AQ$_STREAMS_QUEUE_TA IOT with overflow              10.2
           BLE_G
WMSYS      AQ$_WM$EVENT_QUEUE_T IOT with overflow              10.2
           ABLE_G

The Compatible column shows the minimum database compatibility for Streams to support the object. If the local database compatibility is equal to or higher than the value in the Compatible column for an object, then capture processes and apply processes can process changes to the object successfully. You control the compatibility of an Oracle database using the COMPATIBLE initialization parameter.

If your Streams environment includes databases that are running different versions of the Oracle Database, then you can configure rules that use the GET_COMPATIBLE member function for LCRs to filter out LCRs that are not compatible with particular databases. These rules can be added to the rule sets of capture processes, propagations, and apply processes to filter out incompatible LCRs wherever necessary in a stream.

See Also:

Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
"Rule Conditions that Instruct Streams Clients to Discard Unsupported LCRs" for information about creating rules that use the GET_COMPATIBLE member function for LCRs
"Listing the Database Objects that Are Not Compatible with Streams" for more information about objects that are not compatible with Streams

Monitoring Streams Performance Using AWR and Statspack

You can use Automatic Workload Repository (AWR) to monitor performance statistics related to Streams. If AWR is not available on your database, then you can use the Statspack package to monitor performance statistics related to Streams. The most current instructions and information on installing and using the Statspack package are contained in the spdoc.txt file installed with your database. Refer to that file for Statspack information. On Unix systems, the file is located in the ORACLE_HOME/rdbms/admin directory. On Windows systems, the file is located in the ORACLE_HOME\rdbms\admin directory.

See Also:

Oracle Database Performance Tuning Guide for more information about AWR