Oraclehint.docx
- 文档编号:9872211
- 上传时间:2023-05-21
- 格式:DOCX
- 页数:17
- 大小:23.04KB
Oraclehint.docx
《Oraclehint.docx》由会员分享,可在线阅读,更多相关《Oraclehint.docx(17页珍藏版)》请在冰点文库上搜索。
Oraclehint
其实Oracle的优化器有两种优化方式,
基于规则的优化方式(Rule-BasedOptimization,简称为RBO)
基于代价的优化方式(Cost-BasedOptimization,简称为CBO)
所以hint也不例外,除了/*+rule*/其他的都是CBO优化方式
RBO方式:
优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。
比如我们常见的,当一个where子句中的一列有索引时去走索引。
CBO方式:
它是看语句的代价(Cost),这里的代价主要指Cpu和内存。
优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
统计信息给出表的大小、有少行、每行的长度等信息。
这些统计信息起初在库内是没有的,是做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此应及时更新这些信息。
优化模式包括Rule、Choose、Firstrows、Allrows四种方式:
Rule:
基于规则的方式。
Choolse:
默认的情况下Oracle用的便是这种方式。
指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。
FirstRows:
它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。
AllRows:
也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。
没有统计信息则走RBO的方式
Oracle在那配置默认的优化规则
A、Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。
B、Sessions级别通过ALTERSESSIONSETOPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。
C、语句级别用Hint(/*+...*/)来设定
为什么表的某个字段明明有索引,但执行计划却不走索引?
1、优化模式是all_rows的方式
2、表作过analyze,有统计信息
3、表很小,Oracle的优化器认为不值得走索引。
提示
不区分大小写,多个提示用空格分开
如:
select/*+hint1(tab1)hint2(TAB1idx1)*/col1,col2fromtab1wherecol1='xxx';
如果表使用了别名,那么提示里也必须使用别名
如:
select/*+hint1(t1)*/col1,col2fromtab1t1wherecol1='xxx';
如果使用同一个表的多个用,号分开
如:
select/*+index(t1.A,t1.B)*/col1,col2
fromtab1t1
wherecol1='xxx';
oracle10ghints知识,
10g数据库可以使用更多新的optimizerhints来控制优化行为。
现在让我们快速解析一下这些强大的新hints:
1、spread_min_analysis
使用这一hint,你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。
其他的一些优化,如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等,得到了继续使用。
由于在规则数非常大的情况下,电子表格分析会很长。
这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。
例:
SELECT/*+SPREAD_MIN_ANALYSIS*/...
2、spread_no_analysis
通过这一hint,可以使无电子表格分析成为可能。
同样,使用这一hint可以忽略修订规则和过滤产生。
如果存在一个电子表格分析,编译时间可以被减少到最低程度。
例:
SELECT/*+SPREAD_NO_ANALYSIS*/...
3、use_nl_with_index
这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。
只有在以下情况中,它才使用特定表格作为内部表格:
如果没有指定标签,CBO必须可以使用一些标签,且这些标签至少有一个作为索引键值加入判断;反之,CBO必须能够使用至少有一个作为索引键值加入判断的标签。
例:
SELECT/*+USE_NL_WITH_INDEX(polrecpolrind)*/...
4、CARDINALITY
此hint定义了对由查询或查询部分返回的基数的评价。
注意如果没有定义表格,基数是由整个查询所返回的总行数。
例:
SELECT/*+CARDINALITY([tablespec]card)*/
5、SELECTIVITY
此hint定义了对查询或查询部分选择性的评价。
如果只定义了一个表格,选择性是在所定义表格里满足所有单一表格判断的行部分。
如果定义了一系列表格,选择性是指在合并以任何顺序满足所有可用判断的全部表格后,所得结果中的行部分。
例:
SELECT/*+SELECTIVITY([tablespec]sel)*/
然而,注意如果hintsCARDINALITY和SELECTIVITY都定义在同样的一批表格,二者都会被忽略。
6、no_use_nl
Hintno_use_nl使CBO执行循环嵌套,通过把指定表格作为内部表格,把每个指定表格连接到另一原始行。
通过这一hint,只有hashjoin和sort-mergejoins会为指定表格所考虑。
例:
SELECT/*+NO_USE_NL(employees)*/...
7、no_use_merge
此hint使CBO通过把指定表格作为内部表格的方式,拒绝sort-merge把每个指定表格加入到另一原始行。
例:
SELECT/*+NO_USE_MERGE(employeesdept)*/...
8、no_use_hash
此hint使CBO通过把指定表格作为内部表格的方式,拒绝hashjoins把每个指定表格加入到另一原始行。
例:
SELECT/*+NO_USE_HASH(employeesdept)*/...
9、no_index_ffs
此hint使CBO拒绝对指定表格的指定标签进行fastfull-indexscan。
Syntax:
/*+NO_INDEX_FFS(tablespecindexspec)*/
在SQL优化过程中常见HINT的用法(前10个比较常用,前3个最常用):
1./*+INDEX*/和/*+INDEX(TABLEINDEX1,index2)*/和/*+INDEX(tab1.col1tab2.col2)*/和/*+NO_INDEX*/和/*+NO_INDEX(TABLEINDEX1,index2)*/
表明对表选择索引的扫描方法.第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引;第二种是指定索引名且可指定多个索引;第三种是10g开始有的,指定列名,且表名可不用别名;第四种即全表扫描;第五种表示禁用某个索引,特别适合于准备删除某个索引前的评估操作.如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉.
例如:
SELECT/*+INDEX(BSEMPMSSEX_INDEX)USESEX_INDEXBECAUSETHEREAREFEWMALEBSEMPMS*/FROMBSEMPMSWHERESEX='M';
2./*+ORDERED*/
FROM子句中默认最后一个表是驱动表,ORDERED将from子句中第一个表作为驱动表.特别适合于多表连接非常慢时尝试.
例如:
SELECT/*+ORDERED*/A.COL1,B.COL2,C.COL3FROMTABLE1A,TABLE2B,TABLE3CWHEREA.COL1=B.COL1ANDB.COL1=C.COL1;
3./*+PARALLEL(table1,DEGREE)*/和/*+NO_PARALLEL(table1)*/
该提示会将需要执行全表扫描的查询分成多个部分(并行度)执行,然后在不同的操作系统进程中处理每个部分.该提示还可用于DML语句.如果SQL里还有排序操作,进程数会翻倍,此外还有一个一个负责组合这些部分的进程,如下面的例子会产生9个进程.如果在提示中没有指定DEGREE,那么就会使用创建表时的默认值.该提示在默认情况下会使用APPEND提示.NO_PARALLEL是禁止并行操作,否则语句会使用由于定义了并行对象而产生的并行处理.
例如:
select/*+PARALLEL(tab_test,4)*/col1,col2fromtab_testorderbycol2;
4./*+FIRST_ROWS*/和/*+FIRST_ROWS(n)*/
表示用最快速度获得第1/n行,获得最佳响应时间,使资源消耗最小化.
在update和delete语句里会被忽略,使用分组语句如groupby/distinct/intersect/minus/union时也会被忽略.
例如:
SELECT/*+FIRST_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';
5./*+RULE*/
表明对语句块选择基于规则的优化方法.
例如:
SELECT/*+RULE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';
6./*+FULL(TABLE)*/
表明对表选择全局扫描的方法.
例如:
SELECT/*+FULL(A)*/EMP_NO,EMP_NAMFROMBSEMPMSAWHEREEMP_NO='SCOTT';
7./*+LEADING(TABLE)*/
类似于ORDERED提示,将指定的表作为连接次序中的驱动表.
8./*+USE_NL(TABLE1,TABLE2)*/
将指定表与嵌套的连接的行源进行连接,以最快速度返回第一行再连接,与USE_MERGE刚好相反.
例如:
SELECT/*+ORDEREDUSE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAMFROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
9./*+APPEND*/和/*+NOAPPEND*/
直接插入到表的最后,该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中,所以可以提高速度.当然也会浪费些空间,因为它不会使用那些做了delete操作的块空间.NOAPPEND提示则相反,所以会取消PARALLEL提示的默认APPEND提示.
例如:
insert/*+append*/intotest1select*fromtest4;
insert/*+parallel(test1)noappend*/intotest1select*fromtest4;
10./*+USE_HASH(TABLE1,table2)*/
将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间.类似于在连接表的结果中遍历每个表上每个结果的嵌套循环,指定的hash表将被放入内存,所以需要有足够的内存(hash_area_size或pga_aggregate_target)才能保证语句正确执行,否则将在磁盘里进行.
例如:
SELECT/*+USE_HASH(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
---------------------------------------------------------------------
11./*+USE_MERGE(TABLE)*/
将指定的表与其它行源通过合并排序连接方式连接起来.特别适合于那种在多个表大量行上进行集合操作的查询,它会将指定表检索到的的所有行排序后再被合并,与USE_NL刚好相反.
例如:
SELECT/*+USE_MERGE(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
12./*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.可能会限制某些索引的使用.
例如:
SELECT/*+ALL+_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHEREEMP_NO='SCOTT';
13./*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法.如果经常访问连接表但很少修改它,那就使用集群提示.
例如:
SELECT/*+CLUSTER*/BSEMPMS.EMP_NO,DPT_NOFROMBSEMPMS,BSDPTMSWHEREDPT_NO='TEC304'ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
14./*+INDEX_ASC(TABLEINDEX1,INDEX2)*/
表明对表选择索引升序的扫描方法.从8i开始,这个提示和INDEX提示功能一样,因为默认oracle就是按照升序扫描索引的,除非未来oracle还推出降序扫描索引.
例如:
SELECT/*+INDEX_ASC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO='SCOTT';
15./*+INDEX_COMBINE(TABLEINDEX1,INDEX2)*/
指定多个位图索引,对于B树索引则使用INDEX这个提示,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:
SELECT/*+INDEX_COMBINE(BSEMPMSSAL_BMIHIREDATE_BMI)*/*FROMBSEMPMSWHERESAL<5000000ANDHIREDATE 16./*+INDEX_JOIN(TABLEINDEX1,INDEX2)*/ 合并索引,所有数据都已经包含在这两个索引里,不会再去访问表,比使用索引并通过rowid去扫描表要快5倍. 例如: SELECT/*+INDEX_JOIN(BSEMPMSSAL_HMIHIREDATE_BMI)*/SAL,HIREDATEFROMBSEMPMSWHERESAL<60000; 17./*+INDEX_DESC(TABLEINDEX1,INDEX2)*/ 表明对表选择索引降序的扫描方法. 例如: SELECT/*+INDEX_DESC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHEREDPT_NO='SCOTT'; 18./*+INDEX_FFS(TABLEINDEX_NAME)*/ 对指定的表执行快速全索引扫描,而不是全表扫描的办法.要求要检索的列都在索引里,如果表有很多列时特别适用该提示. 例如: SELECT/*+INDEX_FFS(BSEMPMSIN_EMPNAM)*/*FROMBSEMPMSWHEREDPT_NO='TEC305'; 19./*+NO_EXPAND*/ 对于WHERE后面的OR或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展,缩短解析时间. 例如: SELECT/*+NO_EXPAND*/*FROMBSEMPMSWHEREDPT_NO='TDC506'ANDSEX='M'; 20./*+DRIVING_SITE(TABLE)*/ 强制与ORACLE所选择的位置不同的表进行查询执行.特别适用于通过dblink连接的远程表. 例如: SELECT/*+DRIVING_SITE(DEPT)*/*FROMBSEMPMS,DEPT@BSDPTMSDEPTWHEREBSEMPMS.DPT_NO=DEPT.DPT_NO; 21./*+CACHE(TABLE)*/和/*+NOCACHE(TABLE)*/ 当进行全表扫描时,CACHE提示能够将表全部缓存到内存中,这样访问同一个表的用户可直接在内存中查找数据.比较适合数据量小但常被访问的表,也可以建表时指定cache选项这样在第一次访问时就可以对其缓存.NOCACHE则表示对已经指定了CACHE选项的表不进行缓存. 例如: SELECT/*+FULL(BSEMPMS)CAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS; 22./*+PUSH_SUBQ*/ 当SQL里用到了子查询且返回相对少的行时,该提示可以尽可能早对子查询进行评估从而改善性能,不适用于合并连接或带远程表的连接. 例如: select/*+PUSH_SUBQ*/emp.empno,emp.ename,itemnofromemp,orderswhereemp.empno=orders.empnoandemp.deptno=(selectdeptnofromdeptwhereloc='XXX'); 远程连接其他数据库,注意判断数据库是否启动,或者是否有需要的表,否则会出错 23./*+INDEX_SS(TABLEINDEX1,INDEX2)*/ 指示对特定表的索引使用跳跃扫描,即当组合索引的第一列不在where子句中时,让其使用该索引 参考资料 OracleSQLhints /*+hint*/ /*+hint(argument)*/ /*+hint(argument-1argument-2)*/ Allhintsexcept/*+rule*/causetheCBOtobeused.Therefore,itisgoodpractisetoanalyzetheunderlyingtablesifhintsareused(orthequeryisfullyhinted.Thereshouldbenoschemanamesinhints.Hintsmustusealiasesifaliasnamesareusedfortablenames.Sothefollowingiswrong: select/*+index(scott.empix_emp)*/fromscott.empemp_alias better: select/*+index(emp_aliasix_emp)*/...fromscott.empemp_alias Whyusinghints Itisaperfectvalidquestiontoaskwhyhintsshouldbeused.Oraclecomeswithanoptimizerthatpromisestooptimizeaquery'sexecutionplan.Whenthisoptimizerisreallydoingagoodjob,nohintsshouldberequiredatall.Sometimes,however,thecharacteristicsofthedatainthedatabasearechangingrapidly,sothattheoptimizer(ormoreaccuratly,itsstatistics)areoutofdate.Inthiscase,ahintcouldhelp.Itmustalsobenoted,thatOracleallowstolockthestatisticswhentheylookidealwhichshouldmakethehintsmeaninglessagain. Hintcategories Hintscanbecategorizedasfollows: HintsforOptimizationApproachesandGoals, HintsforAccessPaths,HintsforQueryTransformations, HintsforJoinOrders, HintsforJoinOperations, HintsforParallelExecution, AdditionalHints DocumentedHints HintsforOptimizationApproachesandGoals ALL_ROWS Oneofthehintsthat'invokes'theCostbasedoptimizer ALL_ROWSisusuallyusedforbatchprocessingordatawarehousingsystems. FIRST_ROWS Oneofthehintsthat'invokes'theCostbasedoptimizer FIRST_ROWSisusuallyusedforOLTPsystems. CHOOSE Oneofthehintsthat'invokes'theCostbasedoptimizer Thishintletstheserverchoose(betweenALL_ROWSandFIRST_ROWS,basedonstatisticsgat
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oraclehint