emp表有如下数据。SQL>selectename,deptnofromemp;
ENAMEDEPTNO
----------------------------------------
SMITH20
ALLEN30
WARD30
JONES20
MARTIN30
BLAKE30
CLARK10
SCOTT20
KING10
TURNER30
ADAMS20
JAMES30
FORD20
MILLER10
14rowsselected.
假设我们有如下简单的查询selectename,deptnofromempwhereename='RICH'anddeptno=10;
那么Oracle在执行查询的时候，是先比较ename字段呢？还是先比较deptno字段呢？
显然先比较deptno再比较ename字段的效率明显低于先比较ename，再比较deptno。 那Oracle究竟如何去判断呢？
我们先查询一张表SQL>COLCOLUMN_NAMEFORA30
SQL>SELECTcolumn_name,num_distinct,density
FROMdba_tab_columns
WHEREowner='SCOTT'ANDtable_name='EMP';
COLUMN_NAMENUM_DISTINCTDENSITY
----------------------------------------------------
EMPNO14.071428571
ENAME14.071428571
JOB5.2
MGR6.166666667
HIREDATE13.076923077
SAL12.083333333
COMM4.25
DEPTNO3.333333333
8rowsselected.
Oracle其实知道，你的表中存放数据的一些特征，上面语句显示的只是凤毛麟角。通过这些特征，Oracle优化器就能知道如何去查询，使得执行的效率最高。
以上这些信息，我们称之为对象的统计信息。那么如何收集统计信息呢？
一、 analyze 命令
使用analyze命令可以收集统计信息，如：收集或删除对象的统计信息
验证对象的结构
确定table 或cluster的migrated 和chained rows。
示例：SQL>createuseranalidentifiedbyanal;
Usercreated.
SQL>grantresource,connecttoanal;
Grantsucceeded.
SQL>grantselectanydictionarytoanal;
Grantsucceeded.
SQL>connanal/anal
Connected.
SQL>createtablet1asselect*fromdba_objects;
SQL>createtablet2asselect*fromdba_objects;
SQL>createtablet3asselect*fromdba_objects;
SQL>createtablet4asselect*fromdba_objects;
SQL>createtablet5asselect*fromdba_objects;
SQL>createtablet6asselect*fromdba_objects;
SQL>createuniqueindexpk_t1_idxont1(object_id);
SQL>createuniqueindexpk_t2_idxont2(object_id);
SQL>createuniqueindexpk_t3_idxont3(object_id);
SQL>createuniqueindexpk_t4_idxont4(object_id);
SQL>createuniqueindexpk_t5_idxont5(object_id);
SQL>createuniqueindexpk_t6_idxont6(object_id);
我们先查看一下统计信息是否存在
查看表的统计信息SQL>selecttable_name,num_rows,blocks,empty_blocks
fromuser_tables
wheretable_namein('T1','T2','T3','T4','T5','T6');
查看字段统计信息selecttable_name,
column_name,
num_distinct,
low_value,
high_value,
density
fromuser_tab_columns
wheretable_namein('T1','T2','T3','T4','T5','T6');
查看索引统计信息SQL>coltable_namefora30
SQL>colindex_namefora30
SELECTtable_name,
index_name,
blevel,
leaf_blocks,
distinct_keys,
avg_leaf_blocks_per_keyavg_leaf_blocks,
avg_data_blocks_per_keyavg_data_blocks,
clustering_factor,
num_rows
FROMuser_indexes
TABLE_NAMEINDEX_NAMEBLEVELLEAF_BLOCKSDISTINCT_KEYSAVG_LEAF_BLOCKSAVG_DATA_BLOCKSCLUSTERING_FACTORNUM_ROWS
-------------------------------------------------------------------------------------------------------------------------------------------------------
T6PK_T6_IDX11557456411117474564
T5PK_T5_IDX11557456311117474563
T4PK_T4_IDX11557456211117474562
T3PK_T3_IDX11557456111117474561
T2PK_T2_IDX11557456011117474560
T1PK_T1_IDX11557455911117474559
6rowsselected.
表没有任何统计数据，但是索引已经有统计信息，可见在建立表的时候会默认收集统计信息。
先将索引的统计信息删除SQL>analyzetablet1deletestatistics;
analyzetablet2deletestatistics;
analyzetablet3deletestatistics;
analyzetablet4deletestatistics;
analyzetablet5deletestatistics;
analyzetablet6deletestatistics;
验证索引上是否还存在统计信息SELECTtable_name,
index_name,
blevel,
leaf_blocks,
distinct_keys,
avg_leaf_blocks_per_keyavg_leaf_blocks,
avg_data_blocks_per_keyavg_data_blocks,
clustering_factor,
num_rows
FROMuser_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(
ownnameVARCHAR2,
tabnameVARCHAR2,
partnameVARCHAR2DEFAULTNULL,
estimate_percentNUMBERDEFAULTto_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sampleBOOLEANDEFAULTFALSE,
method_optVARCHAR2DEFAULTget_param('METHOD_OPT'),
degreeNUMBERDEFAULTto_degree_type(get_param('DEGREE')),
granularityVARCHAR2DEFAULTGET_PARAM('GRANULARITY'),
cascadeBOOLEANDEFAULTto_cascade_type(get_param('CASCADE')),
stattabVARCHAR2DEFAULTNULL,
statidVARCHAR2DEFAULTNULL,
statownVARCHAR2DEFAULTNULL,
no_invalidateBOOLEANDEFAULTto_no_invalidate_type(
get_param('NO_INVALIDATE')),
stattypeVARCHAR2DEFAULT'DATA',
forceBOOLEANDEFAULTFALSE);
参数说明如下：
示例：
SQL>coltable_namefora30
SQL>SELECTtable_name,
num_rows,
blocks,
empty_blocks,
avg_row_len
FROMuser_tab_statistics;
TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKSAVG_ROW_LEN
-------------------------------------------------------------------------
T1745591088098
T2
T3
T4
T5
T6
6rowsselected.
删除统计信息DBMS_STATS.DELETE_TABLE_STATS(
ownnameVARCHAR2,
tabnameVARCHAR2,
partnameVARCHAR2DEFAULTNULL,
stattabVARCHAR2DEFAULTNULL,
statidVARCHAR2DEFAULTNULL,
cascade_partsBOOLEANDEFAULTTRUE,
cascade_columnsBOOLEANDEFAULTTRUE,
cascade_indexesBOOLEANDEFAULTTRUE,
statownVARCHAR2DEFAULTNULL,
no_invalidateBOOLEANDEFAULTto_no_invalidate_type(
get_param('NO_INVALIDATE')),
forceBOOLEANDEFAULTFALSE);
锁定统计信息DBMS_STATS.LOCK_TABLE_STATS(
ownnameVARCHAR2,
tabnameVARCHAR2);
锁定以后就不能再执行统计信息SQL>execdbms_stats.lock_table_stats(user,'T1');
PL/SQLproceduresuccessfullycompleted.
SQL>execdbms_stats.gather_table_stats(user,'t1',cascade=>true);
BEGINdbms_stats.gather_table_stats(user,'t1',cascade=>true);END;
*
ERRORatline1:
ORA-20005:objectstatisticsarelocked(stattype=ALL)
ORA-06512:at"SYS.DBMS_STATS",line23829
ORA-06512:at"SYS.DBMS_STATS",line23880
ORA-06512:atline1
导出、导入统计信息要导出统计信息首先要建立一个统计表
语法：DBMS_STATS.CREATE_STAT_TABLE(
ownnameVARCHAR2,
stattabVARCHAR2,
tblspaceVARCHAR2DEFAULTNULL);SQL>execDBMS_STATS.CREATE_STAT_TABLE(user,'STAT_TMP','SYSAUX');
PL/SQLproceduresuccessfullycompleted.
2. 将表t1统计信息导出DBMS_STATS.EXPORT_TABLE_STATS(
ownnameVARCHAR2,
tabnameVARCHAR2,
partnameVARCHAR2DEFAULTNULL,
stattabVARCHAR2,
statidVARCHAR2DEFAULTNULL,
cascadeBOOLEANDEFAULTTRUE,
statownVARCHAR2DEFAULTNULL,
stat_categoryVARCHAR2DEFAULTDEFAULT_STAT_CATEGORY);SQL>EXECDBMS_STATS.EXPORT_TABLE_STATS(ownname=>USER,tabname=>'T1',stattab=>'STAT_TMP');
PL/SQLproceduresuccessfullycompleted.
3. 导入统计信息
语法：DBMS_STATS.IMPORT_TABLE_STATS(
ownnameVARCHAR2,
tabnameVARCHAR2,
partnameVARCHAR2DEFAULTNULL,
stattabVARCHAR2,
statidVARCHAR2DEFAULTNULL,
cascadeBOOLEANDEFAULTTRUE,
statownVARCHAR2DEFAULTNULL,
no_invalidateBOOLEANDEFAULTto_no_invalidate_type(
get_param('NO_INVALIDATE')),
forceBOOLEANDEFAULTFALSE,
stat_categoryVARCHAR2DEFAULTDEFAULT_STAT_CATEGORY);SQL>execdbms_stats.UNlock_table_stats(user,'T1');
PL/SQLproceduresuccessfullycompleted.
SQL>execdbms_stats.delete_table_stats(user,'T1');
PL/SQLproceduresuccessfullycompleted.
SQL>EXECDBMS_STATS.IMPORT_TABLE_STATS(ownname=>USER,tabname=>'T1',stattab=>'STAT_TMP');
PL/SQLproceduresuccessfullycompleted.
SQL>SELECTtable_name,
num_rows,
blocks,
empty_blocks,
avg_row_len
FROMuser_tab_statistics;23456
TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKSAVG_ROW_LEN
-------------------------------------------------------------------------
T1745591088098
T2
T3
T4
T5
T6
STAT_TMP
7rowsselected.
如果是分区表，新的分区来不及收集统计系统，可以使用其它的分区统计信息来生成新分区的统计信息DBMS_STATS.COPY_TABLE_STATS(
ownnameVARCHAR2,
tabnameVARCHAR2,
srcpartnameVARCHAR2,
dstpartnameVARCHAR2,
scale_factorVARCHAR2DEFAULT1,
forceBOOLEANDEFAULTFALSE);
如果表还没有统计信息，那么在执行sql语句时，Oracle会动态的采样表中的一部分数据，生成统计信息。SQL>showparameteroptimizer_dynamic_sampling;
NAMETYPEVALUE
---------------------------------------------------------------------------------------------------
optimizer_dynamic_samplinginteger2
