读相关等待事件——单块读、多块读、直接路径读

2023-10-10 22:10
文章标签 路径 事件 等待 相关 直接 单块 多块

本文主要是介绍读相关等待事件——单块读、多块读、直接路径读，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

单块读

相关概念

 

db file sequential read表示会话在等待IO读响应完成。该事件也会在重建控制文件，dump数据文件头，读取数据文件头（p2=1）时出现。

db file sequential read通常是single-block read，但是也可以从P3中查看是否读取了多个数据块，这种情况只在较早版本的oracle中出现，读取的是临时表空间的排序段。

db file sequential read从磁盘中读取一个块到SGA的一个buffer中。

发生场景：

  1. 通常在使用索引时
  2. 在需要单块读时（全表扫描时，表中的块绝大部分都在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的等待

[oracle@xx31 trace]$   cat /oracle/app/oracle/diag/rdbms/test/test1/trace/test1_ora_88464.trc|grep "db file sequential read"|wc -l
5
[oracle@xx31 trace]$  cat /oracle/app/oracle/diag/rdbms/test/test1/trace/test1_ora_88464.trc|grep "db file scattered read"|wc -l
0
trace：
WAIT #0: nam='SQL*Net message from client' ela= 10518442 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1534129966861302
WAIT #140506785910600: nam='gc cr disk read' ela= 394 p1=1 p2=81040 p3=4 obj#=93881 tim=1534129966864599
WAIT #140506785910600: nam='Disk file operations I/O' ela= 560 FileOperation=2 fileno=0 filetype=15 obj#=93881 tim=1534129966865438
WAIT #140506785910600: nam='db file sequential read' ela= 1026 file#=1 block#=81040 blocks=1 obj#=93881 tim=1534129966866541
=====================
PARSING IN CURSOR #140506785960544 len=337 dep=1 uid=0 oct=3 lid=0 tim=1534129966869134 hv=3345277572 ad='327e774780' sqlid='baj7tjm3q9sn4'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2B
END OF STMT
PARSE #140506785960544:c=2364,e=2355,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1950795681,tim=1534129966869129
EXEC #140506785960544:c=62,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1534129966869258
WAIT #140506785960544: nam='gc cr grant 2-way' ela= 266 p1=1 p2=81040 p3=4 obj#=93881 tim=1534129966869767
WAIT #140506785960544: nam='db file sequential read' ela= 694 file#=1 block#=81040 blocks=1 obj#=93881 tim=1534129966870539
WAIT #140506785960544: nam='gc cr grant 2-way' ela= 230 p1=1 p2=81041 p3=1 obj#=93881 tim=1534129966870972
WAIT #140506785960544: nam='db file sequential read' ela= 779 file#=1 block#=81041 blocks=1 obj#=93881 tim=1534129966871818
...
PARSING IN CURSOR #140506783380160 len=210 dep=1 uid=0 oct=3 lid=0 tim=1534129966874871 hv=864012087 ad='329ea774f0' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and i2
END OF STMT
PARSE #140506783380160:c=1460,e=1460,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=0,tim=1534129966874870
EXEC #140506783380160:c=1043,e=1043,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,plh=2239883476,tim=1534129966876002
WAIT #140506783380160: nam='db file sequential read' ela= 1003 file#=1 block#=3025 blocks=1 obj#=450 tim=1534129966877212
WAIT #140506783380160: nam='gc cr grant 2-way' ela= 275 p1=1 p2=81285 p3=1 obj#=450 tim=1534129966877685
WAIT #140506783380160: nam='db file sequential read' ela= 1063 file#=1 block#=81285 blocks=1 obj#=450 tim=1534129966878800
FETCH #140506783380160:c=0,e=2814,p=2,cr=2,cu=0,mis=0,r=0,dep=1,og=3,plh=2239883476,tim=1534129966878859
STAT #140506783380160 id=1 cnt=0 pid=0 pos=1 obj=448 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=2 pw=0 time=2824 us)'
STAT #140506783380160 id=2 cnt=0 pid=1 pos=1 obj=450 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=2 pw=0 time=2819 us)'
上面的trace信息可分为两个部分，第一次解析时有3次单块读，前两次读的同一个块81040，后一次读的块为81041，这两个块属于同一个对象test（93881）。
SQL> select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_ID=93881 ;OWNER    OBJECT_NAME         OBJECT_ID OBJECT_TYPE
-------- ------------------ ---------- -------------------
SYS      TEST                    93881 TABLE
第二次解析时有2次单块读，两次单块读的块是3025和81285，对象均是I_HH_OBJ#_INTCOL#（450），是一个索引，该索引在表HIST_HEAD$上，从字段上看该表跟统计信息有关。
SQL>  select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_ID=450;OWNER     OBJECT_NAME        OBJECT_ID OBJECT_TYPE
--------- ---------------------------- -------------------
SYS       I_HH_OBJ#_INTCOL#        450 INDEX
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLE_TYPE from dba_indexes where index_name='I_HH_OBJ#_INTCOL#';OWNER   INDEX_NAME         TABLE_OWNER   TABLE_NAME  TABLE_TYPE
------- ------------------ ------------- -----------------------
SYS     I_HH_OBJ#_INTCOL#  SYS           HIST_HEAD$  TABLE

多块读

相关概念

多块读与单块读很相似，但是多块读表示会话在读取多个数据块。

 db file scattered read从磁盘中读取多个块到SGA中不连续的buffer中。The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache,the blocks read are read intomemory locations that are not physically adjacent to each other.scattered read必须是从磁盘读取多个块到buffer cache中，且buffer不连续，块必须是scattered（零散地）分布在内存中。一个full scan的上限就是DB_FILE_MULTIBLOCK_READ_COUNT，默认值为128。

 

发生场景：

  1. 全表扫描
  2. INDEX FAST FULL SCAN

计量测试： 

--继续插入数据
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次。

 

 

 

 

这篇关于读相关等待事件——单块读、多块读、直接路径读的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/183422。

相关文章

禁止平板,iPad长按弹出默认菜单事件

禁止平板,iPad长按弹出默认菜单事件

通过监控按下抬起时间差来禁止弹出事件，把以下代码写在要禁止的页面的页面加载事件里面即可     var date;document.addEventListener('touchstart', event => {date = new Date().getTime();});document.addEventListener('touchend', event => {if (new
阅读更多...
hdu2544（单源最短路径）

hdu2544（单源最短路径）

模板题： //题意：求1到n的最短路径，模板题#include<iostream>#include<algorithm>#include<cstring>#include<stack>#include<queue>#include<set>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#i
阅读更多...
sqlite3 相关知识

sqlite3 相关知识

WAL 模式 VS 回滚模式 特性WAL 模式回滚模式（Rollback Journal）定义使用写前日志来记录变更。使用回滚日志来记录事务的所有修改。特点更高的并发性和性能；支持多读者和单写者。支持安全的事务回滚，但并发性较低。性能写入性能更好，尤其是读多写少的场景。写操作会造成较大的性能开销，尤其是在事务开始时。写入流程数据首先写入 WAL 文件，然后才从 WAL 刷新到主数据库。数据在开始
阅读更多...
poj 1734 （floyd求最小环并打印路径）

poj 1734 （floyd求最小环并打印路径）

题意： 求图中的一个最小环，并打印路径。 解析： ans 保存最小环长度。 一直wa，最后终于找到原因，inf开太大爆掉了。。。 虽然0x3f3f3f3f用memset好用，但是还是有局限性。 代码： #include <iostream>#include <cstdio>#include <cstdlib>#include <algorithm>#incl
阅读更多...
两个月冲刺软考——访问位与修改位的题型(淘汰哪一页)；内聚的类型；关于码制的知识点；地址映射的相关内容

两个月冲刺软考——访问位与修改位的题型(淘汰哪一页)；内聚的类型；关于码制的知识点；地址映射的相关内容

1.访问位与修改位的题型(淘汰哪一页) 访问位：为1时表示在内存期间被访问过，为0时表示未被访问；修改位：为1时表示该页面自从被装入内存后被修改过，为0时表示未修改过。 置换页面时，最先置换访问位和修改位为00的，其次是01(没被访问但被修改过)的，之后是10(被访问了但没被修改过)，最后是11。 2.内聚的类型 功能内聚：完成一个单一功能，各个部分协同工作，缺一不可。 顺序内聚：
阅读更多...
log4j2相关配置说明以及${sys:catalina.home}应用

log4j2相关配置说明以及${sys:catalina.home}应用

${sys:catalina.home} 等价于 System.getProperty("catalina.home") 就是Tomcat的根目录：  C:\apache-tomcat-7.0.77 <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p %c{1}:%L - %msg%n" /> 2017-08-10
阅读更多...
Node Linux相关安装

Node Linux相关安装

下载经编译好的文件cd /optwget https://nodejs.org/dist/v10.15.3/node-v10.15.3-linux-x64.tar.gztar -xvf node-v10.15.3-linux-x64.tar.gzln -s /opt/node-v10.15.3-linux-x64/bin/npm /usr/local/bin/ln -s /opt/nod
阅读更多...
git ssh key相关

git ssh key相关

step1、进入.ssh文件夹   (windows下 下载git客户端)   cd ~/.ssh(windows mkdir ~/.ssh) step2、配置name和email git config --global user.name "你的名称"git config --global user.email "你的邮箱" step3、生成key ssh-keygen
阅读更多...
【408DS算法题】039进阶-判断图中路径是否存在

【408DS算法题】039进阶-判断图中路径是否存在

Index 题目分析实现总结 题目 对于给定的图G，设计函数实现判断G中是否含有从start结点到stop结点的路径。 分析实现 对于图的路径的存在性判断，有两种做法：（本文的实现均基于邻接矩阵存储方式的图） 1.图的BFS BFS的思路相对比较直观——从起始结点出发进行层次遍历，遍历过程中遇到结点i就表示存在路径start->i，故只需判断每个结点i是否就是stop
阅读更多...
Android Environment 获取的路径问题

Android Environment 获取的路径问题

1. 以获取 /System 路径为例 /*** Return root of the "system" partition holding the core Android OS.* Always present and mounted read-only.*/public static @NonNull File getRootDirectory() {return DIR_ANDR
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2