db file sequential read The db file sequential read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10 g , this wait event falls under the User I/O wait class. Keep the following key thoughts in mi
db file sequential read
The db file sequential read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10g, this wait event falls under the User I/O wait class. Keep the following key thoughts in mind when dealing with the db file sequential read wait event.
该等待事件的参数：file#,first block#,and block count（一般是1）可以从dba_extents去确定访问的段,属于I/O类的等待。
The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk.
The two important numbers to look for are the TIME_WAITED and AVERAGE_WAIT by individual sessions.
Significant db file sequential read wait time is most likely an application issue.
Common Causes, Diagnosis, and Actions
The db file sequential read wait event is initiated by SQL statements (both user and recursive) that perform single-block read operations against indexes, rollback (or undo) segments, and tables (when accessed via rowid), control files and data file headers. This wait event normally appears as one of the top five wait events, according to systemwide waits.
Physical I/O requests for these objects are perfectly normal, so the presence of the db file sequential read waits in the database does not necessarily mean that there is something wrong with the database or the application. It may not even be a bad thing if a session spends a lot of time on this event. In contrast, it is definitely bad if a session spends a lot of time on events like enqueue or latch free. This is where this single-block read subject becomes complicated. At what point does the db file sequential read event become an issue? How do you define excessive? Where do you draw the line? These are tough questions, and there is no industry standard guideline. You should establish a guideline for your environment. For example, you may consider it excessive when the db file sequential read wait represents a large portion of a process response time. Another way is to simply adopt the nonscientific hillbilly approach—that is, wait till the users start screaming.
You can easily discover which session has high TIME_WAITED on the db file sequential read wait event from the V$SESSION_EVENT view. The TIME_WAITED must be evaluated with the LOGON_TIME and compared with other nonidle events that belong to the session for a more accurate analysis. Sessions that have logged on for some time (days or weeks) may accumulate a good amount of time on the db file sequential read event. In this case, a high TIME_WAITED may not be an issue. Also, when the TIME_WAITED is put in perspective with other nonidle events, it prevents you from being blindsided. You may find another wait event which is of a greater significance. Based on the following example, SID# 192 deserves your attention and should be investigated:
当进程需要的信息不在SGA，要等从磁盘读入SGA中，此时进程等待此事件。
一般是由sql或者递归sql中发出，从索引，回滚段，表（rowid回表），控制文件，数据文件头处读取信息。
select a.sid, a.event, a.time_waited, a.time_waited / c.sum_time_waited * 100 pct_wait_time, round((sysdate - b.logon_time) * 24) hours_connectedfrom v$session_event a, v$session b, (select sid, sum(time_waited) sum_time_waited from v$session_event where event not in ( 'Null event', 'client message', 'KXFX: Execution Message Dequeue - Slave', 'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 'PX Deq: Table Q Normal', 'Wait for credit - send blocked', 'PX Deq Credit: send blkd', 'Wait for credit - need buffer to send', 'PX Deq Credit: need buffer', 'Wait for credit - free buffer', 'PX Deq Credit: free buffer', 'parallel query dequeue wait', 'PX Deque wait', 'Parallel Query Idle Wait - Slaves', 'PX Idle Wait', 'slave wait', 'dispatcher timer', 'virtual circuit status', 'pipe get', 'rdbms ipc message', 'rdbms ipc reply', 'pmon timer', 'smon timer', 'PL/SQL lock timer', 'SQL*Net message from client', 'WMON goes to sleep') having sum(time_waited) > 0 group by sid) cwhere a.sid = b.sidand a.sid = c.sidand a.time_waited > 0and a.event = 'db file sequential read'order by hours_connected desc, pct_wait_time; SID EVENT TIME_WAITED PCT_WAIT_TIME HOURS_CONNECTED---- ----------------------- ----------- ------------- --------------- 186 db file sequential read 64446 77.0267848 105 284 db file sequential read 1458405 90.992838 105 194 db file sequential read 1458708 91.0204316 105 322 db file sequentia<mark>本文来源gaodaimacom搞#代%码@网-</mark>l read 1462557 91.1577045 105 139 db file sequential read 211325 52.6281055 11 256 db file sequential read 247236 58.0469755 11?<strong>192 db file sequential read 243113 88.0193625 2</strong>
转载请注明原文链接：dbfilesequentialread等待事件总结
