db file sequential read
一：db file sequential read说明
二：db file sequential read解决思路
三：db file sequential read重现过程
四：db file sequential read官方文档
一：db file sequential read说明
db file sequential read事件和Single Block I/O有关。
主要从index,rollback(or undo) segments, tables(通过rowid访问表),control files 和data file headers中进行single-block read.
sequential read是将数据读到连续的内存(这里指的是读到相连的内存，不是说读取的是连续的数据块)。
https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#REFRN00533
The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.
https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94485
This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.
http://www.askmaclean.com/archives/db-file-sequential-read-wait-event.html
可能出现的场景：
(1) INDEX FULL SCAN/UNIQUE SCAN
执行计划中包含了INDEX FULL SCAN/UNIQUE SCAN，可能会出现db file sequential read等待；
(2) INDEX RANGE SCAN
当执行计划包含了INDEX RANGE SCAN-(“TABLE ACCESS BY INDEX ROWID”/”DELETE”/”UPDATE”)， 服务进程将按照”访问索引->找到rowid->访问rowid指定的表数据块并执行必要的操作”顺序访问index和table，每次物理 读取都会进入”db file sequential read”等待，且每次读取的都是一个数据块；
(3) Extent boundary
假设一个Extent区间中有33个数据块，而一次”db file scattered read”多块读所读取的块数为8，那么在读取这个区间时经过4次多块读取后，还剩下一个数据块，但是请记住多块读scattered read是不能跨越一个区间的(span an extent)，此时就会单块读取并出现”db file sequential read”。这是一种正常现象，一般不需要额外关注。
(4) 同一Extent中某个block不在内存中
假设某个区间内有8个数据块，它们可以是块a,b,c,d,e,f,g,h，恰好当前系统中除了d块外的其他数据块都已经被缓存在buffer cache中了，而这时候恰好要访问这个区间中的数据，那么此时就会单块读取d这个数据块，并出现”db file sequential read”等待。注意这种情况不仅于表，也可能发生在索引上。这是一种正常现象，一般不需要额外关注。
(5)chained/migrated rows
即链式或迁移行，这里我们不介绍链式行的形成原因，chained/migrated rows会造成服务进程在fetch一行记录时需要额外地单块读取，从而出现”db file sequential read”。这种现象需要我们特别去关注，因为大量的链式/迁移行将导致如FULL SCAN等操作极度恶化(以往的经验是一张本来全表扫描只需要30分钟的表，在出现大量链式行后，全表扫描需要数个小时)，同时也会对其他操作造成不那么 明显的性能影响。可以通过监控v$sysstat视图中的”table fetch continued row”操作统计来了解系统中链式/迁移行访问的情况，还可以通过DBA_TBALES视图中的CHAIN_CNT来了解表上的链式/迁移行情况，当然这 要求定期收集表上的统计信息；如果没有定期收集的习惯，那么可以配合@?/rdbms/admin/utlchain脚本和analyze table list chained rows 命令来获取必要的链式行信息。
(6) 创建Index entry
显然当对表上执行INSERT操作插入数据时，虽然在执行计划中你看不到过多的细节，但实际上我们需要利用索引来快速验证表上的某些约束是否合理，还需要在索引的叶子块中插入相关的记录，此时也可能出现”db file sequential read”等待事件，当然这还和具体的插入的方式有关系。这是一种正常现象，一般不需要额外关注。
(7)UPDATE/DELETE
针对表上的UPDATE/DELETE，不同于之前提到的”INDEX RANGE SCAN-UPDATE/DELETE”，如果我们使用rowid去更新或删除数据时，服务进程会先访问rowid指向的表块(注意是先访问table block)上的行数据，之后会根据该行上的具体数据去访问索引叶子块(注意Oracle并不知道这些leaf block在哪里，所以这里同样要如range-scan/unique-scan那样去访问index branch block)，这些访问都将会是单块读取，并会出现’db file sequential read’，完成必要的读取后才会执行更新或删除的实际EXEC操作。
二：db file sequential read解决思路
1 如果是某些SQL引起的，例如统计信息不准确，使用低效的索引等，可以通过优化SQL，降低db file sequential read；
2 如果db file sequential read出现特别频繁，Buffer HIT较低，可以考虑增大buffer cache；
3 使用更快的存储；
select name, parameter1, parameter2, parameter3
from v$event_name
where name = 'db file sequential read';
Check the following V$SESSION_WAIT parameter columns:
P1: The absolute file number
P2: The block being read
P3: The number of blocks (should be 1)
---查看含有db file sequential read等待事件的session；
SELECT sid, total_waits, time_waited
FROM v$session_event
WHERE event = 'db file sequential read'
and total_waits > 0
ORDER BY 2 desc;
select sid, event, p1, p2, p3, wait_class
from v$session_wait
where event = 'db file sequential read';
----SEGMENT_TYPE一般都是INDEX,如果是TABLE，可能和行迁移，行链接有关
Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows) [ID 554366.1]
select owner, segment_name, segment_type
from dba_extents
where file_id = 6
and 37475 between block_id and block_id + blocks - 1;
select v.last_call_et,
v.username,
v.sid,
sql.sql_text,
sql.sql_id,
sql.disk_reads,
v.event
from v$session v, v$sql sql
where v.sql_address = sql.address
and v.last_call_et > 0
and v.status = 'ACTIVE'
and v.event = 'db file sequential read'
and v.username is not null;
select * from table(dbms_xplan.display_cursor('dgg3j14mah83z'));
三：db file sequential read重现过程
场景1：INDEX RANGE SCAN
SQL> create table t2 as select * from dba_objects;
SQL> insert into t2 select * from t2;commit;
SQL> insert into t2 select * from t2;commit;
SQL> insert into t2 select * from t2;commit;
......
SQL> select count(*) from t2; ---2761536
SQL> create index i_t2_status on t2(status);
SQL>
begin
dbms_workload_repository.create_snapshot();
end;
SQL> alter system flush buffer_cache;
SQL> alter session set tracefile_identifier='10046';
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> select /*+ index(t2 i_t2_status) */
*
from t2
where object_id = '20' and status='VALID';
SQL> alter session set events '10046 trace name context off';
SQL>
begin
dbms_workload_repository.create_snapshot();
end;
SQL>
select distinct (m.sid), p.pid, p.tracefile
from v$mystat m, v$session s, v$process p
where m.sid = s.sid
and s.paddr = p.addr;
---/u01/app/oracle/diag/rdbms/ncdb/ncdb1/trace/ncdb1_ora_14362_10046.trc
10046
[oracle@rac1 ~]$
tkprof /u01/app/oracle/diag/rdbms/ncdb/ncdb1/trace/ncdb1_ora_14362_10046.trc 10046.trc sys=no
AWR
场景2：UNIQUE SCAN
SQL> create table t1 as select * from dba_objects;
SQL> select * from t1;
SQL> select rowid,a.* from t1 a;
SQL> create unique index uni_t1_id on t1(object_id);
SQL> alter system flush buffer_cache;
---alter system flush shared_pool;
SQL> alter session set tracefile_identifier='10046';
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> select object_id from t1 where object_id=20;
SQL> alter session set events '10046 trace name context off';
SQL> select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;
/u01/app/oracle/diag/rdbms/ncdb/ncdb1/trace/ncdb1_ora_20149_10046.trc
场景3：INDEX FULL SCAN
SQL> alter system flush buffer_cache;
---alter system flush shared_pool;
SQL> alter session set tracefile_identifier='10046';
SQL> alter session set events '10046 trace name context forever, level 12';
SQL>
select /*+ index_ffs(t1 uni_t1_id) */
object_id
from t1
where object_id = 20;
SQL> alter session set events '10046 trace name context off';
SQL> select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;
四：db file sequential read官方文档
https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#REFRN00533
db file sequential read
The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.
Wait Time: The wait time is the actual time it takes to do the I/O
https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94485
10.3.3 db file sequential read
This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.
Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call because of extent boundaries, or buffers present in the buffer cache. These waits would also show up as db file sequential read.
Check the following V$SESSION_WAIT parameter columns:
P1: The absolute file number
P2: The block being read
P3: The number of blocks (should be 1)
See Also:
"db file scattered read" for information about managing excessive I/O, inadequate I/O distribution, and finding the SQL causing the I/O and the segment the I/O is performed on
10.3.3.1 Actions
On a healthy system, physical read waits should be the biggest waits after the idle waits. However, also consider whether there are db filesequential reads on a large data warehouse that should be seeing mostly full table scans with parallel query.
Figure 10-1 depicts the differences between the following wait events:
db file sequential read (single block read into one SGA buffer)
db file scattered read (multiblock read into many discontinuous SGA buffers)
direct read (single or multiblock read into the PGA, bypassing the SGA)
Figure 10-1 Scattered Read, Sequential Read, and Direct Path Read
https://docs.oracle.com/cd/E11882_01/server.112/e10803/config_dg.htm#HABPT4883
Table 9-4 Parallel Recovery Slave Wait Events
| WAITEVENT: "db file sequential read" Reference Note (文档 ID 34559.1) |
|
|
|
| ***Checked for relevance on 06-APR-2012*** "db file sequential read" Reference Note This is a reference note for the wait event "db file sequential read" which includes the following subsections: · Individual wait details (eg: For waits seen in V$SESSION_WAIT) · Systemwide wait details (eg: For waits seen in V$SYSTEM_EVENT) See Note:61998.1 for an introduction to Wait Events. · Versions:7.0 - 12.1 · This signifies a wait for an I/O read request to complete. This call differs from "db file scattered read" in that a sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA). A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read) . · Parameters: P1 = file# P2 = block# P3 = blocks file# This is the file# of the file that Oracle is trying to read from. From Oracle8 onwards it is the ABSOLUTE file number (AFN). block# This is the starting block number in the file from where Oracle starts reading the blocks. Typically only one block is being read.
See Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair. blocks This parameter specifies the number of blocks that Oracle is trying to read from the file# starting at block#. This is usually "1" but if P3 > 1 then this is a multiblock read. Multiblock "db file sequential read"s may be seen in earlier Oracle versions when reading from a SORT (TEMPORARY) segments.
Wait Time: The IO is generally issued as a single IO request to the OS - the wait blocks until the IO request completes. Note than an Oracle read request to the OS may be satisfied from an OS file system cache so the wait time may be very small.
IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO", and "File IO" sections of the AWR (or STATSPACK) reports, along with ADDM and ASH output, to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at V$FILESTAT. See the "Top SQL by Disk Reads" sections of AWR reports for clues about any SQL causing high I/O. It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing: SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits>0 ORDER BY 3,2 ; One can also look at: · Statements with high DISK_READS in V$SQL - shown in the "Top SQL by Disk Reads" section of AWR. · Sessions with high "physical reads" in V$SESSTAT Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance. Tweaking at system level usually only achieves percentage gains. The following points may help: · Check for SQL using unselective index scans · A larger buffer cache can help - test this by actually increasing <<Parameter:DB_CACHE_SIZE>> (or <<Parameter:DB_BLOCK_BUFFERS>> if still using that). Never increase the SGA size if it may induce additional paging or swapping on the system. . · A less obvious issue which can affect the IO rates is how well data is clustered physically. Eg: Assume that you frequently fetch rows from a table where a column is between two values via an index scan. If there are 100 rows in each index block then the two extremes are: 1. Each of the table rows is in a different physical block (100 blocks need to be read for each index block) 2. The table rows are all located in the few adjacent blocks (a handful of blocks need to be read for each index block) Pre-sorting or re-organising data can help to tackle this in severe situations. · See if partitioning can be used to reduce the amount of data you need to look at. · It can help to place files which incur frequent index scans on disks which have are buffered by a cache of some form. eg: flash cache or hardware disk cache. For non-ASM based databases put such datafiles on a filesystem with an O/S file system cache. This can allow some of Oracles read requests to be satisfied from the cache rather than from a real disk IO. Troubleshooting See the following documents for help troubleshooting issues relating to "db file sequential read" waits: Document:1475825.1 Resolving Issues Where Application Queries are Waiting Too Frequently for 'db file sequential read' Operations Known Issues / Bugs: You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
· '*' indicates that an alert exists for that issue. · '+' indicates a particularly notable issue / bug. · See Note:1944526.1 for details of other symbols used |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2155195/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-2155195/
773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
