[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  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: kcbdpcirectRead: 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: kcbdpcirectRead: 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