| 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: "Monitoring & Tuning Oracle7 Parallel Server" ![[*]](jump.gif)
.
Oracle7 Server Reference for more information on dynamic views and monitoring your database.
| FILE_ID | NUMBER | Datafile identifier number (to find file name, query DBA_DATA_FILES or V$DBFILES) |
| FILE_NAME | VARCHAR(257) | The datafile name |
| TS_NAME | VARCHAR(30) | The tablespace name for the datafile |
| START_LK | NUMBER | The first lock corresponding to the datafile |
| NLOCKS | NUMBER | The number of PCM locks allocated to the datafile |
| BLOCKING | NUMBER | The number of blocks protected by a PCM lock on the datafile |
| FILE_ID | NUMBER | Datafile ID number. (To find file name, query DBA_DATA_FILES or V$DBFILES.) |
| FILE_NAME | VARCHAR(257) | The datafile name |
| TS_NAME | VARCHAR(30) | Tablespace name for the datafile |
| FREQUENCY | NUMBER | The ping count |
| FILE# | NUMBER | Datafile identifier number (to find file name, query DBA_DATA_FILES or V$DBFILES) |
| BLOCK# | NUMBER | Block number |
| CLASS# | NUMBER | Class number: 1 data or index blocks 2 sort blocks 3 save undo blocks 4 segment headers 5 save undo segment header blocks 6 free list blocks 7 system undo segment header blocks 8 system undo segment blocks 7+(n*2) undo segment n header block 8+(n*2) undo segment n block |
| STATUS | VARCHAR(4) | Status of block: FREE not currently in use XCUR exclusive SCUR shared current CR consistent read READ being read from disk MREC in media recovery mode IREC in instance recovery mode |
| XNC | NUMBER | Number of PCM lock conversions due to contention with another instance |
| LOCK_ ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| DIRTY | VARCHAR2(1) | Y block modified |
| TEMP | VARCHAR2(1) | Y temporary block |
| PING | VARCHAR2(1) | Y block pinged |
| STALE | VARCHAR2(1) | Y block is stale |
| DIRECT | VARCHAR2(1) | Y direct block |
| NEW | VARCHAR2(1) | Y new block |
| FILE_# | NUMBER | Datafile identifier number (to find file name, query DBA_DATA_FILES or V$DBFILES) |
| BLOCK# | NUMBER | Block number |
| CLASS# | NUMBER | Class number: 1 data or index blocks 2 sort blocks 3 save undo blocks 4 segment headers 5 save undo segment header blocks 6 free list blocks 7 system undo segment header blocks 8 system undo segment blocks 7+(n*2) undo segment n header block 8+(n*2) undo segment n block |
| STATUS | VARCHAR(4) | Status of block: FREE not currently in use XCUR exclusive SCUR shared current CR consistent read READ being read from disk MREC in media recovery mode IREC in instance recovery mode |
| XNC | NUMBER | Number of PCM lock conversions due to contention with another instance |
| LOCK_ ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| NAME | VARCHAR2(30) | Name of the database object containing the block |
| KIND | VARCHAR2(12) | Type of database object: TABLE CLUSTER INDEX UNDO rollback segment FREE EXTENT TEMP SEGMENT |
| OWNER# | NUMBER | Owner number |
| FILE_# | NUMBER | Datafile identifier number (to find file name, query DBA_DATA_FILES or V$DBFILES) |
| BLOCK# | NUMBER | Block number |
| STATUS | VARCHAR(4) | Status of block: FREE not currently in use XCUR exclusive SCUR shared current CR consistent read READ being read from disk MREC in media recovery mode IREC in instance recovery mode |
| XNC | NUMBER | Number of PCM lock conversions due to contention with another instance |
| LOCK_ ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| NAME | VARCHAR2(30) | Name of the database object containing the block |
| KIND | VARCHAR2(12) | Type of database object: TABLE CLUSTER INDEX UNDO rollback segment FREE EXTENT TEMP SEGMENT |
| OWNER# | NUMBER | Owner number |
| INDX | NUMBER | Index into V$LOCK_ELEMENT view |
| CLASS# | NUMBER | Class number: 1 data or index blocks 2 sort blocks 3 save undo blocks 4 segment headers 5 save undo segment header blocks 6 free list blocks 7 system undo segment header blocks 8 system undo segment blocks 7+(n*2) undo segment n header block 8+(n*2) undo segment n block |
| FILE_# | NUMBER | Datafile identifier number (to find file name, query DBA_DATA_FILES or V$DBFILES) |
| BLOCK# | NUMBER | Block number |
| STATUS | VARCHAR(4) | Status of block: FREE not currently in use XCUR exclusive SCUR shared current CR consistent read READ being read from disk MREC in media recovery mode IREC in instance recovery mode |
| XNC | NUMBER | Number of PCM lock conversions due to contention with another instance |
| LOCK_ ELEMENT_ ADDR | RAW(4) | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| NAME | VARCHAR2(30) | Name of the database object containing the block |
| KIND | VARCHAR2(12) | Type of database object: TABLE CLUSTER INDEX UNDO rollback segment FREE EXTENT TEMP SEGMENT |
| OWNER# | NUMBER | Owner number |
| FROM_VAL | VARCHAR2(4) | PCM lock initial state: NULL, S, X, SSX |
| TO_VAL | VARCHAR2(4) | PCM lock state: NULL, S, X, SSX |
| ACTION_VAL | VARCHAR2(51) | Description of lock conversion: Lock buffers for read Lock buffers for write Make buffers CR (no write) Upgrade read lock to write Make buffers CR (write dirty buffers) Downgrade write lock to read (write dirty buffers) Write transaction table/undo blocks Transaction table/undo blocks (write dirty buffers) Make transaction table/undo blocks available share Rearm transaction table write mechanism |
| COUNTER | NUMBER | Number of conversions which have occurred |
NULL to S
A NULL to S conversion (lock buffers for read) occurs when a block is selected in a query. The block may or may not be present in the instance's SGA. If a current copy of the block is in the cache, then only a lock conversion takes place and the block is not read from disk again. After the conversion, the status of the block is SCUR.
NULL to X
A NULL to X conversion (lock buffers for write) occurs when reading a block into the SGA for a DML operation (Insert, Update, Delete) if the lock is not already held in X mode. The status of the block after the conversion is XCUR.
S to NULL
An S to NULL conversion (make buffers CR, no write) occurs when an instance acquires a block for read only (SCUR mode) and another instance wants to modify the same block. The status of the block changes from SCUR to CR.
S to X
An S to X PCM lock conversion (upgrade read lock to write) occurs when a block is read into the SGA by a SELECT statement and then a DML statement is issued against the same block. The status of the block changes from SCUR to XCUR.
X to NULL
An X to NULL conversion (make buffers CR, write dirty buffers) occurs when an instance is modifying a block and another instance wants to modify the same block. The PCM lock on the first instance is converted from X to NULL, a write to disk takes place and the status of block in the first instance's SGA changes from XCUR to CR.
A high and consistently increasing number of these conversions indicates data contention. If the contention is for the same row, then the applications on the two instances should run on the same instance. If the two instances are accessing different rows in the same block, partition the data to reduce these conversions.
X to S
An X to S conversion (downgrade write lock to read, write dirty buffers) occurs when an instance has just modified a block and another instance wants to read the current version of the same block. This causes an X to S lock conversion on the first instance and the status of the block changes from XCUR to SCUR.
On databases where data is modified from only one instance and is queried from one or more other instances, this lock conversion occurs regularly. Run applications that access the same data on the same instance to increase performance.
X to SSX
An X to SSX conversion (write transaction table/undo blocks) occurs when an instance has modified a block but has not yet committed the changes, then another instance queries the same block. When the rollback segment on the first instance has to be read to construct a read consistent query for the second instance, the relevant rollback segment block has to be written out to disk. Run applications that access the same data on the same instance to increase performance.
Immediately after an SSX to X conversion takes place an X to S conversion is unnecessary because no other instance can ever write to this block. The status of the undo block remains unchanged after an X to SSX and a subsequent SSX to X conversion.
SSX to NULL
An SSX to NULL conversion (transaction table/undo blocks, write dirty buffers) occurs when an SSX to X conversion takes more than 3 seconds. This is very rare because the process blocking the conversion releases the lock as soon as it is acquired. If this conversion occurs frequently, the system is very slow and needs to tuned as a whole.
SSX to S
An SSX to S lock conversion (make transaction table/undo blocks available share) occurs when converting an undo block or segment header from X to SSX is not sufficient to satisfy the blocked request. This is rare because there are few cases where one instance wants to get the current version of another instance's undo blocks. This may occur at startup.
SSX to X
An SSX to X conversion (re-arm transaction table write mechanism) takes place immediately after an X to SSX conversion. The number of X to SSX and SSX to X conversions are always identical on a particular instance.
| LOCK_ ELEMENT_ ADDR | RAW(4) | Address of the lock element containing the PCM lock that is covering the buffer. Buffers with the same address are covered by the same PCM lock. |
| INDX | NUMBER | Index into V$LOCK_ELEMENT view |
| CLASS | NUMBER | Class number: 1 data or index blocks 2 sort blocks 3 save undo blocks 4 segment headers 5 save undo segment header blocks 6 free list blocks 7 system undo segment header blocks 8 system undo segment blocks 7+(n*2) undo segment n header block 8+(n*2) undo segment n block |
| FLAG | NUMBER | Status of the lock element: 1 valid 2 old 4 on free list 8 fixed lock element (that is, not fine grained) |
| MODE_HELD | NUMBER | Platform dependent value for lock mode held; often: 0 null 1 SS 2 SX 3 share 4 SSX |
| 5 exclusive | ||
| BLOCK_ COUNT | NUMBER | Number of block covered by PCM lock |
| RELEASING | NUMBER | Non-zero if PCM lock is being downgraded |
| ACQUIRING | NUMBER | Non-zero if PCM lock is being upgraded |
| INVALID | NUMBER | Non-zero if PCM lock is invalid. (A lock may become invalid after a system failure.) |
See Also: Oracle7 Server Reference for a complete description of each view.
"Session Wait Events for Oracle Parallel Server" for descriptions of individual wait events.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |