[20260216]直接路径读与filesystemio_options=asynch(21c).txt
[20260216]直接路径读与filesystemio_options=asynch(21c).txt
--//昨天测试直接路径读遇到的情况，不知道为什么仅仅第1次使用直接路径读，再次执行无论是否刷新数据缓存，都无法实现。
--//另外无意中发现设置参数filesystemio_options=asynch时，并不是读取访问数据块是使用异步IO，而是直接路径读时才使用异步IO。
--//通过测试说明遇到的情况。
--//首先给出oracle官方的解析:
https://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94410
9.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter
You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on
file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
FILESYTEMIO_OPTIONS can be set to one of the following values:
ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.
DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
SETALL: enable both asynchronous and direct I/O on file system files.
NONE: disable both asynchronous and direct I/O on file system files.
--//文档提及设置ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission。
--//另外发现当前内核版本查看/proc/slabinfo是否存在kio*不可行。以前旧版本查询询需要调用linux的slabinfo工具(可在
--///proc/slabinfo目录下查看，这些是内核"slab")，通过查找"kiocb"(内核I/O回调)和"kioctx"(内核I/O上下文)的分配情况，来判断
--//是否有进程使用了这些slab——这表明异步I/O已初始化。
--//注：我的测试在虚拟机器上进行，不知道是否相关。我的测试通过是否调用io_submit()发起I/O请求来确定是否启用异步IO。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> @ hidez _serial_direct_read|^_small_table_threshold$|filesystemio
NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ---------------------- ---------------------------------------------------- ------------- ------------- ------------ ----- ---------
431 1AF 0 filesystemio_options IO operations on filesystem files FALSE ASYNCH ASYNCH FALSE FALSE
1867 74B 0 _small_table_threshold lower threshold level of table size for direct reads TRUE 1018 1018 TRUE DEFERRED
4141 102D 0 _serial_direct_read enable direct read in serial TRUE auto auto TRUE IMMEDIATE
--//我的测试环境只要大于1018数据块，就有可能采用直接路径读，也就是 1018*8/1024 = 7.953125 ,基本就是大于8M。
# cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
# uname -a
Linux centtest 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
--//查询/proc/slabinfo，没有输出。
# grep -i kio /proc/slabinfo
$ ldd $(which oracle) | grep -i aio
libaio.so.1 => /lib64/libaio.so.1 (0x00007f7b07138000)
2.建立测试环境：
SCOTT@book01p> create table t1 as select * from all_objects;
Table created.
--//分析表略。
SCOTT@book01p> @ seg2 t1
SCOTT@book01p> @ pr
==============================
SEG_MB : 12
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : T1
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 1536
HDRFIL : 12
HDRBLK : 170
PL/SQL procedure successfully completed.
--//12M.
$ cat bh_obj.sql
/* Formatted on 2026-02-13 16:42:02 (QP5 v5.277) */
SELECT COUNT (*)
FROM ( SELECT inst_id
,class#
,FILE#
,BLOCK#
,status
,lock_element_addr
,dirty
,temp
,ping
,stale
,direct
,new
FROM gv$bh
WHERE objd =
(SELECT data_object_id
FROM dba_objects
WHERE object_name =
UPPER (
CASE
WHEN INSTR ('&1', '.') > 0 THEN SUBSTR ('&1', INSTR ('&1', '.') + 1)
ELSE '&1'
END)
AND owner =
UPPER (
CASE
WHEN INSTR ('&1', '.') > 0
THEN
UPPER (SUBSTR ('&1', 1, INSTR ('&1', '.') - 1))
ELSE
USER
END))
AND status != 'free'
ORDER BY inst_id);
3.测试：
--//测试前重启数据库，这样表t1不会加载到数据库缓存中。
--//session 1:
SCOTT@book01p> @ spid
==============================
SID : 2
SERIAL# : 34339
PROCESS : 9401
SERVER : DEDICATED
SPID : 9403
PID : 8
P_SERIAL# : 6
KILL_COMMAND : alter system kill session '2,34339' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> column value format 9999999
SCOTT@book01p> @ ses2z 2 "physical reads|physical reads direct|physical read IO requests"
SID NAME VALUE
---------- ------------------------------ --------
2 physical reads 242
2 physical reads cache 242
2 physical read IO requests 242
--//没有physical reads direct。
--//window 1:
$ strace -fp 8778 -e pread,io_submit -y
--//注：strace新版本支持-y参数，这样打开的文件句柄会同时显示文件名，便于观察。
--//session 1:
SCOTT@book01p> @ bh_obj scott.t1
COUNT(*)
----------
0
SCOTT@book01p> select count(*) from t1 ;
COUNT(*)
----------
69913
--//window 1:
$ strace -fp 9403 -e pread,io_submit -y
Process 9403 attached
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0@]@\0\r\210\354\2\0\0\1\6F\321\0\0\2\0\0\0>\0\0\0\f\210\354\2"..., 8192, 195559424) = 8192
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0\210\214@\0\f\376'\4\0\0\1\6\275\265\0\0\1\0\0\0\35\0\0\0\v\376'\4"..., 8192, 294715392) = 8192
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "#\242\0\0\252\0\0\3\211\264%\4\0\0\1\4\367H\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:106496, offset:1400832}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:122880, offset:1515520}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:122880, offset:1646592}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:122880, offset:1777664}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:122880, offset:1908736}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:57344, offset:2039808}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:65536, offset:3145728}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:122880, offset:3219456}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:122880, offset:3350528}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:2113536}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:2621440}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:4210688}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:4718592}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:5259264}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:5767168}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:6307840}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:6815744}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:7356416}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:7864320}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:8404992}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:8912896}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:9453568}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:9961472}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:10502144}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:11010048}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:11550720}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:12058624}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:12599296}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:13107200}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:13647872}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:286720, offset:14155776}}) = 1
--//Sum = 12083200
--//前面2次system表空间的访问与递归执行的sql语句有关，采用pread函数。存在1次访问users表空间。
--//访问的是数据块1392640/8192 = 170。实际上表t1的段头。
SYS@book01p> @ seg2 scott.t1 ''
SYS@book01p> @ pr
==============================
SEG_MB : 12
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : T1
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 1536
HDRFIL : 12
HDRBLK : 170
PL/SQL procedure successfully completed.
--//剩下的是函数io_submit，也就是采用异步IO。
--//将io_submit函数调用的nbytes后面的字节数值相加等于12083200。
106496+122880+122880+122880+122880+57344+65536+122880+122880+507904+524288+507904+524288+507904+524288+
507904+524288+507904+524288+507904+524288+507904+524288+507904+524288+507904+524288+507904+524288+507904+286720
= 12083200
--//12083200/8192 = 1475
SCOTT@book01p> @ ses2z 2 "physical reads|physical reads direct|physical read IO requests"
SID NAME VALUE
---------- ------------------------------ --------
2 physical reads 1744
2 physical reads cache 269
2 physical reads direct 1475
2 physical read IO requests 300
--//physical reads direct=1475,与strace跟踪看到的一致。
SCOTT@book01p> @ bh_obj scott.t1
COUNT(*)
----------
1
--//这也验证有1个数据块进入缓存，通过pread读取。
4.继续测试：
--//session 1:
SCOTT@book01p> select count(*) from t1 ;
COUNT(*)
----------
69913
SCOTT@book01p> @ bh_obj scott.t1
COUNT(*)
----------
1474
SCOTT@book01p> @ ses2z 2 "physical reads|physical reads direct|physical read IO requests"
SID NAME VALUE
---------- ------------------------------ --------
2 physical reads 3219
2 physical reads cache 1744
2 physical reads direct 1475
2 physical read IO requests 337
--//第2次执行没有采用直接路径读，这也是我前面测试的遇到的问题。
--//window 1:
$ strace -fp 9403 -e pread,io_submit -y
Process 9403 attached
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\253\0\0\3N\264%\4\0\0\2\4\377\343\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 40960, 1400832) = 40960
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\260\0\0\3N\264%\4\0\0\2\4\325\246\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1441792) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\271\0\0\3S\264%\4\0\0\2\0044\222\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1515520) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\300\0\0\3S\264%\4\0\0\2\4\331\241\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1572864) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\311\0\0\3W\264%\4\0\0\2\4$\211\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1646592) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\320\0\0\3W\264%\4\0\0\2\4\200\227\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1703936) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\331\0\0\3[\264%\4\0\0\2\4\363t\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1777664) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\340\0\0\3[\264%\4\0\0\2\4\300\17\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1835008) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\351\0\0\3_\264%\4\0\0\2\4\361\256\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1908736) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\360\0\0\3_\264%\4\0\0\2\4< \0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1966080) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\371\0\0\3a\264%\4\0\0\2\4\177\322\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 2039808) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\200\1\0\3c\264%\4\0\0\2\4b\366\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 3145728) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\211\1\0\3g\264%\4\0\0\2\4\211f\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 3219456) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\220\1\0\3g\264%\4\0\0\2\4U\16\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 3276800) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\231\1\0\3k\264%\4\0\0\2\4\376+\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 3350528) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\240\1\0\3k\264%\4\0\0\2\4\310\306\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 3407872) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\1\0\3m\264%\4\0\0\2\4\360\224\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 2113536) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\1\0\3m\264%\4\0\0\2\4\250\264\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 2940928) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\2\0\3o\264%\4\0\0\2\4'\315\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 4210688) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\2\0\3o\264%\4\0\0\2\4\210\274\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 5038080) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\2\0\3q\264%\4\0\0\2\4W8\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 5259264) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\2\0\3q\264%\4\0\0\2\4\234L\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 6086656) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\3\0\3s\264%\4\0\0\2\4\206\374\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 6307840) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\3\0\3s\264%\4\0\0\2\4K|\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 7135232) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\3\0\3u\264%\4\0\0\2\4rP\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 7356416) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\3\0\3u\264%\4\0\0\2\4\324o\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 8183808) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\4\0\3w\264%\4\0\0\2\4)\231\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 8404992) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\4\0\3w\264%\4\0\0\2\4\251e\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 9232384) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\4\0\3y\264%\4\0\0\2\4b>\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 9453568) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\4\0\3y\264%\4\0\0\2\4\305\346\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 10280960) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\5\0\3}\264%\4\0\0\2\4\37g\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 10502144) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\5\0\3}\264%\4\0\0\2\4\272^\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 11329536) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\5\0\3\177\264%\4\0\0\2\4\313=\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 11550720) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\5\0\3\177\264%\4\0\0\2\4\226Y\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 12378112) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\6\0\3\202\264%\4\0\0\2\4e\330\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 12599296) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\6\0\3\202\264%\4\0\0\2\4\300\331\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 13426688) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\6\0\3\203\264%\4\0\0\2\4\303\251\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 794624, 13647872) = 794624
--//可以发现第2次执行调用pread函数，数据块直接进入缓存，也就是在filesystemio_options=asynch的情况下，直接路径读采用异步
--//IO操作.还有点奇怪的是看等号的数值最大827392。
--//827392/8192 = 101,也就是当前数据库的db_file_multiblock_read_count设置101.
SYS@book> show spparameter db_file_multiblock_read_count
SID NAME TYPE VALUE
-------- ----------------------------- ------------------------------ ----------------------------
* db_file_multiblock_read_count integer
--//说明spfile文件里面没有设置db_file_multiblock_read_count。
SYS@book> @ hidez db_file_multiblock_read_count
SYS@book> @ pr
==============================
NUM : 2341
N_HEX : 925
CON_ID : 0
NAME : db_file_multiblock_read_count
DESCRIPTION : db block to be read each IO
DEFAULT_VALUE : TRUE
SESSION_VALUE : 101
SYSTEM_VALUE : 101
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ISSES_MODIFIABLE : TRUE
ISSYS_MODIFIABLE : IMMEDIATE
PL/SQL procedure successfully completed.
5.补充strace跟踪pread,io_submit,io_getevents的情况：
$ strace -fp 10476 -e pread,io_submit,io_getevents -y
Process 10476 attached
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0@]@\0\r\210\354\2\0\0\1\6F\321\0\0\2\0\0\0>\0\0\0\f\210\354\2"..., 8192, 195559424) = 8192
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0\210\214@\0\f\376'\4\0\0\1\6\275\265\0\0\1\0\0\0\35\0\0\0\v\376'\4"..., 8192, 294715392) = 8192
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "#\242\0\0\252\0\0\3\211\264%\4\0\0\1\4\367H\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:106496, offset:1400832}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:122880, offset:1515520}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 106496, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 122880, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:122880, offset:1646592}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:122880, offset:1777664}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 122880, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 122880, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:122880, offset:1908736}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:57344, offset:2039808}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 122880, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 57344, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:65536, offset:3145728}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:122880, offset:3219456}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 65536, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 122880, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:122880, offset:3350528}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:2113536}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 122880, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:2621440}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:4210688}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:4718592}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:5259264}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:5767168}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:6307840}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:6815744}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:7356416}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:7864320}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:8404992}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:8912896}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:9453568}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:9961472}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:10502144}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:11010048}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:11550720}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:12058624}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:12599296}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:13107200}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:13647872}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:286720, offset:14155776}}) = 1
io_getevents(140046100262912, 1, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 286720, 0}}, {0, 0}) = 1
6.看以前链接，如果表通过rowid+between定位，在对应数据块不再数据缓存时，会选择直接路径读，测试21c的情况。
SCOTT@book01p> @ spid
==============================
SID : 390
SERIAL# : 22106
PROCESS : 5162
SERVER : DEDICATED
SPID : 5164
PID : 43
P_SERIAL# : 5
KILL_COMMAND : alter system kill session '390,22106' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ ses2z 390 "physical reads direct"
no rows selected
SCOTT@book01p> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
AAASmfAAMAAAACDAAC 30 SALES CHICAGO
AAASmfAAMAAAACDAAD 40 OPERATIONS BOSTON
SCOTT@book01p> @ ses2z 390 "physical reads direct"
no rows selected
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
--//对应数据块在数据库缓存。
SCOTT@book01p> @ ses2z 390 "physical reads direct"
no rows selected
--//当前会话没有physical reads direct操作。
SCOTT@book01p> alter system flush buffer_cache;
System altered.
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
SCOTT@book01p> column value format 999999999
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 1
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 2
--//相应数据块不再缓存，rowid+between出现physical reads direct。
--//使用strace跟踪：
$ strace -fp 5164 -e pread,io_submit,io_getevents -y
Process 5164 attached
io_submit(140336933646336, 1, {{data:0x7fa2b6fb0e10, pread, filedes:257, buf:0x7fa2b71ee000, nbytes:8192, offset:1073152}}) = 1
io_getevents(140336933646336, 1, 128, {{0x7fa2b6fb0e10, 0x7fa2b6fb0e10, 8192, 0}}, {0, 0}) = 1
--//单独rowid访问第1次采用直接路径读。
SCOTT@book01p> select rowid,dept.* from dept where rowid = 'AAASmfAAMAAAACDAAA' ;
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 3
--//physical reads direct加1.
SCOTT@book01p> select rowid,dept.* from dept where rowid = 'AAASmfAAMAAAACDAAA' ;
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 3
--//再次执行不再采用直接路径读。
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 3
--//数据块已经在数据缓存，不再采用直接路径读。
--//昨天测试直接路径读遇到的情况，不知道为什么仅仅第1次使用直接路径读，再次执行无论是否刷新数据缓存，都无法实现。
--//另外无意中发现设置参数filesystemio_options=asynch时，并不是读取访问数据块是使用异步IO，而是直接路径读时才使用异步IO。
--//通过测试说明遇到的情况。
--//首先给出oracle官方的解析:
https://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94410
9.1.1.2 FILESYSTEMIO_OPTIONS Initialization Parameter
You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on
file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
FILESYTEMIO_OPTIONS can be set to one of the following values:
ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.
DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
SETALL: enable both asynchronous and direct I/O on file system files.
NONE: disable both asynchronous and direct I/O on file system files.
--//文档提及设置ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission。
--//另外发现当前内核版本查看/proc/slabinfo是否存在kio*不可行。以前旧版本查询询需要调用linux的slabinfo工具(可在
--///proc/slabinfo目录下查看，这些是内核"slab")，通过查找"kiocb"(内核I/O回调)和"kioctx"(内核I/O上下文)的分配情况，来判断
--//是否有进程使用了这些slab——这表明异步I/O已初始化。
--//注：我的测试在虚拟机器上进行，不知道是否相关。我的测试通过是否调用io_submit()发起I/O请求来确定是否启用异步IO。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> @ hidez _serial_direct_read|^_small_table_threshold$|filesystemio
NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ---------------------- ---------------------------------------------------- ------------- ------------- ------------ ----- ---------
431 1AF 0 filesystemio_options IO operations on filesystem files FALSE ASYNCH ASYNCH FALSE FALSE
1867 74B 0 _small_table_threshold lower threshold level of table size for direct reads TRUE 1018 1018 TRUE DEFERRED
4141 102D 0 _serial_direct_read enable direct read in serial TRUE auto auto TRUE IMMEDIATE
--//我的测试环境只要大于1018数据块，就有可能采用直接路径读，也就是 1018*8/1024 = 7.953125 ,基本就是大于8M。
# cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
# uname -a
Linux centtest 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
--//查询/proc/slabinfo，没有输出。
# grep -i kio /proc/slabinfo
$ ldd $(which oracle) | grep -i aio
libaio.so.1 => /lib64/libaio.so.1 (0x00007f7b07138000)
2.建立测试环境：
SCOTT@book01p> create table t1 as select * from all_objects;
Table created.
--//分析表略。
SCOTT@book01p> @ seg2 t1
SCOTT@book01p> @ pr
==============================
SEG_MB : 12
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : T1
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 1536
HDRFIL : 12
HDRBLK : 170
PL/SQL procedure successfully completed.
--//12M.
$ cat bh_obj.sql
/* Formatted on 2026-02-13 16:42:02 (QP5 v5.277) */
SELECT COUNT (*)
FROM ( SELECT inst_id
,class#
,FILE#
,BLOCK#
,status
,lock_element_addr
,dirty
,temp
,ping
,stale
,direct
,new
FROM gv$bh
WHERE objd =
(SELECT data_object_id
FROM dba_objects
WHERE object_name =
UPPER (
CASE
WHEN INSTR ('&1', '.') > 0 THEN SUBSTR ('&1', INSTR ('&1', '.') + 1)
ELSE '&1'
END)
AND owner =
UPPER (
CASE
WHEN INSTR ('&1', '.') > 0
THEN
UPPER (SUBSTR ('&1', 1, INSTR ('&1', '.') - 1))
ELSE
USER
END))
AND status != 'free'
ORDER BY inst_id);
3.测试：
--//测试前重启数据库，这样表t1不会加载到数据库缓存中。
--//session 1:
SCOTT@book01p> @ spid
==============================
SID : 2
SERIAL# : 34339
PROCESS : 9401
SERVER : DEDICATED
SPID : 9403
PID : 8
P_SERIAL# : 6
KILL_COMMAND : alter system kill session '2,34339' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> column value format 9999999
SCOTT@book01p> @ ses2z 2 "physical reads|physical reads direct|physical read IO requests"
SID NAME VALUE
---------- ------------------------------ --------
2 physical reads 242
2 physical reads cache 242
2 physical read IO requests 242
--//没有physical reads direct。
--//window 1:
$ strace -fp 8778 -e pread,io_submit -y
--//注：strace新版本支持-y参数，这样打开的文件句柄会同时显示文件名，便于观察。
--//session 1:
SCOTT@book01p> @ bh_obj scott.t1
COUNT(*)
----------
0
SCOTT@book01p> select count(*) from t1 ;
COUNT(*)
----------
69913
--//window 1:
$ strace -fp 9403 -e pread,io_submit -y
Process 9403 attached
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0@]@\0\r\210\354\2\0\0\1\6F\321\0\0\2\0\0\0>\0\0\0\f\210\354\2"..., 8192, 195559424) = 8192
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0\210\214@\0\f\376'\4\0\0\1\6\275\265\0\0\1\0\0\0\35\0\0\0\v\376'\4"..., 8192, 294715392) = 8192
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "#\242\0\0\252\0\0\3\211\264%\4\0\0\1\4\367H\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:106496, offset:1400832}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:122880, offset:1515520}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:122880, offset:1646592}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:122880, offset:1777664}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:122880, offset:1908736}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:57344, offset:2039808}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:65536, offset:3145728}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:122880, offset:3219456}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:122880, offset:3350528}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:2113536}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:2621440}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:4210688}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:4718592}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:5259264}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:5767168}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:6307840}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:6815744}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:7356416}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:7864320}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:8404992}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:8912896}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:9453568}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:9961472}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:10502144}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:11010048}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:11550720}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:12058624}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:12599296}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:524288, offset:13107200}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45dea540, pread, filedes:259, buf:0x7fee45497000, nbytes:507904, offset:13647872}}) = 1
io_submit(140661450588160, 1, {{data:0x7fee45de9e10, pread, filedes:259, buf:0x7fee45407000, nbytes:286720, offset:14155776}}) = 1
--//Sum = 12083200
--//前面2次system表空间的访问与递归执行的sql语句有关，采用pread函数。存在1次访问users表空间。
--//访问的是数据块1392640/8192 = 170。实际上表t1的段头。
SYS@book01p> @ seg2 scott.t1 ''
SYS@book01p> @ pr
==============================
SEG_MB : 12
SEG_OWNER : SCOTT
SEG_SEGMENT_NAME : T1
SEG_PARTITION_NAME :
SEG_SEGMENT_TYPE : TABLE
SEG_TABLESPACE_NAME : USERS
BLOCKS : 1536
HDRFIL : 12
HDRBLK : 170
PL/SQL procedure successfully completed.
--//剩下的是函数io_submit，也就是采用异步IO。
--//将io_submit函数调用的nbytes后面的字节数值相加等于12083200。
106496+122880+122880+122880+122880+57344+65536+122880+122880+507904+524288+507904+524288+507904+524288+
507904+524288+507904+524288+507904+524288+507904+524288+507904+524288+507904+524288+507904+524288+507904+286720
= 12083200
--//12083200/8192 = 1475
SCOTT@book01p> @ ses2z 2 "physical reads|physical reads direct|physical read IO requests"
SID NAME VALUE
---------- ------------------------------ --------
2 physical reads 1744
2 physical reads cache 269
2 physical reads direct 1475
2 physical read IO requests 300
--//physical reads direct=1475,与strace跟踪看到的一致。
SCOTT@book01p> @ bh_obj scott.t1
COUNT(*)
----------
1
--//这也验证有1个数据块进入缓存，通过pread读取。
4.继续测试：
--//session 1:
SCOTT@book01p> select count(*) from t1 ;
COUNT(*)
----------
69913
SCOTT@book01p> @ bh_obj scott.t1
COUNT(*)
----------
1474
SCOTT@book01p> @ ses2z 2 "physical reads|physical reads direct|physical read IO requests"
SID NAME VALUE
---------- ------------------------------ --------
2 physical reads 3219
2 physical reads cache 1744
2 physical reads direct 1475
2 physical read IO requests 337
--//第2次执行没有采用直接路径读，这也是我前面测试的遇到的问题。
--//window 1:
$ strace -fp 9403 -e pread,io_submit -y
Process 9403 attached
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\253\0\0\3N\264%\4\0\0\2\4\377\343\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 40960, 1400832) = 40960
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\260\0\0\3N\264%\4\0\0\2\4\325\246\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1441792) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\271\0\0\3S\264%\4\0\0\2\0044\222\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1515520) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\300\0\0\3S\264%\4\0\0\2\4\331\241\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1572864) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\311\0\0\3W\264%\4\0\0\2\4$\211\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1646592) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\320\0\0\3W\264%\4\0\0\2\4\200\227\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1703936) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\331\0\0\3[\264%\4\0\0\2\4\363t\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1777664) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\340\0\0\3[\264%\4\0\0\2\4\300\17\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1835008) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\351\0\0\3_\264%\4\0\0\2\4\361\256\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 1908736) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\360\0\0\3_\264%\4\0\0\2\4< \0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 1966080) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\371\0\0\3a\264%\4\0\0\2\4\177\322\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 2039808) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\200\1\0\3c\264%\4\0\0\2\4b\366\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 3145728) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\211\1\0\3g\264%\4\0\0\2\4\211f\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 3219456) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\220\1\0\3g\264%\4\0\0\2\4U\16\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 3276800) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\231\1\0\3k\264%\4\0\0\2\4\376+\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 57344, 3350528) = 57344
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\240\1\0\3k\264%\4\0\0\2\4\310\306\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 65536, 3407872) = 65536
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\1\0\3m\264%\4\0\0\2\4\360\224\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 2113536) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\1\0\3m\264%\4\0\0\2\4\250\264\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 2940928) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\2\0\3o\264%\4\0\0\2\4'\315\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 4210688) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\2\0\3o\264%\4\0\0\2\4\210\274\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 5038080) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\2\0\3q\264%\4\0\0\2\4W8\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 5259264) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\2\0\3q\264%\4\0\0\2\4\234L\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 6086656) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\3\0\3s\264%\4\0\0\2\4\206\374\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 6307840) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\3\0\3s\264%\4\0\0\2\4K|\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 7135232) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\3\0\3u\264%\4\0\0\2\4rP\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 7356416) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\3\0\3u\264%\4\0\0\2\4\324o\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 8183808) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\4\0\3w\264%\4\0\0\2\4)\231\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 8404992) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\4\0\3w\264%\4\0\0\2\4\251e\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 9232384) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\4\0\3y\264%\4\0\0\2\4b>\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 9453568) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\4\0\3y\264%\4\0\0\2\4\305\346\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 10280960) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\5\0\3}\264%\4\0\0\2\4\37g\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 10502144) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\5\0\3}\264%\4\0\0\2\4\272^\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 11329536) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\5\0\3\177\264%\4\0\0\2\4\313=\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 11550720) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\347\5\0\3\177\264%\4\0\0\2\4\226Y\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 12378112) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\2\6\0\3\202\264%\4\0\0\2\4e\330\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 827392, 12599296) = 827392
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0g\6\0\3\202\264%\4\0\0\2\4\300\331\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 204800, 13426688) = 204800
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "\6\242\0\0\202\6\0\3\203\264%\4\0\0\2\4\303\251\0\0\1\0\0\0\331\346\2\0G\264%\4"..., 794624, 13647872) = 794624
--//可以发现第2次执行调用pread函数，数据块直接进入缓存，也就是在filesystemio_options=asynch的情况下，直接路径读采用异步
--//IO操作.还有点奇怪的是看等号的数值最大827392。
--//827392/8192 = 101,也就是当前数据库的db_file_multiblock_read_count设置101.
SYS@book> show spparameter db_file_multiblock_read_count
SID NAME TYPE VALUE
-------- ----------------------------- ------------------------------ ----------------------------
* db_file_multiblock_read_count integer
--//说明spfile文件里面没有设置db_file_multiblock_read_count。
SYS@book> @ hidez db_file_multiblock_read_count
SYS@book> @ pr
==============================
NUM : 2341
N_HEX : 925
CON_ID : 0
NAME : db_file_multiblock_read_count
DESCRIPTION : db block to be read each IO
DEFAULT_VALUE : TRUE
SESSION_VALUE : 101
SYSTEM_VALUE : 101
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ISSES_MODIFIABLE : TRUE
ISSYS_MODIFIABLE : IMMEDIATE
PL/SQL procedure successfully completed.
5.补充strace跟踪pread,io_submit,io_getevents的情况：
$ strace -fp 10476 -e pread,io_submit,io_getevents -y
Process 10476 attached
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0@]@\0\r\210\354\2\0\0\1\6F\321\0\0\2\0\0\0>\0\0\0\f\210\354\2"..., 8192, 195559424) = 8192
pread(256</u01/oradata/BOOK/book01p/system01.dbf>, "\6\242\0\0\210\214@\0\f\376'\4\0\0\1\6\275\265\0\0\1\0\0\0\35\0\0\0\v\376'\4"..., 8192, 294715392) = 8192
pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "#\242\0\0\252\0\0\3\211\264%\4\0\0\1\4\367H\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:106496, offset:1400832}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:122880, offset:1515520}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 106496, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 122880, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:122880, offset:1646592}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:122880, offset:1777664}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 122880, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 122880, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:122880, offset:1908736}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:57344, offset:2039808}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 122880, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 57344, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:65536, offset:3145728}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:122880, offset:3219456}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 65536, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 122880, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:122880, offset:3350528}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:2113536}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 122880, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:2621440}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:4210688}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:4718592}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:5259264}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:5767168}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:6307840}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:6815744}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:7356416}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:7864320}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:8404992}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:8912896}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:9453568}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:9961472}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:10502144}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:11010048}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:11550720}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:12058624}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:12599296}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:524288, offset:13107200}}) = 1
io_submit(140046100262912, 1, {{data:0x7f5f0008d540, pread, filedes:259, buf:0x7f5effefa000, nbytes:507904, offset:13647872}}) = 1
io_getevents(140046100262912, 2, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 524288, 0}, {0x7f5f0008d540, 0x7f5f0008d540, 507904, 0}}, {0, 0}) = 2
io_submit(140046100262912, 1, {{data:0x7f5f0008ce10, pread, filedes:259, buf:0x7f5effe6a000, nbytes:286720, offset:14155776}}) = 1
io_getevents(140046100262912, 1, 128, {{0x7f5f0008ce10, 0x7f5f0008ce10, 286720, 0}}, {0, 0}) = 1
6.看以前链接，如果表通过rowid+between定位，在对应数据块不再数据缓存时，会选择直接路径读，测试21c的情况。
SCOTT@book01p> @ spid
==============================
SID : 390
SERIAL# : 22106
PROCESS : 5162
SERVER : DEDICATED
SPID : 5164
PID : 43
P_SERIAL# : 5
KILL_COMMAND : alter system kill session '390,22106' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ ses2z 390 "physical reads direct"
no rows selected
SCOTT@book01p> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
AAASmfAAMAAAACDAAC 30 SALES CHICAGO
AAASmfAAMAAAACDAAD 40 OPERATIONS BOSTON
SCOTT@book01p> @ ses2z 390 "physical reads direct"
no rows selected
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
--//对应数据块在数据库缓存。
SCOTT@book01p> @ ses2z 390 "physical reads direct"
no rows selected
--//当前会话没有physical reads direct操作。
SCOTT@book01p> alter system flush buffer_cache;
System altered.
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
SCOTT@book01p> column value format 999999999
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 1
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 2
--//相应数据块不再缓存，rowid+between出现physical reads direct。
--//使用strace跟踪：
$ strace -fp 5164 -e pread,io_submit,io_getevents -y
Process 5164 attached
io_submit(140336933646336, 1, {{data:0x7fa2b6fb0e10, pread, filedes:257, buf:0x7fa2b71ee000, nbytes:8192, offset:1073152}}) = 1
io_getevents(140336933646336, 1, 128, {{0x7fa2b6fb0e10, 0x7fa2b6fb0e10, 8192, 0}}, {0, 0}) = 1
--//单独rowid访问第1次采用直接路径读。
SCOTT@book01p> select rowid,dept.* from dept where rowid = 'AAASmfAAMAAAACDAAA' ;
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 3
--//physical reads direct加1.
SCOTT@book01p> select rowid,dept.* from dept where rowid = 'AAASmfAAMAAAACDAAA' ;
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 3
--//再次执行不再采用直接路径读。
SCOTT@book01p> select rowid,dept.* from dept where rowid between 'AAASmfAAMAAAACDAAA' and 'AAASmfAAMAAAACDAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- ------------------------------ -------------
AAASmfAAMAAAACDAAA 10 ACCOUNTING NEW YORK
AAASmfAAMAAAACDAAB 20 RESEARCH DALLAS
SCOTT@book01p> @ ses2z 390 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
390 physical reads direct 3
--//数据块已经在数据缓存，不再采用直接路径读。
浙公网安备 33010602011771号