博客 / 詳情
返回[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
