管理Oracle统计信息

 在CBO(基于代价的优化器模式)条件下，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式决定执行计划！可以说统计信息关乎sql的执行计划是否正确，属于sql执行的指导思想，oracle的初始化参数statistics_level控制收集统计信息的级别，有三个参数值:

BASIC :收集基本的统计信息

TYPICAL：收集大部分统计信息(数据库的默认设置)

ALL：收集全部统计信息

 

统计信息包含：

行统计信息(user_tables)：行数(NUM_ROWS)，块数(BLOCKS)，行平均长度(AVG_ROW_LEN)；

列统计信息(user_tab_columns)：列中唯一值的数量（NUM_DISTINCT），NULL值的数量(NUM_NULLS)，数据分布(HISTOGRAM)；         

索引统计(user_index)：--叶块数量(LEAF_BLOCKS)，等级(BLEVEL)，聚簇因子(CLUSTERING_FACTOR)；

 

统计信息的收集时间由时间窗口来调度！

 一：10g关闭自动收集统计信息任务

1. begin 
2.  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); 
3. end; 

二：11g关闭自动收集统计信息任务

1. BEGIN 
2. DBMS_AUTO_TASK_ADMIN.DISABLE( 
3. client_name => 'auto optimizer stats collection', 
4. operation => NULL, 
5. window_name => NULL); 
6. END; 

查询

1. SQL> select client_name ,status from dba_autotask_client; 
2.  
3. CLIENT_NAME                                                      STATUS 
4.  
5. ---------------------------------------------------------------- -------- 
6.  
7. auto optimizer stats collection                                  DISABLED 
8.  
9. auto space advisor                                               ENABLED 
10.  
11. sql tuning advisor                                               ENABLED 

三：手动收集统计信息，采样10%，并行度为8，METHOD_OPT选项代表收集index列分布情况，并生成直方图

1. BEGIN 
2. dbms_stats.gather_schema_stats( 
3. ownname=>'HR', 
4. METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', 
5. CASCADE=>TRUE, 
6. ESTIMATE_PERCENT=>10, 
7. DEGREE=>8); 
8. END; 

四：查看表或索引的统计信息

1. select table_name,  
2.        last_analyzed,  
3.        num_rows,  
4.        avg_row_len,  
5.        row_movement， 
6.        monitoring 
7. from dba_tables 
8. where owner = 'HR'; 
9.  
10. select index_name, 
11.        table_name, 
12.        blevel, 
13.        status, 
14.        clustering_factor, 
15.        last_analyzed, 
16.        num_rows, 
17.        leaf_blocks, 
18.        distinct_keys, 
19.        avg_leaf_blocks_per_key 
20. from dba_indexes 
21. where owner = 'HR'; 

五：导出统计信息

1. begin 
2.   dbms_stats.create_stat_table(ownname => 'HR', 
3.   stattab => 'HR_STAT_BAK'); 
4. end; 
5.  
6. begin  
7.   dbms_stats.export_schema_stats(ownname => 'HR', 
8.   stattab => 'HR_STAT_BAK', 
9.   statid => 'N1', 
10.   statown => 'HR'); 
11. end; 
12.  
13. select * from HR_STAT_BAK； 

六：删除统计信息

1. begin 
2.   dbms_stats.delete_schema_stats(ownname => 'HR'); 
3. end; 

七：导入统计信息

1. begin 
2.   dbms_stats.import_schema_stats(ownname => 'HR', 
3.   stattab => 'HR_STAT_BAK', 
4.   statid => 'N1', 
5.   statown => 'HR'); 
6. end; 

八：异机导入，首先需要将HR_STAT_BAK表通过expdp/impdp方式导入到其他oracle服务器上

1. SQL> exec dbms_stats.upgrade_stat_table('HR','HR_STAT_BAK'); 
2. PL/SQL 过程已成功完成。 
3.  
4. SQL> EXEC dbms_stats.import_schema_stats(ownname => 'HR',stattab => 'HR_STAT_BAK',statid => 'N1',statown=>'HR'); 
5. PL/SQL 过程已成功完成。 

九：锁定与解锁统计信息

1. 1：锁定统计信息 
2. begin 
3.  dbms_stats.lock_schema_stats(ownname=>'HR'); 
4. end; 
5.  
6. 2：查询哪些表或索引的统计信息被锁定 
7. select * from user_tab_statistics where stattype_locked='ALL'; 
8. select * from user_ind_statistics where stattype_locked='ALL'; 
9.  
10. 3：如果在锁定条件下收集统计信息，则会出现如下报错 
11. ERROR at line 1: 
12. ORA-20005: object statistics are locked (stattype = ALL) 
13. ORA-06512: at "SYS.DBMS_STATS", line 20337 
14. ORA-06512: at "SYS.DBMS_STATS", line 20360 
15. ORA-06512: at line 1 
16.  
17. 4：解锁统计信息 
18. begin 
19.  dbms_stats.lock_schema_stats(ownname=>'HR'); 
20. end;