Oracle 学习之性能优化（四）收集统计信息

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);

参数说明如下：

示例：

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

导出、导入统计信息

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