[20260818]21c X$表的分析与直方图信息2.txt
[20260818]21c X$表的分析与直方图信息2.txt
--//偶然发现这个问题，在测试环境演示出来,这与我的个人维护习惯有关。
1.环境：
SYS@book> @ 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.
SYS@book> select dbms_stats.get_param('method_opt') c40 from dual;
C40
----------------------------------------
FOR ALL COLUMNS SIZE AUTO
--//缺省METHOD_OPT=FOR ALL COLUMNS SIZE AUTO。
--//我个人喜欢修改缺省设置FOR ALL COLUMNS SIZE REPEAT。
SYS@book> exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed.
SYS@book> select dbms_stats.get_param('method_opt') c40 from dual;
C40
----------------------------------------
FOR ALL COLUMNS SIZE REPEAT
2.问题再现：
--//以X$KSUMYSTA为例子说明问题。
--//首先删除统计信息，不采用execute dbms_stats.gather_fixed_objects_stats执行方式，加快测试。
SYS@book> execute sys.dbms_stats.delete_table_stats ('SYS', 'X$KGLDP',cascade_columns=> true ,cascade_indexes=> true, cascade_parts=>true ,no_invalidate=> false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
no rows selected
--//x$kgldp统计信息已经删除。
--//method_opt=>'FOR ALL COLUMNS SIZE repeat'
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED COLUMN_NAME DATA_TYPE DENSITY NUM_DISTINCT NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ----------- --------- ---------- ------------ ---------- ----------- --------- ----------- --------- -----------
SYS X$KGLDP 9001 2026-08-18 15:50:37 ADDR RAW(8) .00257732 388 0 9 NONE 1
9001 2026-08-18 15:50:37 INDX NUMBER .000111099 9001 0 4 NONE 1 0 9000
9001 2026-08-18 15:50:37 INST_ID NUMBER 1 1 0 3 NONE 1 1 1
9001 2026-08-18 15:50:37 CON_ID NUMBER .333333333 3 0 3 NONE 1 1 3
9001 2026-08-18 15:50:37 KGLHDADR RAW(8) .000355366 2814 0 9 NONE 1
9001 2026-08-18 15:50:37 KGLHDPAR RAW(8) .001062699 941 0 6 NONE 1
9001 2026-08-18 15:50:37 KGLNAHSH NUMBER .000582411 1717 0 7 NONE 1 4665964 4290981040
9001 2026-08-18 15:50:37 KGLDEPNO NUMBER .006369427 157 0 3 NONE 1 0 156
9001 2026-08-18 15:50:37 KGLRFHDL RAW(8) .000438982 2278 0 9 NONE 1
9001 2026-08-18 15:50:37 KGLRFHSH NUMBER .000438982 2278 0 7 NONE 1 1193494 4291153365
9001 2026-08-18 15:50:37 KGLRFFLG NUMBER .333333333 3 0 3 NONE 1 1 65
9001 2026-08-18 15:50:37 KGLDPOBJ RAW(8) .000355366 2814 0 9 NONE 1
9001 2026-08-18 15:50:37 KGLDPPOS NUMBER .001715266 583 0 3 NONE 1 0 21592
2026-08-18 15:50:37 KGLDPFGR RAW(2000) 0 0 9001 0 NONE 0
14 rows selected.
--//HISTOGRAM=NONE,x$kgldp各个字段没有建立直方图。
--//注：descx.sql脚本有点长，你测试可以执行select * from dba_tab_col_statistics where owner='SYS' and Table_Name='X$KGLDP'。
--//method_opt=>'FOR ALL COLUMNS SIZE repeat'
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED COLUMN_NAME DATA_TYPE DENSITY NUM_DISTINCT NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ----------- --------- ---------- ------------ ---------- ----------- --------- ----------- ---------- ------------
SYS X$KGLDP 9010 2026-08-18 15:51:59 ADDR RAW(8) .004255 235 0 9 HYBRID 1
9012 2026-08-18 15:51:59 INDX NUMBER .000111 9009 0 4 HYBRID 1 9011 9011
9013 2026-08-18 15:51:59 INST_ID NUMBER .000055475 1 0 3 FREQUENCY 1 1 1
9011 2026-08-18 15:51:59 CON_ID NUMBER .333296 3 0 3 HYBRID 1 3 3
9017 2026-08-18 15:51:59 KGLHDADR RAW(8) .000353 2829 0 9 HYBRID 1
9018 2026-08-18 15:51:59 KGLHDPAR RAW(8) .001046 956 0 6 HYBRID 1
9019 2026-08-18 15:51:59 KGLNAHSH NUMBER .000577 1733 0 7 HYBRID 1 4290981040 4290981040
9014 2026-08-18 15:51:59 KGLDEPNO NUMBER .006369 157 0 3 HYBRID 1 156 156
9021 2026-08-18 15:51:59 KGLRFHDL RAW(8) .000439 2280 0 9 HYBRID 1
9022 2026-08-18 15:51:59 KGLRFHSH NUMBER .000439 2280 0 7 HYBRID 1 4291153365 4291153365
9020 2026-08-18 15:51:59 KGLRFFLG NUMBER .333296 3 0 3 HYBRID 1 65 65
9015 2026-08-18 15:51:59 KGLDPOBJ RAW(8) .000354 2827 0 9 HYBRID 1
9016 2026-08-18 15:51:59 KGLDPPOS NUMBER .001712 584 0 3 HYBRID 1 21592 21592
2026-08-18 15:51:59 KGLDPFGR RAW(2000) 0 0 9009 0 NONE 0
14 rows selected.
--//出现奇怪现象，除了最后1个字段，其他字段都建立直方图，奇怪的是NUM_BUCKETS。这样Low_value，High_value出现奇怪现象。
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED COLUMN_NAME DATA_TYPE DENSITY NUM_DISTINCT NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ----------- --------- ---------- ------------ ---------- ----------- --------- ----------- ---------- -----------
SYS X$KGLDP 9295 2026-08-18 15:55:26 ADDR RAW(8) .000053792 214 0 9 FREQUENCY 214
9297 2026-08-18 15:55:26 INDX NUMBER .000108 9294 0 4 HYBRID 2048 0 9296
9298 2026-08-18 15:55:26 INST_ID NUMBER .000053775 1 0 3 FREQUENCY 1 1 1
9296 2026-08-18 15:55:26 CON_ID NUMBER .000053787 3 0 3 FREQUENCY 3 1 3
9302 2026-08-18 15:55:26 KGLHDADR RAW(8) .000229 2948 0 9 HYBRID 2048
9303 2026-08-18 15:55:26 KGLHDPAR RAW(8) .000053746 996 0 6 FREQUENCY 996
9304 2026-08-18 15:55:26 KGLNAHSH NUMBER .00005374 1791 0 7 FREQUENCY 1791 4665964 4290981040
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9299 2026-08-18 15:55:26 KGLDEPNO NUMBER .000053769 157 0 3 FREQUENCY 157 0 156
9306 2026-08-18 15:55:26 KGLRFHDL RAW(8) .000267 2266 0 9 HYBRID 2048
9307 2026-08-18 15:55:26 KGLRFHSH NUMBER .000265 2266 0 7 HYBRID 2048 1193494 4291153365
9305 2026-08-18 15:55:26 KGLRFFLG NUMBER .000053735 3 0 3 FREQUENCY 3 1 65
9300 2026-08-18 15:55:26 KGLDPOBJ RAW(8) .000229 2946 0 9 HYBRID 2048
9301 2026-08-18 15:55:26 KGLDPPOS NUMBER .000053758 627 0 3 FREQUENCY 627 0 21592
2026-08-18 15:55:26 KGLDPFGR RAW(2000) 0 0 9294 0 NONE 0
14 rows selected.
--//这次分析后除了最后1个字段其他字段都建立直方图，并且bucket的数量选择正确。oracle 21c这样情况下出现NUM_DISTINCT<2048的
--//情况下,会出现NUM_BUCKETS=NUM_DISTINCT的情况，看下划线KGLNAHSH每个桶1个值。
--//也就是如果METHOD_OPT=FOR ALL COLUMNS SIZE REPEAT，采用execute dbms_stats.gather_fixed_objects_stats第2次执行执行出现
--//奇怪现象，第3次执行修正了错误，但是建立大量的直方图信息。
3.看看普通表是否会出现类似问题。
--//而普通表执行就不会出现这样的情况：
SYS@book> create table tt as select * from x$kgldp ;
Table created.
SYS@book> execute sys.dbms_stats.delete_table_stats ('SYS', 'tt',cascade_columns=> true ,cascade_indexes=> true, cascade_parts=>true ,no_invalidate=> false)
PL/SQL procedure successfully completed.
--//仅仅贴出第3次分析后的结果：
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'TT', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descz tt '1=1' ''
eXtended describe of tt
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type # distinct Density # nulls Histogram # buckets Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ---------- --------- -------------- -------------- ------- ---------- --------- --------- -----------
SYS TT 9968 2026-08-18 16:09:55 1 ADDR RAW(8) 169 .00591715976 0 1
9968 2026-08-18 16:09:55 2 INDX NUMBER(,) 9968 .00010032103 0 1 0 9967
9968 2026-08-18 16:09:55 3 INST_ID NUMBER(,) 1 1.00000000000 0 1 1 1
9968 2026-08-18 16:09:55 4 CON_ID NUMBER(,) 3 .33333333333 0 1 1 3
9968 2026-08-18 16:09:55 5 KGLHDADR RAW(8) 3114 .00032113038 0 1
9968 2026-08-18 16:09:55 6 KGLHDPAR RAW(8) 1119 .00089365505 0 1
9968 2026-08-18 16:09:55 7 KGLNAHSH NUMBER(,) 2008 .00049800797 0 1 4665964 4290395122
9968 2026-08-18 16:09:55 8 KGLDEPNO NUMBER(,) 157 .00636942675 0 1 0 156
9968 2026-08-18 16:09:55 9 KGLRFHDL RAW(8) 2806 .00035637919 0 1
9968 2026-08-18 16:09:55 10 KGLRFHSH NUMBER(,) 2806 .00035637919 0 1 1193494 4292443036
9968 2026-08-18 16:09:55 11 KGLRFFLG NUMBER(,) 2 .50000000000 0 1 1 65
9968 2026-08-18 16:09:55 12 KGLDPOBJ RAW(8) 3114 .00032113038 0 1
9968 2026-08-18 16:09:55 13 KGLDPPOS NUMBER(,) 775 .00129032258 0 1 0 101427
2026-08-18 16:09:55 14 KGLDPFGR RAW(2000) 0 .00000000000 9968 0
14 rows selected.
--//这个问题仅仅出现在X$表，普通用户表不会存在该问题。我在11g下重复测试不存在这个问题,感觉更像21c上的一个bug。
--//当然如果不修改METHOD_OPT=FOR ALL COLUMNS SIZE AUTO,就不存在该问题。
3.收尾：
SYS@book> drop table sys.tt purge ;
Table dropped.
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
--//顺便提一下dbms_stats.gather_fixed_objects_stats,无法设置method参数。
SYS@book> @ desc_proc sys dbms_stats gather_fixed_objects_stats
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
Owner PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
----- ------------ ------------------------------ ---------- ------------- -------------- --------- ----------
SYS DBMS_STATS GATHER_FIXED_OBJECTS_STATS 1 STATTAB VARCHAR2 IN Y
2 STATID VARCHAR2 IN Y
3 STATOWN VARCHAR2 IN Y
4 NO_INVALIDATE PL/SQL BOOLEAN IN Y
--//dbms_stats.gather_fixed_objects_stats没有修改method_opt参数。
--//偶然发现这个问题，在测试环境演示出来,这与我的个人维护习惯有关。
1.环境：
SYS@book> @ 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.
SYS@book> select dbms_stats.get_param('method_opt') c40 from dual;
C40
----------------------------------------
FOR ALL COLUMNS SIZE AUTO
--//缺省METHOD_OPT=FOR ALL COLUMNS SIZE AUTO。
--//我个人喜欢修改缺省设置FOR ALL COLUMNS SIZE REPEAT。
SYS@book> exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed.
SYS@book> select dbms_stats.get_param('method_opt') c40 from dual;
C40
----------------------------------------
FOR ALL COLUMNS SIZE REPEAT
2.问题再现：
--//以X$KSUMYSTA为例子说明问题。
--//首先删除统计信息，不采用execute dbms_stats.gather_fixed_objects_stats执行方式，加快测试。
SYS@book> execute sys.dbms_stats.delete_table_stats ('SYS', 'X$KGLDP',cascade_columns=> true ,cascade_indexes=> true, cascade_parts=>true ,no_invalidate=> false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
no rows selected
--//x$kgldp统计信息已经删除。
--//method_opt=>'FOR ALL COLUMNS SIZE repeat'
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED COLUMN_NAME DATA_TYPE DENSITY NUM_DISTINCT NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ----------- --------- ---------- ------------ ---------- ----------- --------- ----------- --------- -----------
SYS X$KGLDP 9001 2026-08-18 15:50:37 ADDR RAW(8) .00257732 388 0 9 NONE 1
9001 2026-08-18 15:50:37 INDX NUMBER .000111099 9001 0 4 NONE 1 0 9000
9001 2026-08-18 15:50:37 INST_ID NUMBER 1 1 0 3 NONE 1 1 1
9001 2026-08-18 15:50:37 CON_ID NUMBER .333333333 3 0 3 NONE 1 1 3
9001 2026-08-18 15:50:37 KGLHDADR RAW(8) .000355366 2814 0 9 NONE 1
9001 2026-08-18 15:50:37 KGLHDPAR RAW(8) .001062699 941 0 6 NONE 1
9001 2026-08-18 15:50:37 KGLNAHSH NUMBER .000582411 1717 0 7 NONE 1 4665964 4290981040
9001 2026-08-18 15:50:37 KGLDEPNO NUMBER .006369427 157 0 3 NONE 1 0 156
9001 2026-08-18 15:50:37 KGLRFHDL RAW(8) .000438982 2278 0 9 NONE 1
9001 2026-08-18 15:50:37 KGLRFHSH NUMBER .000438982 2278 0 7 NONE 1 1193494 4291153365
9001 2026-08-18 15:50:37 KGLRFFLG NUMBER .333333333 3 0 3 NONE 1 1 65
9001 2026-08-18 15:50:37 KGLDPOBJ RAW(8) .000355366 2814 0 9 NONE 1
9001 2026-08-18 15:50:37 KGLDPPOS NUMBER .001715266 583 0 3 NONE 1 0 21592
2026-08-18 15:50:37 KGLDPFGR RAW(2000) 0 0 9001 0 NONE 0
14 rows selected.
--//HISTOGRAM=NONE,x$kgldp各个字段没有建立直方图。
--//注：descx.sql脚本有点长，你测试可以执行select * from dba_tab_col_statistics where owner='SYS' and Table_Name='X$KGLDP'。
--//method_opt=>'FOR ALL COLUMNS SIZE repeat'
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED COLUMN_NAME DATA_TYPE DENSITY NUM_DISTINCT NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ----------- --------- ---------- ------------ ---------- ----------- --------- ----------- ---------- ------------
SYS X$KGLDP 9010 2026-08-18 15:51:59 ADDR RAW(8) .004255 235 0 9 HYBRID 1
9012 2026-08-18 15:51:59 INDX NUMBER .000111 9009 0 4 HYBRID 1 9011 9011
9013 2026-08-18 15:51:59 INST_ID NUMBER .000055475 1 0 3 FREQUENCY 1 1 1
9011 2026-08-18 15:51:59 CON_ID NUMBER .333296 3 0 3 HYBRID 1 3 3
9017 2026-08-18 15:51:59 KGLHDADR RAW(8) .000353 2829 0 9 HYBRID 1
9018 2026-08-18 15:51:59 KGLHDPAR RAW(8) .001046 956 0 6 HYBRID 1
9019 2026-08-18 15:51:59 KGLNAHSH NUMBER .000577 1733 0 7 HYBRID 1 4290981040 4290981040
9014 2026-08-18 15:51:59 KGLDEPNO NUMBER .006369 157 0 3 HYBRID 1 156 156
9021 2026-08-18 15:51:59 KGLRFHDL RAW(8) .000439 2280 0 9 HYBRID 1
9022 2026-08-18 15:51:59 KGLRFHSH NUMBER .000439 2280 0 7 HYBRID 1 4291153365 4291153365
9020 2026-08-18 15:51:59 KGLRFFLG NUMBER .333296 3 0 3 HYBRID 1 65 65
9015 2026-08-18 15:51:59 KGLDPOBJ RAW(8) .000354 2827 0 9 HYBRID 1
9016 2026-08-18 15:51:59 KGLDPPOS NUMBER .001712 584 0 3 HYBRID 1 21592 21592
2026-08-18 15:51:59 KGLDPFGR RAW(2000) 0 0 9009 0 NONE 0
14 rows selected.
--//出现奇怪现象，除了最后1个字段，其他字段都建立直方图，奇怪的是NUM_BUCKETS。这样Low_value，High_value出现奇怪现象。
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descx x$kgldp '' ''
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED COLUMN_NAME DATA_TYPE DENSITY NUM_DISTINCT NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS Low_value High_value
----- ---------- ----------- ------------------- ----------- --------- ---------- ------------ ---------- ----------- --------- ----------- ---------- -----------
SYS X$KGLDP 9295 2026-08-18 15:55:26 ADDR RAW(8) .000053792 214 0 9 FREQUENCY 214
9297 2026-08-18 15:55:26 INDX NUMBER .000108 9294 0 4 HYBRID 2048 0 9296
9298 2026-08-18 15:55:26 INST_ID NUMBER .000053775 1 0 3 FREQUENCY 1 1 1
9296 2026-08-18 15:55:26 CON_ID NUMBER .000053787 3 0 3 FREQUENCY 3 1 3
9302 2026-08-18 15:55:26 KGLHDADR RAW(8) .000229 2948 0 9 HYBRID 2048
9303 2026-08-18 15:55:26 KGLHDPAR RAW(8) .000053746 996 0 6 FREQUENCY 996
9304 2026-08-18 15:55:26 KGLNAHSH NUMBER .00005374 1791 0 7 FREQUENCY 1791 4665964 4290981040
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9299 2026-08-18 15:55:26 KGLDEPNO NUMBER .000053769 157 0 3 FREQUENCY 157 0 156
9306 2026-08-18 15:55:26 KGLRFHDL RAW(8) .000267 2266 0 9 HYBRID 2048
9307 2026-08-18 15:55:26 KGLRFHSH NUMBER .000265 2266 0 7 HYBRID 2048 1193494 4291153365
9305 2026-08-18 15:55:26 KGLRFFLG NUMBER .000053735 3 0 3 FREQUENCY 3 1 65
9300 2026-08-18 15:55:26 KGLDPOBJ RAW(8) .000229 2946 0 9 HYBRID 2048
9301 2026-08-18 15:55:26 KGLDPPOS NUMBER .000053758 627 0 3 FREQUENCY 627 0 21592
2026-08-18 15:55:26 KGLDPFGR RAW(2000) 0 0 9294 0 NONE 0
14 rows selected.
--//这次分析后除了最后1个字段其他字段都建立直方图，并且bucket的数量选择正确。oracle 21c这样情况下出现NUM_DISTINCT<2048的
--//情况下,会出现NUM_BUCKETS=NUM_DISTINCT的情况，看下划线KGLNAHSH每个桶1个值。
--//也就是如果METHOD_OPT=FOR ALL COLUMNS SIZE REPEAT，采用execute dbms_stats.gather_fixed_objects_stats第2次执行执行出现
--//奇怪现象，第3次执行修正了错误，但是建立大量的直方图信息。
3.看看普通表是否会出现类似问题。
--//而普通表执行就不会出现这样的情况：
SYS@book> create table tt as select * from x$kgldp ;
Table created.
SYS@book> execute sys.dbms_stats.delete_table_stats ('SYS', 'tt',cascade_columns=> true ,cascade_indexes=> true, cascade_parts=>true ,no_invalidate=> false)
PL/SQL procedure successfully completed.
--//仅仅贴出第3次分析后的结果：
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'TT', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE repeat', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SYS@book> @ descz tt '1=1' ''
eXtended describe of tt
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type # distinct Density # nulls Histogram # buckets Low_value High_value
----- ---------- ----------- ------------------- ---- ----------- ---------- --------- -------------- -------------- ------- ---------- --------- --------- -----------
SYS TT 9968 2026-08-18 16:09:55 1 ADDR RAW(8) 169 .00591715976 0 1
9968 2026-08-18 16:09:55 2 INDX NUMBER(,) 9968 .00010032103 0 1 0 9967
9968 2026-08-18 16:09:55 3 INST_ID NUMBER(,) 1 1.00000000000 0 1 1 1
9968 2026-08-18 16:09:55 4 CON_ID NUMBER(,) 3 .33333333333 0 1 1 3
9968 2026-08-18 16:09:55 5 KGLHDADR RAW(8) 3114 .00032113038 0 1
9968 2026-08-18 16:09:55 6 KGLHDPAR RAW(8) 1119 .00089365505 0 1
9968 2026-08-18 16:09:55 7 KGLNAHSH NUMBER(,) 2008 .00049800797 0 1 4665964 4290395122
9968 2026-08-18 16:09:55 8 KGLDEPNO NUMBER(,) 157 .00636942675 0 1 0 156
9968 2026-08-18 16:09:55 9 KGLRFHDL RAW(8) 2806 .00035637919 0 1
9968 2026-08-18 16:09:55 10 KGLRFHSH NUMBER(,) 2806 .00035637919 0 1 1193494 4292443036
9968 2026-08-18 16:09:55 11 KGLRFFLG NUMBER(,) 2 .50000000000 0 1 1 65
9968 2026-08-18 16:09:55 12 KGLDPOBJ RAW(8) 3114 .00032113038 0 1
9968 2026-08-18 16:09:55 13 KGLDPPOS NUMBER(,) 775 .00129032258 0 1 0 101427
2026-08-18 16:09:55 14 KGLDPFGR RAW(2000) 0 .00000000000 9968 0
14 rows selected.
--//这个问题仅仅出现在X$表，普通用户表不会存在该问题。我在11g下重复测试不存在这个问题,感觉更像21c上的一个bug。
--//当然如果不修改METHOD_OPT=FOR ALL COLUMNS SIZE AUTO,就不存在该问题。
3.收尾：
SYS@book> drop table sys.tt purge ;
Table dropped.
SYS@book> exec dbms_stats.gather_table_stats('SYS', 'X$KGLDP', estimate_percent => NULL, method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
--//顺便提一下dbms_stats.gather_fixed_objects_stats,无法设置method参数。
SYS@book> @ desc_proc sys dbms_stats gather_fixed_objects_stats
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
Owner PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
----- ------------ ------------------------------ ---------- ------------- -------------- --------- ----------
SYS DBMS_STATS GATHER_FIXED_OBJECTS_STATS 1 STATTAB VARCHAR2 IN Y
2 STATID VARCHAR2 IN Y
3 STATOWN VARCHAR2 IN Y
4 NO_INVALIDATE PL/SQL BOOLEAN IN Y
--//dbms_stats.gather_fixed_objects_stats没有修改method_opt参数。
浙公网安备 33010602011771号