Oracle 10g中的SQL优化亮点

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_SS[[@block] tabs [inds]],INDEX_SS_ASC,INDEX_SS_DESC；SS为SKIP SCAN的缩写。skip scan以前讨论的很多。

    NO_USE_N[[@block] 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 a1 where id in ( select /* + qb_name(sub1) */ id
from a1 where id in ( 2 , 10 , 12 ));

Execution Plan
-- --------------------------------------------------------
Plan hash value: 173249654

-- ---------------------------------------------------------------------------------------
| Id   | Operation                   | Name       | Rows   | Bytes | Cost ( % CPU) | Time     |
-- ---------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT             |            |      2 |      34 |      3   ( 34 ) | 00 : 00 : 01 |
|    1 |    TABLE ACCESS BY INDEX ROWID | A1         |      1 |      14 |      1    ( 0 ) | 00 : 00 : 01 |
|    2 |    NESTED LOOPS               |            |      2 |      34 |      3   ( 34 ) | 00 : 00 : 01 |
|    3 |     SORT UNIQUE                |            |      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 a1 where id in ( select /* + qb_name(sub1) full(@sub1 a1) */ id
from a1 where id in ( 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 ACCESS BY INDEX ROWID | A1         |      1 |      14 |      1    ( 0 ) | 00 : 00 : 01 |
|    2 |    NESTED LOOPS               |            |      2 |      34 |      17    ( 6 ) | 00 : 00 : 01 |
|    3 |     SORT UNIQUE                |            |      2 |      6 |      15    ( 0 ) | 00 : 00 : 01 |
|*    4 |        TABLE ACCESS FULL        | A1         |      2 |      6 |      15    ( 0 ) | 00 : 00 : 01 |
|*    5 |      INDEX RANGE SCAN         | IDX_A1_ID |      1 |        |      0    ( 0 ) | 00 : 00 : 01 |
-- ---------------------------------------------------------------------------------------

-- ---------
修改成错误的queryblockname
select * from a1 where id in ( select /* + qb_name(sub1) full(@sub2 a1) */ id from a1 where id in ( 2 , 10 , 12 ));
Execution Plan
-- --------------------------------------------------------
Plan hash value: 173249654

-- ---------------------------------------------------------------------------------------
| Id   | Operation                   | Name       | Rows   | Bytes | Cost ( % CPU) | Time     |
-- ---------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT             |            |      2 |      34 |      3   ( 34 ) | 00 : 00 : 01 |
|    1 |    TABLE ACCESS BY INDEX ROWID | A1         |      1 |      14 |      1    ( 0 ) | 00 : 00 : 01 |
|    2 |    NESTED LOOPS               |            |      2 |      34 |      3   ( 34 ) | 00 : 00 : 01 |
|    3 |     SORT UNIQUE                |            |      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会根据数据库的访问自动调节该参数 

    ·增加了SQL的优化模式,提供了SQL Tuning Adsivor,SQL Profile等工具.可自动优化sql语句 

    ·两个比较重要的视图:v$PROCESS_MEMORY,动态监控每个进程的pga使用,v$sqlstats某种情况下可以替换v$sql视图