| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-01 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-01 |
|
|
View PDF |
This chapter provides sample queries that you can use to monitor various Streams components.
This chapter contains these topics:
|
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:
|
The following sections contain queries that you can run to list Streams administrators and other users who allow access to remote 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 theDBMS_STREAMS_AUTH package |
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.
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
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:
|
|
See Also:
|
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 theV$STREAMS_POOL_ADVICE view. |
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.
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 theV$STREAMS_POOL_ADVICE view. |
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
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. TheCOMPATIBLE initialization parameter controls the compatibility level of the database. |
|
See Also:
|
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:
|
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.
