emp表有如下数据。
sql> select ename,deptno from emp;ename deptno------------------------------ ----------smith 20allen 30ward 30jones 20martin 30blake 30clark 10scott 20king 10turner 30adams 20james 30ford 20miller 1014 rows selected.
假设我们有如下简单的查询
select ename,deptno from emp where ename='rich' and deptno=10;
那么oracle在执行查询的时候，是先比较ename字段呢？还是先比较deptno字段呢？
显然先比较deptno再比较ename字段的效率明显低于先比较ename，再比较deptno。 那oracle究竟如何去判断呢？
我们先查询一张表
sql> col column_name for a30sql> select column_name, num_distinct, density from dba_tab_columns where owner = 'scott' and table_name = 'emp';column_name num_distinct density------------------------------ ------------ ----------empno 14 .071428571ename 14 .071428571job 5 .2mgr 6 .166666667hiredate 13 .076923077sal 12 .083333333comm 4 .25deptno 3 .3333333338 rows selected.
oracle其实知道，你的表中存放数据的一些特征，上面语句显示的只是凤毛麟角。通过这些特征，oracle优化器就能知道如何去查询，使得执行的效率最高。
以上这些信息，我们称之为对象的统计信息。那么如何收集统计信息呢？
一、 analyze 命令
使用analyze命令可以收集统计信息，如：
收集或删除对象的统计信息
验证对象的结构
确定table 或cluster的migrated 和chained rows。
示例：
sql> create user anal identified by anal ;user created.sql> grant resource,connect to anal;grant succeeded.sql> grant select any dictionary to anal;grant succeeded.sql> conn anal/analconnected.sql> create table t1 as select * from dba_objects;sql> create table t2 as select * from dba_objects;sql> create table t3 as select * from dba_objects;sql> create table t4 as select * from dba_objects;sql> create table t5 as select * from dba_objects;sql> create table t6 as select * from dba_objects;sql> create unique index pk_t1_idx on t1(object_id);sql> create unique index pk_t2_idx on t2(object_id);sql> create unique index pk_t3_idx on t3(object_id);sql> create unique index pk_t4_idx on t4(object_id);sql> create unique index pk_t5_idx on t5(object_id);sql> create unique index pk_t6_idx on t6(object_id);
我们先查看一下统计信息是否存在
查看表的统计信息
sql> select table_name, num_rows, blocks, empty_blocks from user_tables where table_name in ('t1', 't2', 't3', 't4', 't5','t6');
查看字段统计信息
select table_name, column_name, num_distinct, low_value, high_value, density from user_tab_columns where table_name in ('t1', 't2', 't3', 't4','t5','t6');
查看索引统计信息
sql> col table_name for a30sql> col index_name for a30select table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key avg_leaf_blocks, avg_data_blocks_per_key avg_data_blocks, clustering_factor, num_rows from user_indexestable_name index_name blevel leaf_blocks distinct_keys avg_leaf_blocks avg_data_blocks clustering_factor num_rows------------------------------ ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------t6 pk_t6_idx 1 155 74564 1 1 1174 74564t5 pk_t5_idx 1 155 74563 1 1 1174 74563t4 pk_t4_idx 1 155 74562 1 1 1174 74562t3 pk_t3_idx 1 155 74561 1 1 1174 74561t2 pk_t2_idx 1 155 74560 1 1 1174 74560t1 pk_t1_idx 1 155 74559 1 1 1174 745596 rows selected.
表没有任何统计数据，但是索引已经有统计信息，可见在建立表的时候会默认收集统计信息。
先将索引的统计信息删除
sql> analyze table t1 delete statistics;analyze table t2 delete statistics;analyze table t3 delete statistics;analyze table t4 delete statistics;analyze table t5 delete statistics;analyze table t6 delete statistics;
验证索引上是否还存在统计信息
select table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key avg_leaf_blocks, avg_data_blocks_per_key avg_data_blocks, clustering_factor, num_rows from user_indexes
执行统计信息命令，并查看统计信息有无变化
analyze table t1 compute statistics for table;
--针对表收集信息，查看user_tables
analyze table t2 compute statistics for all columns;
--针对表字段收集信息，查看user_tab_columns
analyze table t3 compute statistics for all indexed columns;
--收集索引字段信息
analyze table t4 compute statistics;
--收集表，表字段，索引信息
analyze table t5 compute statistics for all indexes;
--收集索引信息
analyze table t6 compute statistics for table for all indexes for all columns;
--收集表，表字段，索引信息
二、dbms_stats包
oracle推荐使用dbms_stats这个包来收集统计信息。这个包的功能非常多。可以收集数据库级别、schema级别及表级别的统计信息。还可以对统计信息删除、锁定、导出、导入等。我们以最常用的表级别统计为例说明dbms_stats该如何使用。
收集的统计信存储在dba_tab_statistics、dba_ind_statistics和dba_tab_col_statistics表中。
dbms_stats.gather_table_stats ( ownname varchar2, tabname varchar2, partname varchar2 default null, estimate_percent number default to_estimate_percent_type (get_param('estimate_percent')), block_sample boolean default false, method_opt varchar2 default get_param('method_opt'), degree number default to_degree_type(get_param('degree')), granularity varchar2 default get_param('granularity'), cascade boolean default to_cascade_type(get_param('cascade')), stattab varchar2 default null, statid varchar2 default null, statown varchar2 default null, no_invalidate boolean default to_no_invalidate_type ( get_param('no_invalidate')), stattype varchar2 default 'data', force boolean default false);
参数说明如下：
650) this.width=650; src=http://www.68idc.cn/help/uploads/allimg/151111/120f051i-0.jpg title=02.png alt=wkiom1xtqfjjm5mqaak5uvu0i1u436.jpg />
650) this.width=650; src=http://www.68idc.cn/help/uploads/allimg/151111/120f025l-1.jpg title=03.png alt=wkiom1xtqgpc6wjhaaxhcpo1cg0620.jpg />
650) this.width=650; src=http://www.68idc.cn/help/uploads/allimg/151111/120f01b1-2.jpg title=04.png alt=wkiol1xtrbujg-h6aayphtoucrs231.jpg />
650) this.width=650; src=http://www.68idc.cn/help/uploads/allimg/151111/120f02n1-3.jpg title=05.png alt=wkiom1xtqh6cxhdeaaj-9movv0u797.jpg />
示例：
sql> col table_name for a30sql> select table_name, num_rows, blocks, empty_blocks, avg_row_len from user_tab_statistics;table_name num_rows blocks empty_blocks avg_row_len------------------------------ ---------- ---------- ------------ -----------t1 74559 1088 0 98t2t3t4t5t66 rows selected.
删除统计信息
dbms_stats.delete_table_stats ( ownname varchar2, tabname varchar2, partname varchar2 default null, stattab varchar2 default null, statid varchar2 default null, cascade_parts boolean default true, cascade_columns boolean default true, cascade_indexes boolean default true, statown varchar2 default null, no_invalidate boolean default to_no_invalidate_type ( get_param('no_invalidate')), force boolean default false);
锁定统计信息
dbms_stats.lock_table_stats ( ownname varchar2, tabname varchar2);
锁定以后就不能再执行统计信息
sql> exec dbms_stats.lock_table_stats(user,'t1');pl/sql procedure successfully completed.sql> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);begin dbms_stats.gather_table_stats(user,'t1',cascade=>true); end;*error at line 1:ora-20005: object statistics are locked (stattype = all)ora-06512: at sys.dbms_stats, line 23829ora-06512: at sys.dbms_stats, line 23880ora-06512: at line 1
导出、导入统计信息
要导出统计信息首先要建立一个统计表
语法：
dbms_stats.create_stat_table ( ownname varchar2, stattab varchar2, tblspace varchar2 default null);
sql> exec dbms_stats.create_stat_table (user,'stat_tmp','sysaux');pl/sql procedure successfully completed.
2. 将表t1统计信息导出
dbms_stats.export_table_stats ( ownname varchar2, tabname varchar2, partname varchar2 default null, stattab varchar2, statid varchar2 default null, cascade boolean default true, statown varchar2 default null, stat_category varchar2 default default_stat_category);
sql> exec dbms_stats.export_table_stats (ownname=>user,tabname=>'t1',stattab=>'stat_tmp');pl/sql procedure successfully completed.
3. 导入统计信息
语法：
dbms_stats.import_table_stats ( ownname varchar2, tabname varchar2, partname varchar2 default null, stattab varchar2, statid varchar2 default null, cascade boolean default true, statown varchar2 default null, no_invalidate boolean default to_no_invalidate_type( get_param('no_invalidate')), force boolean default false, stat_category varchar2 default default_stat_category);
sql> exec dbms_stats.unlock_table_stats(user,'t1');pl/sql procedure successfully completed.sql> exec dbms_stats.delete_table_stats(user,'t1');pl/sql procedure successfully completed.sql> exec dbms_stats.import_table_stats (ownname=>user,tabname=>'t1',stattab=>'stat_tmp');pl/sql procedure successfully completed.sql> select table_name, num_rows, blocks, empty_blocks, avg_row_len from user_tab_statistics; 2 3 4 5 6 table_name num_rows blocks empty_blocks avg_row_len------------------------------ ---------- ---------- ------------ -----------t1 74559 1088 0 98t2t3t4t5t6stat_tmp7 rows selected.
如果是分区表，新的分区来不及收集统计系统，可以使用其它的分区统计信息来生成新分区的统计信息
dbms_stats.copy_table_stats ( ownname varchar2, tabname varchar2, srcpartname varchar2, dstpartname varchar2, scale_factor varchar2 default 1, force boolean default false);
如果表还没有统计信息，那么在执行sql语句时，oracle会动态的采样表中的一部分数据，生成统计信息。
sql> show parameter optimizer_dynamic_sampling ;name type value------------------------------------ --------------------------------- ------------------------------optimizer_dynamic_sampling integer 2