Finding the contention
When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:
SELECT p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait WHERE event = 'read by other session'; | ||
If information collected from the above query repeatedly shows that the same block, (or range of blocks), is experiencing waits, this indicates a "hot" block or object. The following query will give the name and type of the object:
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file AND &block BETWEEN block_id AND block_id + blocks - 1; | ||
Eliminating contention
Depending on the database environment and specific performance situation the following variety of methods can be used to eliminate contention:
1. Tune inefficient queries - This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information we can obtain from the operating system.
2. Redistribute data from the hot blocks �deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:
Depending on the database environment and specific performance situation the following variety of methods can be used to eliminate contention:
1. Tune inefficient queries - This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information we can obtain from the operating system.
2. Redistribute data from the hot blocks �deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:
SELECT data_object_id FROM dba_objects WHERE owner='&owner' AND object_name='&object'; SELECT dbms_rowid.rowid_create(1, FROM dual; --rowid for the first row in the block SELECT dbms_rowid.rowid_create(1, FROM dual; --rowid for the 500th row in the block SELECT FROM WHERE rowid BETWEEN | ||
3. Adjust PCTFREE and PCTUSED � adjusting the PCTFREE value downward for an object will reduce the number of rows physically stored in a block. Adjusting the PCTUSED value for an object keeps that object from getting prematurely put back on the freelist.
Depending on the type of contention, adjusting these values could help distribute data among more blocks and reduce the hot block problem. Be careful to optimize these parameters so blocks do move in and out of the freelist too frequently.
4. Reduce the Block Size � this is very similar to adjusting the PCTFREE and PCTUSED parameters in that the goal is to reduce the amount of data stored within one block. In Oracle 9i and higher this can be achieved by storing the hot object in a tablespace with a smaller block size. In databases prior to Oracle 9i the entire database must be rebuilt with a smaller block size.
5. Optimize indexes � a low cardinality index has a relatively small number of unique values, e.g. a column containing state data with only 50 values. Similar to inefficient queries, the use of a low cardinality index could cause excessive number of blocks to be read into the buffer cache and cause premature aging out of "good" blocks.
Conclusion
When a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended for. Once that is known this document lists several alternative methods for solving the issue.
When a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended for. Once that is known this document lists several alternative methods for solving the issue.
REFERENCES
let me share my experience with regard to the service of sql server 2000 database repair, it automatically eliminates data corruption issues in selected databases
ReplyDeletewhat you have explained here is very generic example,
ReplyDeletewhat if the p1 and p2 values are changing consistently ?