Release 2 (8.1.6)
Part Number A76970-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Parallel Server Administration, Deployment, and Performance Release 2 (8.1.6) Part Number A76970-01 |
|
To reduce contention for shared resources and to gain maximum Oracle Parallel Server performance, ensure that the Distributed Lock Manager is adequately configured for all the locks and resources that your system requires. This chapter covers the following topics:
When you have finished adjusting your system's settings for locks and resources, you can use SQL*Loader to load data into your database as described under the topic:
Oracle8i Parallel Server Concepts for an overview of Parallel Cache Management and non-Parallel Cache Management locks.
See Also:
Planning the allocation of Parallel Cache Management (PCM) locks alone is not sufficient to manage locks on your system. Besides explicitly allocating PCM locks, you must continually monitor the Distributed Lock Manager to ensure it is adequately configured. You must do this on each node for all required PCM and non-PCM locks and resources. Consider also that larger databases and higher degrees of parallelism require increased demands for many resources.
Many different types of non-PCM locks exist, and each is handled differently. Although you cannot directly adjust their number, you can estimate the overall number of non-PCM resources and locks required and adjust the LM_* or GC_* initialization parameters, or both, to guarantee adequate space. You also have the option of minimizing table locks to optimize performance.
Distributed Lock Manager capacity is determined by the settings for the LM_RESS and LM_LOCKS parameters. The Distributed Lock Manager automatically calculates values for LM_RESS and LM_LOCKS based on other parameter settings in your initialization parameter file. The settings that Oracle makes for LM_RESS and LM_LOCKS appear in your alert.log file at startup. These settings, however, are only estimates because enqueue resource usage is application dependent.
If your shared pool runs out space, or if the maximum utilization shown in the V$RESOURCE_LIMIT view is greater than the values Oracle sets for LM_RESS and LM_LOCKS, increase your settings for LM_RESS and LM_LOCKS and re-examine the statistics in V$RESOURCE_LIMIT. Otherwise, you do not need to set LM_RESS and LM_LOCKS.
If the required number of locks or resources increases beyond the amount Oracle initially allocates, Oracle will allocate additional locks and resources from the System Global Area shared pool. This feature prevents the instance from stopping.
Dynamic allocation causes Oracle to write a message to the alert file indicating that you should adjust the initialization parameters for the next time the database is started. Since performance and memory usage may be adversely affected by dynamic allocation, it is highly recommended that you accurately compute your lock and resource needs.
The recommended default value for SHARED_POOL_SIZE is 16MB for 64-bit applications and 8MB for 32-bit applications.
Another way to ensure your system has enough space for the required non-PCM locks and resources is to adjust the values of the following Oracle initialization parameters:
Do not, however, specify actual parameter values considerably greater than needed for each instance. Setting these parameters unnecessarily high incurs overhead.
Obtaining table locks, such as DML locks, for inserts, deletes, and updates can diminish performance in Oracle Parallel Server. Locking a table in Oracle Parallel Server is undesirable because all instances holding locks on the table must release those locks. Consider disabling these locks entirely using one of the two methods described under the following headings:
To prevent users from acquiring table locks, use the following statement:
ALTER TABLE table_name DISABLE TABLE LOCK
Users attempting to lock a table when its table lock is disabled will receive an error.
To re-enable table locking, use the following statement:
ALTER TABLE table_name ENABLE TABLE LOCK
Once you execute this syntax, all currently executing transactions commit before enabling the table lock. The statement does not need to wait for new transactions starting after issuing the ENABLE statement.
To determine whether a table has its table lock enabled or disabled, query the column TABLE_LOCK in the data dictionary table USER_TABLES. If you have select privilege on DBA_TABLES or ALL_TABLES, you can query the table lock state of other users tables.
Table locks are set with the initialization parameter DML_LOCKS. If the DROP TABLE, CREATE INDEX, and LOCK TABLE statements are not needed, set DML_LOCKS to zero to minimize lock conversions and achieve maximum performance.
Once you have configured your Distributed Lock Manager, you are ready to load data into the database. An efficient method of loading data is to use SQL*Loader.
With SQL*Loader, you can use either conventional or direct path loading for Oracle Parallel Server databases. However, each method has advantages and disadvantages.
The conventional path method imposes identical data integrity rules as Oracle imposes on regular user-based inserts. Redo logs, rollback segments, indexes, and triggers function as they would during normal insert processing. Because overall data integrity is more important than processing speed, Oracle recommends using the conventional path method for data loads unless it is critical that you reduce the duration of load processing.
When using the conventional path method and running loads in parallel, you should avoid:
SQL*Loader's direct path method bypasses most processing within the System Global Area. When SQL*Loader adds rows to tables, Oracle does not record the rows in rollback segments. Oracle therefore cannot create read-consistent blocks for queries from the new data. However, users can read new records after the data is written to disk.
Parallel direct loads can write block images into the same data file block addresses. To avoid this, use the PARALLEL keyword to set a flag in the control file. Each parallel SQL*Loader session checks the flag to ensure there is not a non-parallel direct load running against the same table. This forces Oracle to create new extents for each session.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|