Is an Apply Server Performing Poorly for
Certain Transactions?
COLUMN
SERVER_ID HEADING 'Apply Server ID' FORMAT 99999999
COLUMN
STATE HEADING 'Apply Server State' FORMAT A20
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied Message|Number'
FORMAT
99999999
COLUMN MESSAGE_SEQUENCE HEADING 'Message Sequence|Number' FORMAT
99999999
SELECT SERVER_ID, STATE, APPLIED_MESSAGE_NUMBER,
MESSAGE_SEQUENCE
FROM V$STREAMS_APPLY_SERVER
ORDER BY SERVER_ID;
If this query is run repeatedly, the Apply
server state, applied message number, and message sequence
number should continue to change for each Apply server as it
applies transactions. If these values do not change for one or
more Apply servers, the Apply server may not be performing well.
For each table to which the Apply process applies changes, every
key column must have an index.
When there are a large number of tables, it
may be necessary to determine the specific table and DML or DDL
operation that is causing an Apply server to perform poorly. To
do so, run the following SQL query when an Apply server is
taking an inordinately long time to apply a transaction. In this
example, assume that the name of the Apply process is LN4_APPLY
and that Apply server number two is performing poorly:
COLUMN
OPERATION HEADING 'Operation' FORMAT A20
COLUMN OPTIONS HEADING 'Options' FORMAT A20
COLUMN OBJECT_OWNER HEADING 'Object|Owner' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
COLUMN COST HEADING 'Cost' FORMAT 99999999
