Thursday, February 19, 2009

db file scattered read


The db file scattered read event is posted by the session when it issues an I/O request to read multiple data blocks.
The blocks read from the datafiles are scattered into the buffer cache.
These blocks need not remain contiguous in the buffer cache.
The event typically occurs during full table scans or index fast full scans.
The initialization parameter, DB_FILE_MULTIBLOCK_READ_COUNT determines the maximum number of data blocks to read.

Waiting on datafile I/O completion is normal in any Oracle database.
The presence of this wait event does not necessarily indicate a performance problem.
However, if the time spent waiting for multiblock reads is significant compared to other waits, you must investigate the reason for it.

This may be:

1. Perhaps creation of new indexes could reduce the full table scans, occurring to our database.
2. Frequent DML operations (update, delete) could fragment your segments. You can identify them, following the steps described here and here. Then move your fragmented tables and rebuild your fragmented indexes.
3. Use Parallelsm, if full table scans/index fast full scans cannot be avoided.
4. Use tablespace with bigger block size.

Use the script posted in "Identify waiting sessions".

From that, you can:

1. Using the SQL hash value from the CURRENT_EVENT cursor, you can capture the SQL text from the GV$SQLTEXT view as shown next and store it in the appropriate history table. If you are also interested in the SQL statistics, you can get the information from the V$SQLAREA using the same SQL hash value from the cursor:

SELECT INST_ID,
         HASH_VALUE,
         ADDRESS,
         PIECE,
         SQL_TEXT
    FROM GV$SQLTEXT
   WHERE HASH_VALUE = 4190307765                              --sql_hash_value
                                AND INST_ID = 1                 --RAC Instance
ORDER BY INST_ID, PIECE;

2. Find the segment being read:
SELECT DISTINCT A.OBJECT_NAME, A.SUBOBJECT_NAME
  FROM DBA_OBJECTS A, SYS.X$BH B
 WHERE     (A.OBJECT_ID = B.OBJ OR A.DATA_OBJECT_ID = B.OBJ)
       AND B.FILE# = 604                                                  --P1
       AND B.DBABLK = 2003;                                               --P2

No comments:

Post a Comment