This metric shows information about the number of messages enqueued by a capture 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.
If queue publishers other than the capture process enqueue messages into a buffered queue, then the values shown can include messages from these other queue publishers.
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 a capture process:
SELECT CAPTURE_NAME, P.NUM_MSGS NUM_MSGS, Q.SPILL_MSGS SPILL_MSGS FROM V$BUFFERED_PUBLISHERS P, V$BUFFERED_QUEUES Q, DBA_CAPTURE C WHERE C.QUEUE_NAME = P.QUEUE_NAME AND C.QUEUE_OWNER = P.QUEUE_SCHEMA AND C.QUEUE_NAME = Q.QUEUE_NAME AND C.QUEUE_OWNER = Q.QUEUE_SCHEMA AND C.CAPTURE_NAME = P.SENDER_NAME AND P.SENDER_ADDRESS IS NULL AND P.SENDER_PROTOCOL = 1;
The number of spilled messages should be kept as low as possible for the best performance. A high number of spilled messages can result in the following cases:
There might be a problem with a propagation that propagates the messages captured by the capture process, or 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 propagations and apply processes are enabled, and correct any problems with these propagations and apply processes.
The Streams pool might 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.