| Oracle7 Parallel Server Concepts and Administrator's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Parallel Server Concepts and Administrator's Guide | Library |
Product |
Contents |
Index |
See Also: The "Parallel Cache Management Instance Locks" chapter ![[*]](jump.gif)
for a conceptual discussion of PCM locks and GC_* parameters.
"Initialization Parameters" for descriptions of all the initialization parameters used to allocate locks for the parallel server.
See Also: "Application Analysis" chapter .
SQL> SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BLOCKS
2 FROM DBA_DATA_FILES;
Results are displayed 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
. . .
Figure out the breakdown of operations on your system on a daily basis. The distinction between operations needing X locks and those needing S locks is the key. Every time you have to go from one mode to the other, you need locks. Take into consideration the interaction of different instances on a table. Also take into consideration the number of rows in a block, the number of rows in a table, and the growth rate. Based on this analysis, you can group your objects into files, and assign free list groups.
| Object | Operations needing X mode: Writes | Ops needing S mode: Reads | TS/Datafile | ||
| INSERTS | UPDATES | DELETES | SELECTS | ||
| A | 80% | 20% full table scan? single row? | |||
| B | 100% | ||||
| C | |||||
| D | |||||
| Object | Instance 1 | Instance 2 | Instance 3 |
| D | INSERT UPDATE DELETE | SELECT | |
| E | |||
| F | |||
| Table Name | TS to put it in | Row Size | Number of Columns |
If data is read-only, then once an instance owns the PCM locks for the read-only tablespace, the instance never disowns them. No distributed lock management operations are required after the initial lock acquisition. For best results, partition your read-only tablespace so that it is covered by its own set of PCM locks.
You can do this by placing read-only data in a tablespace which does not contain any writable data. Then you can allocate PCM locks to the datafiles in the tablespace, using the GC_FILES_TO_LOCKS parameter.
Do not put read-only data and writable data in the same tablespace.
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 datafiles 1, 2, and 3, while Instance Y primarily updates data in datafiles 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 which cover 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 datafiles 3 and 4, I/O will increase. This happens because both instances regularly use the same set of PCM locks.
When the parallel server is started, GC_DB_LOCKS is rounded up to the next prime number to ensure that resized or new datafiles not specified in GC_FILES_TO_LOCKS can be covered by PCM locks.
If the database is started with GC_DB_LOCKS set to zero, then fine grain locking is enabled.
Use the following guidelines to set this parameter:
The syntax for setting this parameter is:
GC_DB_LOCKS=maxlocks
where
maxlocks
specifies the maximum number of PCM locks to be allocated for all data blocks
See Also: "What Prime Numbers Result from GC_* Parameter Values" .
Note: Whenever you add or resize a datafile, create a tablespace, or drop a tablespace and its datafiles, you should adjust the value of GC_FILES_TO_LOCKS before restarting Oracle in parallel mode.
See Also: The "Parallel Cache Management Instance Locks" chapter to understand how the number of data blocks covered by a single PCM lock is determined.
Syntax
The syntax for setting the GC_FILES_TO_LOCKS parameter specifies the translation between the database address and class of a database block, and the lock name which will protect it. You cannot specify
this translation for files which are not mentioned in the GC_FILES_TO_LOCKS parameter.
The syntax for setting this parameter is:
GC_FILES_TO_LOCKS="{file_list=#locks[!blocks][EACH[:]} . . ."
where
file_list
specifies a single file, range of files, or list of files and ranges as follows:
fileidA[-fileidC][,fileidE[-fileidG]] ...
Query the data dictionary view DBA_DATA_FILES to find the correspondence between file names and file ID numbers.
#locks
sets the number of PCM locks to assign to file_list
A value of zero (0) for #locks means that fine grain lock will be used instead of hashed locks.
!blocks
specifies the number of contiguous data blocks to be covered by each lock
EACH
specifies #locks as the number of locks to be allocated to each file in file_list
Spaces are not permitted within the quotation marks of the GC_FILES_TO_LOCKS parameter.
Omitting EACH and "!blocks" means that #locks PCM locks are allocated collectively to file_list and individual PCM locks cover data blocks across every file in file_list. However, if any datafile contains fewer data blocks than the number of PCM locks, some PCM locks will not cover a data block in that datafile.
The default value for !blocks is 1. When specified, blocks contiguous data blocks are covered by each one of the #locks PCM locks. To specify a value for blocks, you must use the "!" separator. You would primarily specify blocks (and not specify the EACH keyword) to allocate sets of PCM locks to cover multiple datafiles. You can use blocks to allocate a set of PCM locks to cover a single datafile where PCM lock contention on that datafile will be minimal, thus reducing PCM lock management.
Always set the !blocks value to avoid breaking data partitioning gained by using free list groups. Normally you do not need to pre-allocate disk space. When a row is inserted into a table and new extents need to be allocated, contiguous blocks specified with !blocks in GC_FILES_TO_LOCKS are allocated to the free list group associated with an instance.
GC_FILES_TO_LOCKS = "1=300:2=100"
The following entry specifies a total of 1500 locks--500 each for files 1, 2, and 3:
GC_FILES_TO_LOCKS = "1-3=500EACH"
By contrast, the following entry specifies a total of only 500 locks, spread across the three files:
GC_FILES_TO_LOCKS = "1-3=500"
The following entry indicates that 1000 distinct locks should be used to protect file 1. The data in the files is protected in groups of 25 blocks.
GC_FILES_TO_LOCKS = "1=1000!25"
GC_FILES_TO_LOCKS="1=0!4"
This specifies fine grain locks with a group factor of 4 for file 1.
If GC_RELEASABLE_LOCKS is set, then the data blocks in files not mentioned in GC_FILES_TO_LOCKS will not default to releasable locks. In this case, you can make the data blocks in all files fine grained by specifying:
GC_DB_LOCKS=0
Additionally, whenever you add or resize a datafile, create a tablespace, or drop a tablespace and its datafiles, you should adjust the value of GC_FILES_TO_LOCKS and GC_DB_LOCKS before restarting Oracle in parallel mode.
Checking for Valid Number of Locks
Make sure the number of locks allocated is not larger than the number of data blocks allocated:
SELECT E.FILE_ID,
F.FILE_NAME,
SUM(E.BLOCKS) ALLOCATED,
F.BLOCKS "FILE SIZE"
FROM DBA_EXTENTS E,
DBA_DATA_FILES F
WHERE E.FILE_ID = F.FILE_ID
GROUP BY E.FILE_ID, F.FILE_NAME, F.BLOCKS
ORDER BY E.FILE_ID; You can execute the following query to extend this with information on the locks per file allocated:
SELECT I.KCLFIBUK BUCKET#,
H.KCLFHSIZ LOCKS,
SUM(F.BLOCKS) BLOCKS
FROM X$KCLFH H,
X$KCLFI I,
DBA_DATA_FILES F
WHERE I.KCLFIBUK = H.INDX
AND I.INDX = F.FILE_ID
GROUP BY I.KCLFIBUK, H.KCLFHS;
SELECT KCLFIBUK BUCKET#,
FILE_NAME NAME,
FILE_ID FILE#
FROM X$KCLFI,
DBA_DATA_FILES
WHERE FILE_ID = INDX;
The first query shows the number of locks and datablocks allocated to a bucket. The second query shows which files are allocated to which bucket.
Checking for Valid Lock Assignments
To avoid problems with lock assignments, check the following:
SELECT E.FILE_ID FILE_ID,
COUNT(DISTINCT OWNER||NAME ) OBJS
FROM DBA_EXTENTS E,
EXT_TO_OBJ_VIEW V
WHERE E.FILE_ID = FILE#
AND E.BLOCK_ID >= LOWB
AND E.BLOCK_ID <= HIGHB
AND KIND != `FREE EXTENT'
AND KIND != `UNDO'
GROUP BY E.FILE_ID;
Examine the files which store multiple objects. Run CATPARR.SQL to use EXT_TO_OBJ_VIEW. Make sure that they can coexist in the same file (that is, make sure the GC_DB_LOCKS/GC_FILES_TO_LOCKS settings are compatible).
In this case, the datafile will be assigned to the pool of remaining locks and the file must contend with all the files which were not mentioned in the GC_FILES_TO_LOCKS parameter.
To Detect False Pinging on Segment Header and Set GC_SEGMENTS
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'GC_SEGMENTS';
SELECT COUNT(*)
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('INDEX', 'TABLE', 'CLUSTER');
SELECT MOD(DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(
HEADER_FILE, HEADER_BLOCK),
LPRIME(R.VALUE)) LOCK_ELEMENT,
SEGMENT_NAME,
SEGMENT_TYPE
FROM DBA_SEGMENTS,
V$PARAMETER R
WHERE R.NAME = 'GC_SEGMENTS'
AND (SEGMENT_TYPE = 'INDEX'
OR SEGMENT_TYPE = 'TABLE'
OR SEGMENT_TYPE = 'CLUSTER')
ORDER BY LOCK_ELEMENT;
SELECT LPRIME(VALUE) FROM V$PARAMETER WHERE NAME = 'GC_SEGMENTS';
See Also: "What Prime Numbers Result from GC_ Parameter Values?" for the source for the PL/SQL function lprime.
SELECT S.SEGMENT_NAME NAME,
SUM(RBLOCKS) BLOCKS
FROM DBA_SEGMENTS S,
DBA_EXTENTS R
WHERE S.SEGMENT_TYPE = 'ROLLBACK'
AND S.SEGMENT_NAME = R.SEGMENT_NAME
GROUP BY S.SEGMENT_NAME;
This query displays the number of blocks allocated to each rollback segment. When there is a lot of pinging on the undo blocks, try increasing the number of GC_ROLLBACK_LOCKS.
Setting GC_SAVE_ ROLLBACK_LOCKS
Use the same value as for GC_ROLLBACK_LOCKS.
There are no direct statistics of false pinging--only indications which you can interpret. This section describes some indications you can watch out for.
The following SQL statement shows the number of lock operations which caused a write, and the number of blocks actually written:
SELECT VALUE/(A.COUNTER + B.COUNTER + C.COUNTER) "PING RATE"
FROM V$SYSSTAT,
V$LOCK_ACTIVITY A,
V$LOCK_ACTIVITY B,
V$LOCK_ACTIVITY C
WHERE A.FROM_VAL = 'X'
AND A.TO_VAL = 'NULL'
AND B.FROM_VAL = 'X'
AND B.TO_VAL = 'S'
AND C.FROM_VAL = 'X'
AND C.TO_VAL = 'SSX'
AND NAME = 'DBWR cross instance writes'; The following table shows how to interpret the ping rate.
| Ping Rate | Meaning |
| < 1 | False pinging may be occurring, but there are more lock operations than writes for pings. DBWR is writing out blocks fast enough, causing no write for a lock activity. This is also known as a soft ping (no I/O is required for the ping, only lock activity). |
| = 1 | Each lock activity which involves a potential write, does indeed cause the write to happen. False pinging may be occurring. |
| > 1 | False pings are definitely occurring. |
Use the following formula to calculate the percentage of pings which are definitely false:
Then check the total number of writes and calculate the number due to false pings:
SELECT Y.VALUE "ALL WRITES",
Z.VALUE "PING WRITES",
Z.VALUE * pingrate "FALSE PINGS",
FROM V$SYSSTAT Z,
V$SYSSTAT Y,
WHERE Z.NAME = 'DBWR cross instance writes'
AND Y.NAME = 'physical writes'; Here, ping_rate is given by the following SQL statement:
CREATE OR REPLACE VIEW PING_RATE AS
SELECT ((VALUE/(A.COUNTER+B.COUNTER+C.COUNTER))-1)/
(VALUE/(A.COUNTER+B.COUNTER+C.COUNTER)) RATE
FROM V$SYSSTAT,
V$LOCK_ACTIVITY A,
V$LOCK_ACTIVITY B,
V$LOCK_ACTIVITY C
WHERE A.FROM_VAL = `X'
AND A.TO_VAL = `NULL'
AND B.FROM_VAL = `X'
AND B.TO_VAL = `S'
AND C.FROM_VAL = `X'
AND C.TO_VAL = `SSX'
AND NAME = `DBWR cross instance writes';
Needless to say, the goal is not only to reduce overall pinging, but also to reduce false pinging. To reduce false pings, look at the distribution of instance locks in GC_FILES_TO_LOCKS, and check the data in the files.
SELECT * FROM V$SYSTEM_EVENT WHERE EVENT = 'lock element cleanup'
This SQL statement displays a table like the following:
TOTAL_ TOTAL_ TIME_ AVERAGE_ EVENT WAITS TIMEOUTS WAITED WAIT -------------------- ------ ------- ------ ---------- lock element cleanup 32709 44 685660 20.9624262
This means that a lock conversion took 20.9 hundredths of a second (0.209 seconds).
SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'lock element cleanup'
SELECT LPRIME(D.VALUE) /* GC_DB_LOCKS */
+ LPRIME(1) /* FOR TEMP BLOCKS */
+ LPRIME(S.VALUE) /* GC_SEGMENTS */
+ LPRIME(FL.VALUE) /* FREE LIST GROUPS LOCKS */
+ LPRIME(T.VALUE) /*GC_TABLESPACES */
+ LPRIME(SR.VALUE) /* GC_SAVE_ROLLBACK_LOCKS */
+ (RL.VALUE + 1) * RS.VALUE "PCM LOCKS"
/* NUMBER OF BLOCKS FOR RBS */
FROM V$PARAMETER D,
V$PARAMETER S,
V$PARAMETER T,
V$PARAMETER SR,
V$PARAMETER RS,
V$PARAMETER RL,
V$PARAMETER FL,
V$PARAMETER FG
WHERE D.NAME = 'gc_db_locks'
AND S.NAME = 'gc_segments'
AND T.NAME = 'gc_tablespaces'
AND SR.NAME = 'gc_save_rollback_locks'
AND RS.NAME = 'gc_rollback_segments'
AND RL.NAME = 'gc_rollback_locks'
AND FL.NAME = 'gc_freelist_groups'
AND FG.NAME = 'gc_releasable_locks';Alternatively, query the V$LOCK_ELEMENT table:
SELECT COUNT(*) FROM V$LOCK_ELEMENT;
For planning purposes, you can use the following procedure to determine the number of PCM locks:
EXECUTE PCM_LOCKS(100,100,100,100,100,20,50,100, :TOTAL_LOCKS,:TOTAL_RESOURCES);
CREATE OR REPLACE PROCEDURE PCM_LOCKS
(GC_DB_LOCKS IN NUMBER,
GC_ROLLBACK_LOCKS IN NUMBER,
GC_ROLLBACK_SEGMENTS IN NUMBER,
GC_SAVE_ROLLBACK_LOCKS IN NUMBER,
GC_SEGMENTS IN NUMBER,
GC_TABLESPACES IN NUMBER,
GC_FREELIST_GROUPS IN NUMBER,
GC_RELEASABLE_LOCKS IN NUMBER,
INSTANCES IN NUMBER,
TOTAL_PCM_LOCKS IN OUT NUMBER
TOTAL_PCM_RESOURCES IN OUT NUMBER) AS
BEGIN
TOTAL_PCM_LOCKS := LPRIME(GC_DB_LOCKS);
TOTAL_PCM_LOCKS := TOTAL_PCM_LOCKS + 3;
TOTAL_PCM_LOCKS := TOTAL_PCM_LOCKS +
LPRIME(GC_SEGMENTS);
TOTAL_PCM_LOCKS := TOTAL_PCM_LOCKS +
LPRIME(GC_FREELIST_GROUPS);
TOTAL_PCM_LOCKS := TOTAL_PCM_LOCKS +
LPRIME(GC_SAVE_ROLLBACK_LOCKS);
TOTAL_PCM_LOCKS := TOTAL_PCM_LOCKS +
(GC_ROLLBACK_SEGMENTS *
(GC_ROLLBACK_LOCKS + 1));
TOTAL_PCM_LOCKS := TOTAL_PCM_LOCKS +
GC_RELEASABLE_LOCKS;
TOTAL_PCM_RESOURCES := TOTAL_PCM_LOCKS;
TOTAL_PCM_LOCKS := INSTANCES * TOTAL_PCM_LOCKS;
END; If you enter your own initialization parameters into the preceding statement, the system calculates the number of PCM locks that will be required. A test such as this can help you ensure that there is enough space for PCM locks when you configure your DLM. For example:
VARIABLE TOT_LOCKS NUMBER;
VARIABLE TOT_RES NUMBER;
EXECUTE PCM_LOCKS(100,100,100,100,100,20,40,100,2,:TOT_LOCKS,:TOT_RES);
PRINT TOT_LOCKS TOT_RES
Use the following PL/SQL function to find the next prime number for any value:
CREATE OR REPLACE FUNCTION LPRIME(X IN NUMBER) RETURN NUMBER IS
W NUMBER;
I NUMBER;
BEGIN
W := X;
IF W <= 2
THEN
W := 3;
RETURN W;
END IF;
IF MOD(W, 2) = 0
THEN
W := W + 1;
END IF;
LOOP
I := 3;
LOOP
IF I * I > W
THEN
RETURN W;
END IF;
IF MOD(W, I) = 0
THEN
EXIT;
END IF;
I := I + 2;
END LOOP;
W := W + 2;
END LOOP;
END;
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |