收集统计信息
? DBMS_STATS包
– 专门为CBO提供信息源
– 可以进行数据分析的多种组合
– 可以对分区进行分析
– 可以对分析数据进行管理（备份、恢复、删除等）
– dbms_stats可以并行分析,并行进行，并且对多个用户，多个Table；
– dbms_stats有自动分析的功能；
Oracle自动收集统计信息
? Oracle11g的默认设置；
? user_tab_modifications跟踪记录表的修改；
? 当分析对象的数据变化超过10%时， Oracle会重新分析；
? 定时任务gather_stats_job负责定时调度收集统计信息。
? 在Oracle11g中，默认自动收集统计信息的时间为：
– 周一~周五， 22点启动，收集4个小时
– 周六，周日， 6点启动，收集20个小时
实验：
——在sys用户下创建测试表t：
SYS@ORA11GR2>create table t as select * from dba_objects;
Table created.
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
--此时数据库为表上的所有字段收集了缺省的统计信息，每个列两个Bucket：
SYS@ORA11GR2>col column_name for a30
SYS@ORA11GR2>col owner for a10
SYS@ORA11GR2>col table_name for a10
SYS@ORA11GR2>col endpoint_actual_value for a10
查看sys用户下的表t收集的直方图信息：
SYS@ORA11GR2>select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T CREATED 1
2457674.81
SYS T CREATED 0
2456529.48
SYS T DATA_OBJECT_ID 1
88767
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T DATA_OBJECT_ID 0
0
SYS T GENERATED 0
4.0500E+35
SYS T GENERATED 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T LAST_DDL_TIME 1
2457674.81
SYS T LAST_DDL_TIME 0
2452549.53
SYS T NAMESPACE 0
1
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T NAMESPACE 1
64
SYS T OBJECT_ID 1
88767
SYS T OBJECT_ID 0
2
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_NAME 0
2.4504E+35
SYS T OBJECT_NAME 1
6.2963E+35
SYS T OBJECT_TYPE 0
3.4943E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_TYPE 1
4.5849E+35
SYS T OWNER 0
3.3913E+35
SYS T OWNER 1
4.5831E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T SECONDARY 1
4.6211E+35
SYS T SECONDARY 0
4.0500E+35
SYS T STATUS 1
4.4786E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T STATUS 0
4.4786E+35
SYS T SUBOBJECT_NAME 1
4.5340E+35
SYS T SUBOBJECT_NAME 0
1.8867E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TEMPORARY 1
4.6211E+35
SYS T TEMPORARY 0
4.0500E+35
SYS T TIMESTAMP 0
2.5558E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TIMESTAMP 1
2.6059E+35
28 rows selected.
或者用PLSQL/DEVELOPER执行看起来更方便
——同时，列的低值、高值等信息会被收集记录在dba_tab_col_statistics中：
SYS@ORA11GR2>select table_name,column_name,num_distinct,low_value,high_value,density from dba_tab_col_statistics where owner = 'SYS' and table_name='T';
TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ---------------- ---------- ---------- -----------------
T OWNER 29 415045585F 584442 0
3033303230
30
T OBJECT_NAM 52232 2F31303030 7943624372 0
E 333233645F 5375625361
44656C6567 6D706C696E
617465496E 6754797065
766F636174 3232395F54
696F6E4861
TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ---------------- ---------- ---------- -----------------
T SUBOBJECT_ 149 2456534E5F 575248245F 0
NAME 31 5741495453
545F323337
3834333830
395F30
T OBJECT_ID 86989 C103 C3095844 0
T DATA_OBJEC 9105 80 C3095844 0
T_ID
T OBJECT_TYP 45 434C555354 584D4C2053 0
TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ---------------- ---------- ---------- -----------------
E 4552 4348454D41
T CREATED 982 787108180C 78740A0C14 0
2624 1D12
T LAST_DDL_T 1107 78660A010D 78740A0C14 0
IME 2A32 1D12
T TIMESTAMP 1143 313939302D 323031362D 0
30382D3236 31302D3132
3A31313A32 3A31393A32
TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ---------------- ---------- ---------- -----------------
353A3030 383A3137
T STATUS 1 56414C4944 56414C4944 1
T TEMPORARY 2 4E 59 1
T GENERATED 2 4E 59 1
T SECONDARY 2 4E 59 1
T NAMESPACE 21 C102 C141 0
T EDITION_NA 0 0
ME
15 rows selected.
或者用PLSQL/DEVELOPER执行
这些基本信息在执行计划生成时会被参考。
——不能通过for all columns size 1的收集方式删除上诉统计信息：
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all columns size 1');
PL/SQL procedure successfully completed.
或删除owner字段统计信息，也是无用的。
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns owner size 1');
PL/SQL procedure successfully completed.
——通过下面语句查询，发现直方图信息并未被删除
SYS@ORA11GR2>select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T CREATED 1 2457674.81
SYS T CREATED 0 2456529.48
SYS T DATA_OBJEC 1 88767
T_ID
SYS T DATA_OBJEC 0 0
T_ID
SYS T GENERATED 0 4.0500E+35
SYS T GENERATED 1 4.6211E+35
SYS T LAST_DDL_T 1 2457674.81
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME
SYS T LAST_DDL_T 0 2452549.53
IME
SYS T NAMESPACE 0 1
SYS T NAMESPACE 1 64
SYS T OBJECT_ID 1 88767
SYS T OBJECT_ID 0 2
SYS T OBJECT_NAM 0 2.4504E+35
E
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OBJECT_NAM 1 6.2963E+35
E
SYS T OBJECT_TYP 0 3.4943E+35
E
SYS T OBJECT_TYP 1 4.5849E+35
E
SYS T OWNER 0 3.3913E+35
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OWNER 1 4.5831E+35
SYS T SECONDARY 1 4.6211E+35
SYS T SECONDARY 0 4.0500E+35
SYS T STATUS 1 4.4786E+35
SYS T STATUS 0 4.4786E+35
SYS T SUBOBJECT_ 1 4.5340E+35
NAME
SYS T SUBOBJECT_ 0 1.8867E+35
NAME
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T TEMPORARY 1 4.6211E+35
SYS T TEMPORARY 0 4.0500E+35
SYS T TIMESTAMP 0 2.5558E+35
SYS T TIMESTAMP 1 2.6059E+35
28 rows selected.
——但是使用delete_column_stats可以彻底删除列的柱状图信息：
SYS@ORA11GR2>exec dbms_stats.delete_column_stats(user,'T','OWNER');
PL/SQL procedure successfully completed.
--此时会发现，owner字段在直方图中已经删除
SYS@ORA11GR2>select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T CREATED 1 2457674.81
SYS T CREATED 0 2456529.48
SYS T DATA_OBJEC 1 88767
T_ID
SYS T DATA_OBJEC 0 0
T_ID
SYS T GENERATED 0 4.0500E+35
SYS T GENERATED 1 4.6211E+35
SYS T LAST_DDL_T 1 2457674.81
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME
SYS T LAST_DDL_T 0 2452549.53
IME
SYS T NAMESPACE 1 64
SYS T NAMESPACE 0 1
SYS T OBJECT_ID 1 88767
SYS T OBJECT_ID 0 2
SYS T OBJECT_NAM 0 2.4504E+35
E
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OBJECT_NAM 1 6.2963E+35
E
SYS T OBJECT_TYP 1 4.5849E+35
E
SYS T OBJECT_TYP 0 3.4943E+35
E
SYS T SECONDARY 0 4.0500E+35
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T SECONDARY 1 4.6211E+35
SYS T STATUS 0 4.4786E+35
SYS T STATUS 1 4.4786E+35
SYS T SUBOBJECT_ 0 1.8867E+35
NAME
SYS T SUBOBJECT_ 1 4.5340E+35
NAME
SYS T TEMPORARY 0 4.0500E+35
SYS T TEMPORARY 1 4.6211E+35
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T TIMESTAMP 1 2.6059E+35
SYS T TIMESTAMP 0 2.5558E+35
26 rows selected.
--再次收集t表，owner字段又回来了
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T CREATED 1 2457674.81
SYS T CREATED 0 2456529.48
SYS T DATA_OBJEC 1 88767
T_ID
SYS T DATA_OBJEC 0 0
T_ID
SYS T GENERATED 0 4.0500E+35
SYS T GENERATED 1 4.6211E+35
SYS T LAST_DDL_T 1 2457674.81
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME
SYS T LAST_DDL_T 0 2452549.53
IME
SYS T NAMESPACE 0 1
SYS T NAMESPACE 1 64
SYS T OBJECT_ID 1 88767
SYS T OBJECT_ID 0 2
SYS T OBJECT_NAM 0 2.4504E+35
E
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OBJECT_NAM 1 6.2963E+35
E
SYS T OBJECT_TYP 0 3.4943E+35
E
SYS T OBJECT_TYP 1 4.5849E+35
E
SYS T OWNER 0 3.3913E+35
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OWNER 1 4.5831E+35
SYS T SECONDARY 1 4.6211E+35
SYS T SECONDARY 0 4.0500E+35
SYS T STATUS 1 4.4786E+35
SYS T STATUS 0 4.4786E+35
SYS T SUBOBJECT_ 1 4.5340E+35
NAME
SYS T SUBOBJECT_ 0 1.8867E+35
NAME
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T TEMPORARY 1 4.6211E+35
SYS T TEMPORARY 0 4.0500E+35
SYS T TIMESTAMP 0 2.5558E+35
SYS T TIMESTAMP 1 2.6059E+35
28 rows selected.
——以for columns owner size 1方式收集sys用户下t表的直方图信息：
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns owner size 1');
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T CREATED 1 2457674.81
SYS T CREATED 0 2456529.48
SYS T DATA_OBJEC 1 88767
T_ID
SYS T DATA_OBJEC 0 0
T_ID
SYS T GENERATED 0 4.0500E+35
SYS T GENERATED 1 4.6211E+35
SYS T LAST_DDL_T 1 2457674.81
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME
SYS T LAST_DDL_T 0 2452549.53
IME
SYS T NAMESPACE 0 1
SYS T NAMESPACE 1 64
SYS T OBJECT_ID 1 88767
SYS T OBJECT_ID 0 2
SYS T OBJECT_NAM 0 2.4504E+35
E
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OBJECT_NAM 1 6.2963E+35
E
SYS T OBJECT_TYP 0 3.4943E+35
E
SYS T OBJECT_TYP 1 4.5849E+35
E
SYS T OWNER 0 3.3913E+35
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OWNER 1 4.5831E+35
SYS T SECONDARY 1 4.6211E+35
SYS T SECONDARY 0 4.0500E+35
SYS T STATUS 1 4.4786E+35
SYS T STATUS 0 4.4786E+35
SYS T SUBOBJECT_ 1 4.5340E+35
NAME
SYS T SUBOBJECT_ 0 1.8867E+35
NAME
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T TEMPORARY 1 4.6211E+35
SYS T TEMPORARY 0 4.0500E+35
SYS T TIMESTAMP 0 2.5558E+35
SYS T TIMESTAMP 1 2.6059E+35
28 rows selected.
——不加其他字段，只加用户、表的方式收集sys用户下t表的直方图信息：
SYS@ORA11GR2>exec dbms_stats.delete_table_stats(user,'T');
PL/SQL procedure successfully completed.
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all columns size 1');
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T CREATED 1 2457674.81
SYS T CREATED 0 2456529.48
SYS T DATA_OBJEC 1 88767
T_ID
SYS T DATA_OBJEC 0 0
T_ID
SYS T GENERATED 0 4.0500E+35
SYS T GENERATED 1 4.6211E+35
SYS T LAST_DDL_T 1 2457674.81
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME
SYS T LAST_DDL_T 0 2452549.53
IME
SYS T NAMESPACE 0 1
SYS T NAMESPACE 1 64
SYS T OBJECT_ID 1 88767
SYS T OBJECT_ID 0 2
SYS T OBJECT_NAM 0 2.4504E+35
E
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OBJECT_NAM 1 6.2963E+35
E
SYS T OBJECT_TYP 0 3.4943E+35
E
SYS T OBJECT_TYP 1 4.5849E+35
E
SYS T OWNER 0 3.3913E+35
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T OWNER 1 4.5831E+35
SYS T SECONDARY 1 4.6211E+35
SYS T SECONDARY 0 4.0500E+35
SYS T STATUS 1 4.4786E+35
SYS T STATUS 0 4.4786E+35
SYS T SUBOBJECT_ 1 4.5340E+35
NAME
SYS T SUBOBJECT_ 0 1.8867E+35
NAME
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYS T TEMPORARY 1 4.6211E+35
SYS T TEMPORARY 0 4.0500E+35
SYS T TIMESTAMP 0 2.5558E+35
SYS T TIMESTAMP 1 2.6059E+35
28 rows selected.
总结：针对某张表收集信息，对每个字段默认是收集2个直方图，而以for all columns size 1或for columns owner size 1，和默认对表收集统计信息的直方图显示效果是一样的.