收集列的基本信息，不收集柱状图，指定的size等于1(bucket“小桶”=1)
exec dbms_stats.gather_table_stats(user, 'DEPT', cascade=>false, method_opt=>'for columns col1 size 1');
收集列的柱状图信息，指定的size大于等于2小于等于254(bucket“小桶” between 2 and 254)
sec@ora10g>exec dbms_stats.gather_table_stats(user, 'DEPT', cascade=>false, method_opt=>'for columns col1 size 2');
10g中删除列已有的柱状图信息同时保留列的基本统计信息方法：需要重新收集size为1的统计信息
exec dbms_stats.gather_table_stats(user, 'DEPT', cascade=>false, method_opt=>'for columns col1 size 1');
11g中删除已有的柱状图信息同时保留列的基本统计信息得到了简化
exec dbms_stats.delete_column_stats(user, 'DEPT','COL1',col_stat_type=>'HISTOGRAM');
删除整个列的统计信息，使用delete_column_stats
exec dbms_stats.delete_column_stats(user, 'DEPT', 'COL1');
混合打法：同一个过程中收集多个列的统计信息，不同的列指定不同的size数
exec dbms_stats.gather_table_stats(user, 'DEPT', cascade=>false, method_opt => 'for columns col1 size 1 for columns col2 size 2');
收集统计信息的一些例子
例子1对表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'DEPT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
/
上面的例子收集SCOTT.DEPT表的统计信息。这里面值得关注的一个参数就是method_opt。这个参数控制是否收集列的直方图信息。通常情况下，是不会收集直方图的，关于直方图不是三言两语可以说明白的。它的四个选项method_opt=>'for all columns size skewonly'
ORACLE会根据数据分布收集直方图
method_opt=>'for all columns size repeat'
只有以前收集过直方图，才会收集直方图信息，所以一般我们会设置method_opt 为repeat
method_opt=>'for all columns size auto'
ORACLE会根据数据分布以及列的workload来确定是否收集直方图
method_opt=>'for all columns size interger'
我们自己指定一个bucket值
例子2对某一个schma收集统计信息
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
ptions => 'gather auto',
degree => DBMS_STATS.AUTO_DEGREE,
method_opt => 'for all columns size repeat',
cascade => TRUE
);
END;
/
上面的例子收集SCOTT模式下所有对象的统计信息。里面值得注意的一个参数就是options。前面已经讲到过，他与表监控有关。它有四个选项
Options =>’gather’ 收集所有对象的统计信息
Options =>’gather empty’ 只收集还没被统计的表
Options =>’gather stale’ 只收集修改量超过10%的表
Options =>’gather auto’ 相当于empty+stale ，所以我们一般设置为AUTO。
例子3 对一个分区表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'ALL',
cascade=>TRUE
);
END;
/
上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity，他有7个选项。
granularity => 'ALL' 收集分区，子分区，全局的统计信息
granularity => 'AUTO' 这个是默认的设置，ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
granularity => 'DEFAULT' 这个是过期了的
granularity => 'GLOBAL' 收集全局统计信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分区统计信息，但是不收集子分区统计信息
granularity => 'PARTITION' 收集分区统计信息
granularity => 'SUBPARTITION' 收集子分区统计信息
当然我们可以指定partname，自己控制对哪个分区收集统计信息
1 DECLARE
2 I NUMBER;
3 BEGIN
4 FOR i IN 1..100 LOOP
5 INSERT INTO BHH VALUES(I);
6 END LOOP;
7* END;
QL> /
L/SQL 过程已成功完成。
QL> commit;
提交完成。
QL> select count(*) from bhh;
COUNT(*)
---------
100
QL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID';
未选定行
QL> exec dbms_stats.gather_table_stats('SCOTT','BHH');
L/SQL 过程已成功完成。
QL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID';
OLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
----------------------------- ------------ ----------- ---------------
D 100 1 NONE
QL>
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'BHH',
estimate_percent =>100,
method_opt => 'for all columns size 254',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
强制id列不产生直方图信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'BHH',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS ID SIZE 1 ');
END
alter table bhh add id2 number
update bhh set id2=1;
update bhh set id2=100 where id=100;
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID 100 1 NONE
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME ='BHH';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 0 1
BHH ID2 0 1
BHH ID 1 100
BHH ID2 1 100
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'BHH',
estimate_percent =>100,
method_opt => 'for all columns size auto',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID2';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID2 2 2 FREQUENCY
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME ='BHH';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID2 99 1
BHH ID2 100 100
BHH ID 0 1
BHH ID 1 100
SQL> update bhh set id2=40 where id=50;
已更新 1 行。
SQL> commit;
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID2';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID2 3 3 FREQUENCY
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME ='BHH';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID2 98 1
BHH ID2 99 40
BHH ID2 100 100
BHH ID 0 1
BHH ID 1 100
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'BHH',
estimate_percent =>100,
method_opt => 'for all columns size auto',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
SQL> update bhh set id2=40 where id=55;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID2';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID2 3 3 FREQUENCY
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME ='BHH';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID2 97 1
BHH ID2 99 40
BHH ID2 100 100
BHH ID 0 1
BHH ID 1 100
说明：
id2 为1 有97个重复的
id2 为40 有99-97=2个重复值
id2 为100 有100-99个重复值
SQL> update bhh set id2=80 where id=30;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID2';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID2 4 4 FREQUENCY
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME ='BHH';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID2 96 1
BHH ID2 98 40
BHH ID2 99 80
BHH ID2 100 100
BHH ID 0 1
BHH ID 1 100
已选择6行。
实验二：查看id列上的统计信息，id列是1-100 unique值
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'BHH',
4 estimate_percent =>100,
5 method_opt => 'for all columns size repeat',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL 过程已成功完成。
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID 100 1 NONE
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID2';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID2 4 4 FREQUENCY
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'BHH',
4 estimate_percent =>100,
5 method_opt => 'for all columns size 254',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL 过程已成功完成。
SQL>
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID2';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID2 4 4 FREQUENCY
SQL> select column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where table_name='BHH'AND COLUMN_NAME='ID';
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
---------- ------------ ----------- ---------------
ID 100 100 HEIGHT BALANCED
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HIST
2 WHERE TABLE_NAME ='BHH';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 1 1
BHH ID 2 2
BHH ID 3 3
BHH ID 4 4
BHH ID 5 5
BHH ID 6 6
BHH ID 7 7
BHH ID 8 8
BHH ID 9 9
BHH ID 10 10
BHH ID 11 11
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 12 12
BHH ID 13 13
BHH ID 14 14
BHH ID 15 15
BHH ID 16 16
BHH ID 17 17
BHH ID 18 18
BHH ID 19 19
BHH ID 20 20
BHH ID 21 21
BHH ID 22 22
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 23 23
BHH ID 24 24
BHH ID 25 25
BHH ID 26 26
BHH ID 27 27
BHH ID 28 28
BHH ID 29 29
BHH ID 30 30
BHH ID 31 31
BHH ID 32 32
BHH ID 33 33
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 34 34
BHH ID 35 35
BHH ID 36 36
BHH ID 37 37
BHH ID 38 38
BHH ID 39 39
BHH ID 40 40
BHH ID 41 41
BHH ID 42 42
BHH ID 43 43
BHH ID 44 44
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 45 45
BHH ID 46 46
BHH ID 47 47
BHH ID 48 48
BHH ID 49 49
BHH ID 50 50
BHH ID 51 51
BHH ID 52 52
BHH ID 53 53
BHH ID 54 54
BHH ID 55 55
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 56 56
BHH ID 57 57
BHH ID 58 58
BHH ID 59 59
BHH ID 60 60
BHH ID 61 61
BHH ID 62 62
BHH ID 63 63
BHH ID 64 64
BHH ID 65 65
BHH ID 66 66
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 67 67
BHH ID 68 68
BHH ID 69 69
BHH ID 70 70
BHH ID 71 71
BHH ID 72 72
BHH ID 73 73
BHH ID 74 74
BHH ID 75 75
BHH ID 76 76
BHH ID 77 77
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 78 78
BHH ID 79 79
BHH ID 80 80
BHH ID 81 81
BHH ID 82 82
BHH ID 83 83
BHH ID 84 84
BHH ID 85 85
BHH ID 86 86
BHH ID 87 87
BHH ID 88 88
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 89 89
BHH ID 90 90
BHH ID 91 91
BHH ID 92 92
BHH ID 93 93
BHH ID 94 94
BHH ID 95 95
BHH ID 96 96
BHH ID 97 97
BHH ID 98 98
BHH ID 99 99
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- --------------- --------------
BHH ID 100 100
BHH ID2 96 1
BHH ID2 98 40
BHH ID2 99 80
BHH ID2 100 100
已选择104行。