本文介绍了Oracle数据库中的两种关键I/O读取事件：dbfilesequentialread和dbfilescatteredread。这两种事件分别代表了连续读和分散读操作，并详细解释了它们在实际应用中的含义及如何通过查询来获取这两种读取方式的平均等待时间。
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache or user PGA memory.
db file sequential read:
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.
db file scattered read:
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads. However, scattered read will never read just one block (p3 is always >= 2).
The following query shows average wait time for sequential versus
scattered reads:
SQL> tti "AVERAGE WAIT TIME FOR READ REQUESTS" SQL> SQL> SELECT a.average_wait "SEQ READ", b.average_wait "SCAT READ" 2 FROM sys.v_$system_event a, sys.v_$system_event b 3 WHERE a.event = 'db file sequential read' 4 AND b.event = 'db file scattered read'; SEQ READ SCAT READ ---------- ---------- .74 1.6
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
