添加hints干预优化

添加hints干预优化基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在ORACLE中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。各种连接的解释：1嵌套循环连接在嵌套循环连接中,Oracle从第一个行源中读取第一行,然后和第二个行源中的数据进行对比。所有匹配的记录放在结果集中,然后Oracle将读取第一个行源中的下一行。按这种方式直至第一个数据源中的所在行都经过处理。第一个记录源通常称为外部表,或者驱动表,相应的第二个行源称为内部表。使用嵌套循环连接是一种从连接结果中提取第一批记录的最快速的方法。在驱动行源表(就是您正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时,嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中提取的记录)非常庞大时,其他的连接方法可能更加有效。图1-1说明了程序清单1-1中查询执行的方法。select/*+ordered*/ename,dept.deptnofromdept,empwheredept.deptno=emp.deptno1.1.2排列合并连接在排列合并连接中,Oracle分别将第一个源表、第二个源表按它们各自要连接的列排序,然后将两个已经排序的源表合并。如果找到匹配的数据,就放到结果集中。在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。但是,排列合并连接只能用于等价连接(WHERED.deptno=E.dejptno,而不是WHERED.deptno=E.deptno)。排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。图1-2解释了程序清单1-2查询执行的方法。select/*+ordered*/ename,dept.deptnofromemp,deptwheredept.deptno=emp.deptno1.1.3哈希连接当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。在哈希连接中,Oracle访问一张表(通常是较大的表),并在内存中建立一张基于连接键的哈希表。然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否有匹配的记录。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接(HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE)。这和嵌套循环连接有点类似——Oracle先建立一张哈希表以利于操作进行。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢）。最后，只有基于代价的优化器才可以使用哈希连接。图1-3解释了执行程序清单1-3查询的方法。select/*+ordered*/ename,dept.deptnofromemp,deptwheredept.deptno=emp.deptno1.1.4索引连接在Oracle8i之前,您必须访问表,直到索引包含了所有需要的信息。从Oracle8i起，如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引，而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列，而您只想访问有限的列时，这种方法非常有效。WHERE子句约束条件越多，执行速度越快。因为优化器在评估执行查询的优化路径时，将把约束条件作为选项看待。您必须在合适的列（那些满足整个查询的列）上建立索引，这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引，或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描，连接索引的优势在于：快速全局扫描只有一个单一索引满足整个查询。索引连接可以有多个索引满足整个查询。程序清单1-4中的两个索引（一个在ENAME上，一个在DEPTNO上）创建于执行相应的查询之前。注意该查询不需要直接访问表！图1-4解释了索引的合并过程。程序清单1-4合并两个索引的查询selectENAME,DEPTNOfromEMPwhereDEPTNO=20andENAME=’DULLY’；各种hints：hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：1)使用的优化器的类型2)基于代价的优化器的优化目标，是all_rows还是first_rows。3)表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。4)表之间的连接类型5)表之间的连接顺序6)语句的并行程度除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。如何使用hints:Hints只应用在它们所在sql语句块(statementblock，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT,UPDATE,orDELETE关键字的后面使用hints的语法：{DELETE|INSERT|SELECT|UPDATE}/*+hint[text][hint[text]]...*/or{DELETE|INSERT|SELECT|UPDATE}--+hint[text][hint[text]]...注解：1)DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。2)“+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。3)hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。4)text是其它说明hint的注释性文本如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。使用全套的hints：当使用hints时，在某些情况下，为了确保让优化器产生最优的执行计划，我们可能指定全套的hints。例如，如果有一个复杂的查询，包含多个表连接，如果你只为某个表指定了INDEX提示(指示存取路径在该表上使用索引)，优化器需要来决定其它应该使用的访问路径和相应的连接方法。因此，即使你给出了一个INDEX提示，优化器可能觉得没有必要使用该提示。这是由于我们让优化器选择了其它连接方法和存取路径，而基于这些连接方法和存取路径，优化器认为用户给出的INDEX提示无用。为了防止这种情况，我们要使用全套的hints，如：不但指定要使用的索引，而且也指定连接的方法与连接的顺序等。下面是一个使用全套hints的例子，ORDERED提示指出了连接的顺序，而且为不同的表指定了连接方法：SELECT/*+ORDEREDINDEX(b,jl_br_balances_n1)USE_NL(jb)USE_NL(glccglf)USE_MERGE(gpgsb)*/b.application_id,b.set_of_books_id,b.personnel_id,p.vendor_idPersonnel,p.segment1PersonnelNumber,p.vendor_nameNameFROMjl_br_journalsj,jl_br_balancesb,gl_code_combinationsglcc,fnd_flex_values_vlglf,gl_periodsgp,gl_sets_of_booksgsb,po_vendorspWHERE...指示优化器的方法与目标的hints：ALL_ROWS--基于代价的优化器，以吞吐量为目标FIRST_ROWS(n)--基于代价的优化器，以响应时间为目标CHOOSE--根据是否有统计信息，选择不同的优化器RULE--使用基于规则的优化器例子：SELECT/*+FIRST_ROWS(10)*/employee_id,last_name,salary,job_idFROMemployeesWHEREdepartment_id=20;SELECT/*+CHOOSE*/employee_id,last_name,salary,job_idFROMemployeesWHEREemployee_id=7566;SELECT/*+RULE*/employee_id,last_name,salary,job_idFROMemployeesWHEREemployee_id=7566;指示存储路径的hints：FULL/*+FULL(table)*/指定该表使用全表扫描ROWID/*+ROWID(table)*/指定对该表使用rowid存取方法，该提示用的较少INDEX/*+INDEX(table[index])*/使用该表上指定的索引对表进行索引扫描INDEX_FFS/*+INDEX_FFS(table[index])*/使用快速全表扫描NO_INDEX/*+NO_INDEX(table[index])*/不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描SELECT/*+FULL(e)*/employee_id,last_nameFROMemployeeseWHERElast_nameLIKE:b1;SELECT/*+ROWID(employees)*/*FROMemployeesWHERErowid'AAAAtkAABAAAFNTAAA'ANDemployee_id=155;SELECT/*+INDEX(Asex_index)usesex_indexbecausetherearefewmalepatients*/A.name,A.height,A.weightFROMpatientsAWHEREA.sex=’m’;SELECT/*+NO_INDEX(employeesemp_empid)*/employee_idFROMemployeesWHEREemployee_id200;指示连接顺序的hints:ORDERED/*