Query from SYS.STREAMS$_APPLY_SPILL_MSGS_PART Takes Long Time
(Doc ID 756049.1)
Last updated on MAY 15, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [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.
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.
Changes
Cause
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
Symptoms |
Changes |
Cause |
Solution |
References |