[20260213]测试直接路径读的阈值(11g).txt

[20260213]测试直接路径读的阈值(11g).txt --//测试直接路径读遇到一些问题，先在11g下测试看看。 --//别人测试直接路径读遇到一些问题，使用如下链接还提供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; / 1.环境： SCOTT@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 _small_table_threshold SYS@book> @ pr ============================== NUM                           : 838 N_HEX                         :   346 NAME                          : _small_table_threshold DESCRIPTION                   : lower threshold level of table size for direct reads DEFAULT_VALUE                 : TRUE SESSION_VALUE                 : 1205 SYSTEM_VALUE                  : 1205 ISSES_MODIFIABLE              : TRUE ISSYS_MODIFIABLE              : DEFERRED PL/SQL procedure successfully completed. --//1205 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@book> grant select on sys.V_$SEGMENT_STATISTICS 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; / SCOTT@book> @ m8.txt 1000 4000 1170 PL/SQL procedure successfully completed. --//实际上记录的1170+10表示插入1180条记录，如果加上段头，L1，L2的数量，应该接近1205. --//这个测试有一个小问题，没有分析表，表T的分析数据是空的，我反复测试中就遇到这个问题，差点误导。 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                                                                                  1           DISABLED --//分析表后统计如下： 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          1180          1208         0      0    101 2026-02-14 09:23:06          1           DISABLED --//看blocks数量就非常接近了1205。 --//再次执行就存在误导的情况。 SCOTT@book> @ m8.txt 10 4000 10 PL/SQL procedure successfully completed. --//因为即使truncate 表T，但是统计信息并不会清除。 SCOTT@book> truncate table t; Table truncated. 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          1180          1208         0      0    101 2026-02-14 09:23:06          1           DISABLED --//换一句话讲测试前不能有任何表相关统计信息。 --//再次分析表后的情况。 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             0             0         0      0      0 2026-02-14 09:31:30          1           DISABLED SCOTT@book> @ m8.txt 10 4000 4000 PL/SQL procedure successfully completed. --//已经到最大值，实际上没有测试到直接路径读的情况。 --//仅仅删除统计信息后再测试才有效。 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@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                                                                                  1           DISABLED --//再次测试： SCOTT@book> @ m8.txt 998 4000 1168 PL/SQL procedure successfully completed. SCOTT@book> 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@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          1178          1206         0      0    101 2026-02-14 09:39:01          1           DISABLED