ORACLE优化器问题。

Question

ORACLE8i(8.1.7)
optimizer_features_enable＝8.1.7
optimizer_mode      CHOOSEORACLE9i(9.2.0)
optimizer_features_enable＝9.2.0
optimizer_mode      CHOOSE两个数据库环境，数据库一样，索引一样，为什么执行效率在9i的环境是如此的差？发现在8i的环境下，数据库会尽量使用索引，而9i却没有使用索引，进行全表扫描？？

Answer 1

先分析以下表再说：
analyze table t compute statistics;

Answer 2

[Q]怎么样分析表或索引 
[A]命令行方式可以采用analyze命令 
如Analyze table tablename compute statistics; 
Analyze index indexname estimate statistics; 
ANALYZE TABLE tablename COMPUTE STATISTICS 
    FOR TABLE 
    FOR ALL COLUMNS 
    FOR ALL INDEXES 
    FOR ALL INDEXED COLUMNS; 
等等。 
如果想分析整个用户或数据库，还可以采用 
Dbms_utility(8i以前的工具包) 
Dbms_stats(8i以后提供的工具包) 
如 
dbms_stats.gather_schema_stats(ownname=>User,estimate_percent=>100,cascade=> TRUE); 
这是对命令与工具包的一些总结 
1． 对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。 
a) 可以并行进行，对多个用户，多个Table 
b) 可以得到整个分区表的数据和单个分区的数据。 
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区 
d) 可以倒出统计信息 
e) 可以用户自动收集统计信息 
2． DBMS_STATS的缺点 
a) 不能Validate Structure 
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。 
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True 
3． 对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。 [Q]怎么样快速重整索引 
[A]通过rebuild语句，可以快速重整或移动索引到别的表空间 
rebuild有重建整个索引数的功能，可以在不删除原始索引的情况下改变索引的存储参数 
语法为 
alter index index_name rebuild tablespace ts_name 
       storage(……); 
如果要快速重建整个用户下的索引，可以用如下脚本，当然，需要根据你自己的情况做相应修改 
SQL> set heading off 
SQL> set feedback off 
SQL> spool d:\index.sql 
SQL> SELECT 'alter index ' || index_name || ' rebuild ' 
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);' 
FROM all_indexes 
WHERE ( tablespace_name != 'INDEXES' 
OR next_extent != ( 256 * 1024 ) 
) 
AND owner = USER 
SQL>spool off 
另外一个合并索引的语句是 
alter index index_name coalesce，这个语句仅仅是合并索引中同一级的leaf block 
消耗不大，对于有些索引中存在大量空间浪费的情况下，有一些作用。 

Answer 3

先把SQL语句在8i,9i下的执行计划打印出来看看.
如果相同，再分析表和索引;
如果不同, 用hints指定(不推荐)

Answer 4

为什么要对表进行分析？因为我选的是optimizer_mode      CHOOSE，所以应该不要对表进行分析，这样，Oracle会选索引，如何对表进行了分析，反而会进行全表扫描。

Answer 5

表和索引都一样，但两个环境下执行计划不一样。除非指定索引
--------------------------------------------------------
执行计划不同是正常的，关键是你需要弄清楚哪种执行计划是
最优计划。分析表和索引可能是必要的.如何获得精确的sql执行时间?  1. 启动sql_trace,然后在8i,9i分别运行你的SQL语句;
  2. 用Tkprof 解析一下已经生成的trace文件
  3. 打开这个解析好的文件，观察里面的性能指标.