时间：2021-07-01 10:21:17 帮助过：33人阅读
收集或删除对象的统计信息
验证对象的结构
确定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/anal Connected. 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 a30 SQL> col index_name for a30 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 TABLE_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 74564 T5 PK_T5_IDX 1 155 74563 1 1 1174 74563 T4 PK_T4_IDX 1 155 74562 1 1 1174 74562 T3 PK_T3_IDX 1 155 74561 1 1 1174 74561 T2 PK_T2_IDX 1 155 74560 1 1 1174 74560 T1 PK_T1_IDX 1 155 74559 1 1 1174 74559 6 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 a30 SQL> 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 98 T2 T3 T4 T5 T6 6 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 23829 ORA-06512: at "SYS.DBMS_STATS", line 23880 ORA-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 98 T2 T3 T4 T5 T6 STAT_TMP 7 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
本文出自 “叮咚” 博客，请务必保留此出处http://lqding.blog.51cto.com/9123978/1685735
Oracle 学习之性能优化（四）收集统计信息
标签：oracle 统计信息