This chapter provides sample queries that you can use to monitor various Oracle Streams components.
The following topics describe monitoring various Oracle Streams components:
Monitoring Oracle Streams Administrators and Other Oracle Streams Users
Monitoring Oracle Streams Performance Using AWR and Statspack
Note:
The Oracle Streams tool in Oracle Enterprise Manager Cloud Control is also an excellent way to monitor an Oracle Streams environment. See the online Help for the Oracle Streams tool for more information.See Also:
Oracle Database Reference for information about the data dictionary views described in this chapterThe following sections contain queries that you can run to list Oracle Streams administrators and other users who allow access to remote Oracle Streams administrators:
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about configuring Oracle Streams administrators and other Oracle Streams users using theDBMS_STREAMS_AUTH packageYou can grant privileges to a local Oracle 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 Oracle 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 an Oracle 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 an Oracle Streams administrator.
To list the local Oracle 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 an Oracle 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:
Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administratorYou can configure a user to allow access to remote Oracle Streams administrators by running the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_STREAMS_AUTH package. Such a user allows the remote Oracle 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 Oracle Streams administrator at a downstream capture database administers the source database using this connection.
To list the users who allow to remote Oracle 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 Oracle Streams pool is a portion of memory in the System Global Area (SGA) that is used by Oracle Streams. The Oracle Streams pool stores enqueued messages in memory, and it provides memory for capture processes and apply processes. The Oracle Streams pool always stores LCRs captured by a capture process, and it can store other types of messages that are enqueued manually into a buffered queue.
The Oracle Streams pool size is managed automatically when the MEMORY_TARGET, MEMORY_MAX_TARGET, or SGA_TARGET initialization parameter is set to a nonzero value. If these parameters are all set to 0 (zero), then you can specify the size of the Oracle 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 Oracle Streams pool. Each query shows the following information about the Oracle Streams pool:
STREAMS_POOL_SIZE_FOR_ESTIMATE shows the size, in megabytes, of the Oracle Streams pool for the estimate. The size ranges from values smaller than the current Oracle Streams pool size to values larger than the current Oracle Streams pool size, and there is a separate row for each increment. There always is an entry that shows the current Oracle Streams pool size, and there always are 20 increments. The range and the size of the increments depend on the current size of the Oracle Streams pool.
STREAMS_POOL_SIZE_FACTOR shows the size factor of an estimate as it relates to the current size of the Oracle Streams pool. For example, a size factor of.2 means that the estimate is for 20% of the current size of the Oracle Streams pool, while a size factor of 1.6 means that the estimate is for 160% of the current size of the Oracle Streams pool. The row with a size factor of 1.0 shows the current size of the Oracle 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 ensure that you consider the memory resources required by these Oracle Streams clients before you decrease the size of the Oracle Streams pool.
Tips:
In general, the best size for the Oracle 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 typical amount of dequeue activity by propagations and apply processes in a database. If dequeue activity is far lower than typical, or far higher than typical, then the query results might not be a good guide for adjusting the size of the Oracle Streams pool.
See Also:
Oracle Streams Replication Administrator's Guide for information about configuring the Oracle Streams pool
Oracle Streams Replication Administrator's Guide for more information about the STREAMS_POOL_SIZE initialization parameter
Consider the following results returned by the V$STREAMS_POOL_ADVICE view:
COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE HEADING 'Oracle Streams Pool Size|for Estimate(MB)'
FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Oracle 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;
Oracle Streams Pool Estimated Estimated Estimated
Oracle Streams Pool Size Size Spill Spill Unspill
for Estimate(MB) Factor Count Time Count
------------------------ ------------------- --------- ------------ ---------
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 Oracle Streams pool, is the optimal size for the Oracle 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 Oracle 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 'Oracle Streams Pool|Size for Estimate'
FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Oracle 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;
Oracle Streams Pool Estimated Estimated Estimated
Oracle Streams Pool Size Size Spill Spill Unspill
for Estimate(MB) Factor Count Time Count
------------------------ ------------------- --------- ------------ ---------
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 Oracle 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 'Oracle Streams Pool|Size for Estimate'
FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Oracle 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;
Oracle Streams Pool Estimated Estimated Estimated
Oracle Streams Pool Size Size Spill Spill Unspill
for Estimate(MB) Factor Count Time Count
------------------------ ------------------- --------- ------------ ---------
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 Oracle Streams pool, is the optimal size for the Oracle 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 Oracle Streams pool, it might take some time for the new size to result in new output for theV$STREAMS_POOL_ADVICE view.Some database objects and data types are not compatible with Oracle Streams capture processes, synchronous captures, and apply processes. If one of these Oracle Streams clients tries to process an unsupported database object or data type, errors result.
The queries in the following sections show Oracle Streams compatibility for database objects and columns in the local database:
This section contains these topics:
Listing the Database Objects That Are Not Compatible with Capture Processes
Listing the Database Objects Recently Compatible with Capture Processes
A database object is not compatible with capture processes if capture processes cannot capture changes to it. The query in this section displays the following information about database objects that are not compatible with capture processes:
The object owner
The object name
The reason why the object is not compatible with capture processes
Whether capture processes automatically filter out changes to the database object (AUTO_FILTERED column)
If capture processes automatically filter out changes to a database object, then the rule sets used by the capture processes do not need to filter them out explicitly. For example, capture processes automatically filter out changes to domain indexes. However, if changes to incompatible database objects are not filtered out automatically, then the rule sets used by the 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. Also suppose that the query in this section shows that one object in this schema is not compatible with capture processes, 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 database object.
Run the following query to list the database objects in the local database that are not compatible with capture processes:
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? -------- ------------------------------ ------------------------------ --------- IX AQ$_ORDERS_QUEUETABLE_G column with user-defined type NO IX AQ$_ORDERS_QUEUETABLE_H unsupported column exists NO IX AQ$_ORDERS_QUEUETABLE_I unsupported column exists NO IX AQ$_ORDERS_QUEUETABLE_L AQ queue table NO IX AQ$_ORDERS_QUEUETABLE_S AQ queue table NO IX AQ$_ORDERS_QUEUETABLE_T AQ queue table NO IX AQ$_STREAMS_QUEUE_TABLE_C AQ queue table NO IX AQ$_STREAMS_QUEUE_TABLE_G column with user-defined type NO IX AQ$_STREAMS_QUEUE_TABLE_H unsupported column exists NO IX AQ$_STREAMS_QUEUE_TABLE_I unsupported column exists NO IX AQ$_STREAMS_QUEUE_TABLE_L AQ queue table NO IX AQ$_STREAMS_QUEUE_TABLE_S AQ queue table NO IX AQ$_STREAMS_QUEUE_TABLE_T AQ queue table NO IX ORDERS_QUEUETABLE column with user-defined type NO IX STREAMS_QUEUE_TABLE column with user-defined type NO OE ACTION_TABLE column with user-defined type NO OE CATEGORIES_TAB column with user-defined type NO . . .
Notice that the Auto Filtered? column is YES for the sh.dr$sup_text_indx$i domain index. A capture process automatically filters out data manipulation language (DML) changes to this database object, even if the rules sets for a capture process instruct the capture process to capture changes to it. By default, a capture process also filters out data definition language (DDL) changes to these database objects. However, if you want to capture these DDL changes, then use the DBMS_CAPTURE_ADM.SET_PARAMETER procedure to set the set_autofiltered_table_ddl capture process parameter to N and configure the capture process rule sets to capture these DDL changes.
Because the Auto Filtered? column is NO for other database objects listed in the example output, capture processes do not filter out changes to these database objects automatically. If a capture process attempts to process changes to these unsupported database 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 capture processes at lower compatibility levels. The COMPATIBLE initialization parameter controls the compatibility level of the database.
For capture processes, you cannot use rule-based transformations to exclude a column of an unsupported data type. The entire database object must be excluded to avoid capture errors.
The DBA_STREAMS_UNSUPPORTED view only pertains to capture processes in Oracle Database 11g Release 1 (11.1) and later databases. This view does not pertain to synchronous captures and apply processes.
See Also:
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_CAPTURE_ADM.SET_PARAMETER procedure
The query in this section displays the following information about database objects that have become compatible with capture processes in a recent release of Oracle Database:
The object owner
The object name
The reason why the object was not compatible with capture processes in previous releases of Oracle Database
The Oracle Database release in which the object became compatible with capture processes
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;
The following is a sample of the output from this query:
Owner Object Name Reason Compatible ---------- -------------------- ------------------------------ ---------- HR COUNTRIES IOT 10.1 OE WAREHOUSES table with XMLType column 11.1 SH CAL_MONTH_SALES_MV materialized view 10.1 SH FWEEK_PSCAT_SALES_MV materialized view 10.1
The Compatible column shows the minimum database compatibility for capture processes to support the database object. If the local database compatibility is equal to or higher than the value in the Compatible column for a database object, then capture processes can capture changes to the database object successfully. You control the compatibility of a database using the COMPATIBLE initialization parameter.
If your Oracle 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, synchronous captures, propagations, and apply processes to filter out incompatible LCRs wherever necessary in a stream.
Note:
TheDBA_STREAMS_NEWLY_SUPPORTED view only pertains to capture processes in Oracle Database 11g Release 1 (11.1) and later databases. This view does not pertain to synchronous captures and apply processes.See Also:
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
"Listing the Database Objects That Are Not Compatible with Capture Processes"
"Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs" for information about creating rules that use the GET_COMPATIBLE member function for LCRs
A database object or a column in a table is not compatible with synchronous captures if synchronous captures cannot capture changes to it. For example, synchronous captures cannot capture changes to object tables. Synchronous captures can capture changes to relational tables, but they cannot capture changes to columns of some data types.
The query in this section displays the following information about database objects and columns that are not compatible with synchronous captures:
The object owner
The object name
The column name
The reason why the column is not compatible with synchronous captures
To list the columns that are not compatible with synchronous captures in the local database, run the following query:
COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20
COLUMN SYNC_CAPTURE_REASON HEADING 'Synchronous|Capture Reason' FORMAT A25
SELECT OWNER,
TABLE_NAME,
COLUMN_NAME,
SYNC_CAPTURE_REASON
FROM DBA_STREAMS_COLUMNS
WHERE SYNC_CAPTURE_VERSION IS NULL;
When a query on the DBA_STREAMS_COLUMNS view returns NULL for SYNC_CAPTURE_VERSION, it means that synchronous captures do not support the column. The WHERE clause in the query ensures that the query only returns columns that are not supported by synchronous captures.
The following is a sample of the output from this query:
Object Synchronous
Owner Object Name Column Name Capture Reason
-------- -------------------- -------------------- -------------------------
.
.
.
SH SALES_TRANSACTIONS_E UNIT_COST external table
XT
OE LINEITEM_TABLE SYS_XDBPD$ object table
OE LINEITEM_TABLE ITEMNUMBER object table
PM PRINT_MEDIA AD_FINALTEXT table with nested table c
olumn
.
.
.
To avoid synchronous capture errors, configure the synchronous capture rule set to ensure that the synchronous capture does not try to capture changes to an unsupported database object, such as an object table. To avoid synchronous capture errors while capturing changes to relational tables, you have the following options:
Configure the synchronous capture rule set to ensure that the synchronous capture does not try to capture changes to a table that contains one or more unsupported columns.
Configure rule-based transformations to exclude columns that are not supported by synchronous captures.
Note:
Synchronous capture is available in Oracle Database 11g Release 1 (11.1) and later databases. It is not available in previous releases of Oracle Database.This section contains these topics:
Listing Database Objects and Columns Not Compatible with Apply Processes
Listing Columns That Have Become Compatible with Apply Processes Recently
A database object or a column in a table is not compatible with apply processes if apply processes cannot apply changes to it. For example, apply processes cannot apply changes to object tables. Apply processes can apply changes to relational tables, but they cannot apply changes to columns of some data types.
The query in this section displays the following information about database objects and columns that are not compatible with apply processes:
The object owner
The object name
The column name
The reason why the column is not compatible with apply processes
To list the columns that are not compatible with apply processes in the local database, run the following query:
COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20
COLUMN APPLY_REASON HEADING 'Apply Process Reason' FORMAT A25
SELECT OWNER,
TABLE_NAME,
COLUMN_NAME,
APPLY_REASON
FROM DBA_STREAMS_COLUMNS
WHERE APPLY_VERSION IS NULL;
When a query on the DBA_STREAMS_COLUMNS view returns NULL for APPLY_VERSION, it means that apply processes do not support the column. The WHERE clause in the query ensures that the query only returns columns that are not supported by apply processes.
The following is a sample of the output from this query:
Object
Owner Object Name Column Name Apply Process Reason
-------- -------------------- -------------------- -------------------------
.
.
.
SH SALES_TRANSACTIONS_E CHANNEL_ID external table
XT
OE ACTION_TABLE ACTIONED_BY object table
OE LINEITEM_TABLE PART object table
PM ONLINE_MEDIA PRODUCT_AUDIO ADT column
OE CATEGORIES_TAB CATEGORY_DESCRIPTION object table
.
.
.
To avoid apply errors, configure the apply process rule sets to ensure that the apply process does not try to apply changes to an unsupported database object, such as an object table. To avoid apply errors while applying changes to relational tables, you have the following options:
Configure the apply process rule sets to ensure that the apply process does not try to apply changes to a table that contains one or more unsupported columns.
Configure rule-based transformations to exclude columns that are not supported by apply processes.
Configure procedure DML handlers to exclude columns that are not supported by apply processes.
The query in this section displays the following information about database objects and columns that have become compatible with apply processes in a recent release of Oracle Database:
The object owner
The object name
The column name
The reason why the object was not compatible with apply processes in previous releases of Oracle Database
The Oracle Database release in which the object became compatible with apply processes
Run the following query to display this information for the local database:
COLUMN OWNER HEADING 'Object|Owner' FORMAT A8
COLUMN TABLE_NAME HEADING 'Object Name' FORMAT A15
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A15
COLUMN APPLY_VERSION HEADING 'Apply|Process|Vesion' FORMAT 99.9
COLUMN APPLY_REASON HEADING 'Apply|Process Reason' FORMAT A25
SELECT OWNER,
TABLE_NAME,
COLUMN_NAME,
APPLY_VERSION,
APPLY_REASON
FROM DBA_STREAMS_COLUMNS
WHERE APPLY_VERSION > 11;
When a query on the DBA_STREAMS_COLUMNS view returns a non-NULL value for APPLY_VERSION, it means that apply processes support the column. The WHERE clause in the query ensures that the query only returns columns that are supported by apply processes. This query returns the columns that have become supported by apply processes in Oracle Database 11g Release 1 and later.
The following is a sample of the output from this query:
Apply
Object Process Apply
Owner Object Name Column Name Vesion Process Reason
-------- --------------- --------------- ------- -------------------------
OE WAREHOUSES WAREHOUSE_SPEC 11.1 XMLType column
The Apply Process Version column shows the minimum database compatibility for apply processes to support the column. If the local database compatibility is equal to or higher than the value in the Apply Process Version column for a column, then apply processes can apply changes to the column successfully. You control the compatibility of a database using the COMPATIBLE initialization parameter.
If your Oracle 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, synchronous captures, propagations, and apply processes to filter out incompatible LCRs wherever necessary in a stream.
Note:
When this query returnsNULL for Apply Process Reason, it means that the column has always been supported by apply processes since the first Oracle Database release that included Oracle Streams.See Also:
Oracle Database Reference and Oracle Database Upgrade Guide for more information about the COMPATIBLE initialization parameter
"Listing Database Objects and Columns Not Compatible with Apply Processes"
"Rule Conditions that Instruct Oracle Streams Clients to Discard Unsupported LCRs" for information about creating rules that use the GET_COMPATIBLE member function for LCRs
You can use Automatic Workload Repository (AWR) to monitor performance statistics related to Oracle Streams. If AWR is not available on your database, then you can use the Statspack package to monitor performance statistics related to Oracle Streams. The most current instructions and information about 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
Chapter 23, "Monitoring the Oracle Streams Topology and Performance" for information about monitoring performance using the Oracle Streams Performance Advisor