[20260308]关于db_file_multiblock_read_count参数疑问2.txt
--//前面验证db_file_multiblock_read_count的计算：
--//1.可以看出db_file_multiblock_read_count在没有设置的情况下数据库缺省按照_db_block_buffers/sessions计算，如果该值小于
--//128，直接取整，如果大于128，db_file_multiblock_read_count按照128计算。
--//2.这种情况的出现_db_block_buffers很小，sessions很大的情况下在目前的生产环境很少配置,所以很少遇到。
--//3.另外db_file_multiblock_read_count最大值等于4096.
--//疑问2：在前面的测试直接路径读的过程中，还出现一个现象，在设置filesystemio_options=asynch的情况下，直接路径读使用
--//io_submit,io_getevents函数，而读入数据缓存的情况下使用竟然是pread函数，后续测试是否与db_file_multiblock_read_count不
--//等于128有关，写一篇blog验证看看。
1.环境：
SYS@book> @ 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 db_file_multiblock_read_count|^_small_table_threshold$|filesystemio|_db_block_buffers|^sessions$
NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ----------------------------- ------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
53 35 0 sessions user and system sessions TRUE 392 392 FALSE IMMEDIATE
431 1AF 0 filesystemio_options IO operations on filesystem files FALSE ASYNCH ASYNCH FALSE FALSE
1854 73E 0 _db_block_buffers Number of database blocks cached in memory: hidden parameter TRUE 50908 50908 FALSE FALSE
1867 74B 0 _small_table_threshold lower threshold level of table size for direct reads TRUE 1018 1018 TRUE DEFERRED
2341 925 0 db_file_multiblock_read_count db block to be read each IO TRUE 128 128 TRUE IMMEDIATE
--//50908/392 = 129.867,如果大于128，db_file_multiblock_read_count按照128计算。
--//_small_table_threshold=1018
2.测试：
SCOTT@book01p> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace users;
Table created.
--//如果插入长度100的字符串，一个数据块仅仅保存1条记录。
SCOTT@book01p> INSERT /*+ append */ INTO T SELECT RPAD(to_char(level,'0000'), 100, '*') FROM DUAL CONNECT BY LEVEL <= 1018;
1018 rows created.
SCOTT@book01p> commit;
Commit complete.
--//该表占用空间一定大于_small_table_threshold值，我前面的测试第1次全表扫描，会采用直接路径读，第2次不会。
SCOTT@book01p> @ spid
==============================
SID : 116
SERIAL# : 36311
PROCESS : 4721
SERVER : DEDICATED
SPID : 4723
PID : 49
P_SERIAL# : 8
KILL_COMMAND : alter system kill session '116,36311' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ ses2z 116 "physical reads direct|table scans \(direct read\)"
no rows selected
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
1018
SCOTT@book01p> @ ses2z 116 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
116 physical reads direct 1018
116 table scans (direct read) 1
--//第1次执行采用直接路径读。
$ strace -f -p 4723 -e readv,pread,io_submit-a 80 -y
Process 4723 attached
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:106496, offset:1400832}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:122880, offset:1515520}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:122880, offset:1646592}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:122880, offset:1777664}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:122880, offset:1908736}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:122880, offset:2039808}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:122880, offset:2170880}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:122880, offset:2301952}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:1032192, offset:3162112}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:1032192, offset:4210688}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:1032192, offset:5259264}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:1032192, offset:6307840}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:1032192, offset:7356416}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:1032192, offset:8404992}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9ae10, pread, filedes:259, buf:0x7fa97d9c8000, nbytes:1032192, offset:9453568}}) = 1
io_submit(140366038552576, 1, {{data:0x7fa97de9b540, pread, filedes:259, buf:0x7fa97db28000, nbytes:147456, offset:10502144}}) = 1
--//使用异步IO。
SCOTT@book01p> @ ses2z 116 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
116 physical reads direct 1018
116 table scans (direct read) 1
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
1018
SCOTT@book01p> @ ses2z 116 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
116 physical reads direct 1018
116 table scans (direct read) 1
--//第2次执行没有采用直接路径读。
$ strace -f -p 4723 -e readv,pread,io_submit,io_getevents -a 80 -y
...
pread(259, "\6\242\0\0\253\0\0\3\224@J\4\0\0\2\4\277\316\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 40960, 1400832) = 40960
pread(259, "\6\242\0\0\260\0\0\3\224@J\4\0\0\2\4\243\317\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 1441792) = 65536
pread(259, "\6\242\0\0\271\0\0\3\230@J\4\0\0\2\4\270\317\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 1515520) = 57344
pread(259, "\6\242\0\0\300\0\0\3\230@J\4\0\0\2\4\304\315\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 1572864) = 65536
pread(259, "\6\242\0\0\311\0\0\3\234@J\4\0\0\2\4\265\314\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 1646592) = 57344
pread(259, "\6\242\0\0\320\0\0\3\234@J\4\0\0\2\4\243\314\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 1703936) = 65536
pread(259, "\6\242\0\0\331\0\0\3\240@J\4\0\0\2\4\270\312\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 1777664) = 57344
pread(259, "\6\242\0\0\340\0\0\3\240@J\4\0\0\2\4\204\312\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 1835008) = 65536
pread(259, "\6\242\0\0\351\0\0\3\244@J\4\0\0\2\4\265\313\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 1908736) = 57344
pread(259, "\6\242\0\0\360\0\0\3\244@J\4\0\0\2\4\243\311\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 1966080) = 65536
pread(259, "\6\242\0\0\371\0\0\3\246@J\4\0\0\2\4\270\311\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 2039808) = 57344
pread(259, "\6\242\0\0\0\1\0\3\250@J\4\0\0\2\4D\306\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 2097152) = 65536
pread(259, "\6\242\0\0\t\1\0\3\254@J\4\0\0\2\4\265\306\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 2170880) = 57344
pread(259, "\6\242\0\0\20\1\0\3\254@J\4\0\0\2\4\243\306\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 2228224) = 65536
pread(259, "\6\242\0\0\31\1\0\3\260@J\4\0\0\2\4\271\316\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 57344, 2301952) = 57344
pread(259, "\6\242\0\0 \1\0\3\260@J\4\0\0\2\4\205\316\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 65536, 2359296) = 65536
pread(259, "\6\242\0\0\202\1\0\3\262@J\4\0\0\2\4\267\317\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 3162112) = 1032192
pread(259, "\6\242\0\0\2\2\0\3\264@J\4\0\0\2\4\270\312\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 4210688) = 1032192
pread(259, "\6\242\0\0\202\2\0\3\266@J\4\0\0\2\4\275\311\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 5259264) = 1032192
pread(259, "\6\242\0\0\2\3\0\3\270@J\4\0\0\2\4\274\307\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 6307840) = 1032192
pread(259, "\6\242\0\0\202\3\0\3\272@J\4\0\0\2\4\272\314\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 7356416) = 1032192
pread(259, "\6\242\0\0\2\4\0\3\274@J\4\0\0\2\4\261\312\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 8404992) = 1032192
pread(259, "\6\242\0\0\202\4\0\3\276@J\4\0\0\2\4\262\311\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 1032192, 9453568) = 1032192
pread(259, "\6\242\0\0\2\5\0\3\302@J\4\0\0\2\4\276\317\0\0\1\0\0\0\366\5\3\0\216@J\4"..., 147456, 10502144) = 147456
--//见鬼，这次使用pread函数，与昨天测试看到的情况不一致。
4.修改回来原来的参数看看：
SYS@book> alter system set processes=320 scope=spfile;
System altered.
--//重启数据库。
SYS@book> @hidez db_file_multiblock_read_count|^_small_table_threshold$|filesystemio|_db_block_buffers|^sessions$|^processes$
NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ------ ----------------------------- ------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
52 34 0 processes user processes FALSE 320 320 FALSE IMMEDIATE
53 35 0 sessions user and system sessions TRUE 504 504 FALSE IMMEDIATE
431 1AF 0 filesystemio_options IO operations on filesystem files FALSE ASYNCH ASYNCH FALSE FALSE
1854 73E 0 _db_block_buffers Number of database blocks cached in memory: hidden parameter TRUE 50908 50908 FALSE FALSE
1867 74B 0 _small_table_threshold lower threshold level of table size for direct reads TRUE 1018 1018 TRUE DEFERRED
2341 925 0 db_file_multiblock_read_count db block to be read each IO TRUE 101 101 TRUE IMMEDIATE
6 rows selected.
SCOTT@book01p> @ desc t
Name Null? Type
------------------------------- -------- ----------------------------
1 V VARCHAR2(100)
--//这样执行1次可以避免一些不必要的递归语句。
SCOTT@book01p> @ spid
==============================
SID : 142
SERIAL# : 1
PROCESS : 5579
SERVER : DEDICATED
SPID : 5581
PID : 57
P_SERIAL# : 4
KILL_COMMAND : alter system kill session '142,1' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ ses2z 142 "physical reads direct|table scans \(direct read\)"
no rows selected
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
1018
SCOTT@book01p> @ ses2z 142 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
142 physical reads direct 1018
142 table scans (direct read) 1
--//第1次执行采用直接路径读。
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
1018
SCOTT@book01p> @ ses2z 142 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
142 physical reads direct 1018
142 table scans (direct read) 1
--//第2次执行没有采用直接路径读。
SCOTT@book01p> alter session set db_file_multiblock_read_count=128;
Session altered.
SYS@book> alter system flush buffer_cache;
System altered.
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
1018
SCOTT@book01p> @ ses2z 142 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
142 physical reads direct 1018
142 table scans (direct read) 1
--//第2次执行没有采用直接路径读。
--//strace跟踪结果：
$ strace -f -p 5581 -e pread,io_submit -a 80 -y
Process 5581 attached
pread(259, "#\242\0\0\252\0\0\3^]J\4\0\0\1\4\271\254\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:106496, offset:1400832}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:122880, offset:1515520}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:122880, offset:1646592}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:122880, offset:1777664}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:122880, offset:1908736}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:122880, offset:2039808}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:122880, offset:2170880}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:122880, offset:2301952}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:3162112}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:3670016}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:4210688}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:4718592}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:5259264}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:5767168}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:6307840}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:6815744}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:7356416}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:7864320}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:8404992}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:8912896}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:507904, offset:9453568}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4802540, pread, filedes:259, buf:0x7fa6b46af000, nbytes:524288, offset:9961472}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b4801e10, pread, filedes:259, buf:0x7fa6b461f000, nbytes:147456, offset:10502144}}) = 1
--//第1次执行采用直接路径读，使用异步IO。
pread(259, "\6\242\0\0\253\0\0\3\260_J\4\0\0\1\4\200\v\0\0\1\0\0\0\373\5\3\0\260_J\4"..., 40960, 1400832) = 40960
pread(259, "\6\242\0\0\260\0\0\3\36]J\4\0\0\2\4'\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1441792) = 65536
pread(259, "\6\242\0\0\271\0\0\3\"]J\4\0\0\2\4<\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1515520) = 57344
pread(259, "\6\242\0\0\300\0\0\3\"]J\4\0\0\2\4@\323\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1572864) = 65536
pread(259, "\6\242\0\0\311\0\0\3']J\4\0\0\2\0041\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1646592) = 57344
pread(259, "\6\242\0\0\320\0\0\3']J\4\0\0\2\4'\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1703936) = 65536
pread(259, "\6\242\0\0\331\0\0\3+]J\4\0\0\2\4<\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1777664) = 57344
pread(259, "\6\242\0\0\340\0\0\3+]J\4\0\0\2\4\0\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1835008) = 65536
pread(259, "\6\242\0\0\351\0\0\0030]J\4\0\0\2\0041\325\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1908736) = 57344
pread(259, "\6\242\0\0\360\0\0\0030]J\4\0\0\2\4'\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1966080) = 65536
pread(259, "\6\242\0\0\371\0\0\0033]J\4\0\0\2\4<\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 2039808) = 57344
pread(259, "\6\242\0\0\0\1\0\0035]J\4\0\0\2\4\300\330\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 2097152) = 65536
pread(259, "\6\242\0\0\t\1\0\3:]J\4\0\0\2\0041\330\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 2170880) = 57344
pread(259, "\6\242\0\0\20\1\0\3:]J\4\0\0\2\4'\330\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 2228224) = 65536
pread(259, "\6\242\0\0\31\1\0\3?]J\4\0\0\2\4=\320\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 2301952) = 57344
pread(259, "\6\242\0\0 \1\0\3?]J\4\0\0\2\4\1\320\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 2359296) = 65536
pread(259, "\6\242\0\0\202\1\0\3B]J\4\0\0\2\0043\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 3162112) = 827392
pread(259, "\6\242\0\0\347\1\0\3B]J\4\0\0\2\4]\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 3989504) = 204800
pread(259, "\6\242\0\0\2\2\0\3E]J\4\0\0\2\4<\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 4210688) = 827392
pread(259, "\6\242\0\0g\2\0\3E]J\4\0\0\2\4Z\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 5038080) = 204800
pread(259, "\6\242\0\0\202\2\0\3H]J\4\0\0\2\0049\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 5259264) = 827392
pread(259, "\6\242\0\0\347\2\0\3H]J\4\0\0\2\4Y\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 6086656) = 204800
pread(259, "\6\242\0\0\2\3\0\3L]J\4\0\0\2\0048\331\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 6307840) = 827392
pread(259, "\6\242\0\0g\3\0\3L]J\4\0\0\2\4R\331\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 7135232) = 204800
pread(259, "\6\242\0\0\202\3\0\3O]J\4\0\0\2\4>\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 7356416) = 827392
pread(259, "\6\242\0\0\347\3\0\3O]J\4\0\0\2\4\\\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 8183808) = 204800
pread(259, "\6\242\0\0\2\4\0\3Q]J\4\0\0\2\0045\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 8404992) = 827392
pread(259, "\6\242\0\0g\4\0\3Q]J\4\0\0\2\4W\325\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 9232384) = 204800
pread(259, "\6\242\0\0\202\4\0\3S]J\4\0\0\2\0046\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 827392, 9453568) = 827392
pread(259, "\6\242\0\0\347\4\0\3S]J\4\0\0\2\4P\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 204800, 10280960) = 204800
pread(259, "\6\242\0\0\2\5\0\3U]J\4\0\0\2\4:\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 147456, 10502144) = 147456
pread(259, "#\242\0\0\252\0\0\3^]J\4\0\0\1\4\271\254\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
--//第2次执行没有采用直接路径读，没有使用异步IO。仔细看还可以发现读入数据缓存的跳过L1，L2段。
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72da0000, nbytes:40960, offset:1400832}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:1441792}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:1515520}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:1572864}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:1646592}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:1703936}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:1777664}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:1835008}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:1908736}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:1966080}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:2039808}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:2097152}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:2170880}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:2228224}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9c000, nbytes:57344, offset:2301952}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b49882f0, pread, filedes:259, buf:0x72d9a000, nbytes:65536, offset:2359296}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:3162112}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:4210688}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:5259264}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:6307840}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:7356416}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:8404992}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72cae000, nbytes:1032192, offset:9453568}}) = 1
io_submit(140354070921216, 1, {{data:0x7fa6b480a7e0, pread, filedes:259, buf:0x72d86000, nbytes:147456, offset:10502144}}) = 1
--//第3次执行前修改会话参数db_file_multiblock_read_count=128参数，实际上根本没有变，没有采用直接路径读，而非常奇怪的使用异步IO。
--//后记：如果仔细对比nbytes:后面的数字，第1次与第3次的执行出现很奇怪的变化。
--//视乎这种情况的出现与直接路径读类似对齐的方式读取。参考后面的文章：
--//[20260309]关于db_file_multiblock_read_count参数疑问3.txt
3.小结：
--//不知道什么因素影响这些变化。
--//有点不甘心，回到proeceees=246的环境测试，尝试修改会话的db_file_multiblock_read_count=128，可以再现这个现象。
$ strace -f -p 6162 -e pread,io_submit -a 80 -y
Process 6162 attached
pread(259, "#\242\0\0\252\0\0\3^]J\4\0\0\1\4\271\254\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
pread(259, "\6\242\0\0\253\0\0\3\260_J\4\0\0\1\4\200\v\0\0\1\0\0\0\373\5\3\0\260_J\4"..., 40960, 1400832) = 40960
pread(259, "\6\242\0\0\260\0\0\3\36]J\4\0\0\2\4'\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1441792) = 65536
pread(259, "\6\242\0\0\271\0\0\3\"]J\4\0\0\2\4<\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1515520) = 57344
pread(259, "\6\242\0\0\300\0\0\3\"]J\4\0\0\2\4@\323\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1572864) = 65536
pread(259, "\6\242\0\0\311\0\0\3']J\4\0\0\2\0041\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1646592) = 57344
pread(259, "\6\242\0\0\320\0\0\3']J\4\0\0\2\4'\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1703936) = 65536
pread(259, "\6\242\0\0\331\0\0\3+]J\4\0\0\2\4<\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1777664) = 57344
pread(259, "\6\242\0\0\340\0\0\3+]J\4\0\0\2\4\0\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1835008) = 65536
pread(259, "\6\242\0\0\351\0\0\0030]J\4\0\0\2\0041\325\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 1908736) = 57344
pread(259, "\6\242\0\0\360\0\0\0030]J\4\0\0\2\4'\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 1966080) = 65536
pread(259, "\6\242\0\0\371\0\0\0033]J\4\0\0\2\4<\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 2039808) = 57344
pread(259, "\6\242\0\0\0\1\0\0035]J\4\0\0\2\4\300\330\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 2097152) = 65536
pread(259, "\6\242\0\0\t\1\0\3:]J\4\0\0\2\0041\330\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 2170880) = 57344
pread(259, "\6\242\0\0\20\1\0\3:]J\4\0\0\2\4'\330\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 2228224) = 65536
pread(259, "\6\242\0\0\31\1\0\3?]J\4\0\0\2\4=\320\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 57344, 2301952) = 57344
pread(259, "\6\242\0\0 \1\0\3?]J\4\0\0\2\4\1\320\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 65536, 2359296) = 65536
pread(259, "\6\242\0\0\202\1\0\3B]J\4\0\0\2\0043\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 3162112) = 1032192
pread(259, "\6\242\0\0\2\2\0\3E]J\4\0\0\2\4<\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 4210688) = 1032192
pread(259, "\6\242\0\0\202\2\0\3H]J\4\0\0\2\0049\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 5259264) = 1032192
pread(259, "\6\242\0\0\2\3\0\3L]J\4\0\0\2\0048\331\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 6307840) = 1032192
pread(259, "\6\242\0\0\202\3\0\3O]J\4\0\0\2\4>\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 7356416) = 1032192
pread(259, "\6\242\0\0\2\4\0\3Q]J\4\0\0\2\0045\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 8404992) = 1032192
pread(259, "\6\242\0\0\202\4\0\3S]J\4\0\0\2\0046\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 1032192, 9453568) = 1032192
pread(259, "\6\242\0\0\2\5\0\3U]J\4\0\0\2\4:\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 147456, 10502144) = 147456
--//SCOTT@book01p> alter session set db_file_multiblock_read_count=128;
--//Session altered.
pread(259, "#\242\0\0\252\0\0\3^]J\4\0\0\1\4\271\254\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da6000, nbytes:40960, offset:1400832}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1441792}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1515520}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1572864}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1646592}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1703936}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1777664}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1835008}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1908736}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1966080}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:2039808}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:2097152}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:2170880}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:2228224}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:2301952}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee47512f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:2359296}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:3162112}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:4210688}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:5259264}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:6307840}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:7356416}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:8404992}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73cb4000, nbytes:1032192, offset:9453568}}) = 1
io_submit(140183074361344, 1, {{data:0x7f7ee45437e0, pread, filedes:259, buf:0x73d8c000, nbytes:147456, offset:10502144}}) = 1
--//测试中还遇到同时调用io_submit,pread的情况:
SCOTT@book01p> alter session set db_file_multiblock_read_count=120;
Session altered.
pread(259, "#\242\0\0\252\0\0\3^]J\4\0\0\1\4\271\254\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 1392640) = 8192
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da6000, nbytes:40960, offset:1400832}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1441792}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1515520}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1572864}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1646592}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1703936}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1777664}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1835008}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:1908736}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:1966080}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:2039808}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:2097152}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:2170880}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:2228224}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da2000, nbytes:57344, offset:2301952}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da0000, nbytes:65536, offset:2359296}}) = 1
pread(259, "\6\242\0\0\202\1\0\3B]J\4\0\0\2\0043\321\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 3162112) = 983040
--//983040/8192 = 120
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:4145152}}) = 1
--//49152/8192 = 6
--//120个数据块采用pread函数。6个数据块采用io_submit。
pread(259, "\6\242\0\0\2\2\0\3E]J\4\0\0\2\4<\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 4210688) = 983040
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:5193728}}) = 1
pread(259, "\6\242\0\0\202\2\0\3H]J\4\0\0\2\0049\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 5259264) = 983040
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:6242304}}) = 1
pread(259, "\6\242\0\0\2\3\0\3L]J\4\0\0\2\0048\331\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 6307840) = 983040
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:7290880}}) = 1
pread(259, "\6\242\0\0\202\3\0\3O]J\4\0\0\2\4>\322\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 7356416) = 983040
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:8339456}}) = 1
pread(259, "\6\242\0\0\2\4\0\3Q]J\4\0\0\2\0045\324\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 8404992) = 983040
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:9388032}}) = 1
pread(259, "\6\242\0\0\202\4\0\3S]J\4\0\0\2\0046\327\0\0\1\0\0\0\373\5\3\0\30]J\4"..., 983040, 9453568) = 983040
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73da4000, nbytes:49152, offset:10436608}}) = 1
io_submit(140546369323008, 1, {{data:0x7fd37a8642f0, pread, filedes:259, buf:0x73d8c000, nbytes:147456, offset:10502144}}) = 1
[20260308]关于db_file_multiblock_read_count参数疑问2.txt
-
lfree
2026-03-15 16:35:30 -
Oracle
- 原创