[20260219]关于设置filesystemio_options=setall的情况.txt

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

[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</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 <0.011658> 16:45:57.113936 pread(259</u01/oradata/BOOK/book01p/users01.dbf>, "#\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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</u01/oradata/BOOK/book01p/users01.dbf>, "\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

编辑推荐：

相关推荐

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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推