emp表有如下数据。 SQLselectename,deptnofromemp;ENAMEDEPTNO—————————————-SMITH20ALLEN30WARD30JONES20MARTIN30BLAKE30CLARK10SCOTT20KING10TURNER30ADAMS20JAMES30FORD20MILLER1014rowsselected. 假设我们有如下简单的查询 se
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=”https://img.gaodaima.com/d/file/2021/11/10/4d745a36e6c3b5e0a929614a39b89b8e.jp本文来源gaodaimacom搞#^代%!码&网(g” title=”02.PNG” alt=”wKiom1XTQfjjM5MQAAK5UvU0I1U436.jpg” />
650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/6406422d0a9799327386843aa04fb47f.jpg” title=”03.PNG” alt=”wKiom1XTQgPC6wJhAAXhcpo1cG0620.jpg” />
650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/d65482a15e05160ce42da03a93ba4b08.jpg” title=”04.PNG” alt=”wKioL1XTRBujG-h6AAYphtOucrs231.jpg” />
650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/8863874c1901cc702c4b78e682cf24c5.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
转载请注明原文链接：Oracle学习之性能优化（四）收集统计信息
