Page 675
As well as the global cache (GC_) parameters discussed in the previous section, you may need to use a few additional parameters in your init.ora file in order to manage your parallel database successfully. Table 27.2 shows in which release, Version 7.3 or Oracle8, you can use these parameters.
Table 27.2 Initialization Parameters for Parallel Server, Versions 7.3 and 8.0Parameter Name | Version 7.3 | Version 8.0 |
ALLOW_PARTIAL_SN_RESULTS | X | |
CACHE_SIZE_THRESHOLD | X | X |
DELAYED_LOGGING_BLOCK_CLEANOUTS | X | X |
DML_LOCKS | X | X |
IFILE | X | X |
INSTANCE_GROUP | X | |
INSTANCE_NUMBER | X | X |
LM_LOCKS | X | |
LM_PROCS | X | |
LM_RESS | X | |
MAX_COMMIT_PROPAGATION_DELAY | X | X |
OPS_ADMIN_GROUP | X | |
PARALLEL_DEFAULT_MAX_INSTANCES | X | X |
PARALLEL_INSTANCE_GROUP | X | |
PARALLEL_SERVER | X | |
THREAD | X | X |
See the following sections for suggestions on when and how to use these parameters.
ALLOW_PARTIAL_SN_RESULTS If set to TRUE, ALLOW_PARTIAL_SN_RESULTS enables a query of the global transaction tables to complete even if not all the group's instances are online. If set to FALSE, a query that cannot access one or more instances in the queried group will fail. This should be set to TRUE to access global views (GV$_) in batch programs so that they will not fail; set to FALSE to use these views interactively to receive an alert should any instance have failed.
CACHE_SIZE_THRESHOLD CACHE_SIZE_THRESHOLD is the maximum number of blocks from any table to be cached in a single instance's database buffer cache. The default setting of 1/10 of DB_BLOCK_BUFFERS is generally the preferred value.
Page 676
DELAYED_LOGGING_BLOCK_CLEANOUTS This parameter determines whether or not the database will attempt to perform block cleanout when a transaction commits. You should set this value to TRUE in Parallel Server instances. This reduces the likelihood that a pinged block will need to be cleaned out by the receiving instance. By not requiring cleanout, the receiving instance avoids the additional block pings needed to obtain rollback information from the instance that made the changes.
DML_LOCKS This parameter controls locks on tables being modified by concurrent transactions. Setting the value to zero on all instances improves performance, but the following statements will be disabled: DROP TABLE, CREATE INDEX, and explicit lock statements (LOCK TABLE ). Assign a value of zero when there is no requirement to use the previous commands.
IFILE IFILE identifies another parameter file to be included within the current file. Use this parameter to include a common file containing all the parameters that need to be identical across all instances. Your release-specific documentation identifies which parameters must have the same value on all nodes. Ideally, keep this file on a shared file system so that every instance can read a single copy.
CAUTION |
The included parameter file will be logically inserted where the IFILE parameter is included in the current init.ora file. If a parameter from the included file appears later in the current file, then the instance will use the value from the current file, overriding the included file's value. |
INSTANCE_GROUP This parameter identifies the group to which the instance belongs for purposes of global views. Only use this parameter if you need to manage the instances in separate groups, typically because they are performing different types of function.
INSTANCE_NUMBER This parameter associates the instance with the freelists in manually allocated extents and in specific freelist groups assigned to tables and indexes. If a table or index has fewer freelist groups assigned than the INSTANCE_NUMBER value, modular arithmetic is applied to identify the freelist group to be used. The default value is zero, in which case an instance number is assigned at startup time based on the order in which the instance starts relative to other instances.
LM_LOCKS LM_LOCKS is the number of locks to be used by the DLM layer. This value should be no less than:
2 * LM_RESS
If your applications freeze while trying to execute DML, you might need to add more locks for the DLM by increasing this parameter above the suggested minimum value.
Page 677
TIP |
The Oracle8 Parallel Server Concepts and Administration manual contains detailed charts showing the computational steps you should follow to evaluate a reasonable value for this, and for the other lock manager (LM_) parameters. |
LM_PROCS This parameter controls the number of Oracle processes that can use the lock management service. This value should be at least (PROCESSES + Number of Active Instances).
LM_RESS This parameter defines the number of resources used by the DLM layer. This value should be:
2 * ( ENQUEUE_RESOURCES + GC_FILES_TO_LOCKS + GC_RELEASABLE_LOCKS + GC_ROLLBACK_LOCKS)
You are advised to not reduce the value below the result of this formula. If you don't know the value of one or more of the parameters needed for the calculation, you should start one instance and query the V$PARAMETER table for the values you need.
MAX_COMMIT_PROPAGATION_DELAY This parameter is the maximum length of time an instance is allowed to maintain the same System Change Number (SCN) in its SGA. In databases that modify blocks in one set of instances and query them concurrently in other instances, the default value may not synchronize the SCN in a timely manner. This could lead to an instance believing it was seeing a block that was committed later than the instance's current value. Generally, the default value is sufficient and should be used unless queries are failing to see sufficiently current changes required by the application. Faster propagation rates can hurt overall performance.
OPS_ADMIN_GROUP OPS_ADMIN_GROUP defines groups for instance startup, shutdown, and monitoring activities. The parameter enables the DBA to control subsets of related instances in a partitioned environment.
PARALLEL_DEFAULT_MAX_INSTANCES This parameter specifies the default number of instances to be employed by a query coordinator performing a parallel query when a table is scanned with a PARALLEL option of INSTANCES DEFAULT. The default value is derived from various operating system values and is optimal for most databases.
PARALLEL_INSTANCE_GROUP This parameter identifies the group of instances that will be used by a query parallelized across multiple instances. Only query slaves in the requested group are used for performing parallel query operations. This parameter is useful when the database is partitioned into subsets of related instances.
PARALLEL_SERVER This parameter identifies the instance as one that will be opened in parallel with other instances. It replaces the PARALLEL, or alternate SHARED, option of the STARTUP command in Version 7.3. Set to FALSE, the instance must be the only one running against the database or else the instance fails to open.
THREAD THREAD assigns the number of the redo log threads to be used by the instance. If the number is zero, the instance attempts to acquire a free public thread of redo. If the number is non-zero, the instance tries to acquire the redo thread of that number, whether it is enabled as a private or a public thread. If a required thread is available, the instance acquires it for its