[20260413]FOR ALL COLUMNS SIZE repeat(21c).txt
--//生产系统19c遇到直方图仅仅出现bucket数量小于NUM_DISTINCT的情况，按照桶数量没有超过254桶，应该建立
--//top frequency histogram或者frequency histogram，而实际上建立的hybrid histogram，导致返回cards存在较大误差。
--//还好记忆以前遇到设置METHOD_OPT=FOR ALL COLUMNS SIZE REPEAT时，会出现这样情况。
--//参看以前笔记，找到链接https://jonathanlewis.wordpress.com/2017/06/01/histogram-upgrade-2/,
--//解决问题很简单，重新分析该字段，加大桶数量等于254，问题消失。
--//顺便在21c测试该问题是否已经解决。
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.
2.测试例子建立：
SCOTT@book01p> create table t (id number,pad varchar2(20),flag varchar2(1));
Table created.
SCOTT@book01p> insert into t select rownum,lpad('a',20,'a') ,'a' from dual connect by level<=2e4;
20000 rows created.
SCOTT@book01p> insert into t select rownum+2e4,lpad('d',20,'d') ,'d' from dual connect by level<=2;
2 rows created.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> exec dbms_stats.Gather_table_stats(ownname => user, tabname => 'T',method_opt => 'FOR ALL COLUMNS ');
PL/SQL procedure successfully completed.
SCOTT@book01p> SELECT flag, COUNT(*) FROM t GROUP BY flag;
F COUNT(*)
- ----------
a 20000
d 2
SCOTT@book01p> @ desczz t flag
eXtended describe of t
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .
SCOTT@book01p> @ pr
==============================
DESC_OWNER : SCOTT
DESC_TABLE_NAME : T
SAMPLE_SIZE : 20002
LAST_ANALYZED : 2026-04-13 09:55:45
DESC_COLUMN_ID : 3
DESC_COLUMN_NAME : FLAG
DESC_NULLABLE :
DESC_DATA_TYPE : VARCHAR2(1)
NUM_DISTINCT : 2
DESC_DENSITY : .000024997500249975
NUM_NULLS : 0
HISTOGRAM : FREQUENCY
NUM_BUCKETS : 2
TRANS_LOW : a
TRANS_HIGH : d
PL/SQL procedure successfully completed.
--//建立了频率直方图.bucket=2.
SCOTT@test01p> set numw 36
SCOTT@book01p> select * from USER_TAB_HISTOGRAMS where column_name='FLAG';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE ENDPOINT_ACTUAL_VALUE_RAW ENDPOINT_REPEAT_COUNT SCOPE
---------- ----------- --------------- ------------------------------------ --------------------- ------------------------- --------------------- -------
T FLAG 20000 503652795277878000000000000000000000 0 SHARED
T FLAG 20002 519229685853483000000000000000000000 0 SHARED
--//如果应用模式发生变化,加入了2个值，导致字段flag的数据分别发生变化。
SCOTT@book01p> insert into t select rownum+2e4+2,lpad('b',20,'b') ,'b' from dual connect by level<=2e4;
20000 rows created.
SCOTT@book01p> insert into t select rownum+4e4+2,lpad('c',20,'c') ,'c' from dual connect by level<=2;
2 rows created.
SCOTT@book01p> commit;
Commit complete.
--//重新分析表：
SCOTT@book01p> exec dbms_stats.Gather_table_stats(ownname => user, tabname => 'T',method_opt => 'FOR ALL COLUMNS size repeat',No_Invalidate => false);
PL/SQL procedure successfully completed.
--//加入No_Invalidate => false,主要使执行计划能马上使用新的统计信息.
SCOTT@book01p> @ desczz t flag
SCOTT@book01p> @ pr
==============================
DESC_OWNER : SCOTT
DESC_TABLE_NAME : T
SAMPLE_SIZE : 5502
LAST_ANALYZED : 2026-04-13 10:02:07
DESC_COLUMN_ID : 3
DESC_COLUMN_NAME : FLAG
DESC_NULLABLE :
DESC_DATA_TYPE : VARCHAR2(1)
NUM_DISTINCT : 4
DESC_DENSITY : .25
NUM_NULLS : 0
HISTOGRAM : HYBRID
NUM_BUCKETS : 2
TRANS_LOW : a
TRANS_HIGH : d
PL/SQL procedure successfully completed.
--//因为bucket桶没有变化还是2,直方图变成了HYBRID.
SCOTT@book01p> select * from USER_TAB_HISTOGRAMS where table_name='T' and column_name='FLAG';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE ENDPOINT_ACTUAL_VALUE_RAW ENDPOINT_REPEAT_COUNT SCOPE
---------- ----------- --------------- ------------------------------------ --------------------- ------------------------- --------------------- -------
T FLAG 2770 503652795277878000000000000000000000 2770 SHARED
T FLAG 5502 519229685853483000000000000000000000 1 SHARED
3.如果查询条件出现flag='c'看看情况：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> @ cntg t flag
select count(*) , flag from t group by flag order by 1 desc;
COUNT(*) F
------------------------------------ -
20000 a
20000 b
2 d
2 c
SCOTT@book01p> select * from t where flag='c';
ID PAD F
----- -------------------- -
40003 cccccccccccccccccccc c
40004 cccccccccccccccccccc c
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g3pmd0h5vnw5r, child number 0
-------------------------------------
select * from t where flag='c'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 69 (100)| | 2 |00:00:00.01 | 191 |
|* 1 | TABLE ACCESS FULL| T | 1 | 6621 | 181K| 69 (2)| 00:00:01 | 2 |00:00:00.01 | 191 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='c')
23 rows selected.
--//可以发现E-rows 与 A-rows 存在很大的差距,正是业务模式发生了变化,导致使用method_opt => 'FOR ALL COLUMNS size repeat'存在问题.
--//这个问题在11G是不存在的,11g不会建立混合直方图，在使用method_opt => 'FOR ALL COLUMNS size repeat',会增加bucket的数量.
--//如果应用不存在这样的问题,估计关系不大.
--//简单的使用如下命令可以确定,"可能"有问题的直方图.
SCOTT@book01p> SELECT * FROM DBA_TAB_COL_STATISTICS WHERE owner = USER and table_name ='T' and NUM_DISTINCT<>NUM_BUCKETS and NUM_DISTINCT<254;
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN HISTOGRAM SCOPE
----- ---------- ----------- ------------ ---------- ---------- ------- --------- ----------- ------------------- ----------- ------------ ---------- ----------- --------- -------
SCOTT T PAD 4 6161616161 6464646464 .249994 0 2 2026-04-13 10:59:31 40004 YES NO 21 HYBRID SHARED
6161616161 6464646464
6161616161 6464646464
6161616161 6464646464
SCOTT T FLAG 4 61 64 .249994 0 2 2026-04-13 10:59:31 40004 YES NO 2 HYBRID SHARED
--//如果不指定repeat，采用method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE auto'分析：
SCOTT@test01p> exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE auto', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from USER_TAB_HISTOGRAMS where table_name='T' and column_name='FLAG';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A ENDPOINT_ACTUAL_VALU ENDPOINT_REPEAT_COUNT SCOPE
---------- -------------------- --------------- ------------------------------------ ---------- -------------------- --------------------- -------
T FLAG 20000 503652795277878000000000000000000000 0 SHARED
T FLAG 40000 508845092136413000000000000000000000 0 SHARED
T FLAG 40002 514037388994948000000000000000000000 0 SHARED
T FLAG 40004 519229685853483000000000000000000000 0 SHARED
--//分析使用No_Invalidate => false,这样重新执行sql会重新分析.
SCOTT@book01p> @ desczz t flag
SCOTT@book01p> @ pr
==============================
DESC_OWNER : SCOTT
DESC_TABLE_NAME : T
SAMPLE_SIZE : 40004
LAST_ANALYZED : 2026-04-13 10:11:17
DESC_COLUMN_ID : 3
DESC_COLUMN_NAME : FLAG
DESC_NULLABLE :
DESC_DATA_TYPE : VARCHAR2(1)
NUM_DISTINCT : 4
DESC_DENSITY : .0000124987501249875
NUM_NULLS : 0
HISTOGRAM : FREQUENCY
NUM_BUCKETS : 4
TRANS_LOW : a
TRANS_HIGH : d
PL/SQL procedure successfully completed.
--//这样建立的直方图=FREQUENCY,bucket的数量等于4个。
SCOTT@book01p> select * from t where flag='c';
ID PAD F
----- -------------------- -
40003 cccccccccccccccccccc c
40004 cccccccccccccccccccc c
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g3pmd0h5vnw5r, child number 1
-------------------------------------
select * from t where flag='c'
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 69 (100)| | 2 |00:00:00.01 | 191 |
|* 1 | TABLE ACCESS FULL| T | 1 | 2 | 56 | 69 (2)| 00:00:01 | 2 |00:00:00.01 | 191 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='c')
23 rows selected.
--//这样就ok了.
--//对于这样的情况最佳的方式手工设置分析参数。
BEGIN
dbms_stats.Set_table_prefs(user, 'T', 'METHOD_OPT'=>'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SIZE AUTO flag');
END;
/
--//避免这个问题. 总之19c，21c要注意分析方法METHOD_OPT'=>'FOR ALL COLUMNS SIZE repeat'带来的问题.
--//特别是一些应用模式发生变化的情况.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3116161/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
