dbfilesequentialread等待事件总结 - 数据库编程

eq 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) c where a.sid = b.sid and a.sid = c.sid and a.time_waited > 0 and 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 sequential read 1462557 91.1577045 105 139 db file sequential read 211325 52.6281055 11 256 db file sequential read 247236 58.0469755 11 192 db file sequential read 243113 88.0193625 2

There are two things you can do to minimize the db file sequential read waits:

Optimize the SQL statement that initiated most of the waits by reducing the number of physical and logical reads.

Reduce the average wait time.

Unless you trace a session with the event 10046 or have a continuously running wait event data collector as discussed in Chapter 4, it is difficult to determine the SQL statement that is responsible for the cumulated wait time. Take the preceding SID #192 again, for example. The 243113 centiseconds wait time may be caused by one long-running or many fast SQL statements. The latter case may not be an issue. Furthermore, the SQL statement that is currently running may or may not be the one that is responsible for the waits. This is why interactive diagnosis without historical data is often unproductive. You can query the V$SQL view for statements with high average DISK_READS, but then how can you tell they belong to the session Due to these limitations, you may have to identify and trace the session the next time around to nail down the offending SQL statement. Once you have found it, the optimization goal is to reduce the amount of physical and logical reads.

Note

In addition to the DISK_READS column, the V$SQL and V$SQLAREA views in Oracle Database 10g have exciting new columns: USER_IO_WAIT_TIME, DIRECT_WRITES, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, PLSQL_EXEC_TIME, and JAVA_EXEC_TIME. You can discover the SQL statement with the highest cumulative or average USER_IO_WAIT_TIME.

Another thing you can do to minimize the impact of the db file sequential read event is reduce the AVERAGE_WAIT time. This is the average time a session has to wait for a single block fetch from disk; the information is available in the V$SESSION_EVENT view. In newer storage subsystems, an average single-block read shouldn’t take more than 10ms (milliseconds) or 1cs (centisecond). You should expect an average wait time of 4 to 8ms (0.4 to 0.8cs) with SAN (storage area network) due to large caches. The higher the average wait time, the costlier it is to perform a single-block read, and the overall process response time will suffer. On the other hand, a lower average wait time is more forgiving and has a lesser impact on the response times of processes that perform a lot of single-block reads. (We are not encouraging you to improve the average wait time to avoid SQL optimization. If the application has SQL statements that perform excessive amounts of

dbfilesequentialread等待事件总结(二)