oracle 10G中一些SQL优化的亮点.doc

10G中一些SQL优化的亮点 1、优化器默认为CBO,OPTIMIZER_MODE默认值为ALL_ROWS。不再使用古老的RBO模式,但RULE、CHOOSE并没有彻底消失,有些时候仍然可以作为我们调试的工具。 2、CPU Costing的计算方式现在默认为CPU+I/O两者之和.可通过DBMS_XPLAN.DISPLAY_CURSOR观察更为详细的执行计划。 3、增加了几个有用SQL Hints: INDEX_SSblock tabs inds,INDEX_SS_ASC,INDEX_SS_DESC；SS为SKIP SCAN的缩写。skip scan以前讨论的很多。 NO_USE_Nblock tabs,NO_USE_HAHS,NO_USE_MERGE,NO_INDEX_FFS,NO_INDEX_SS,NO_STAR_TRANSFORMATION,NO_QUERY_TRANSFORMATION. 这几个HINT不用解释，一看就知道目的是什么。 USE_NL_WITH_INDEX(block tabs index):这个提示和Nested Loops有关，通过提示我们可以指定Nested Loops循环中的内部表,也就是开始循环连接其他表的表。CBO是否会执行取决于指定表是否有索引键关联。 QB_NAME(blockname) 这个提示可以给某个查询定义一个name，并且可以在其他hints中使用这个name，并且将这个hints作用到这个name对应的查询中.其实从10G开始,Oracle对一些特定的查询自动使用queryblockname 4、10G中支持在hint中使用queryblockname select * from a1where idin (select /*+ qb_name(sub1)*/ idfrom a1where idin (2,10,12);ExecutionPlan-Plan hash value:173249654-| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 2 | 34 | 3 (34)| 00:00:01 | 1 | TABLE ACCESSBY INDEX ROWID| A1 | 1 | 14 | 1 (0)| 00:00:01 | 2 | NESTED LOOPS | | 2 | 34 | 3 (34)| 00:00:01 | 3 | SORTUNIQUE | | 2 | 6 | 1 (0)| 00:00:01 | 4 | INLIST ITERATOR | | | | | |* 5 | INDEX RANGE SCAN | IDX_A1_ID| 2 | 6 | 1 (0)| 00:00:01 |* 6 | INDEX RANGE SCAN | IDX_A1_ID| 1 | | 0 (0)| 00:00:01 |-select * from a1where idin (select /*+ qb_name(sub1) full(sub1 a1)*/ idfrom a1where idin (2,10,12);Plan hash value:1882950619-| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 2 | 34 | 17 (6)| 00:00:01 | 1 | TABLE ACCESSBY INDEX ROWID| A1 | 1 | 14 | 1 (0)| 00:00:01 | 2 | NESTED LOOPS | | 2 | 34 | 17 (6)| 00:00:01 | 3 | SORTUNIQUE | | 2 | 6 | 15 (0)| 00:00:01 |* 4 | TABLE ACCESSFULL | A1 | 2 | 6 | 15 (0)| 00:00:01 |* 5 | INDEX RANGE SCAN | IDX_A1_ID| 1 | | 0 (0)| 00:00:01 |-修改成错误的queryblocknameselect * from a1where idin (select /*+ qb_name(sub1) full(sub2 a1)*/ idfrom a1where idin (2,10,12);ExecutionPlan-Plan hash value:173249654-| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 2 | 34 | 3 (34)| 00:00:01 | 1 | TABLE ACCESSBY INDEX ROWID| A1 | 1 | 14 | 1 (0)| 00:00:01 | 2 | NESTED LOOPS | | 2 | 34 | 3 (34)| 00:00:01 | 3 | SORTUNIQUE | | 2 | 6 | 1 (0)| 00:00:01 | 4 | INLIST ITERATOR | | | | | |* 5 | INDEX RANGE SCAN | IDX_A1_ID| 2 | 6 | 1 (0)| 00:00:01 |* 6 | INDEX RANGE SCAN | IDX_A1_ID| 1 | | 0 (0)| 00:00:01 |- 如果指定的queryblockname未定义,还是保持以前的执行计划,证明queryblockname起作用了. 5、新的hints.spread_no_analysis、spread_min_analysis 用于优化analyze查询.具体以后测试下 6、10GR2的一些变化. 增强了AWR的报告, 提供了专门的ash报告,可以通过新的ashrpt.sql($ORACLE_HOME/rdbms/admin下)脚本产生我们需要的ash报告;提供了类似于statspack获取AWR库中某个sql(通过脚本)的统计信息和执行信息 streams_pool_size现在成为ASSM中的一员 自动调节DB_FILE_MULTIBLOCK_READ_COUNT参数,Oracle会根据