快捷搜索:

Oracle 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视图

您可能还会对下面的文章感兴趣: