[20260215]测试直接路径读的阈值(21c).txt

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

[20260215]测试直接路径读的阈值(21c).txt --//别人测试直接路径读遇到一些问题，使用如下链接还提供1个测试脚本：http://blog.itpub.net/22034023/viewspace-773483/ --//转抄如下： 11GR1 11GR2 备注块阀值 _small_table_threshold*5 _small_table_threshold 统计信息里记录的表的block数目(11GR2)超过此阀值。 Block cache阀值 50% 50% 少于此阀值脏块阀值 25% 25% 少于此阀值满足以上条件时，Oracle会进行直接路径读取。 Oracle为直接路径读取设置的三个"门槛"，非常的合理：第一个阀值：表大小，太小的表从direct path read中的获益太小。但是特别需要引起你的警惕，如果表上存在统计信息，那么ORACLE会采取表的统计信息中记录的block与_small_table_threshold的设定值来做比较，而不是表的真实大小(dba_segments中记录的值)。这可能导致一些不是你预期的情况发生。如果你的统计信息与表的真实情况差异很大，那么你应该仔细考虑可能发生什么样的结果。如果你的表没有统计信息，ORACLE会依据表的真实大小来决定是否进行direct path read。第二个阀值：脏块阀值，由于direct path read需要出发一个段的检查点，因此脏块太多，刷新脏块可能会导致IO繁忙第三个阀值：表在内存里的cache率,如果cache率很高，那么还是走传统路径更快。direct path read的出现，需要让ORACLE公司的开发人员设计一个单独的结构来存储每个表有多少数据是脏数据，有多少数据被cache。不过这个结构目前还并未暴露给我们查询。在flush buffer cache后，这个结构被清空。(flush shared_pool并不会被清空) CREATE OR REPLACE FUNCTION GET_ADR_TRSH(P_STEP IN NUMBER, P_START IN NUMBER DEFAULT 0, P_STOP IN NUMBER DEFAULT NULL) RETURN NUMBER IS L_PRD NUMBER; L_CNT NUMBER; L_BLOCKS NUMBER := 0; L_START NUMBER := P_START; BEGIN EXECUTE IMMEDIATE 'truncate table t'; LOOP INSERT /*+ append */ INTO T SELECT RPAD('*', 100, '*') FROM DUAL CONNECT BY LEVEL <= P_STEP + L_START; COMMIT; L_BLOCKS := L_BLOCKS + P_STEP + L_START; L_START := 0; EXECUTE IMMEDIATE 'alter system flush buffer_cache'; SELECT /*+ full(t) */ COUNT(*) INTO L_CNT FROM T; SELECT VALUE INTO L_PRD FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct'; EXIT WHEN(L_PRD > 0 OR L_BLOCKS > NVL(P_STOP, L_BLOCKS)); END LOOP; RETURN L_BLOCKS - P_STEP; END; / --//遇到的问题是使用该脚本在19c测试无效，重复验证测试。 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 ^_small_table_threshold$ SYS@book> @ pr ============================== NUM : 1867 N_HEX : 74B CON_ID : 0 NAME : _small_table_threshold DESCRIPTION : lower threshold level of table size for direct reads DEFAULT_VALUE : TRUE SESSION_VALUE : 1018 SYSTEM_VALUE : 1018 ISSES_MODIFIABLE : TRUE ISSYS_MODIFIABLE : DEFERRED PL/SQL procedure successfully completed. --//1018 2.测试环境建立： 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. --//建立测试函数GET_ADR_TRSH略。 SYS@book01p> grant select on sys.v_$segment_statistics to scott; Grant succeeded. SYS@book01p> grant alter system to scott; Grant succeeded. $ cat m8.txt set serveroutput on DECLARE L_TRSH NUMBER; BEGIN L_TRSH := GET_ADR_TRSH(10, &&1, &&2); DBMS_OUTPUT.PUT_LINE(L_TRSH); END; / 3.测试： --//问题1：验证测试无法获取块阈值。 SCOTT@book01p> @ m8.txt 895 8000 7995 PL/SQL procedure successfully completed. SCOTT@book01p> @ ses2z 146 "physical reads direct" no rows selected --//实际上没有1次physical reads direct，因为已经测试到最大值，这是遇到的第1个问题。 --//插入后并没有分析表，如果仔细阅读脚本可以发现采用直接append插入数据，第1次插入P_START+10条，后续每次在插入10条。实际 --//上第1次插入已经存在统计信息。 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 905 929 0 0 101 2026-02-15 16:09:38 1 DISABLED --//仅仅记录了第1次执行插入的统计信息(895+10=905)，以后的查询以该信息决定是否采用直接路径读，还没有到达块阈值。 --//这样即使后面继续有数据插入(每次10条)，也不会采用直接路径读。 --//这是因为12c开始采用这类加载模式自动生成统计信息。而以前测试每次执行时都是动态取样获取统计信息。 SCOTT@book01p> drop table t purge ; Table dropped. SCOTT@book01p> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace users; Table created. SCOTT@book01p> alter session set "_optimizer_gather_stats_on_load"=false; Session altered. --//规避统计信息加载。 SCOTT@book01p> @ m8.txt 895 8000 985 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 1 DISABLED --//测试完成也没有统计信息。 SCOTT@book01p> exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>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 4.第2个问题提出： --//现在分析表，如果没有其他用户加载相关表T数据块，理论每次都是执行全表扫描都采用直接路径读？ --//避免其他干扰因素，重启数据库再测试。 SCOTT@book01p> column value format 9999999 SCOTT@book01p> @ spid ============================== SID : 272 SERIAL# : 2461 PROCESS : 4759 SERVER : DEDICATED SPID : 4761 PID : 62 P_SERIAL# : 4 KILL_COMMAND : alter system kill session '272,2461' immediate; PL/SQL procedure successfully completed. SCOTT@book01p> @ ses2z 272 "physical reads direct" no rows selected SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 995 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 995 --//直接路径读，奇怪这里仅仅记录数据块执行直接路径读的次数，实际上全表扫描读取段头，可以确定该扫描那些数据块，并没有读 --//L1，L2. SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 995 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 995 --//可以发现没有变化，也就是第2次执行没有采用直接路径读。 SCOTT@book01p> @ bh_obj scott.t COUNT(*) ---------- 994 --//表T数据块已经加载到数据库缓存。 SCOTT@book01p> alter system flush buffer_cache; System altered. SCOTT@book01p> @ bh_obj scott.t COUNT(*) ---------- 0 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 995 SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 995 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 995 --//刷新数据缓存后，再次执行依旧没有采用直接路径读，为什么？ 5.继续分析： SCOTT@book01p> @ modtab scott.t numblks 8000 input argument list : owner.table_name table_stat_attribute value table_stat_attribute = numrows numblks avgrlen @desc_proc sys dbms_stats set_table_stats exec dbms_stats.set_table_stats('SCOTT','T',numblks=>8000,NO_INVALIDATE=>false,force=>true); press enter to continue ... PL/SQL procedure successfully completed. --//人为修改统计信息数据块数量8000。 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 995 SCOTT@book01p> alter system flush buffer_cache; System altered. SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 995 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 1990 --//再次采用直接路径读。 SCOTT@book01p> alter system flush buffer_cache; System altered. SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 995 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 2985 SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 995 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 3980 --//修改数据块大小=8000，以后的每次执行都是直接路径读。 --//做一个猜测21c以后，不考虑Block cache阀值，脏块阀值的情况下，oracle改变算法，大于数据块块阀值到某个值之间，第1次执行 --//全表扫描选择直接路径读，而后续的全表扫描并不采用直接路径读.如何确定这个值呢? 6.建立新的脚本: --//以GET_ADR_TRSH为蓝本,修改如下: CREATE OR REPLACE FUNCTION GET_ADR_TRSH1(P_STEP IN NUMBER, P_START IN NUMBER DEFAULT 0, P_STOP IN NUMBER DEFAULT NULL) RETURN NUMBER IS L_PRD NUMBER; L_PRD1 NUMBER; L_PRD2 NUMBER; L_CNT NUMBER; L_BLOCKS NUMBER := 0; L_START NUMBER := P_START; BEGIN EXECUTE IMMEDIATE 'truncate table t'; LOOP INSERT /*+ append */ INTO T SELECT RPAD('*', 100, '*') FROM DUAL CONNECT BY LEVEL <= P_STEP + L_START; COMMIT; L_BLOCKS := L_BLOCKS + P_STEP + L_START; L_START := 0; EXECUTE IMMEDIATE 'alter system flush buffer_cache'; SELECT /*+ full(t) */ COUNT(*) INTO L_CNT FROM T; SELECT VALUE INTO L_PRD1 FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct'; EXECUTE IMMEDIATE 'alter system flush buffer_cache'; SELECT /*+ full(t) */ COUNT(*) INTO L_CNT FROM T; SELECT VALUE INTO L_PRD2 FROM V$SEGMENT_STATISTICS WHERE OWNER = USER AND OBJECT_NAME = 'T' AND STATISTIC_NAME = 'physical reads direct'; L_PRD := L_PRD2 - L_PRD1; EXIT WHEN(L_PRD > 0 OR L_BLOCKS > NVL(P_STOP, L_BLOCKS)); END LOOP; RETURN L_BLOCKS - P_STEP; END; / $ cat m8z.txt set serveroutput on DECLARE L_TRSH NUMBER; BEGIN L_TRSH := GET_ADR_TRSH1(10, &&1, &&2); DBMS_OUTPUT.PUT_LINE(L_TRSH); END; / SCOTT@book01p> alter session set "_optimizer_gather_stats_on_load"=false; Session altered. SCOTT@book01p> drop table t purge ; Table dropped. SCOTT@book01p> create table t (v varchar2(100)) pctused 1 pctfree 99 tablespace users; Table created. SCOTT@book01p> @ m8z.txt 895 8000 5575 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 1 DISABLED SCOTT@book01p> exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>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 5585 5683 0 0 101 2026-02-15 17:17:10 1 DISABLED --//blocks=5683. SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 23720 SCOTT@book01p> select count(*) from t; COUNT(*) ---------- 5585 SCOTT@book01p> @ ses2z 272 "physical reads direct" SID NAME VALUE ---------- ------------------------------ -------- 272 physical reads direct 29305 --//这样后续全表扫描才会选择直接路径读方式执行. --//5683/1018 = 5.58251473477406679764,大于5倍，这个数值的来源于那里，实在猜测不出来。 --//还有1个疑问： SYS@book> select count(*) from x$bh; COUNT(*) ---------- 35754 SYS@book01p> select count(*) from x$bh; COUNT(*) ---------- 5552 --//两者存在巨大不同，难道pdb下限制每个数据块缓存的数量吗。 SYS@book> @ hidez _table_threshold NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ---- ----- ---------- --------------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- --------- 1867 74B 0 _small_table_threshold lower threshold level of table size for direct reads TRUE 1018 1018 TRUE DEFERRED 1868 74C 0 _pdb_small_table_threshold lower threshold level of table size for direct reads in a pdb TRUE 20 20 FALSE FALSE 1869 74D 0 _pmem_small_table_threshold lower threshold of PMEM table size as percentage to PMEM cache for TRUE 20 20 TRUE DEFERRED direct reads --//_pdb_small_table_threshold = 20 表示什么，应该是百分比吗？ 7.总结： --//也许从某个版本oracle,oracle改变算法，当数据块数量在块阈值与某个数值之间，全表扫描，第1次执行采用直接路径读，后续的执 --//行并不采用直接路径读。只有大于该数值以后，排除Block cache阀值，脏块阀值的情况下才选择直接路径读。 --//还有1个情况，当数据块数量在块阈值与某个数值之间时，如何操作再次使执行语句采用直接路径读。无论执行： alter system flush buffer_cache; alter system flush shared_pool; 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-16 15:59:08 1 DISABLED SCOTT@book01p> @ ses2z 271 "physical reads direct" no rows selected SCOTT@book01p> select Count(*) from t; COUNT(*) ---------- 1000 SCOTT@book01p> @ ses2z 271 "physical reads direct" no rows selected --//无法如何测试都无法再现直接路径读。 --//如果这类情况不算。这样直接路径读的阈值提高不少，而且如何确定不是很清楚。 8.附上测试脚本： $ cat tpt/ses2z.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- -- File name: ses2.sql (SEssion Statistics 2) -- Purpose: Display Session statistics for given sessions, filter by -- statistic name and show only stats with value > 0 -- -- Author: Tanel Poder -- Copyright: (c) http://www.tanelpoder.com -- -- Usage: @ses2 <sid> <statname> -- @ses2 10 % -- @ses2 10 parse -- @ses2 10,11,12 redo -- @ses2 "select sid from v$session where username = 'APPS'" parse -- -------------------------------------------------------------------------------- select ses.sid, sn.name, ses.value from v$sesstat ses, v$statname sn where sn.statistic# = ses.statistic# and ses.sid in (&1) --and lower(sn.name) like lower('%&2%') and regexp_like (lower(sn.name) ,lower('&2')) and ses.value > 0 / $ 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); $ cat modtab.sql set verify off set termout off column v_owner new_value v_owner column v_table new_value v_table select upper(CASE WHEN INSTR('&1','.') > 0 THEN SUBSTR('&1',INSTR('&1','.')+1) ELSE '&1' END ) v_table, nvl(upper(CASE WHEN INSTR('&1','.') > 0 THEN UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)) ELSE user END),user) v_owner from dual; set termout on prompt prompt input argument list : owner.table_name table_stat_attribute value prompt prompt table_stat_attribute = numrows numblks avgrlen prompt prompt @desc_proc sys dbms_stats set_table_stats prompt prompt exec dbms_stats.set_table_stats('&v_owner','&v_table',&2=>&3,NO_INVALIDATE=>false,force=>true);; prompt pause press enter to continue ... exec dbms_stats.set_table_stats('&v_owner','&v_table',&2=>&3,NO_INVALIDATE=>false,force=>true); prompt

编辑推荐：

返回列表

[20260215]测试直接路径读的阈值(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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推