[20260317]直接路径读决策(11g).txt
[20260317]直接路径读决策(11g).txt
--//前几天测试直接路径读遇到的情况大于_small_table_threshold的情况下全部没有数据缓存的情况下，21c全表扫描第1次采用直接路
--//径读，第2次没有有采用直接路径读，上网查了一些资料，找到链接：
https://fritshoogland.wordpress.com/2015/06/29/investigating-the-full-table-direct-path-buffered-decision/
--//作者遇到与我类似的情况，他的情况实际上第1次也是没有采用直接路径读。不过文章提供一个命令可以判断是否采用直接路径读。
--//拿该命令做一个分析诊断。
--//(nsmtio: non smart IO)
--//先在11g上做一个测试：
1.环境：
SYS@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> @ hidez filesystemio|^_small_table|^_very_large_object_threshold$|_direct_read_decision_statistics_driven
NUM N_HEX NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ---------------------------------------- --------------------------------------------------------- ------------- ------------- ------------ ----- ---------
257 101 filesystemio_options IO operations on filesystem files FALSE NONE NONE FALSE FALSE
838 346 _small_table_threshold lower threshold level of table size for direct reads TRUE 1185 1185 TRUE DEFERRED
839 347 _very_large_object_threshold upper threshold level of object size for direct reads TRUE 500 500 TRUE DEFERRED
2012 7DC _direct_read_decision_statistics_driven enable direct read decision based on optimizer statistics TRUE TRUE TRUE TRUE IMMEDIATE
2.建立测试环境：
SCOTT@book> create table t (v varchar2(100)) pctused 1 pctfree 99 STORAGE(INITIAL 64m) tablespace users;
Table created.
SCOTT@book> INSERT /*+ append */ INTO T SELECT RPAD(to_char(level,'0000'), 100, '*') FROM DUAL CONNECT BY LEVEL <= 1185;
1185 rows created.
SCOTT@book> commit;
Commit complete.
--//每条记录占一块，这样插入1185条记录，表一定大于_small_table_threshold。
--//分析表略。
SCOTT@book> @ 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 1185 1219 0 0 101 2026-03-17 08:47:34 1 DISABLED
3.测试：
--//测试前重启数据库，避免其他因素干扰，主要因为21c下测试全表扫描执行第1次采用直接路径读，第2次不采用直接路径读，后面不
--//管刷新数据缓存，还是共享池，问题无法再现。
SCOTT@book01p> @ desc t
Name Null? Type
----------- -------- -------------
1 V VARCHAR2(100)
--//主要目的取消一些递归。
SCOTT@book> alter session set events 'trace[nsmtio]';
Session altered.
SCOTT@book> select count(*) from T;
COUNT(*)
----------
1185
SCOTT@book> select count(*) from T;
COUNT(*)
----------
1185
SCOTT@book> alter session set events 'trace[nsmtio] off';
Session altered.
SCOTT@book> @ ses2z 10 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
10 physical reads direct 2370
10 table scans (direct read) 2
--//很明显11g没有21c遇到的问题，2次执行都是采用直接路径读。
--//跟踪文件内容如下：
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 89221, objn: 89221
ckpt: 1, nblks: 1219, ntcache: 0, ntdist:0
NSMTIO: Additional Info: VLOT=296450
Object# = 89221, Object_Size = 1219 blocks
SqlId = a3k62kvyuj1th, plan_hash_value = 2966233522, Partition# = 0
*** 2026-03-17 08:50:03.757
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 89221, objn: 89221
ckpt: 1, nblks: 1219, ntcache: 1, ntdist:0
NSMTIO: Additional Info: VLOT=296450
Object# = 89221, Object_Size = 1219 blocks
SqlId = a3k62kvyuj1th, plan_hash_value = 2966233522, Partition# = 0
--//11g记录的信息很少，21c记录的信息非常多，注意一个细节 MTT < OBJECT_SIZE < VLOT，也就是oracle认为在MTT与VLOT之间。
--//_small_table_threshold = 1185
--//nblks 可以猜测表示表块占用数量 1219
--//VLOT=296450 ，该数字的来源是 _db_block_buffers*5
SYS@book> @ hidez _db_block_buffers
SYS@book> @ pr
==============================
NUM : 825
N_HEX : 339
NAME : _db_block_buffers
DESCRIPTION : Number of database blocks cached in memory: hidden parameter
DEFAULT_VALUE : TRUE
SESSION_VALUE : 59290
SYSTEM_VALUE : 59290
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE
PL/SQL procedure successfully completed.
--//59290*5 = 296450 ,也就是oracle 11.2.0.4版本认为大于_small_table_threshold就是中表, 而小于_small_table_threshold就是
--//小表,而21c(也许12c以后就做了改变)。
--//对比前后ntcache值，0->1, 可以猜测表示缓存数据块的数量，也就是执行第1次，仅仅保存1个块在数据缓存。以前已经提到这个是
--//表的段头。
SYS@book> @ bh_obj scott.t
COUNT(*)
----------
1
SYS@book> @ bh_objz scott.t
INST_ID CLASS# FILE# BLOCK# STATUS LOCK_ELEMENT_ADD DIRTY TEMP PING STALE DIRECT NEW
---------- ---------- ---------- ---------- ---------- ---------------- ----- ---- ---- ----- ------ ---
1 4 4 1953 xcur 00 N N N N N N
SYS@book> @ find_objz scott.t '' '' 4
FILE_ID BLOCK_ID END_ID+1 BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
------- -------- ---------- ------- --------------- ----- ------------ --------- ---------- -------------------- ------------ ------ ------ ------
4 1920 10112 8192 TABLE SCOTT T 0 67108864 USERS 4 4 4 1953
--//oracle 11g,根据object_Size大小分成MTT，VLOT分成3段，STT=MTT。
--//OBJECT<MTT 小表
--//MTT<OBJECT<VLOT 中表
--//OBJECT_SIZE>VLOT 大表
--//oracle 21c 根据object_Size大小分成STT，MTT，VLOT分成4段。
--//OBJECT<STT 小表
--//STT<OBJECT<MTT 中小表? 不知道如何命名。
--//MTT<OBJECT<VLOT 中表
--//OBJECT_SIZE>VLOT 大表
--//关于这方面信息看21c下测试。
4.附上测试使用代码：
$ cat bh_objz.sql
column STATUS format a10
column DIRTY format a5
column TEMP format a4
column PING format a4
column STALE format a5
column DIRECT format a6
column NEW format a3
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))
-- WHERE owner = 'SCOTT' AND object_name = '&1'
AND status != 'free'
ORDER BY inst_id;
--//前几天测试直接路径读遇到的情况大于_small_table_threshold的情况下全部没有数据缓存的情况下，21c全表扫描第1次采用直接路
--//径读，第2次没有有采用直接路径读，上网查了一些资料，找到链接：
https://fritshoogland.wordpress.com/2015/06/29/investigating-the-full-table-direct-path-buffered-decision/
--//作者遇到与我类似的情况，他的情况实际上第1次也是没有采用直接路径读。不过文章提供一个命令可以判断是否采用直接路径读。
--//拿该命令做一个分析诊断。
--//(nsmtio: non smart IO)
--//先在11g上做一个测试：
1.环境：
SYS@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> @ hidez filesystemio|^_small_table|^_very_large_object_threshold$|_direct_read_decision_statistics_driven
NUM N_HEX NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ---------------------------------------- --------------------------------------------------------- ------------- ------------- ------------ ----- ---------
257 101 filesystemio_options IO operations on filesystem files FALSE NONE NONE FALSE FALSE
838 346 _small_table_threshold lower threshold level of table size for direct reads TRUE 1185 1185 TRUE DEFERRED
839 347 _very_large_object_threshold upper threshold level of object size for direct reads TRUE 500 500 TRUE DEFERRED
2012 7DC _direct_read_decision_statistics_driven enable direct read decision based on optimizer statistics TRUE TRUE TRUE TRUE IMMEDIATE
2.建立测试环境：
SCOTT@book> create table t (v varchar2(100)) pctused 1 pctfree 99 STORAGE(INITIAL 64m) tablespace users;
Table created.
SCOTT@book> INSERT /*+ append */ INTO T SELECT RPAD(to_char(level,'0000'), 100, '*') FROM DUAL CONNECT BY LEVEL <= 1185;
1185 rows created.
SCOTT@book> commit;
Commit complete.
--//每条记录占一块，这样插入1185条记录，表一定大于_small_table_threshold。
--//分析表略。
SCOTT@book> @ 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 1185 1219 0 0 101 2026-03-17 08:47:34 1 DISABLED
3.测试：
--//测试前重启数据库，避免其他因素干扰，主要因为21c下测试全表扫描执行第1次采用直接路径读，第2次不采用直接路径读，后面不
--//管刷新数据缓存，还是共享池，问题无法再现。
SCOTT@book01p> @ desc t
Name Null? Type
----------- -------- -------------
1 V VARCHAR2(100)
--//主要目的取消一些递归。
SCOTT@book> alter session set events 'trace[nsmtio]';
Session altered.
SCOTT@book> select count(*) from T;
COUNT(*)
----------
1185
SCOTT@book> select count(*) from T;
COUNT(*)
----------
1185
SCOTT@book> alter session set events 'trace[nsmtio] off';
Session altered.
SCOTT@book> @ ses2z 10 "physical reads direct|table scans \(direct read\)"
SID NAME VALUE
---------- ------------------------------ ----------
10 physical reads direct 2370
10 table scans (direct read) 2
--//很明显11g没有21c遇到的问题，2次执行都是采用直接路径读。
--//跟踪文件内容如下：
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 89221, objn: 89221
ckpt: 1, nblks: 1219, ntcache: 0, ntdist:0
NSMTIO: Additional Info: VLOT=296450
Object# = 89221, Object_Size = 1219 blocks
SqlId = a3k62kvyuj1th, plan_hash_value = 2966233522, Partition# = 0
*** 2026-03-17 08:50:03.757
NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
NSMTIO: kcbdpc:DirectRead: tsn: 4, objd: 89221, objn: 89221
ckpt: 1, nblks: 1219, ntcache: 1, ntdist:0
NSMTIO: Additional Info: VLOT=296450
Object# = 89221, Object_Size = 1219 blocks
SqlId = a3k62kvyuj1th, plan_hash_value = 2966233522, Partition# = 0
--//11g记录的信息很少，21c记录的信息非常多，注意一个细节 MTT < OBJECT_SIZE < VLOT，也就是oracle认为在MTT与VLOT之间。
--//_small_table_threshold = 1185
--//nblks 可以猜测表示表块占用数量 1219
--//VLOT=296450 ，该数字的来源是 _db_block_buffers*5
SYS@book> @ hidez _db_block_buffers
SYS@book> @ pr
==============================
NUM : 825
N_HEX : 339
NAME : _db_block_buffers
DESCRIPTION : Number of database blocks cached in memory: hidden parameter
DEFAULT_VALUE : TRUE
SESSION_VALUE : 59290
SYSTEM_VALUE : 59290
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE
PL/SQL procedure successfully completed.
--//59290*5 = 296450 ,也就是oracle 11.2.0.4版本认为大于_small_table_threshold就是中表, 而小于_small_table_threshold就是
--//小表,而21c(也许12c以后就做了改变)。
--//对比前后ntcache值，0->1, 可以猜测表示缓存数据块的数量，也就是执行第1次，仅仅保存1个块在数据缓存。以前已经提到这个是
--//表的段头。
SYS@book> @ bh_obj scott.t
COUNT(*)
----------
1
SYS@book> @ bh_objz scott.t
INST_ID CLASS# FILE# BLOCK# STATUS LOCK_ELEMENT_ADD DIRTY TEMP PING STALE DIRECT NEW
---------- ---------- ---------- ---------- ---------- ---------------- ----- ---- ---- ----- ------ ---
1 4 4 1953 xcur 00 N N N N N N
SYS@book> @ find_objz scott.t '' '' 4
FILE_ID BLOCK_ID END_ID+1 BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
------- -------- ---------- ------- --------------- ----- ------------ --------- ---------- -------------------- ------------ ------ ------ ------
4 1920 10112 8192 TABLE SCOTT T 0 67108864 USERS 4 4 4 1953
--//oracle 11g,根据object_Size大小分成MTT，VLOT分成3段，STT=MTT。
--//OBJECT<MTT 小表
--//MTT<OBJECT<VLOT 中表
--//OBJECT_SIZE>VLOT 大表
--//oracle 21c 根据object_Size大小分成STT，MTT，VLOT分成4段。
--//OBJECT<STT 小表
--//STT<OBJECT<MTT 中小表? 不知道如何命名。
--//MTT<OBJECT<VLOT 中表
--//OBJECT_SIZE>VLOT 大表
--//关于这方面信息看21c下测试。
4.附上测试使用代码：
$ cat bh_objz.sql
column STATUS format a10
column DIRTY format a5
column TEMP format a4
column PING format a4
column STALE format a5
column DIRECT format a6
column NEW format a3
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))
-- WHERE owner = 'SCOTT' AND object_name = '&1'
AND status != 'free'
ORDER BY inst_id;
浙公网安备 33010602011771号