Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Streams Pool in the SGA

Oracle Tips by Burleson 

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 Structures

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 meaningful.

The above text is an excerpt from:

Oracle Streams
High Speed Replication and Data Sharing

ISBN 0-9745993-5-2

by Madhu Tumma

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks