A queue buffer would normally overflow if
there were not enough shared_pool_size available to hold
captured events. Captured events that overflow a queue buffer
are stored in the appropriate aq$_<queue_table_name>_p
table on disk. When queue buffers spill over to the physical
table, the system will normally experience performance issues.
There will also be additional overhead of processing.
When the number of Capture processes was
increased or the parallelism for Capture or Apply process was
introduced, there was a need for additional buffers. Since the
size of the SGA that can be used by the Streams was limited to
10% of the Shared pool, in the total Shared pool size had to be
increased considerably in order to enhance the buffer capacity
needed for Streams.
With the 10g release, Oracle provides a
great relief by allowing the configuration of a separate Streams
pool which is controlled by the streams_pool_size
initialization parameter. The limitation of using the shared
pool is lifted by using the separate Streams pool area. Figure
4.2 shows the memory components in a typical Oracle instance.
Figure 4.2 Oracle Instance Memory
The streams_pool_size parameter is
dynamic. If this parameter is set to zero when an instance
starts, increasing it beyond zero has no effect on the current
instance because the instance is already using the Shared pool
for Streams allocations. In other words, if the
streams_pool_size is set to a value greater than zero at the
start of the instance, the dynamic adjustment becomes