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);
参数说明如下：
导出、导入统计信息要导出统计信息首先要建立一个统计表
语法：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会动态的采样表中的一部分数据，生成统计信息。
本文转自51CTO博客，版权归原作者所有！如果侵犯到您的版权，请及时联系我们删除！SQL>showparameteroptimizer_dynamic_sampling; NAMETYPEVALUE --------------------------------------------------------------------------------------------------- optimizer_dynamic_samplinginteger2
