_disable_streams_pool_auto_tuning

Posted By admin On 11.12.20

Purpose: The 'undocumented' underscore parameters are parameters officially undocumented and unsupported by Oracle but that may be utilized, after receiving permission from Oracle Support to address specific issues such as bugs. Almost 94% of time was spent on the “Streams AQ: enqueue blocked on low memory” wait event. After digging, Oracle MOS portal helped me in this case. In database version 11.2.0.4, Oracle has implemented a new algorithm for modifying components of the SGA memory, which has issue while changing the size of the stream pool size.

Disable Windows Auto Tuning Level
Disable Receive Window Auto Tuning
_disable_streams_pool_auto_tuning

While helping with migration of one Retail production system, I need to export certain schemas by using Data Pump utility.

As the database is pretty small (about 3TB), I’ve just executed expdp command and expected it will finish within 2 to 3 hours.

When I checked in the evening, on my surprise, Data Pump job was still running.

After monitoring the Data Pump session, soon I’ve realized what is going on.

Almost 94% of time was spent on the “Streams AQ: enqueue blocked on low memory” wait event.

After digging, Oracle MOS portal helped me in this case.

In database version 11.2.0.4, Oracle has implemented a new algorithm for modifying components of the SGA memory, which has issue while changing the size of the stream pool size.

You can see on the picture below how it look like.

To resolve the problem, there are several workarounds, but this is what is working in my case.

I’ve changed the minimum size of the stream pool to 256MB by executing the following command:

This value is dependent of the number of parallel processes (parameter parallel=4 in my case).
If your database is very busy, you’ll have to do the following:

and restart the database.

If you’ll still have the same wait event even after increasing minimum amount of the stream pool size, you’ll have to execute the following command:

and bounce the database.

After increasing the value and disabling auto tuning, performance of the Data Pump were back to normal.

The same database export that I have to kill after 24 hours, now takes only 16.5 minutes with parallel degree of 4.

Comments

There are no comments yet. Why not start the discussion?

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This chapter provides instructions for preparing a database or a distributed database environment to use Streams.

This chapter contains these topics:

Configuring a Streams Administrator

To manage a Streams environment, either create a new user with the appropriate privileges or grant these privileges to an existing user. You should not use the SYS or SYSTEM user as a Streams administrator, and the Streams administrator should not use the SYSTEM tablespace as its default tablespace.

Complete the following steps to configure a Streams administrator at each database in the environment that will use Streams:

Connect in SQL*Plus as an administrative user who can create users, grant privileges, and create tablespaces. Remain connected as this administrative user for all subsequent steps.
Either create a tablespace for the Streams administrator or use an existing tablespace. For example, the following statement creates a new tablespace for the Streams administrator:
Create a new user to act as the Streams administrator or use an existing user. For example, to create a new user named strmadmin and specify that this user uses the streams_tbs tablespace, run the following statement:
Note:
For security purposes, use a password other than strmadminpw for the Streams administrator.
Grant the Streams administrator DBA role:
Optionally, run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. You might choose to run this procedure on the Streams administrator created in Step3 if any of the following conditions are true:
- The Streams administrator will run user-created subprograms that execute subprograms in Oracle-supplied packages associated with Streams. An example is a user-created stored procedure that executes a procedure in the DBMS_STREAMS_ADM package.
- The Streams administrator will run user-created subprograms that query data dictionary views associated with Streams. An example is a user-created stored procedure that queries the DBA_APPLY_ERROR data dictionary view.
A user must have explicit EXECUTE privilege on a package to execute a subprogram in the package inside of a user-created subprogram, and a user must have explicit SELECT privilege on a data dictionary view to query the view inside of a user-created subprogram. These privileges cannot be through a role. You can run the GRANT_ADMIN_PRIVILEGE procedure to grant such privileges to the Streams administrator, or you can grant them directly.
Depending on the parameter settings for the GRANT_ADMIN_PRIVILEGE procedure, it either grants the privileges needed to be a Streams administrator directly, or it generates a script that you can edit and then run to grant these privileges.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about this procedure
Use theGRANT_ADMIN_PRIVILEGEprocedure to grant privileges directly:
Use theGRANT_ADMIN_PRIVILEGEprocedure to generate a script:
1. Use the SQL statement CREATEDIRECTORY to create a directory object for the directory into which you want to generate the script. A directory object is similar to an alias for the directory. For example, to create a directory object called admin_dir for the /usr/admin directory on your computer system, run the following procedure:
2. Run the GRANT_ADMIN_PRIVILEGE procedure to generate a script named grant_strms_privs.sql and place this script in the /usr/admin directory on your computer system:
  Notice that the grant_privileges parameter is set to false so that the procedure does not grant the privileges directly. Also, notice that the directory object created in Step a is specified for the directory_name parameter.
3. Edit the generated script if necessary and save your changes.
4. Execute the script in SQL*Plus:
5. Check the spool file to ensure that all of the grants executed successfully. If there are errors, then edit the script to correct the errors and rerun it.
If necessary, grant the Streams administrator the following privileges:
- If no apply user is specified for an apply process, then the necessary privileges to perform DML and DDL changes on the apply objects owned by another user. If an apply user is specified, then the apply user must have these privileges.
- If no apply user is specified for an apply process, then EXECUTE privilege on any PL/SQL procedure owned by another user that is executed by a Streams apply process. These procedures can be used in apply handlers or error handlers. If an apply user is specified, then the apply user must have these privileges.
- EXECUTE privilege on any PL/SQL function owned by another user that is specified in a custom rule-based transformation for a rule used by a Streams capture process, propagation, apply process, or messaging client. For a capture process, if a capture user is specified, then the capture user must have these privileges. For an apply process, if an apply user is specified, then the apply user must have these privileges.
- Privileges to alter database objects where appropriate. For example, if the Streams administrator must create a supplemental log group for a table in another schema, then the Streams administrator must have the necessary privileges to alter the table.
- If the Streams administrator does not own the queue used by a Streams capture process, propagation, apply process, or messaging client, and is not specified as the queue user for the queue when the queue is created, then the Streams administrator must be configured as a secure queue user of the queue if you want the Streams administrator to be able to enqueue messages into or dequeue messages from the queue. The Streams administrator might also need ENQUEUE or DEQUEUE privileges on the queue, or both. See 'Enabling a User to Perform Operations on a Secure Queue' for instructions.
- EXECUTE privilege on any object types that the Streams administrator might need to access.
Repeat all of the previous steps at each database in the environment that will use Streams.

Disable Windows Auto Tuning Level

Setting Initialization Parameters Relevant to Streams

Table 10-1 lists initialization parameters that are important for the operation, reliability, and performance of a Streams environment. Set these parameters appropriately for your Streams environment. This table specifies whether each parameter is modifiable. A modifiable initialization parameter can be modified using the ALTERSYSTEM statement while an instance is running. Some of the modifiable parameters can also be modified for a single session using the ALTERSESSION statement.

Disable Receive Window Auto Tuning

Table 10-1 Initialization Parameters Relevant to Streams

Parameter Values Description

Parameter	Values	Description
Default:`10.0.0`Little snitch license key 4. Range:`9.2.0` to Current Release Number Modifiable?: No	This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate. To use the new Streams features introduced in Oracle Database 10g Release 1, this parameter must be set to `10.1.0` or higher. To use downstream capture, this parameter must be set to `10.1.0` or higher at both the source database and the downstream database. To use the new Streams features introduced in Oracle Database 10g Release 2, this parameter must be set to `10.2.0` or higher.
Default:`false` Range:`true` or `false` Modifiable?: Yes	Specifies whether a database link is required to have the same name as the database to which it connects. To use Streams to share information between databases, set this parameter to `true` at each database that is participating in your Streams environment.
Default:`0` Range:`0` to `1000` Modifiable?: Yes	Specifies the number of `J`n job queue processes for each instance (`J000` .. `J999`). Job queue processes handle requests created by `DBMS_JOB`. This parameter must be set to at least `2` at each database that is propagating messages in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
Default:`'SEND, RECEIVE, NODG_CONFIG'` Range: Values: `SEND` `NOSEND` `RECEIVE` `NORECEIVE` `DG_CONFIG` `NODG_CONFIG` Modifiable?: Yes	Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (`DB_UNIQUE_NAME`) for each database in the Data Guard configuration. To use downstream capture and copy the redo data to the downstream database using redo transport services, specify the `DB_UNIQUE_NAME` of the source database and the downstream database using the `DG_CONFIG` attribute.
Default: None Range: None Modifiable?: Yes	Defines up to ten log archive destinations, where `n` is `1`, `2`, `3`, .. `10`. To use downstream capture and copy the redo data to the downstream database using redo transport services, at least one log archive destination must be at the site running the downstream capture process.
Default:`enable` Range: One of the following: `alternate` `reset` `defer` `enable` Modifiable?: Yes	Specifies the availability state of the corresponding destination. The parameter suffix (`1` through `10`) specifies one of the ten corresponding `LOG_ARCHIVE_DEST_n` destination parameters. To use downstream capture and copy the redo data to the downstream database using redo transport services, make sure the destination that corresponds to the `LOG_ARCHIVE_DEST_n` destination for the downstream database is set to `enable`.
Default:`4` Range:`0` to `255` Modifiable?: No	Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process. In a Streams environment, make sure this parameter is set to the default value of `4` or higher.
Default: Derived automatically Range:`0` to `3599` Modifiable?: Yes	Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value. In a Streams environment, each capture process and apply process can use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.
Default:`40` to operating system-dependent Range:`6` to operating system-dependent Modifiable?: No	Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Make sure the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
Default:Derived from: `(1.1 * PROCESSES) + 5` Range:`1` to `231` Modifiable?: No	Specifies the maximum number of sessions that can be created in the system. To run one or more capture processes or apply processes in a database, you might need to increase the size of this parameter. Each background process in a database requires a session.
Default: Initial size of SGA at startup Range:`0` to operating system-dependent Modifiable?: No	Specifies the maximum size of SGA for the lifetime of a database instance. To run multiple capture processes on a single database, you might need to increase the size of this parameter.
Default:`0` (SGA autotuning is disabled) Ableton for android free download. Range:`64` to operating system-dependent Modifiable?: Yes	Specifies the total size of all System Global Area (SGA) components. If this parameter is set to a nonzero value, then the size of the Streams pool is managed by Automatic Shared Memory Management. Oracle recommends enabling the autotuning of the various pools within the SGA by setting `SGA_TARGET` to a large nonzero value and setting `STREAMS_POOL_SIZE` to `0`. When `SGA_TARGET` and `STREAMS_POOL_SIZE` are set in this way, Oracle automatically tunes the SGA and the Streams pool size to meet the workload requirements.
Default: If `SGA_TARGET` is set: If the parameter is not specified, then the default is `0` (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool. If `SGA_TARGET` is not set (32-bit platforms): `32 MB`, rounded up to the nearest granule size If `SGA_TARGET` is not set (64-bit platforms): `84 MB`, rounded up to the nearest granule size Range: The granule size to operating system-dependent Modifiable?: Yes	Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If the `SGA_TARGET` and `STREAMS_POOL_SIZE` initialization parameters are set to zero, then Streams transfers an amount equal to 10% of the shared pool from the buffer cache to the Streams pool.
Default:`0` Range: Minimum: `0` Maximum: operating system-dependent Modifiable?: Yes	Specifies (in bytes) the size of the Streams pool. The Streams pool contains buffered queue messages. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the `SGA_TARGET` initialization parameter is set to a nonzero value, then the Streams pool size is set by Automatic Shared memory management, and `STREAMS_POOL_SIZE` specifies the minimum size. This parameter is modifiable. If this parameter is reduced to zero when an instance is running, then Streams processes and jobs will not run. You should increase the size of the Streams pool for each of the following factors: 10 MB for each capture process parallelism 10 MB or more for each buffered queue. The buffered queue is where the logical change records (LCRs) are stored. 1 MB for each apply process parallelism For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If a database has two buffered queues, then increase the Streams pool by 20 MB or more. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB. You can use the `V$STREAMS_POOL_ADVICE` dynamic performance view to determine an appropriate setting for this parameter. See Also:'Streams Pool'
Default: If `STATISTICS_LEVEL` is set to `TYPICAL` or `ALL`, then `true` If `STATISTICS_LEVEL` is set to `BASIC`, then `false` The default for `STATISTICS_LEVEL` is `TYPICAL`. Range:`true` or `false` Modifiable?: Yes	Specifies whether or not statistics related to time are collected. To collect elapsed time statistics in the dynamic performance views related to Streams, set this parameter to `true`. The views that include elapsed time statistics include: `V$STREAMS_CAPTURE`, `V$STREAMS_APPLY_COORDINATOR`, `V$STREAMS_APPLY_READER`, `V$STREAMS_APPLY_SERVER`.
Default:`900` Range:`0` to 2³²-1 (max value represented by 32 bits) Modifiable?: Yes	Specifies (in seconds) the amount of committed undo information to retain in the database. For a database running one or more capture processes, make sure this parameter is set to specify an adequate undo retention period. If you are running one or more capture processes and you are unsure about the proper setting, then try setting this parameter to at least `3600`. If you encounter 'snapshot too old' errors, then increase the setting for this parameter until these errors cease. Make sure the undo tablespace has enough space to accommodate the `UNDO_RETENTION` setting.

Default:10.0.0Little snitch license key 4.

Range:9.2.0 to Current Release Number

Modifiable?: No

This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate.

To use the new Streams features introduced in Oracle Database 10g Release 1, this parameter must be set to 10.1.0 or higher. To use downstream capture, this parameter must be set to 10.1.0 or higher at both the source database and the downstream database.

To use the new Streams features introduced in Oracle Database 10g Release 2, this parameter must be set to 10.2.0 or higher.

Default:false

Range:true or false

Modifiable?: Yes

Specifies whether a database link is required to have the same name as the database to which it connects.

To use Streams to share information between databases, set this parameter to true at each database that is participating in your Streams environment.

Default:0

Range:0 to 1000

Modifiable?: Yes

Specifies the number of Jn job queue processes for each instance (J000 .. J999). Job queue processes handle requests created by DBMS_JOB.

This parameter must be set to at least 2 at each database that is propagating messages in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.

Default:'SEND, RECEIVE, NODG_CONFIG'

Range: Values:

SEND
NOSEND
RECEIVE
NORECEIVE
DG_CONFIG
NODG_CONFIG

Modifiable?: Yes

Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration.

To use downstream capture and copy the redo data to the downstream database using redo transport services, specify the DB_UNIQUE_NAME of the source database and the downstream database using the DG_CONFIG attribute.

Default: None

Range: None

Modifiable?: Yes

Defines up to ten log archive destinations, where n is 1, 2, 3, .. 10.

To use downstream capture and copy the redo data to the downstream database using redo transport services, at least one log archive destination must be at the site running the downstream capture process.

Default:enable

Range: One of the following:

alternate
reset
defer
enable

Modifiable?: Yes

Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding LOG_ARCHIVE_DEST_n destination parameters.

To use downstream capture and copy the redo data to the downstream database using redo transport services, make sure the destination that corresponds to the LOG_ARCHIVE_DEST_n destination for the downstream database is set to enable.

Default:4

Range:0 to 255

Modifiable?: No

Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

In a Streams environment, make sure this parameter is set to the default value of 4 or higher.

Default: Derived automatically

Range:0 to 3599

Modifiable?: Yes

Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value.

In a Streams environment, each capture process and apply process can use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.

Default:40 to operating system-dependent

Range:6 to operating system-dependent

Modifiable?: No

Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.

Make sure the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.

Default:Derived from:

(1.1 * PROCESSES) + 5

Range:1 to 231

Modifiable?: No

Specifies the maximum number of sessions that can be created in the system.

To run one or more capture processes or apply processes in a database, you might need to increase the size of this parameter. Each background process in a database requires a session.

Default: Initial size of SGA at startup

Range:0 to operating system-dependent

Modifiable?: No

Specifies the maximum size of SGA for the lifetime of a database instance.

To run multiple capture processes on a single database, you might need to increase the size of this parameter.

Default:0 (SGA autotuning is disabled) Ableton for android free download.

Range:64 to operating system-dependent

Modifiable?: Yes

Specifies the total size of all System Global Area (SGA) components.

If this parameter is set to a nonzero value, then the size of the Streams pool is managed by Automatic Shared Memory Management.

Oracle recommends enabling the autotuning of the various pools within the SGA by setting SGA_TARGET to a large nonzero value and setting STREAMS_POOL_SIZE to 0. When SGA_TARGET and STREAMS_POOL_SIZE are set in this way, Oracle automatically tunes the SGA and the Streams pool size to meet the workload requirements.

Default:

If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

If SGA_TARGET is not set (32-bit platforms): 32 MB, rounded up to the nearest granule size

If SGA_TARGET is not set (64-bit platforms): 84 MB, rounded up to the nearest granule size

Range: The granule size to operating system-dependent

Modifiable?: Yes

Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures.

If the SGA_TARGET and STREAMS_POOL_SIZE initialization parameters are set to zero, then Streams transfers an amount equal to 10% of the shared pool from the buffer cache to the Streams pool.

Default:0

Range:

Minimum: 0

Maximum: operating system-dependent

Modifiable?: Yes

Specifies (in bytes) the size of the Streams pool. The Streams pool contains buffered queue messages. In addition, the Streams pool is used for internal communications during parallel capture and apply.

If the SGA_TARGET initialization parameter is set to a nonzero value, then the Streams pool size is set by Automatic Shared memory management, and STREAMS_POOL_SIZE specifies the minimum size.

This parameter is modifiable. If this parameter is reduced to zero when an instance is running, then Streams processes and jobs will not run.

You should increase the size of the Streams pool for each of the following factors:

10 MB for each capture process parallelism
10 MB or more for each buffered queue. The buffered queue is where the logical change records (LCRs) are stored.
1 MB for each apply process parallelism

For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If a database has two buffered queues, then increase the Streams pool by 20 MB or more. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB.

You can use the V$STREAMS_POOL_ADVICE dynamic performance view to determine an appropriate setting for this parameter.

_disable_streams_pool_auto_tuning

Configuring Network Connectivity and Database Links

If you plan to use Streams to share information between databases, then configure network connectivity and database links between these databases:

For Oracle databases, configure your network and Oracle Net so that the databases can communicate with each other.
See Also:
Oracle Database Net Services Administrator's Guide
For non-Oracle databases, configure an Oracle gateway for communication between the Oracle database and the non-Oracle database.
See Also:
Oracle Database Heterogeneous Connectivity Administrator's Guide
If you plan to propagate messages from a source queue at a database to a destination queue at another database, then create a private database link between the database containing the source queue and the database containing the destination queue. Each database link should use a CONNECTTO clause for the user propagating messages between databases.
For example, to create a database link to a database named dbs2.net connecting as a Streams administrator named strmadmin, run the following statement:
See Also:
Oracle Database Administrator's Guide for more information about creating database links