This metric shows information about the number of messages to be dequeued by the apply process that have spilled from memory to the queue spill table. Messages in a buffered queue can spill from memory into the queue spill table if they have been staged in the buffered queue for a period of time without being dequeued, or if there is not enough space in memory to hold all of the messages.
The rest of the information in this section is only valid for this metric when it appears in either the Enterprise Manager Grid Control or the Enterprise Manager Database Control (if applicable).
The following table shows how often the metric's value is collected.
Target Version |
Collection Frequency |
10.1.0.x; 10.2.0.x; 11.1.0.x; 11.2.0.x |
Every 5 Minutes |
The SPILL_MSGS
column in the following query shows this metric for an apply process:
SELECT APPLY_NAME, S.NUM_MSGS NUM_MSGS, Q.SPILL_MSGS SPILL_MSGS FROM DBA_APPLY A, V$BUFFERED_QUEUES Q,V$BUFFERED_SUBSCRIBERS S WHERE A.QUEUE_NAME = S.QUEUE_NAME AND A.QUEUE_OWNER = S.QUEUE_SCHEMA AND A.QUEUE_NAME = Q.QUEUE_NAME AND A.QUEUE_OWNER = Q.QUEUE_SCHEMA AND S.SUBSCRIBER_ADDRESS IS NULL;
The number of spilled messages should be kept as low as possible for the best performance. A high number of spilled messages might result in the following cases:
There might be a problem with an apply process that applies messages captured by the capture process. When this happens, the number of messages can build in a queue because they are not being consumed. In this case, make sure the relevant apply processes are enabled, and correct any problems with these apply processes.
The Streams pool may be too small to hold the captured messages. In this case, increase the size of the Streams pool. If the database is Oracle Database 10g release 2 (10.2) or higher, then you can configure Automatic Shared Memory Management to manage the size of the Streams pool automatically. Set the SGA_TARGET
initialization parameter to use Automatic Shared Memory Management.
Related Topics
About Alerts
About the Metric Detail Page
Editing Thresholds
Understanding Line Charts
Copyright © 1996, 2009, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.