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 |
|
This chapter explains the initialization parameters you set to allocate Parallel Cache Management (PCM) locks and non-PCM locks to data files in Oracle Parallel Server environments. It contains the following sections:
Tuning PCM locks may not provide optimal application scalability. For more information, please refer to Chapter 5 and Appendix A.
Note:
See Also:
This section describes planning the use and maintenance of PCM locks. It covers:
The Distributed Lock Manager (DLM) allows you to allocate only a finite number of locks. For this reason you need to analyze and plan for the number of locks your application requires. You also need to know how much memory the locks and resources require. In planning locks, consider the following issues:
The key to allocating PCM locks is to analyze how often data is changed using the INSERT, UPDATE, and DELETE statements. You can then determine how to group objects into files based on whether they should be read-only or read/write. Finally, assign locks based on the groupings you have made. In general, follow these guidelines:
The key distinction is not between types of objects (index or table), but between operations being performed on an object. The operation dictates the quantity of locks needed.
You must allocate locks at various levels by specifying:
Begin by examining your data files and the blocks they contain.
Use the following statement to determine the file ID, file name, tablespace name, and number of blocks for all databases.
SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BLOCKS FROM DBA_DATA_FILES;
Oracle displays results as in the following example:
FILE_NAME FILE_ID TABLESPACE_NAME BLOCKS --------------------------------------------------------------- /v7/data/data01.dbf 1 SYSTEM 200 /v7/data/data02.dbf 2 ROLLBACK 1600 . . .
Use the following approach to estimate the number of locks required for particular uses.
Many locks are needed on heavily used, concurrently updated data files, but a query-only application does not need many locks; a single lock on the data file suffices.
This reduces lock contention, minimizes I/O activity, and increases accessibility of the data in the files.
This avoids unnecessary lock management overhead.
This section explains how fixed locks and releasable locks are assigned to blocks. (1:1 locks, of course, have a one-to-one correspondence to blocks.)
Two data structures in the System Global Area control file-to-lock mapping. The first structure maps each file (DB_FILES) to a bucket (index) in the second structure. This structure contains information on the number of locks allocated to this bucket, base lock number, and grouping factor. To find the number of locks for a tablespace, count the number of actual fixed locks that protect the different files. If files share locks, you count the shared locks only once.
SELECT * FROM FILE_LOCK ORDER BY FILE_ID;
For example, Oracle responds with something similar to the following if you set GC_FILES_TO_LOCKS="1=500:5=200":
FILE_ID FILE_NAME TS_NAME START_LK NLOCKS BLOCKING ------- --------------- -------------- ---------- ---------- ---------- 1 \\.\OPS_SYS01 SYSTEM 100 1500 1 2 \\.\OPS_USR01 USER_DATA 1600 3000 1 3 \\.\OPS_RBS01 ROLLBACK_DATA 0 100 1 4 \\.\OPS_TMP01 TEMPORARY_DATA 0 100 1 5 \\.\OPS_USR03 TRAVEL_DEMO 4600 4000 1 6 \\.\PROBLEM_REP PROBLEM_REP 0 100 1 6 rows selected.
In this example, both file 1 and file 5 have different values for START_LCK. You therefore sum their NLOCKS values for a total of 700 locks.
If, however, you had set GC_FILES_TO_LOCKS="1-2=500:5=200", your results would look like the following:
FILE_ID FILE_NAME TABLESPACE_NAME START_LK NLOCKS BLOCKING 1 file1 system 1 500 1 1 file2 system 1 500 1 1 file3 system 0 1 file4 system 0 1 file5 system 501 200 1
This time, file 1 and file 2 have the same value for START_LCK indicating that they share the locks. File 5 has a different value for START_LCK. You therefore count once the 500 locks shared by files 1 and 2, and add an additional 200 locks for file 5, for a total of 700.
You need only concern yourself with the number of blocks in the data and undo block classes. Data blocks (class 1) contain data from indexes or tables. Undo header blocks (class 10) are also known as the rollback segment headers or transaction tables. System undo blocks (class 11) are part of the rollback segment and provide storage for undo records.
User undo segment n header blocks are identified as class 10 + (n x 2), where n represents the rollback segment number. A value of n = 0 indicates the system rollback segment; a value of n > 0 indicates a non-system rollback segment. Similarly, user undo segment n header blocks are identified as class 10 + ( (n x 2) + 1).
The following query shows the number of locks allocated per class:
SELECT CLASS, COUNT(*) FROM V$LOCK_ELEMENT GROUP BY CLASS ORDER BY CLASS;
The following query shows the number of fixed (non-releasable) PCM locks:
SELECT COUNT(*) FROM V$LOCK_ELEMENT WHERE bitand(flag, 4)!=0;
The following query shows the number of releasable PCM locks:
SELECT COUNT(*) FROM V$LOCK_ELEMENT WHERE bitand(flag, 4)=0;
For a data class block the file number is determined from the data block address (DBA). The bucket is found through the X$KCLFI dynamic performance table. Data class blocks are fixed to lock element numbers as follows:
Other block classes are fixed to lock element numbers as follows:
(DBA) modulo (locks_in_class)
The following examples show different ways of mapping blocks to PCM locks and how the same locks are used on multiple data files.
Figure 7-1 shows an example of mapping blocks to PCM locks for the parameter value GC_FILES_TO_LOCKS = "1=60:2-3=40:4=140:5=30".
In data file 1 shown in Figure 7-1, 60 PCM locks map to 120 blocks, which is a multiple of 60. Each PCM lock therefore covers two data blocks.
In data files 2 and 3, 40 PCM locks map to a total of 160 blocks. A PCM lock can cover either one or two data blocks in data file 2, and two or three data blocks in data file 3. Thus, one PCM lock may cover three, four, or five data blocks across both data files.
In data file 4, each PCM lock maps exactly to a single data block, since there is the same number of PCM locks as data blocks.
In data file 5, 30 PCM locks map to 170 blocks, which is not a multiple of 30. Each PCM lock therefore covers five or six data blocks.
Each of the PCM locks illustrated in Figure 7-1 can be held in either read-lock mode or read-exclusive mode.
The following parameter setting allocates 500 PCM locks to data file 1; 400 PCM locks each to files 2, 3, 4, 10, 11, and 12; 150 PCM locks to file 5; 250 PCM locks to file 6; and 300 PCM locks collectively to files 7 through 9:
GC_FILES_TO_LOCKS = "1=500:2-4,10-12=400EACH:5=150:6=250:7-9=300"
This example assigns a total of (500 + (6*400) + 150 + 250 + 300) = 3600 PCM locks. You may specify more than this number of PCM locks if you intend to add more data files.
In Example 2, 300 PCM locks are allocated to data files 7, 8, and 9 collectively with the clause "7-9=300". The keyword EACH is omitted. If each of these data files contains 900 data blocks, for a total of 2700 data blocks, then each PCM lock covers 9 data blocks. Because the data files are multiples of 300, the 9 data blocks covered by the PCM lock are spread across the 3 data files; that is, one PCM lock covers 3 data blocks in each data file.
The following parameter value allocates 200 PCM locks each to files 1 through 3; 50 PCM locks to data file 4; 100 PCM locks collectively to data files 5, 6, 7, and 9; and 20 data locks in contiguous 50-block groups to data files 8 and 10 combined:
GC_FILES_TO_LOCKS = "1-3=200EACH 4=50:5-7,9=100:8,10=20!50"
In this example, a PCM lock assigned to the combined data files 5, 6, 7, and 9 covers one or more data blocks in each data file, unless a data file contains fewer than 100 data blocks. If data files 5 to 7 contain 500 data blocks each and data file 9 contains 100 data blocks, then each PCM lock covers 16 data blocks: one in data file 9 and five each in the other data files. Alternatively, if data file 9 contained 50 data blocks, half of the PCM locks would cover 16 data blocks (one in data file 9); the other half of the PCM locks would only cover 15 data blocks (none in data file 9).
The 20 PCM locks assigned collectively to data files 8 and 10 cover contiguous groups of 50 data blocks. If the data files contain multiples of 50 data blocks and the total number of data blocks is not greater than 20 times 50 (that is, 1000), then each PCM lock covers data blocks in either data file 8 or data file 10, but not in both. This is because each of these PCM locks covers 50 contiguous data blocks. If the size of data file 8 is not a multiple of 50 data blocks, then one PCM lock must cover data blocks in both files. If the sizes of data files 8 and 10 exceed 1000 data blocks, then some PCM locks must cover more than one group of 50 data blocks, and the groups might be in different files.
Examples 5, 6, and 7 show the results of specifying various values of GC_FILES_TO_LOCKS. In the examples, files 1 and 2 each have 16 blocks of data.
GC_FILES_TO_LOCKS="1-2=4"
In this example four locks are specified for files 1 and 2. Therefore, the number of blocks covered by each lock is 8 ((16+16)/4). The blocks are not contiguous.
GC_FILES_TO_LOCKS="1-2=4!8"
In this example, four locks are specified for files 1 and 2. However, the locks must cover 8 contiguous blocks.
GC_FILES_TO_LOCKS="1-2=4!4EACH"
In this example four locks are specified for file 1 and four for file 2. The locks must cover four contiguous blocks.
The following example shows releasable locking mixed with fixed locking.
GC_FILES_TO_LOCKS="1=4:2=0"
File 1 has fixed PCM locking with 4 locks. On file 2, releasable locks are allocated on demand--none are initially allocated.
On large applications, carefully study the business processes involved. Worksheets similar to those in this section may be useful.
Determine the types of operations your system performs on a daily basis. The distinction between operations needing X locks and those needing S locks is a key issue. Every time Oracle converts a lock from one mode to the other, you need locks. Consider the interaction of different instances on a table. Also consider the number of rows in a block, the number of rows in a table, and the table's growth rate. Based on this analysis, group your objects into files, and assign free list groups.
| Object | Operations needing X mode: Writes | Oracle Parallel Server needing S mode: Reads | TS/Data File | ||
| INSERTS | UPDATES | DELETES | SELECTS | ||
|
A |
|
80% |
|
Single row? |
|
|
B |
|
|
|
100% |
|
|
C |
|
|
|
|
|
|
D |
|
|
|
|
|
| Object | Instance 1 | Instance 2 | Instance 3 |
|
D |
DELETE |
SELECT |
|
|
E |
|
|
|
|
F |
|
|
|
| Table Name | TS to put it in | Row Size | Number of Columns |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
In many cases, you need relatively few PCM locks to cover read-only data compared to data that is updated frequently. This is because read-only data can be shared by all instances of an Oracle Parallel Server. Data that is never updated can be covered by a single PCM lock. Data that is not read-only should be covered by more than a single PCM lock.
If data is read-only, then once an instance owns the PCM locks for the read-only tablespace, the instance never disowns them. The DLM operations are not required after the initial lock acquisition.
For best results, partition your read-only tablespace so it is covered by its own set of PCM locks. Do this by placing read-only data in a tablespace that does not have writable data. Then allocate PCM locks to the data files in the tablespace using the GC_FILES_TO_LOCKS parameter.
You can map PCM locks to particular data blocks to partition PCM locks among instances based on the data each instance accesses.
This technique minimizes unnecessary distributed lock management. Likewise, it minimizes the disk I/O caused by an instance having to write out data blocks because a requested data block was covered by a PCM lock owned by another instance.
For example, if Instance X primarily updates data in data files 1, 2, and 3, while Instance Y primarily updates data in data files 4 and 5, you can assign one set of PCM locks to files 1, 2, and 3 and another set to files 4 and 5. Then each instance acquires ownership of the PCM locks for the data it updates. One instance disowns the PCM locks only if the other instance needs access to the same data.
By contrast, if you assign one set of PCM locks to data files 3 and 4, I/O increases. This is because both instances regularly use the same set of PCM locks.
This section describes some of the most common non-PCM instance locks. It covers the following information:
Chapter 10 for details on how to calculate the number of non-PCM resources and locks to configure in the DLM.
See Also:
This section explains how Oracle uses non-PCM locks to manage locks for transactions, tables, and other entities within an Oracle environment. Prefixes for each type of lock, such as "TX" for transaction locks and "TM" for table locks, refer to the naming scheme Oracle uses to identify them.
Figure 7-9 highlights non-PCM locks in relation to other locks used in Oracle.
Whereas PCM locks are static (you allocate them when you design your application), non-PCM locks are very dynamic. Their number and corresponding space requirements will change as your system's initialization parameter values change.
Row locks are locks that protect selected rows. A transaction acquires a global enqueue and an exclusive lock for each individual row modified by one of the following statements:
These locks are stored in the block, and each lock refers to the global transaction enqueue.
A transaction lock is acquired in exclusive mode when a transaction initiates its first change. It is held until the transaction performs a COMMIT or ROLLBACK. SMON also acquires it in exclusive mode when recovering (undoing) a transaction. Transaction locks are used as a queuing mechanism for processes awaiting the release of an object locked by a transaction in progress.
Table locks are DML locks that protect entire tables. A transaction acquires a table lock when a table is modified by one of the following statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. A table lock can be held in any of several modes: null (N), row share (RS), row exclusive (RX), share lock (S), share row exclusive (SRX), and exclusive (X).
When an instance attempts to mount the database, a table lock is used to ensure that all participating instances either have DML_LOCKS = 0 or DML_LOCKS != 0. If they do not, Oracle displays error ORA-61 and the mount attempt fails. Table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction. This occurs if and only if the DML_LOCKS parameter is non-zero.
You can also selectively turn table locks on or off for a particular table, using the statement:
ALTER TABLE tablename DISABLE|ENABLE TABLE LOCK
If DML_LOCKS is set to zero, then no DDL operations are allowed. The same is true for tables that have disabled table locks.
|
See Also:
"Minimizing Table Locks to Optimize Performance" for more information about minimizing instance locks and disabling table locks for improved performance. |
The System Change Number (SCN) is a logical timestamp that Oracle uses to order events within a single instance, and across all instances. One of the schemes Oracle uses to generate SCNs is the lock scheme.
The lock SCN scheme keeps the global SCN in the value block of the SCN lock. Oracle increments this value in response to many database events, most notably after COMMITs. A process incrementing the global SCN obtains the SCN lock in exclusive mode, increments the SCN, writes the lock value block, and downgrades the lock. Access to the SCN lock value is batched. Oracle keeps a cache copy of the global SCN in memory. A process may obtain an SCN without any communication overhead by reading the SCN fetched by other processes.
The SCN implementation can differ from platform to platform. On most platforms, Oracle uses the lock SCN scheme when the MAX_COMMIT_PROPAGATION_DELAY initialization parameter is smaller than a platform-specific threshold (typically 7).
Oracle uses the Lamport SCN scheme when MAX_COMMIT_PROPAGATION_DELAY is larger than the threshold.You can examine the alert log after an instance is started to see which SCN generation scheme has been picked.
When a database object (table, view, procedure, function, package, package body, trigger, index, cluster, synonym) is referenced during parsing or during the compiling of a SQL (DML/DDL) or PL/SQL statement, the process parsing or compiling the statement acquires the library cache lock in the correct mode. In Oracle8 the lock is held only until the parse or compilation completes (for the duration of the parse call).
The data dictionary cache contains information from the data dictionary, the meta-data store. This cache provides efficient access to the data dictionary.
Creating a new table, for example, causes the meta-data of that table to be cached in the data dictionary. If you drop a table, the meta-data needs to be removed from the data dictionary cache. To synchronize access to the data dictionary cache, latches are used in exclusive mode and in single shared mode. Instance locks are used in multiple shared (parallel) mode.
In Oracle Parallel Server, the data dictionary cache on all nodes may contain the meta-data of a table that gets dropped on one instance. The meta-data for this table needs to be flushed from the data dictionary cache of every instance. This is performed and synchronized by instance locks.
The mount lock shows whether an instance has mounted a particular database. This lock is only used with Oracle Parallel Server. It is the only multi-instance lock used by Oracle Parallel Server in exclusive mode and prevents another instance from mounting the database in shared mode.
In Oracle Parallel Server single shared mode, a DM lock is held in shared mode, so another instance can mount the same database in shared mode. In Oracle Parallel Server exclusive mode, however, another instance cannot to obtain the lock.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|