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

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。

相关文章

SpringBoot项目配置logback-spring.xml屏蔽特定路径的日志

SpringBoot项目配置logback-spring.xml屏蔽特定路径的日志

《SpringBoot项目配置logback-spring.xml屏蔽特定路径的日志》在SpringBoot项目中,使用logback-spring.xml配置屏蔽特定路径的日志有两种常用方式,文中的... 目录方案一：基础配置（直接关闭目标路径日志）方案二：结合 Spring Profile 按环境屏蔽关
阅读更多...
CSS3中的字体及相关属性详解

CSS3中的字体及相关属性详解

《CSS3中的字体及相关属性详解》：本文主要介绍了CSS3中的字体及相关属性，详细内容请阅读本文，希望能对你有所帮助... 字体网页字体的三个来源：用户机器上安装的字体，放心使用。保存在第三方网站上的字体，例如Typekit和Google,可以link标签链接到你的页面上。保存在你自己Web服务器上的字
阅读更多...
VSCode设置python SDK路径的实现步骤

VSCode设置python SDK路径的实现步骤

《VSCode设置pythonSDK路径的实现步骤》本文主要介绍了VSCode设置pythonSDK路径的实现步骤,包括命令面板切换、settings.json配置、环境变量及虚拟环境处理,具有一定... 目录一、通过命令面板快速切换（推荐方法）二、通过 settings.json 配置（项目级/全局）三、
阅读更多...
使用Python和Matplotlib实现可视化字体轮廓(从路径数据到矢量图形)

使用Python和Matplotlib实现可视化字体轮廓(从路径数据到矢量图形)

《使用Python和Matplotlib实现可视化字体轮廓(从路径数据到矢量图形)》字体设计和矢量图形处理是编程中一个有趣且实用的领域,通过Python的matplotlib库,我们可以轻松将字体轮廓... 目录背景知识字体轮廓的表示实现步骤1. 安装依赖库2. 准备数据3. 解析路径指令4. 绘制图形关键
阅读更多...
解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》：本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock
阅读更多...
如何更改pycharm缓存路径和虚拟内存分页文件位置(c盘爆红)

如何更改pycharm缓存路径和虚拟内存分页文件位置(c盘爆红)

《如何更改pycharm缓存路径和虚拟内存分页文件位置(c盘爆红)》：本文主要介绍如何更改pycharm缓存路径和虚拟内存分页文件位置(c盘爆红)问题,具有很好的参考价值,希望对大家有所帮助,如有... 目录先在你打算存放的地方建四个文件夹更改这四个路径就可以修改默认虚拟内存分页js文件的位置接下来从高级-
阅读更多...
Java 的 Condition 接口与等待通知机制详解

Java 的 Condition 接口与等待通知机制详解

《Java的Condition接口与等待通知机制详解》在Java并发编程里,实现线程间的协作与同步是极为关键的任务,本文将深入探究Condition接口及其背后的等待通知机制,感兴趣的朋友一起看... 目录一、引言二、Condition 接口概述2.1 基本概念2.2 与 Object 类等待通知方法的区别
阅读更多...
一文详解如何查看本地MySQL的安装路径

一文详解如何查看本地MySQL的安装路径

《一文详解如何查看本地MySQL的安装路径》本地安装MySQL对于初学者或者开发人员来说是一项基础技能,但在安装过程中可能会遇到各种问题,：本文主要介绍如何查看本地MySQL安装路径的相关资料,需... 目录1. 如何查看本地mysql的安装路径1.1. 方法1：通过查询本地服务1.2. 方法2：通过MyS
阅读更多...
解决tomcat启动时报Junit相关错误java.lang.ClassNotFoundException: org.junit.Test问题

解决tomcat启动时报Junit相关错误java.lang.ClassNotFoundException: org.junit.Test问题

《解决tomcat启动时报Junit相关错误java.lang.ClassNotFoundException:org.junit.Test问题》：本文主要介绍解决tomcat启动时报Junit相... 目录tomcat启动时报Junit相关错误Java.lang.ClassNotFoundException
阅读更多...
Python如何调用指定路径的模块

Python如何调用指定路径的模块

《Python如何调用指定路径的模块》要在Python中调用指定路径的模块,可以使用sys.path.append,importlib.util.spec_from_file_location和exe... 目录一、sys.path.append() 方法1. 方法简介2. 使用示例3. 注意事项二、imp
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2