Query from SYS.STREAMS$_APPLY_SPILL_MSGS_PART Takes Long Time (Doc ID 756049.1)

Last updated on MAY 14, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Information in this document applies to any platform.

Symptoms

Any of the following problem is seen:

select from SYS.STREAMS$_APPLY_SPILL_MSGS_PART takes a long time

or Sysaux tablespace keeps growing due to table streams$_apply_spill_msgs_part

or Apply process is not applying LCRs

or Apply process is always in an idle state and applying very slowly ; there seems to be a major performance issue at apply side

Information on SYS.STREAMS$_APPLY_SPILL_MSGS_PART
SYS.STREAMS$_APPLY_SPILL_MSGS_PART is the apply spillover table. In apply spill, only large transactions (ie, transactions that are over TXN_LCR_SPILL_THRESHOLD in number of LCRs) or long-running transactions (open transactions that have not received LCRs for over 10 minutes) are spilled to the apply spill table. The apply spill table is located in SYSAUX and uses a partitioned table to store the LCRs. Each transaction is a separate partition of the table (key is apply_name and transaction id). When a transaction has been applied and it is time to remove the LCRs for that transaction, a drop partition is performed for the transactionid. This is a very quick operation and the space in SYSAUX is returned.

If an error occurs while applying a transaction from the apply spill table, the transaction will remain in the apply spill table and be marked as an error. DBA_APPLY_ERROR will show the transaction as an error; it will no longer be visible in DBA_APPLY_SPILL_TXN.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms