Oracle数据的优化器有两种优化方法：基于代价/成本的优化器（CBO）和基于规则的优化器（RBO），系统在对SQL进行优化的时候，使用哪种优化决定于初始化参数OPTIMIZER_MODE，该参数值可以设置为：CHOOSE，RULE，ALL_ROWS，FIRST_ROWS。在Oracle9i以后的版本中还引入了新的优化参数命令：FIRST_ROWS_1，FIRST_ROWS_10， FIRST_ROWS_100，FIRST_ROWS_1000。（具体的说明将在以后的BLOG文章中介绍）Oracle推荐使用CBO优化方式，当系统使用CBO方式优化SQL的时候，要使其执行计划达到最优化，需要定期执行数据统计，必须保证统计数据的及时性，否则可能得不到预计的优化效果，或与预计的优化效果相差悬殊。
要对数据库对象生成统计信息，可以有以下方法：
- 完全统计法：analyze table table_name compute statistics;
- 抽样估计法：analyze table table_name estimate statistics sample 30 percent;
我们可以采用以下两种方法，对数据库的表和索引及簇表定期分析生成统计信息，保证应用的正常性能。
1. 在系统设置定时任务，执行分析脚本。
在数据库服务器端，我们以UNIX用户oracle，运行脚本analyze，在analyze中，我们生成待执行 sql脚本，并运行。（假设我们要分析scott用户下的所有表和索引）
Analyze脚本内容如下：
| sqlplus scott/tiger << EOF set pagesize 5000 set heading off SPOOL ANALYTAB.SQL SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT ;" FROM USER_TABLES； SPOOL OFF SPOOL ANALYIND.SQL SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;" FROM USER_TABLES； SPOOL OFF SPOOL ANALYZE.LOG @ANALYTAB.SQL @ANALYIND.SQL SPOOL OFF EXIT |
在UNIX平台上crontab加入，以上文件，设置为每个月或合适的时间段运行。
或者将如下脚本保存成analyze.sql，然后在sqlplus里面执行：
| set pagesize 5000 set linesize 300 set trims on set heading off set feedback off SPOOL analyTab.sql SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;' FROM USER_TABLES; SPOOL OFF SPOOL analyIdx.sql SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS FOR ALL INDEXES;' FROM USER_TABLES; SPOOL OFF SPOOL analyLog.log @@analyTab.sql @@analyIdx.sql SPOOL OFF |
2. 利用ORACLE提供的程序包（PACKAGE）对相关的数据库对象进行分析。
有以下的程序包可以对表，索引，簇表进行分析。
包中的存储过程的相关参数解释如下：
- TYPE可以是：TABLE，INDEX，CLUSTER中其一。
- SCHEMA为：TABLE，INDEX，CLUSTER的所有者，NULL为当前用户。
- NAME为：相关对象的名称。
- METHOD是：ESTIMATE，COMPUTE，DELETE中其一，当选用ESTIMATE，下面两项，ESTIMATE_ROWS和 ESTIMATE_PERCENT不能同时为空值。
- ESTIMATE_ROWS是：估算的抽样行数。
- ESTIMATE_PERCENT是：估算的抽样百分比。
- METHOD_OPT是：有以下选项，
- FOR TABLE /*只统计表*/
- [FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只统计有索引的表列*/
- FOR ALL INDEXES /*只分析统计相关索引*/
- PARTNAME是：指定要分析的分区名称。
