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

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。

相关文章

Python的time模块一些常用功能(各种与时间相关的函数)

Python的time模块一些常用功能(各种与时间相关的函数)

《Python的time模块一些常用功能(各种与时间相关的函数)》Python的time模块提供了各种与时间相关的函数,包括获取当前时间、处理时间间隔、执行时间测量等,：本文主要介绍Python的... 目录1. 获取当前时间2. 时间格式化3. 延时执行4. 时间戳运算5. 计算代码执行时间6. 转换为指
阅读更多...
C#如何动态创建Label,及动态label事件

C#如何动态创建Label,及动态label事件

《C#如何动态创建Label,及动态label事件》：本文主要介绍C#如何动态创建Label,及动态label事件,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录C#如何动态创建Label,及动态label事件第一点：switch中的生成我们的label事件接着，
阅读更多...
JavaScript Array.from及其相关用法详解(示例演示)

JavaScript Array.from及其相关用法详解(示例演示)

《JavaScriptArray.from及其相关用法详解(示例演示)》Array.from方法是ES6引入的一个静态方法,用于从类数组对象或可迭代对象创建一个新的数组实例,本文将详细介绍Array... 目录一、Array.from 方法概述1. 方法介绍2. 示例演示二、结合实际场景的使用1. 初始化二
阅读更多...
Linux修改pip和conda缓存路径的几种方法

Linux修改pip和conda缓存路径的几种方法

《Linux修改pip和conda缓存路径的几种方法》在Python生态中,pip和conda是两种常见的软件包管理工具,它们在安装、更新和卸载软件包时都会使用缓存来提高效率,适当地修改它们的缓存路径... 目录一、pip 和 conda 的缓存机制1. pip 的缓存机制默认缓存路径2. conda 的缓
阅读更多...
Windows系统下如何查找JDK的安装路径

Windows系统下如何查找JDK的安装路径

《Windows系统下如何查找JDK的安装路径》：本文主要介绍Windows系统下如何查找JDK的安装路径,文中介绍了三种方法,分别是通过命令行检查、使用verbose选项查找jre目录、以及查看... 目录一、确认是否安装了JDK二、查找路径三、另外一种方式如果很久之前安装了JDK，或者在别人的电脑上，想
阅读更多...
Python中Windows和macOS文件路径格式不一致的解决方法

Python中Windows和macOS文件路径格式不一致的解决方法

《Python中Windows和macOS文件路径格式不一致的解决方法》在Python中,Windows和macOS的文件路径字符串格式不一致主要体现在路径分隔符上,这种差异可能导致跨平台代码在处理文... 目录方法 1：使用 os.path 模块方法 2：使用 pathlib 模块（推荐）方法 3：统一使
阅读更多...
一文教你解决Python不支持中文路径的问题

一文教你解决Python不支持中文路径的问题

《一文教你解决Python不支持中文路径的问题》Python是一种广泛使用的高级编程语言,然而在处理包含中文字符的文件路径时,Python有时会表现出一些不友好的行为,下面小编就来为大家介绍一下具体的... 目录问题背景解决方案1. 设置正确的文件编码2. 使用pathlib模块3. 转换路径为Unicod
阅读更多...
spring @EventListener 事件与监听的示例详解

spring @EventListener 事件与监听的示例详解

《spring@EventListener事件与监听的示例详解》本文介绍了自定义Spring事件和监听器的方法,包括如何发布事件、监听事件以及如何处理异步事件,通过示例代码和日志,展示了事件的顺序... 目录1、自定义Application Event2、自定义监听3、测试4、源代码5、其他5.1 顺序执行
阅读更多...
MySQL9.0默认路径安装下重置root密码

MySQL9.0默认路径安装下重置root密码

《MySQL9.0默认路径安装下重置root密码》本文主要介绍了MySQL9.0默认路径安装下重置root密码,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们... 目录问题描述环境描述解决方法正常模式下修改密码报错原因问题描述mysqlChina编程采用默认安装路径，
阅读更多...
一文详解Java Condition的await和signal等待通知机制

一文详解Java Condition的await和signal等待通知机制

《一文详解JavaCondition的await和signal等待通知机制》这篇文章主要为大家详细介绍了JavaCondition的await和signal等待通知机制的相关知识,文中的示例代码讲... 目录1. Condition的核心方法2. 使用场景与优势3. 使用流程与规范基本模板生产者-消费者示例
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2