oracle定期表分析

sql 代码

1. ORACLE9以后如果你想用基于成本的优化器，需要定期(每周)对数据库里的表和索引做analyze分析。   
2. 　   
3. 　　数据库参数文件initorasid.ora里默认的优化器 optimizer_mode= choose   
4.       
5.    你要改成 optimizer_mode =first_rows (OLTP系统)   
6.      optimizer_mode =all_rows (DSS 系统)   
7.       
8.    下面是一个可以在UNIX环境自动生成分析表和索引的脚本analyze.sh   
9.       
10.    (sys用户的密码password要根据情况修改。)   
11.       
12. ---------------------------------------------------------------------------------------            
13.  su - oracle -c "sqlplussys/password"<
14.     
15.  set pages 9999   
16.  set heading off  
17.  set echo off  
18.  set feedback off  
19.     
20.  spool /oracle_backup/bin/analyze.sql;   
21.     
22.  select  
23.  'analyze table '||owner||'.'||table_name||'estimate statistics sample 5000 rows;'   
24.  from dba_tables   
25.  where owner not in('SYS','SYSTEM','PERFSTAT');   
26.   
27.  select  
28.  'analyze index '||owner||'.'||index_name||'compute statistics;'   
29.  from dba_indexes   
30.  where owner not in('SYS','SYSTEM','PERFSTAT');   
31.   
32.  spool off;   
33.   
34.  set echo on  
35.  set feedback on  
36.  spool /oracle_backup/log/analyze.log;   
37.  @/oracle_backup/bin/analyze.sql   
38.  spool off;   
39.  exit;   
40. ---------------------------------------------------------------------------------------    
41.   
42.    如果你经常变动的表和索引只属于某个特定的用户(如果是test)可以把上面的   
43.       
44.     owner not in('SYS','SYSTEM','PERFSTAT')　改成   
45.     owner in('TEST')      
46.       
47.    来进行定期的分析。   
48.       
49.    注意事项：如果你使用的是默认的优化器(choose)，一定不要定期使用上面那个analyze.sh脚本。   
50.          因为这时优化器可能更倾向于全表扫描。   
51.       
52.    如果统计分析资料不全，SQL运行时会对缺少统计资料的表进行数据采集。会大大降低SQL的执行速度。   
53.       
54.    我们要用下面这个del_analyze.sh脚本定期删除可能产生的分析结果,保证优化器按规则(rule)执行。   
55.       
56. ---------------------------------------------------------------------------------------            
57.  su - oracle -c "sqlplussys/password"<
58.   
59.  set pagesize9999;   
60.  set linesize 120;   
61.  set heading off;   
62.  set echo off;   
63.  set feedback off;   
64.   
65.  spool/oracle_backup/bin/del_analyze.sql;   
66.   
67.  select  
68.  'analyze table '||owner||'.'||table_name||'delete statistics;'   
69.  from dba_tables   
70.  where owner not in('SYS','SYSTEM','PERFSTAT');   
71.   
72.  select  
73.  'analyze index '||owner||'.'||index_name||'delete statistics;'   
74.  from dba_indexes   
75.  where owner not in('SYS','SYSTEM','PERFSTAT');   
76.   
77.  spool off;   
78.   
79.  set echo on;   
80.  set feedback on;   
81.  spool /oracle_backup/log/del_analyze.log;   
82.  @/oracle_backup/bin/del_analyze.sql   
83.  spool off;   
84.  exit;   
85. ---------------------------------------------------------------------------------------