Query from SYS.STREAMS$_APPLY_SPILL_MSGS_PART Takes Long Time
Last updated on MAY 14, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 to 184.108.40.206 [Release 10.1 to 12.1]
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
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms