14 XStream Dynamic Performance (V$) Views

This chapter describes the dynamic performance (V$) views related to XStream.

This chapter contains these topics:

V$STREAMS_APPLY_READER
V$STREAMS_APPLY_SERVER
V$STREAMS_CAPTURE
V$STREAMS_MESSAGE_TRACKING
V$STREAMS_TRANSACTION

V$STREAMS_APPLY_READER

V$STREAMS_APPLY_READER displays information about each apply reader. The apply reader for an apply process is a process which reads (dequeues) messages from the queue, computes message dependencies, builds transactions, and passes the transactions on to the apply process coordinator in commit order for assignment to the apply servers.

Column	Data Type	Description
`SID`	`NUMBER`	Session ID of the reader's session
`SERIAL#`	`NUMBER`	Serial number of the reader's session
`APPLY#`	`NUMBER`	Apply process number. An apply process is an Oracle background process, prefixed by `ap`.
`APPLY_NAME`	`VARCHAR2(30)`	Name of the apply process that spilled one or more transactions
`STATE`	`VARCHAR2(36)`	State of the reader: `INITIALIZING` - Starting up `IDLE` - Performing no work `DEQUEUE MESSAGES` - Dequeuing messages from the apply process queue `SCHEDULE MESSAGES` - Computing dependencies between messages and assembling messages into transactions `SPILLING` - Spilling unapplied messages from memory to hard disk `PAUSED - WAITING FOR DDL TO COMPLETE` - Waiting for a DDL LCR to be applied
`TOTAL_MESSAGES_DEQUEUED`	`NUMBER`	Total number of messages dequeued since the apply process was last started
`TOTAL_MESSAGES_SPILLED`	`NUMBER`	Number of messages spilled by the reader since the apply process was last started
`DEQUEUE_TIME`	`DATE`	Time when the last message was received
`DEQUEUED_MESSAGE_NUMBER`	`NUMBER`	Number of the last message received
`DEQUEUED_MESSAGE_CREATE_TIME`	`DATE`	For captured messages, creation time at the source database of the last message received. For user-enqueued messages, time when the message was enqueued into the queue at the local database.
`SGA_USED`	`NUMBER`	Amount (in bytes) of SGA memory used by the apply process since it was last started
`ELAPSED_DEQUEUE_TIME`	`NUMBER`	Time elapsed (in hundredths of a second) dequeuing messages since the apply process was last started
`ELAPSED_SCHEDULE_TIME`	`NUMBER`	Time elapsed (in hundredths of a second) scheduling messages since the apply process was last started. Scheduling includes computing dependencies between messages and assembling messages into transactions.
`ELAPSED_SPILL_TIME`	`NUMBER`	Elapsed time spent spilling messages (in hundredths of a second) since the apply process was last started
`LAST_BROWSE_NUM`	`NUMBER`	Last browse SCN
`OLDEST_SCN_NUM`	`NUMBER`	Oldest SCN
`LAST_BROWSE_SEQ`	`NUMBER`	Last browse sequence number
`LAST_DEQ_SEQ`	`NUMBER`	Last dequeue sequence number
`OLDEST_XIDUSN`	`NUMBER`	Transaction ID undo segment number of the oldest transaction to be applied/being applied
`OLDEST_XIDSLT`	`NUMBER`	Transaction ID slot number of the oldest transaction to be applied/being applied
`OLDEST_XIDSQN`	`NUMBER`	Transaction ID sequence number of the oldest transaction to be applied/ being applied
`SPILL_LWM_SCN`	`NUMBER`	Spill low-watermark SCN
`PROXY_SID`	`NUMBER`	When the apply process uses combined capture and apply, the session ID of the propagation receiver that is responsible for direct communication between capture and apply. If the apply process does not use combined capture and apply, then this column is `0`.
`PROXY_SERIAL`	`NUMBER`	When the apply process uses combined capture and apply, the serial number of the propagation receiver that is responsible for direct communication between capture and apply. If the apply process does not use combined capture and apply, then this column is `0`.
`PROXY_SPID`	`VARCHAR2(12)`	When the apply process uses combined capture and apply, the process identification number of the propagation receiver that is responsible for direct communication between capture and apply. If the apply process does not use combined capture and apply, then this column is `0`.
`CAPTURE_BYTES_RECEIVED`	`NUMBER`	When the apply process uses combined capture and apply, the number of bytes received by the apply process from the capture process since the apply process last started. If the apply process does not use combined capture and apply, then this column is not populated.
`DEQUEUED_POSITION`	`RAW(64)`	Dequeued position (for an apply process that is functioning as an XStream inbound server)
`LAST_BROWSE_POSITION`	`RAW(64)`	Last browse position (for an apply process that is functioning as an XStream inbound server)
`OLDEST_POSITION`	`RAW(64)`	Oldest position (for an apply process that is functioning as an XStream inbound server)
`SPILL_LWM_POSITION`	`RAW(64)`	Spill low watermark position (for an apply process that is functioning as an XStream inbound server)
`OLDEST_TRANSACTION_ID`	`VARCHAR2(128)`	Oldest transaction ID (for an apply process that is functioning as an XStream inbound server)

Note:

The ELAPSED_DEQUEUE_TIME and ELAPSED_SCHEDULE_TIME columns are only populated if the TIMED_STATISTICS initialization parameter is set to true, or if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

V$STREAMS_APPLY_SERVER

V$STREAMS_APPLY_SERVER displays information about each apply server and its activities. An apply server receives events from the apply coordinator for an apply process. For each event received, an apply server either applies the event or sends the event to the appropriate apply handler.

Column	Data Type	Description
`SID`	`NUMBER`	Session ID of the apply server's session
`SERIAL#`	`NUMBER`	Serial number of the apply server's session
`APPLY#`	`NUMBER`	Apply process number. An apply process is an Oracle background process, prefixed by `ap`.
`APPLY_NAME`	`VARCHAR2(30)`	Name of the apply process
`SERVER_ID`	`NUMBER`	Parallel execution server number of the apply server
`STATE`	`VARCHAR2(20)`	State of the apply server: `IDLE` - Performing no work `RECORD LOW-WATERMARK` - Performing an administrative job that maintains information about the apply progress, which is used in the `ALL_APPLY_PROGRESS` and `DBA_APPLY_PROGRESS` data dictionary views `ADD PARTITION` - Performing an administrative job that adds a partition that is used for recording information about in-progress transactions `DROP PARTITION` - Performing an administrative job that purges rows that were used to record information about in-progress transactions `EXECUTE TRANSACTION` - Applying a transaction `WAIT COMMIT` - Waiting to commit a transaction until all other transactions with a lower commit SCN are applied. This state is possible only if the `COMMIT_SERIALIZATION` apply process parameter is set to a value other than `DEPENDENT_TRANSACTIONS` and the `PARALLELISM` apply process parameter is set to a value greater than `1`. `WAIT DEPENDENCY` - Waiting to apply a logical change record (LCR) in a transaction until another transaction, on which it has a dependency, is applied. This state is possible only if the `PARALLELISM` apply process parameter is set to a value greater than `1`. `ROLLBACK TRANSACTION` - Rolling back a transaction `TRANSACTION CLEANUP` - Cleaning up an applied transaction, which includes removing LCRs from the apply process's queue `INITIALIZING` - Starting up `WAIT FOR CLIENT` - Waiting for an XStream In client application to request more logical change records (LCRs)
`XIDUSN`	`NUMBER`	Transaction ID undo segment number of the transaction currently being applied
`XIDSLT`	`NUMBER`	Transaction ID slot number of the transaction currently being applied
`XIDSQN`	`NUMBER`	Transaction ID sequence number of the transaction currently being applied
`COMMITSCN`	`NUMBER`	Commit system change number (SCN) of the transaction currently being applied
`DEP_XIDUSN`	`NUMBER`	Transaction ID undo segment number of a transaction on which the transaction being applied by this apply server depends
`DEP_XIDSLT`	`NUMBER`	Transaction ID slot number of a transaction on which the transaction being applied by this apply server depends
`DEP_XIDSQN`	`NUMBER`	Transaction ID sequence number of a transaction on which the transaction being applied by this apply server depends
`DEP_COMMITSCN`	`NUMBER`	Commit system change number (SCN) of the transaction on which this apply server depends
`MESSAGE_SEQUENCE`	`NUMBER`	Number of the current message being applied by the apply server. This value is reset to `1` at the beginning of each transaction.
`TOTAL_ASSIGNED`	`NUMBER`	Total number of transactions assigned to the apply server since the apply process was last started
`TOTAL_ADMIN`	`NUMBER`	Total number of administrative jobs done by the apply server since the apply process was last started. See the `STATE` information in this view for the types of administrative jobs.
`TOTAL_ROLLBACKS`	`NUMBER`	Number of transactions assigned to this server which were rolled back
`TOTAL_MESSAGES_APPLIED`	`NUMBER`	Total number of messages applied by this apply server since the apply process was last started
`APPLY_TIME`	`DATE`	Time the last message was applied
`APPLIED_MESSAGE_NUMBER`	`NUMBER`	Number of the last message applied
`APPLIED_MESSAGE_CREATE_TIME`	`DATE`	Creation time at the source database of the last captured message applied. No information about user-enqueued messages is recorded in this column.
`ELAPSED_DEQUEUE_TIME`	`NUMBER`	Time elapsed (in hundredths of a second) dequeuing messages since the apply process was last started
`ELAPSED_APPLY_TIME`	`NUMBER`	Time elapsed (in hundredths of a second) applying messages since the apply process was last started
`COMMIT_POSITION`	`RAW(64)`	Commit position of the transaction (for an apply process that is functioning as an XStream inbound server)
`DEP_COMMIT_POSITION`	`RAW(64)`	Commit position of the transaction the slave depends on (for an apply process that is functioning as an XStream inbound server)
`LAST_APPLY_POSITION`	`RAW(64)`	Position of the last message applied (for an apply process that is functioning as an XStream inbound server)
`TRANSACTION_ID`	`VARCHAR2(128)`	Transaction ID that the slave is applying (for an apply process that is functioning as an XStream inbound server)
`DEP_TRANSACTION_ID`	`VARCHAR2(128)`	Transaction ID of the transaction the slave depends on (for an apply process that is functioning as an XStream inbound server)

Note:

The ELAPSED_DEQUEUE_TIME and ELAPSED_APPLY_TIME columns are only populated if the TIMED_STATISTICS initialization parameter is set to true, or if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

V$STREAMS_CAPTURE

V$STREAMS_CAPTURE displays information about each capture process.

Column	Data Type	Description
`SID`	`NUMBER`	Session identifier of the capture process
`SERIAL#`	`NUMBER`	Session serial number of the capture process session
`CAPTURE#`	`NUMBER`	Capture process number. A capture process is an Oracle background process, prefixed by `cp`.
`CAPTURE_NAME`	`VARCHAR2(30)`	Name of the capture process
`LOGMINER_ID`	`NUMBER`	Session ID of the LogMiner session associated with the capture process
`STARTUP_TIME`	`DATE`	Time when the capture process was last started
`STATE`	`VARCHAR2(551)`	State of the capture process: `INITIALIZING` `CAPTURING CHANGES` `EVALUATING RULE` `ENQUEUING MESSAGE` `SHUTTING DOWN` `ABORTING` `CREATING LCR` `WAITING FOR DICTIONARY REDO` `WAITING FOR REDO` `PAUSED FOR FLOW CONTROL` `DICTIONARY INITIALIZATION` `WAITING FOR A SUBSCRIBER TO BE ADDED` `WAITING FOR BUFFERED QUEUE TO SHRINK` `SUSPENDED FOR AUTO SPLIT/MERGE`
`TOTAL_PREFILTER_DISCARDED`	`NUMBER`	Total number of prefiltered messages discarded
`TOTAL_PREFILTER_KEPT`	`NUMBER`	Total number of prefiltered messages kept
`TOTAL_PREFILTER_EVALUATIONS`	`NUMBER`	Total number of prefilter evaluations
`TOTAL_MESSAGES_CAPTURED`	`NUMBER`	Total number of redo entries passed by LogMiner to the capture process for detailed rule evaluation since the capture process last started. A capture process converts a redo entry into a message and performs detailed rule evaluation on the message when capture process prefiltering cannot discard the change.
`CAPTURE_TIME`	`DATE`	Time when the most recent message was captured
`CAPTURE_MESSAGE_NUMBER`	`NUMBER`	Number of the most recently captured message
`CAPTURE_MESSAGE_CREATE_TIME`	`DATE`	Creation time of the most recently captured message
`TOTAL_MESSAGES_CREATED`	`NUMBER`	Count associated with `ELAPSED_LCR_TIME` to calculate rate
`TOTAL_FULL_EVALUATIONS`	`NUMBER`	Count associated with `ELAPSED_RULE_TIME` to calculate rate
`TOTAL_MESSAGES_ENQUEUED`	`NUMBER`	Total number of messages enqueued since the capture process was last started
`ENQUEUE_TIME`	`DATE`	Time when the last message was enqueued
`ENQUEUE_MESSAGE_NUMBER`	`NUMBER`	Number of the last enqueued message
`ENQUEUE_MESSAGE_CREATE_TIME`	`DATE`	Creation time of the last enqueued message
`AVAILABLE_MESSAGE_NUMBER`	`NUMBER`	For local capture, the last redo SCN flushed to the log files. For downstream capture, the last SCN added to LogMiner through the archive logs.
`AVAILABLE_MESSAGE_CREATE_TIME`	`DATE`	For local capture, the time the SCN was written to the log file. For downstream capture, the time the most recent archive log (containing the most recent SCN) was added to LogMiner.
`ELAPSED_CAPTURE_TIME`	`NUMBER`	Elapsed time (in hundredths of a second) scanning for changes in the redo log since the capture process was last started
`ELAPSED_RULE_TIME`	`NUMBER`	Elapsed time (in hundredths of a second) evaluating rules since the capture process was last started
`ELAPSED_ENQUEUE_TIME`	`NUMBER`	Elapsed time (in hundredths of a second) enqueuing messages since the capture process was last started
`ELAPSED_LCR_TIME`	`NUMBER`	Elapsed time (in hundredths of a second) creating logical change records (LCRs) since the capture process was last started
`ELAPSED_REDO_WAIT_TIME`	`NUMBER`	Elapsed time (in hundredths of a second) spent by the capture process in the `WAITING FOR REDO` state
`ELAPSED_PAUSE_TIME`	`NUMBER`	Elapsed flow control pause time (in hundredths of a second)
`STATE_CHANGED_TIME`	`DATE`	Time at which the state of the capture process changed
`APPLY_NAME`	`VARCHAR2(30)`	Reserved for internal use
`APPLY_DBLINK`	`VARCHAR2(128)`	Reserved for internal use
`APPLY_MESSAGES_SENT`	`NUMBER`	Reserved for internal use
`APPLY_BYTES_SENT`	`NUMBER`	Reserved for internal use
`OPTIMIZATION`	`NUMBER`	Indicates whether a capture process uses combined capture and apply (greater than zero) or a capture process does not use combined capture and apply (`0`)

Note:

The ELAPSED_CAPTURE_TIME, ELAPSED_RULE_TIME, ELAPSED_ENQUEUE_TIME, ELAPSED_LCR_TIME, and ELAPSED_REDO_WAIT_TIME columns are only populated if the TIMED_STATISTICS initialization parameter is set to true, or if the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL.

V$STREAMS_MESSAGE_TRACKING

V$STREAMS_MESSAGE_TRACKING displays information about LCRs tracked through the stream that are processed by each Streams client. Use the DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING procedure to specify a tracking label that becomes part of each LCR generated by the current session.

If the actions parameter in the DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING procedure is set to DBMS_STREAMS_ADM.ACTION_MEMORY, then information about the LCRs is tracked in memory and this view is populated with information about the LCRs. If the actions parameter is set to DBMS_STREAMS_ADM.ACTION_TRACE, then this view is not populated and information about the LCRs is sent to the trace files at each database.

Column	Data Type	Description
`TRACKING_LABEL`	`VARCHAR2(30)`	User-specified tracking label
`TAG`	`RAW(30)`	First 30 bytes of the tag of the LCR
`COMPONENT_NAME`	`VARCHAR2(30)`	Name of the component that processed the LCR
`COMPONENT_TYPE`	`VARCHAR2(30)`	Type of the component that processed the LCR
`ACTION`	`VARCHAR2(50)`	Action performed on the LCR
`ACTION_DETAILS`	`VARCHAR2(100)`	Details of the action
`TIMESTAMP`	`TIMESTAMP(9) WITH TIME ZONE`	Time when the action was performed
`MESSAGE_CREATION_TIME`	`DATE`	Time when the message was created
`MESSAGE_NUMBER`	`NUMBER`	SCN of the message
`TRACKING_ID`	`RAW(16)`	Globally unique OID of the LCR
`SOURCE_DATABASE_NAME`	`VARCHAR2(128)`	Name of the source database
`OBJECT_OWNER`	`VARCHAR2(30)`	Owner of the object
`OBJECT_NAME`	`VARCHAR2(30)`	Name of the object
`XID`	`VARCHAR2(128)`	Transaction ID
`COMMAND_TYPE`	`VARCHAR2(30)`	Command type of the LCR
`MESSAGE_POSITION`	`RAW(64)`	Position of the message (for an apply process that is functioning as an XStream inbound server)

V$STREAMS_TRANSACTION

V$STREAMS_TRANSACTION displays information about transactions that are being processed by capture processes or apply processes. This view can be used to identify long running transactions and to determine how many logical change records (LCRs) are being processed in each transaction. This view only contains information about captured LCRs. It does not contain information about user-enqueued LCRs or user messages.

This view only shows information about LCRs that are being processed because they satisfied the rule sets for the Streams process at the time of the query. For capture processes, this view only shows information about changes in transactions that the capture process has converted to LCRs. It does not show information about all the active transactions present in the redo log. For apply processes, this view only shows information about LCRs that the apply process has dequeued. It does not show information about LCRs in the apply process's queue.

Information about a transaction remains in the view until the transaction commits or until the entire transaction is rolled back.

Column	Data Type	Description
`STREAMS_NAME`	`VARCHAR2(30)`	Name of the Streams process
`STREAMS_TYPE`	`VARCHAR2(10)`	Type of the Streams process: `CAPTURE` `APPLY` `PROPAGATION_SENDER`
`XIDUSN`	`NUMBER`	Transaction ID undo segment number of the transaction
`XIDSLT`	`NUMBER`	Transaction ID slot number of the transaction
`XIDSQN`	`NUMBER`	Transaction ID sequence number of the transaction
`CUMULATIVE_MESSAGE_COUNT`	`NUMBER`	Number of LCRs processed in the transaction. If the Streams process is restarted while the transaction is being processed, then this column shows the number of LCRs processed in the transaction since the Streams process was started.
`TOTAL_MESSAGE_COUNT`	`NUMBER`	Total Number of LCRs processed in the transaction by an apply process. This column does not pertain to capture processes.
`FIRST_MESSAGE_TIME`	`DATE`	Timestamp of the first LCR processed in the transaction. If a capture process is restarted while the transaction is being processed, then this column shows the timestamp of the first LCR processed after the capture process was started.
`FIRST_MESSAGE_NUMBER`	`NUMBER`	System change number (SCN) of the first message in the transaction. If a capture process is restarted while the transaction is being processed, then this column shows the SCN of the first message processed after the capture process was started.
`LAST_MESSAGE_TIME`	`DATE`	Timestamp of the last LCR processed in the transaction
`LAST_MESSAGE_NUMBER`	`NUMBER`	SCN of the most recent message encountered for the transaction
`FIRST_MESSAGE_POSITION`	`RAW(64)`	Position of the first message seen (for an apply process that is functioning as an XStream inbound server)
`LAST_MESSAGE_POSITION`	`RAW(64)`	Position of the last message seen (for an apply process that is functioning as an XStream inbound server)
`TRANSACTION_ID`	`VARCHAR2(128)`	Transaction ID (for an apply process that is functioning as an XStream inbound server)