[20260216]直接路径读与filesystemio_options=asynch(21c).txt

来源：这里教程网时间：2026-03-03 23:30:25 作者：

[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 --//数据块已经在数据缓存，不再采用直接路径读。

编辑推荐：

返回列表

[20260216]直接路径读与filesystemio_options=asynch(21c).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推