[20260219]关于设置filesystemio_options=setall的情况.txt
--//节前遇到的问题，设置filesystemio_options=asynch，并非全部读写操作都是异步IO操作，发现在读取时许多操作还是使用pread函
--//数。而测试出现直接路径读才会使用异步IO操作，并且还发现在21c下直接路径读的阈值提高不少(注不包括前面测试遇到的特殊情况)，
--//而且如何确定直接路径读的块阈值不是很清楚如何确定,补充设置filesystemio_options=setall的情况。
--//按照官方文档介绍：
Direct and Asynchronous I/O
I/O operations in UNIX and Linux systems typically go through the file system cache. Although this doesn't represent a
problem in itself, this extra processing does require resources. Bypassing the file system cache reduces CPU
requirements, and frees up the file system cache for other non-database file operations. Operations against raw devices
automatically bypass the file system cache.
When a synchronous I/O request is submitted to the operating system, the writing process blocks until the write is
complete before continuing processing. With asynchronous I/O, processing continues while the I/O request is submitted
and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks associated with I/O
operations.
Oracle can take advantage of direct I/O and asynchronous I/O on supported platforms using the FILESYSTEMIO_OPTIONS
parameter, whose possible values are listed below.
ASYNCH - Enabled asynchronous I/O where possible.
DIRECTIO- Enabled direct I/O where possible.
SETALL- Enabled both direct I/O and asynchronous I/O where possible.
NONE - Disabled both direct I/O and asynchronous I/O.
--//设置SETALL相当于同时支持异步IO和直接IO，这样OS不做缓存数据文件，同时验证异步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> alter system set filesystemio_options=setall scope=spfile;
System altered.
--//利用前几天的测试结果：
--//SCOTT@book> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace users;
--//Table created.
--//实际上这样每块仅仅1条记录。
--//SCOTT@book> execute sys.dbms_stats.delete_table_stats ('SCOTT', 'T',cascade_columns=> true ,cascade_indexes=> true, cascade_parts=>true ,no_invalidate=> false)
--//PL/SQL procedure successfully completed.
--//利用前面测试建立的表：
SCOTT@book01p> @ tab2z ^T$
Show tables matching condition "^T$" (if schema is not specified then current user's tables only are shown)...
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- -------------------- --------
SCOTT T TAB 995 1019 0 0 101 2026-02-15 16:21:17 1 DISABLED
--//实际上记录的985+10表示插入995条记录，如果加上段头，L1，L2的数量等于1019，应该接近1018.
--//说明该脚本测试到目前为止正确，块阀值=_small_table_threshold。
--//重启数据库。
SYS@book> @ hidez _serial_direct_read|^_small_table_threshold$|^filesystemio_options$
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 SETALL SETALL 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
2.测试：
--//测试前，使用cachestats观察OS缓存的情况，关于nocache的安装可以看以前的链接。
$ cachestats /u01/oradata/BOOK/book01p/users01.dbf
/u01/oradata/BOOK/book01p/users01.dbf pages in cache: 0/123522 (0.0%) [filesize=494088.0K, pagesize=4K]
--//可以发现os并不缓存数据文件。
--//session 1:
SCOTT@book01p> @ spid
==============================
SID : 141
SERIAL# : 30834
PROCESS : 4533
SERVER : DEDICATED
SPID : 4535
PID : 49
P_SERIAL# : 4
KILL_COMMAND : alter system kill session '141,30834' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ ses2z 141 "physical reads direct"
no rows selected
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
995
--//window 1:
$ strace -Ttt -fp 4535 -e pread,io_submit -y
Process 4535 attached
16:45:57.100296 pread(256, "\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 <0.011658>
16:45:57.113936 pread(259, "#\242\0\0\272\1\0\3\305 ,\4\0\0\1\4]G\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 8192, 3620864) = 8192 <0.012638>
16:45:57.126953 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:106496, offset:3629056}}) = 1 <0.000155>
16:45:57.127253 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:122880, offset:3743744}}) = 1 <0.000104>
16:45:57.128189 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:122880, offset:3874816}}) = 1 <0.000082>
16:45:57.129535 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:122880, offset:4005888}}) = 1 <0.000074>
16:45:57.130521 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:57344, offset:4136960}}) = 1 <0.000070>
16:45:57.131418 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:65536, offset:142606336}}) = 1 <0.000069>
16:45:57.131929 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:122880, offset:142680064}}) = 1 <0.000084>
16:45:57.135262 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:122880, offset:142811136}}) = 1 <0.000074>
16:45:57.136169 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:57344, offset:142942208}}) = 1 <0.000118>
16:45:57.138413 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:65536, offset:143261696}}) = 1 <0.000073>
16:45:57.138895 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:14696448}}) = 1 <0.000259>
16:45:57.141185 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:524288, offset:15204352}}) = 1 <0.000259>
16:45:57.155547 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:15745024}}) = 1 <0.000164>
16:45:57.160333 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:524288, offset:16252928}}) = 1 <0.000134>
16:45:57.164141 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:16793600}}) = 1 <0.000091>
16:45:57.169064 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:524288, offset:17301504}}) = 1 <0.000129>
16:45:57.172710 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:17842176}}) = 1 <0.000165>
16:45:57.177886 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:524288, offset:18350080}}) = 1 <0.000105>
16:45:57.181714 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:18890752}}) = 1 <0.000137>
16:45:57.186514 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:524288, offset:19398656}}) = 1 <0.000102>
16:45:57.190232 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:19939328}}) = 1 <0.000124>
16:45:57.193926 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:524288, offset:20447232}}) = 1 <0.000106>
16:45:57.198899 io_submit(140374611800064, 1, {{data:0x7fab7cd80e40, pread, filedes:259, buf:0x7fab7cb6e000, nbytes:507904, offset:20987904}}) = 1 <0.000128>
16:45:57.202726 io_submit(140374611800064, 1, {{data:0x7fab7cd81570, pread, filedes:259, buf:0x7fab7cbfe000, nbytes:483328, offset:21495808}}) = 1 <0.000146>
^CProcess 4535 detached
--//第1次执行采用直接路径读。
$ cachestats /u01/oradata/BOOK/book01p/users01.dbf
/u01/oradata/BOOK/book01p/users01.dbf pages in cache: 0/123522 (0.0%) [filesize=494088.0K, pagesize=4K]
--//可以发现OS并不缓存数据文件。
--//session 1:
SCOTT@book01p> @ ses2z 141 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
141 physical reads direct 995
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
995
SCOTT@book01p> @ ses2z 141 "physical reads direct"
SID NAME VALUE
---------- ------------------------------ ----------
141 physical reads direct 995
--//第2次执行并没有采用直接路径读。
--//window 1:
$ strace -Ttt -fp 4535 -e pread,io_submit -y
Process 4535 attached
16:47:35.054255 pread(259, "\6\242\0\0\273\1\0\0035\264,\4\0\0\1\6\330U\0\0\1\0\0\0\372\354\2\0\237 ,\4"..., 40960, 3629056) = 40960 <0.006724>
16:47:35.061322 pread(259, "\6\242\0\0\300\1\0\0035\264,\4\0\0\1\6\201D\0\0\1\0\0\0\372\354\2\0\2600,\4"..., 65536, 3670016) = 65536 <0.000912>
16:47:35.062394 pread(259, "\6\242\0\0\311\1\0\0035\264,\4\0\0\1\6+\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 3743744) = 57344 <0.000982>
16:47:35.063539 pread(259, "\6\242\0\0\320\1\0\0035\264,\4\0\0\1\6;\360\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 3801088) = 65536 <0.008656>
16:47:35.072355 pread(259, "\6\242\0\0\331\1\0\0035\264,\4\0\0\1\6\32\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 3874816) = 57344 <0.000866>
16:47:35.073424 pread(259, "\6\242\0\0\340\1\0\0035\264,\4\0\0\1\6(\360\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 3932160) = 65536 <0.000922>
16:47:35.074528 pread(259, "\6\242\0\0\351\1\0\0035\264,\4\0\0\1\0063\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 4005888) = 57344 <0.000890>
16:47:35.075595 pread(259, "\6\242\0\0\360\1\0\0035\264,\4\0\0\1\6-\360\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 4063232) = 65536 <0.000998>
16:47:35.076811 pread(259, "\6\242\0\0\371\1\0\0035\264,\4\0\0\1\6,\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 4136960) = 57344 <0.000729>
16:47:35.077767 pread(259, "\6\242\0\0\0D\0\0035\264,\4\0\0\1\6\334\265\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 142606336) = 65536 <0.008438>
16:47:35.086462 pread(259, "\6\242\0\0\tD\0\0035\264,\4\0\0\1\6\35\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 142680064) = 57344 <0.000778>
16:47:35.087472 pread(259, "\6\242\0\0\20D\0\0035\264,\4\0\0\1\6%\360\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 142737408) = 65536 <0.001017>
16:47:35.088667 pread(259, "\6\242\0\0\31D\0\0035\264,\4\0\0\1\0064\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 142811136) = 57344 <0.000912>
16:47:35.089755 pread(259, "\6\242\0\0 D\0\0035\264,\4\0\0\1\6\26\360\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 142868480) = 65536 <0.000881>
16:47:35.090813 pread(259, "\6\242\0\0)D\0\0035\264,\4\0\0\1\6'\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 57344, 142942208) = 57344 <0.001029>
16:47:35.092090 pread(259, "\6\242\0\0PD\0\0035\264,\4\0\0\1\6g\360\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 65536, 143261696) = 65536 <0.000883>
16:47:35.093334 pread(259, "\6\242\0\0\2\7\0\0035\264,\4\0\0\1\6;\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 14696448) = 827392 <0.016184>
16:47:35.110120 pread(259, "\6\242\0\0g\7\0\0035\264,\4\0\0\1\6D\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 204800, 15523840) = 204800 <0.001488>
16:47:35.111976 pread(259, "\6\242\0\0\202\7\0\0035\264,\4\0\0\1\0063\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 15745024) = 827392 <0.006372>
16:47:35.118997 pread(259, "\6\242\0\0\347\7\0\0035\264,\4\0\0\1\6b\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 204800, 16572416) = 204800 <0.001658>
16:47:35.121065 pread(259, "\6\242\0\0\2\10\0\0035\264,\4\0\0\1\0067\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 16793600) = 827392 <0.004740>
16:47:35.126458 pread(259, "\6\242\0\0g\10\0\0035\264,\4\0\0\1\6D\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 204800, 17620992) = 204800 <0.001964>
16:47:35.128835 pread(259, "\6\242\0\0\202\10\0\0035\264,\4\0\0\1\6\7\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 17842176) = 827392 <0.005709>
16:47:35.135273 pread(259, "\6\242\0\0\347\10\0\0035\264,\4\0\0\1\6^\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 204800, 18669568) = 204800 <0.001823>
16:47:35.137464 pread(259, "\6\242\0\0\2\t\0\0035\264,\4\0\0\1\6)\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 18890752) = 827392 <0.005688>
16:47:35.143813 pread(259, "\6\242\0\0g\t\0\0035\264,\4\0\0\1\6T\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 204800, 19718144) = 204800 <0.001492>
16:47:35.145726 pread(259, "\6\242\0\0\202\t\0\0035\264,\4\0\0\1\6%\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 19939328) = 827392 <0.006168>
16:47:35.152540 pread(259, "\6\242\0\0\347\t\0\0035\264,\4\0\0\1\6j\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 204800, 20766720) = 204800 <0.001510>
16:47:35.154451 pread(259, "\6\242\0\0\2\n\0\0035\264,\4\0\0\1\0069\361\0\0\1\0\0\0\372\354\2\0\31 ,\4"..., 827392, 20987904) = 827392 <0.006195>
16:47:35.161411 pread(259, "\6\242\0\0g\n\0\0035\264,\4\0\0\1\6\317U\0\0\1\0\0\0\372\354\2\0\270 ,\4"..., 163840, 21815296) = 163840 <0.001464>
--//第2次执行并没有采用直接路径读。
$ cachestats /u01/oradata/BOOK/book01p/users01.dbf
/u01/oradata/BOOK/book01p/users01.dbf pages in cache: 0/123522 (0.0%) [filesize=494088.0K, pagesize=4K]
--//可以发现OS并不缓存数据文件。
$ find /u01/oradata/BOOK/ -name '*' -type f -print | xargs -IQ cachestats Q
/u01/oradata/BOOK/book01p/system01.dbf pages in cache: 0/79362 (0.0%) [filesize=317448.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/sysaux01.dbf pages in cache: 0/138242 (0.0%) [filesize=552968.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/temp01.dbf pages in cache: 0/76802 (0.0%) [filesize=307208.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/users01.dbf pages in cache: 0/123522 (0.0%) [filesize=494088.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/tsp_audit01.dbf pages in cache: 0/137218 (0.0%) [filesize=548872.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/undotbs01.dbf pages in cache: 0/153602 (0.0%) [filesize=614408.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/system01.dbf pages in cache: 0/71682 (0.0%) [filesize=286728.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/sysaux01.dbf pages in cache: 0/87042 (0.0%) [filesize=348168.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/undotbs01.dbf pages in cache: 0/25602 (0.0%) [filesize=102408.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/temp012024-08-09_09-14-00-460-AM.dbf pages in cache: 0/8962 (0.0%) [filesize=35848.0K, pagesize=4K]
/u01/oradata/BOOK/system01.dbf pages in cache: 0/360962 (0.0%) [filesize=1443848.0K, pagesize=4K]
/u01/oradata/BOOK/sysaux01.dbf pages in cache: 0/473602 (0.0%) [filesize=1894408.0K, pagesize=4K]
/u01/oradata/BOOK/undotbs01.dbf pages in cache: 0/32002 (0.0%) [filesize=128008.0K, pagesize=4K]
/u01/oradata/BOOK/users01.dbf pages in cache: 0/26562 (0.0%) [filesize=106248.0K, pagesize=4K]
/u01/oradata/BOOK/control01.ctl pages in cache: 0/4572 (0.0%) [filesize=18288.0K, pagesize=4K]
/u01/oradata/BOOK/control02.ctl pages in cache: 0/4572 (0.0%) [filesize=18288.0K, pagesize=4K]
/u01/oradata/BOOK/redo02.log pages in cache: 0/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
/u01/oradata/BOOK/redo03.log pages in cache: 0/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
/u01/oradata/BOOK/temp01.dbf pages in cache: 0/60674 (0.0%) [filesize=242696.0K, pagesize=4K]
/u01/oradata/BOOK/redo01.log pages in cache: 0/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
--//没有一个数据文件包括日志文件，控制文件，临时文件使用操作系统缓存。
3.小结：
--//从测试可以发现设置filesystemio_options=setall,与前面测试filesystemio_options=asynch一样，读取数据文件并不全部采用异
--//步IO,直接路径读采用异步IO，并且在设置filesystemio_options=setall的情况下操作系统并不缓存数据文件。
--//数据文件不使用操作系统缓存是一把双刃剑，如果采用setall可以设置sga大一些，另外如果存储数据文件的磁盘IO能力不行，不建
--//议采用直接IO。而且采用操作系统缓存,可能一定程度"缓解"磁盘IO，掩盖数据库自身的性能问题。这部分内容可以参考以前的测试。
--//实际上设置filesystemio_options=setall，非常像asm，操作系统缓存并不会缓存raw设置的文件。这样sga相对可以设置大一些，另
--//外rac需要建立asm实例，消耗一定的内存。
--//顺便贴上filesystemio_options=asynch的数据库情况：
$ find /u01/oradata/BOOK/ -name '*' -type f -print | xargs -IQ cachestats Q
/u01/oradata/BOOK/book01p/system01.dbf pages in cache: 16320/79362 (20.6%) [filesize=317448.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/sysaux01.dbf pages in cache: 494/138242 (0.4%) [filesize=552968.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/temp01.dbf pages in cache: 40/76802 (0.1%) [filesize=307208.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/users01.dbf pages in cache: 40/123522 (0.0%) [filesize=494088.0K, pagesize=4K]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/u01/oradata/BOOK/book01p/tsp_audit01.dbf pages in cache: 40/137218 (0.0%) [filesize=548872.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/undotbs01.dbf pages in cache: 66/153602 (0.0%) [filesize=614408.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/system01.dbf pages in cache: 7842/71682 (10.9%) [filesize=286728.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/sysaux01.dbf pages in cache: 180/87042 (0.2%) [filesize=348168.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/undotbs01.dbf pages in cache: 40/25602 (0.2%) [filesize=102408.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/temp012024-08-09_09-14-00-460-AM.dbf pages in cache: 40/8962 (0.4%) [filesize=35848.0K, pagesize=4K]
/u01/oradata/BOOK/system01.dbf pages in cache: 70320/360962 (19.5%) [filesize=1443848.0K, pagesize=4K]
/u01/oradata/BOOK/sysaux01.dbf pages in cache: 27082/473602 (5.7%) [filesize=1894408.0K, pagesize=4K]
/u01/oradata/BOOK/undotbs01.dbf pages in cache: 88/32002 (0.3%) [filesize=128008.0K, pagesize=4K]
/u01/oradata/BOOK/users01.dbf pages in cache: 40/26562 (0.2%) [filesize=106248.0K, pagesize=4K]
/u01/oradata/BOOK/control01.ctl pages in cache: 1864/4572 (40.8%) [filesize=18288.0K, pagesize=4K]
/u01/oradata/BOOK/control02.ctl pages in cache: 652/4572 (14.3%) [filesize=18288.0K, pagesize=4K]
/u01/oradata/BOOK/redo02.log pages in cache: 168/38401 (0.4%) [filesize=153600.5K, pagesize=4K]
/u01/oradata/BOOK/redo03.log pages in cache: 4/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
/u01/oradata/BOOK/temp01.dbf pages in cache: 40/60674 (0.1%) [filesize=242696.0K, pagesize=4K]
/u01/oradata/BOOK/redo01.log pages in cache: 4/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
SCOTT@book01p> select count(*) from t ;
COUNT(*)
----------
995
$ find /u01/oradata/BOOK/ -name '*' -type f -print | xargs -IQ cachestats Q
/u01/oradata/BOOK/book01p/system01.dbf pages in cache: 17434/79362 (22.0%) [filesize=317448.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/sysaux01.dbf pages in cache: 524/138242 (0.4%) [filesize=552968.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/temp01.dbf pages in cache: 40/76802 (0.1%) [filesize=307208.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/users01.dbf pages in cache: 3984/123522 (3.2%) [filesize=494088.0K, pagesize=4K]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//3984?
/u01/oradata/BOOK/book01p/tsp_audit01.dbf pages in cache: 40/137218 (0.0%) [filesize=548872.0K, pagesize=4K]
/u01/oradata/BOOK/book01p/undotbs01.dbf pages in cache: 68/153602 (0.0%) [filesize=614408.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/system01.dbf pages in cache: 7842/71682 (10.9%) [filesize=286728.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/sysaux01.dbf pages in cache: 180/87042 (0.2%) [filesize=348168.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/undotbs01.dbf pages in cache: 40/25602 (0.2%) [filesize=102408.0K, pagesize=4K]
/u01/oradata/BOOK/pdbseed/temp012024-08-09_09-14-00-460-AM.dbf pages in cache: 40/8962 (0.4%) [filesize=35848.0K, pagesize=4K]
/u01/oradata/BOOK/system01.dbf pages in cache: 70386/360962 (19.5%) [filesize=1443848.0K, pagesize=4K]
/u01/oradata/BOOK/sysaux01.dbf pages in cache: 27082/473602 (5.7%) [filesize=1894408.0K, pagesize=4K]
/u01/oradata/BOOK/undotbs01.dbf pages in cache: 88/32002 (0.3%) [filesize=128008.0K, pagesize=4K]
/u01/oradata/BOOK/users01.dbf pages in cache: 40/26562 (0.2%) [filesize=106248.0K, pagesize=4K]
/u01/oradata/BOOK/control01.ctl pages in cache: 1864/4572 (40.8%) [filesize=18288.0K, pagesize=4K]
/u01/oradata/BOOK/control02.ctl pages in cache: 652/4572 (14.3%) [filesize=18288.0K, pagesize=4K]
/u01/oradata/BOOK/redo02.log pages in cache: 171/38401 (0.4%) [filesize=153600.5K, pagesize=4K]
/u01/oradata/BOOK/redo03.log pages in cache: 4/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
/u01/oradata/BOOK/temp01.dbf pages in cache: 40/60674 (0.1%) [filesize=242696.0K, pagesize=4K]
/u01/oradata/BOOK/redo01.log pages in cache: 4/38401 (0.0%) [filesize=153600.5K, pagesize=4K]
--//可以发现/u01/oradata/BOOK/book01p/users01.dbf缓存的变化。
--//cachestats显示的pages in cache数量存在问题.数据块大小8K，pagesize=4K，最多增加2000个，不知道问题在那里。
--//也许操作系统会预读取相关，另外写一篇分析看看。
# xfs_info /dev/sdb1
meta-data=/dev/sdb1 isize=512 agcount=4, agsize=1310656 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0 spinodes=0
data = bsize=4096 blocks=5242624, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
--//不熟悉xfs文件系统，放弃这部分内容探究。
[20260219]关于设置filesystemio_options=setall的情况.txt
-
lfree
2026-02-22 20:42:51 -
Oracle
- 原创