My Oracle Support Banner

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

Last updated on OCTOBER 06, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.1 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.


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

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.




To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.