收集方式
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
tabname=>'WH',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
no_invalidate=>false,
force=>true,
cascade=>true,
degree=>DBMS_STATS.AUTO_DEGREE);
end;
/
method_opt
Accepts either of the following options, or both in combination:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension
- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
AUTO会根据column数据分布情况以及column的使用情况(根据COL_USAGE$来判断,COL_USAGE$是被用来记录数据库的列级使用信息,这些信息被用来辅助统计信息收集)进行判断是不是收集这个column的histogram。而SKEWONLY只是根据column的数据分布情况决定是否收集histogram。
1.建立一个数据均匀分布的表
SQL> create table system.wh as select * from dba_objects;
Table created.
SQL> create index system.ll on system.wh(object_id);
Index created.
2.查看列情况
SQL> select o.OBJECT_ID from dba_objects o where o.OWNER = 'SYSTEM' and o.OBJECT_NAME = 'WH';
OBJECT_ID
----------
104897
SQL> select * from col_usage$ o where o.obj# = 104697;
no rows selected
3.做几次查询
SQL> select count(*) from system.wh where object_id=1;
COUNT(*)
----------
0
SQL> select count(*) from system.wh where object_id>100;
COUNT(*)
----------
74840
4.收统计信息
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
3 tabname=>'WH',
4 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO',
6 no_invalidate=>false,
7 force=>true,
8 cascade=>true,
9 degree=>DBMS_STATS.AUTO_DEGREE);
10 end;
11 /
PL/SQL procedure successfully completed.
5.查看列情况
SQL> select o.obj#,o.intcol#,o.equality_preds,o.range_preds from col_usage$ o where o.obj# = 104897;
OBJ# INTCOL# EQUALITY_PREDS RANGE_PREDS
---------- ---------- -------------- -----------
104897 4 1 1
可以看到 等于查询与 范围查询,各记录了一次
6.查看一下列的统计信息
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';
HISTOGRAM
---------------
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
15 rows selected.
7.手工收一下列直方图
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
tabname=>'WH',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'FOR COLUMNS SIZE 10 OBJECT_ID',
no_invalidate=>false,
force=>true,
cascade=>true,
degree=>DBMS_STATS.AUTO_DEGREE);
end;
/
8.查看一下列信息
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';
HISTOGRAM
---------------
NONE
NONE
NONE
HEIGHT BALANCED
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
15 rows selected.
在oracle9i中,默认的统计信息收集是不收集直方图信息的,也就是说默认的MOTHOD_OPT模式为FOR ALL COLUMNS SIZE 1
在10g开始,dbms_stats包中默认的METHOD_OPT做了调整,默认的METHOD_OPT值为FOR ALL COLUMNS SIZE AUTO
是否收集列统计信息是由ORACLE自己判断的,感觉如果数据是均匀分布的,oracle 应该不会去收集
使用auto收集直方图有两个条件:1,当前列是倾斜的;2,当前列被where语句中作为谓词条件出现过;
---------- ---------- -------------- ----------- ---------- ---------- -------------- -----------
1.创建数据分布不均匀的表
SQL> create table system.wh(num1 number(10),num2 number(10));
Table created.
SQL> begin
2 for i in 1 .. 10000 loop
3 if i < 9900 then
4 insert into system.wh values(10000,i);
5 commit;
6 else
7 insert into system.wh values(100,i);
8 commit;
9 end if;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
3 tabname=>'WH',
4 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO',
6 no_invalidate=>false,
7 force=>true,
8 cascade=>true,
9 degree=>DBMS_STATS.AUTO_DEGREE);
10 end;
11 /
PL/SQL procedure successfully completed.
2.查询数据
SQL> select count(*),num1 from system.wh where num1=100 group by num1;
COUNT(*) NUM1
---------- ----------
101 100
3.看一下列查询信息
SQL> select o.obj#,o.intcol#,o.equality_preds from col_usage$ o where o.obj# = 104899;
OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
104899 1 1
4.看一下列统计信息情况
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';
HISTOGRAM
---------------
FREQUENCY
NONE
看来列直方图是均匀分布,oracle是不收集的,如果是倾斜的,oracle 就会去收集
删除列直方图
dbms_stats.delete_column_stats(ownname => 'SYSTEM,tabname => 'WH',colname => 'OBJECT_ID',col_stat_type => 'HISTOGRAM')
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
tabname=>'WH',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
no_invalidate=>false,
force=>true,
cascade=>true,
degree=>DBMS_STATS.AUTO_DEGREE);
end;
/
method_opt
Accepts either of the following options, or both in combination:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension
- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
AUTO会根据column数据分布情况以及column的使用情况(根据COL_USAGE$来判断,COL_USAGE$是被用来记录数据库的列级使用信息,这些信息被用来辅助统计信息收集)进行判断是不是收集这个column的histogram。而SKEWONLY只是根据column的数据分布情况决定是否收集histogram。
1.建立一个数据均匀分布的表
SQL> create table system.wh as select * from dba_objects;
Table created.
SQL> create index system.ll on system.wh(object_id);
Index created.
2.查看列情况
SQL> select o.OBJECT_ID from dba_objects o where o.OWNER = 'SYSTEM' and o.OBJECT_NAME = 'WH';
OBJECT_ID
----------
104897
SQL> select * from col_usage$ o where o.obj# = 104697;
no rows selected
3.做几次查询
SQL> select count(*) from system.wh where object_id=1;
COUNT(*)
----------
0
SQL> select count(*) from system.wh where object_id>100;
COUNT(*)
----------
74840
4.收统计信息
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
3 tabname=>'WH',
4 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO',
6 no_invalidate=>false,
7 force=>true,
8 cascade=>true,
9 degree=>DBMS_STATS.AUTO_DEGREE);
10 end;
11 /
PL/SQL procedure successfully completed.
5.查看列情况
SQL> select o.obj#,o.intcol#,o.equality_preds,o.range_preds from col_usage$ o where o.obj# = 104897;
OBJ# INTCOL# EQUALITY_PREDS RANGE_PREDS
---------- ---------- -------------- -----------
104897 4 1 1
可以看到 等于查询与 范围查询,各记录了一次
6.查看一下列的统计信息
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';
HISTOGRAM
---------------
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
15 rows selected.
7.手工收一下列直方图
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
tabname=>'WH',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'FOR COLUMNS SIZE 10 OBJECT_ID',
no_invalidate=>false,
force=>true,
cascade=>true,
degree=>DBMS_STATS.AUTO_DEGREE);
end;
/
8.查看一下列信息
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';
HISTOGRAM
---------------
NONE
NONE
NONE
HEIGHT BALANCED
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
15 rows selected.
在oracle9i中,默认的统计信息收集是不收集直方图信息的,也就是说默认的MOTHOD_OPT模式为FOR ALL COLUMNS SIZE 1
在10g开始,dbms_stats包中默认的METHOD_OPT做了调整,默认的METHOD_OPT值为FOR ALL COLUMNS SIZE AUTO
是否收集列统计信息是由ORACLE自己判断的,感觉如果数据是均匀分布的,oracle 应该不会去收集
使用auto收集直方图有两个条件:1,当前列是倾斜的;2,当前列被where语句中作为谓词条件出现过;
---------- ---------- -------------- ----------- ---------- ---------- -------------- -----------
1.创建数据分布不均匀的表
SQL> create table system.wh(num1 number(10),num2 number(10));
Table created.
SQL> begin
2 for i in 1 .. 10000 loop
3 if i < 9900 then
4 insert into system.wh values(10000,i);
5 commit;
6 else
7 insert into system.wh values(100,i);
8 commit;
9 end if;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
3 tabname=>'WH',
4 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO',
6 no_invalidate=>false,
7 force=>true,
8 cascade=>true,
9 degree=>DBMS_STATS.AUTO_DEGREE);
10 end;
11 /
PL/SQL procedure successfully completed.
2.查询数据
SQL> select count(*),num1 from system.wh where num1=100 group by num1;
COUNT(*) NUM1
---------- ----------
101 100
3.看一下列查询信息
SQL> select o.obj#,o.intcol#,o.equality_preds from col_usage$ o where o.obj# = 104899;
OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
104899 1 1
4.看一下列统计信息情况
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';
HISTOGRAM
---------------
FREQUENCY
NONE
看来列直方图是均匀分布,oracle是不收集的,如果是倾斜的,oracle 就会去收集
删除列直方图
dbms_stats.delete_column_stats(ownname => 'SYSTEM,tabname => 'WH',colname => 'OBJECT_ID',col_stat_type => 'HISTOGRAM')
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7569309/viewspace-2134155/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7569309/viewspace-2134155/