读相关等待事件——单块读、多块读、直接路径读
单块读
相关概念
db file sequential read表示会话在等待IO读响应完成。该事件也会在重建控制文件，dump数据文件头，读取数据文件头（p2=1）时出现。
db file sequential read通常是single-block read，但是也可以从P3中查看是否读取了多个数据块，这种情况只在较早版本的oracle中出现，读取的是临时表空间的排序段。
db file sequential read从磁盘中读取一个块到SGA的一个buffer中。
发生场景：
- 通常在使用索引时
- 在需要单块读时（全表扫描时，表中的块绝大部分都在buffer中）
计量测试
SQL> create table test(col1 number);Table created.SQL> insert into test values(1);1 row created.
SQL> alter system flush buffer_cache2 /System altered.SQL> oradebug setmypid;
Statement processed.
SQL> Oradebug unlimit
Statement processed.--查询初始计量值
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event 2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');
EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
-------------------------- ----------- ----------- -----------------
db file sequential read 42 12 120100
db file scattered read 21 21 213947
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event 2 where EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
-------------------------- ----------- ----------- -----------------
db file sequential read 222146 23742 237421261
db file scattered read 8279 2691 26912209
direct path read 44 6 64325;
--开启10046
SQL> Oradebug event 10046 trace name context forever,level 8;
Statement processed.
--查询小表
SQL> select * from test;COL1
----------1
--关闭10046
SQL> Oradebug event 10046 trace name context off;SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event 2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
-------------------------------------- ----------- -----------------
db file sequential read 47 12 124665
db file scattered read 21 21 213947
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event 2 where EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------------------ ----------- -----------------
db file sequential read 222153 23743 237430783
db file scattered read 8279 2691 26912209
direct path read 44 6 64325
--tracefile path
SQL> Oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/ngjkdb1/ngjkdb11/trace/ngjkdb11_ora_88464.trc
从上面的测试可用看出，在查询小表前后db file sequential read增加了5次，db file scattered read没有增加。在trace中可以找到5次关于db file sequential read的等待，没有db file scattered read的等待
--继续插入数据
SQL> insert into test select * from test;1 row created....
SQL> /1048576 rows created.SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event 2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------------------ ----------- -----------------
db file sequential read 105 31 309751
db file scattered read 17 8 78877
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event 2 where EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------ ----------- ----------- -----------------
db file sequential read 226701 24553 245525814
db file scattered read 8305 2702 27023315
direct path read 44 6 64325SQL> select count(*) from test;COUNT(*)
----------2097152SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event 2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------- ----------- ----------- -----------------
db file sequential read 132 34 338516
db file scattered read 146 32 322342
SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event 2 where EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------------------ ----------- -----------------
db file sequential read 226743 24557 245569479
db file scattered read 8434 2727 27266780
direct path read 44 6 64325
直接路径读
相关概念
direct path read从磁盘中读取一个或多个块直接到PGA中，不通过SGA。
发生场景：
11g后引入serial table scan ，全表扫描至少超过5倍的_small_table_threshold值
计量测试
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.indx = b.indx 4 and a.ksppinm='_serial_direct_read'5
SQL> /NAME VALUE DESCRIPTION
---------
_serial_direct_read auto enable direct read in serial
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.indx = b.indx 4 and a.ksppinm like '%small_table%' ;
NAME VALUE DESCRIPTION
---------------------- ---------- ----------------------------------------
_small_table_threshold 7679 lower threshold level of table size fordirect reads
_small_table_threshold为7679，也就是说我们创建的表占用的块数必须大于7679个。因为有块头和水位线的关系，我们就粗略的算一个块占8k，那么我们的表应该大于7679*8/1024= 59.9921875mb。如果我们建立一个表的一条记录占用1k，那么就需要至少60k行。
创建一张大表，该表有3300w+数据
SQL> create table tbig(a char(1024 byte));Table created.SQL> insert into tbig values('a');1 row created.SQL> insert into tbig select * from tbig;1 row created.
SQL> /65536 rows created.SQL> alter system flush buffer_cache2
SQL> /System altered.SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event 2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
----------------------------------- ----------- -----------------
db file sequential read 272 133 1328785SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event 2 where EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
----------------------------------- ----------- -----------------
db file sequential read 359234531 100436020 1.0044E+12
db file scattered read 63025426 5089881 5.0899E+10
direct path read 416564 341760 3417600930SQL> select * from (select * from tbig order by 1)where rownum<=10;
...SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$session_event 2 where sid=(select sid from v$mystat where rownum<=1) and EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------------------ ----------- -----------------
db file sequential read 309 133 1334823
db file scattered read 28 0 797
direct path read 148 12 122384SQL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO from v$system_event 2 where EVENT in ('db file sequential read','db file scattered read','direct path read');EVENT TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
----------------------------------- ----------- -----------------
db file sequential read 359251382 100439191 1.0044E+12
db file scattered read 63025485 5089885 5.0899E+10
direct path read 416712 341772 3417723314
session的direct path read从无到有增加了148次，system的direct path read增加了416712-416564=148次。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！