Thursday, February 19, 2009

db file sequential read

The db file sequential read wait event occurs when the process waits for an I/O completion for a sequential read.
The name is a bit misleading, suggesting a multiblock operation, but this is a single block read operation.
The event gets posted when reading from an index, rollback or undo segments, table access by rowid, rebuilding control files, dumping datafile headers, or the datafile headers.

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 single block reads is significant compared to other waits, you must investigate the reason for it.

This may be:

1. Frequent DML operations (update, delete) could fragment your indexes. You can identify them, following the steps described here and here. Then rebuild them.
2. Perhaps the index used is not the appropriate one for your query. Create an index that matches your query better, or, if it already exists, tune it, so it would be chosen.
3. Reduce the Disk I/O contention, by distributing the index to different filesystems.
4. Increase the db_cache_size parameter.

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