860高性能SQL优化-Oracle 高性能SQL优化.ppt

1、Oracle 高性能SQL优化,航天信息股份有限公司,什么是Oracle性能管理,Oracle性能管理是一种利用已证实过的方法，反复试验、判断和实现优化方案的循序渐进的过程。SQL语句优化是性能管理的重要组成部分。,概述,Oracle性能管理的类型（1）,主动的性能管理？以一种高性能的体系结构思想指导设计和开发完善的系统。它也指定期监控系统的性能，注意各种苗头，在它们成为实际问题前加以解决。,概述,Oracle性能管理的类型（2）,被动的性能管理？涉及性能评估、故障排除、优化以及在现有硬件和软件体系结构内对环境进行的优化。它在出现问题时进行处理。相对于所获得的性能收益，其代价一般较高。,概述,

2、什么是优化,优化是有目的地更改系统的一个或多个组件，使其满足一个或多个目标的过程。对Oracle来说，优化是进行有目的的调整组件以改善性能，即增加吞吐量，减少响应时间。,概述,SQL优化的目标,去掉不必要的大型表的全表扫描 缓存小型表的全表扫描 检验优化索引的使用 检验优化的连接技术 检查子查询,概述,不同优化阶段性能收益,设计阶段,开发阶段,测试阶段,运行维护阶段,生命周期,效果,概述,SQL调整的障碍,对特定的SQL生成器的调整 调整不可再用的SQL语句 来自管理方面的抵制 来自SQL程序员的抵制,概述,SQL语句处理过程,SQL执行,SQL语句的解析过程,调入库缓存 (内存) 检验安全性

3、 检验语法 重新格式化查询 (查询重写),SQL执行,减少SQL解析的技术,将所有的SQL语句放置在存储过程中在SQL语句中避免使用直接量,SQL执行,生成执行计划,对Oracle来说查询速度和查询效率是两个完全不同的概念。Oracle有两个优化目标，这两个目标分别反映了为了满足SQL的要求而使用的各自不同的通用手段。最大速度 以最短的时间返回结果集(Oracle的first_rows优化器模式)最小资源占用 使用少量的机器和磁盘资源 (all_rows优化器模式),SQL执行,SQL语句性能评估的方法,SQL语句的执行计划执行SQL语句所需要的时间,SQL执行,执行SQL语句所需要的时间,S

4、QL执行,查看SQL语句的执行计划,SQL执行,SQL语句的执行计划,表访问方式 全表扫描 散列获取 ROWID访问,索引访问方式 索引范围扫描 单个索引扫描 降序索引范围扫描 And-equal过滤器,连接操作方式 嵌套循环连接 散列连接 排序合并连接 Star连接 Connect by连接,SQL执行,SQL语句的优化器,优化器种类: 基于规则的优化器-RBO 基于成本的优化器-CBO优化器模式: Rule Choose First_rows All_rows,SQL优化器,基于规则的优化器-RBO,RBO不使用任何表或索引的统计数据,而是用试探法来确定到达数据的最佳访问路径。RBO根据迭

5、代过程来生成执行计划，并且检查from子句中的每个表，以及在该查询中每个表与其他表的所有连接方式。每一个可行的路径根据它们所要消耗的成本进行排序，并选择成本最低的路径。,SQL优化器,RBO选择低成本的依据,01）ROWID读取02）使用簇连接的单独记录03）使用散列簇主键的单独记录04）使用主键的单独记录05）簇连接06）散列簇主键07）索引簇主键08）复合主键09）单字段索引10）索引字段的结合范围查找 11）排序合并连接 12）索引字段上的MAX或MIN13）索引字段上的ORDER BY 14）全表扫描,SQL优化器,基于规则优化器的特性,总是使用索引。 总是从驱动表开始。 只有在不可避

6、免的情况下，才使用全表扫描。 任何索引都可以 有时会选择一个并非最理想的索引来对查询服务。,SQL优化器,基于规则的SQL语句优化,更改基于规则的驱动表。 如果基于规则的优化器没有使用正确的索引，可通过索引提示强制使用我们想要使用的索引，或者在索引中混入数据类型的方式禁用那些不想的查询中使用的索引。,SQL优化器,基于成本的优化器-CBO,Oracle基于成本的优化器的创建目的是为基于规则的优化提供更加复杂的替代方式。CBO 优化器需要了解表和索引统计数据的细节, SQL优化根据成本进行排序,并选择成本最低的路径。,SQL优化器,表和索引的统计数据,表数据 记录的数目 物理数据块的数目 索引数

7、据 索引中惟一值的数目 索引中值的分布 索引的可选择性 索引簇因素,SQL优化器,全表扫描索引访问快速完全索引扫描,SQL访问数据块的方法,优化SQL表访问,基于规则的优化器如果探测到 可用的索引，总是选择使用索引，否则使用全表扫描。 基于成本的优化器拥有有关表中数据的信息，当估计到全表扫描的成本低于索引访问的成本时，即使存在索引，它也选择全表扫描。 ALL_ROWS 优化器模式更倾向于全表扫描，更适用于批量处理的查询。 FIRST_ROWS 优化器模式确保以最快的速度返回记录。如果目标表中不存在可用索引，那么它将执行全表扫描。,优化器如何选择全表扫描,优化SQL表访问,使用NULL条件的查询

8、 对没有索引字段的查询 带有like条件且掩码开始端使用%字符 带有Not Equals条件的查询 内置函数使索引无效 使用ALL_ROWS提示 使用并行提示,可能导致全表扫描的语法,优化SQL表访问,更改表访问方式,将子查询替换为标准的连接 重新书写SQL语句以更改表访问方法 添加或删除索引 使用提示强制改变表访问方法 在话会级更改optimizer_goal参数 在实例级optimizer_mode,优化SQL表访问,SQL优化过程概述,定位 ：定位不友好的、影响大的SQL语句 解释：用 PL/SQL或Explain Plan获得执行计划 调整：使用索引、提示和查询重写等手段对SQL语句进

9、行优化。关注点是：执行计划和运行时间。,过程概述,调整SQL语句,提示: 添加SQL提示会修改执行计划 索引: 添加B树索引去掉全表扫描 重新书写: 改变执行计划，尤其是在使用RBO时更改FROM子句中表的顺序时更明显。 位图索引: 查询中where子句中提及的所有低基数字段添加位图索引。 PL/SQL： SQL被替换为对PL/SQL软件包的调用，软件包中含执行查询所需的存储过程。,过程概述,评估全表扫描的合法性,对于原始排序的表 读取少于记录数40%的查询应使用索引范围扫描。反之，使用全表扫描。 对于未排序的表 读取少于记录数7%的查询应使用索引范围扫描。反之，使用全表扫描。,全表扫描和并行

10、查询,对小表的全表扫描,select DISTINCT A.FUNC_DM, A.FUNC_MC, A.URL, A.FUNC_DM_F, A.“DESC“, A.YJCZBZfrom HTJS.HT_GNRKB A, HTJS.HT_ROLE_MENU Bwhere A.SWJG_JC = 5 and B.ROLE_ID = 报税业务操作员 and not A.FUNC_DM = bs and substr(A.FUNC_DM, 0, 2) = bs and (B.YXBZ = Y and substr(A.FUNC_DM, 0, length(B.FUNC_DM) = B.FUNC_DM)

11、and A.FUNC_DM not in (select A.FUNC_DM from HTJS.HT_GNRKB A, HTJS.HT_ROLE_MENU B whereB.ROLE_ID = 报税业务操作员 and B.YXBZ = Nand substr(A.FUNC_DM, 0, length(B.FUNC_DM) = B.FUNC_DM) ;,全表扫描和并行查询,该语句的执行计划,全表扫描和并行查询,避免全表扫描的方法,添加B树索引 添加位图索引 添加基于函数的索引 强制CBO使用带有index提示的索引,全表扫描和并行查询,Oracle并行查询,持久并行机制不推荐alter tab

12、le table_name parallel degree N ; 单个查询的并行机制select /*+ FULL(emp) PARALLEL(emp,5) */ enamefrom emp;,全表扫描和并行查询,设置最优的并行度,服务器上CPU的数量 存储表所在磁盘数 如果根据分区实施并行化，则其中一个因素是基于分区划分的将被分区访问的分区的数目。 如果并行DML操作使用全局索引维护，所有全局索引中事务处理空闲表的最小值需要更新。 最优的并行度可以安全地设置为N-1，而N代表SMP或MPP簇中处理器的数量,全表扫描和并行查询,并行查询和表连接,带有并行查询的嵌套循环连接排序合并连接和并行查

13、询并行化散列连接,全表扫描和并行查询,带有并行查询的嵌套循环连接,在嵌套循环连接中，通常使用索引对表进行连接。然而，可以创建一个执行计划，其中该执行计划调用嵌套循环连接，一般只对连接中的一个驱动表执行全表扫描。在大多数情况下，索引访问更快!,全表扫描和并行查询,并行查询和表连接,排序合并连接和并行查询,合并连接总是对表执行全表扫描，所以排序合并连接可以提供最理想的并行查询。排序合并连接最适合产生非常大结果集的查询，可能适合于没有where子句的大表连接，或没有可用索引对表进行连接的查询。,全表扫描和并行查询,并行查询和表连接,并行化散列连接,Oracle将驱动表读入hash_area_size

14、中的一个RAM队列中，生成内存数组，并使用专用的散列方法将内存数组与更大的表连接起来。对于等值连接操作，散列连接可能胜过嵌套循环连接，特别是驱动表小到可以完全装入hash_area_size的情况下。如果驱动表太大，散列连接会把临时段写入TEMP表空间中，导致查询速度降低。,全表扫描和并行查询,并行查询和表连接,Oracle的排序,Oracle的排序通常发生在如下情况： SQL语句中包含order by子句 SQL中包含group by子句 SQL中包含select distinct子句 创建索引时 SQL中包含union或minus SQL优化器调用排序合并连接,优化SQL语句排序,添加索引

15、避免排序,避免全表扫描和不必要排序的最好方法是添加索引。使用first_rows模式时，Oracle通常使用索引代替排序操作，从而避免order by子句。,优化SQL语句排序,不必要的排序,缺失索引查询需要字段索引 排序合并连接就会执行排序以连接关键字，通常嵌套循环连接好 使用distinct索引中使用distinct子句通常会调用索引以去掉重复记录,优化SQL语句排序,SQL查询中使用提示的重要原则(1),仔细检查提示语法最好使用完整注释语法，如使用/*+ hint*/ 如果在查询中为表指定了别名，那么不能使用表名称select /*+ index(e,dept_idx)*/ename,d

16、eptnofrom emp e; 不要在提示中使用模式名称，否则提示被忽略。select /*+ index(scott.emp,dept_idx) */enamefrom emp;,使用提示进行优化,SQL查询中使用提示的重要原则(2),检验提示 如果提示指定不可用的访问路径，提示将被忽略。如索引范围扫描上指定一个parallel提示，则提示被忽略。提示和查询之间的矛盾并不是显而易见的，下面的emp表的ename不存在索引时，则Select /*+ first_rows */ename,deptnofrom emporder by ename;,使用提示进行优化,使提示无效条件,Cluste

17、r与非簇表一同使用 Hash与非簇表一同使用 Hash_aj不存在子查询 Index指定索引不存在 Index_combine不存在位图索引 Merge_aj不存在子查询 Parallel调用的不是TABLE ACCESS FULL计划 Push_subq不存在子查询 Star事实表中存在不恰当索引 Use_concat在where子句中不存在多个or条件 Use_nl表中不存在索引,使用提示进行优化,优化器提示(1),first_rows提示 是一种基于成本的优化方法，目的是提供最快的反应时间，要保证查询中所涉及的表及索引拥有统计数据。一般来说，optimizer_mode=choose模式

18、的SQL都应该使用first_rows和rule提示进行测试，观察运行性能是否有所提高。,使用提示进行优化,优化器提示(2),all_rows提示 是一种基于成本的优化方法，目的是提供最佳吞量和最小的资源消耗。倾向于使用全表扫描，而且不适用于OLTP数据库。在基于规则的数据库中添加提示时，要保证查询中所涉及的表及索引拥有统计资料。,使用提示进行优化,优化器提示(3),rule提示在怀凝CBO生成了非优化的执行计划时，通常在调整SQL语句时先尝试使用rule提示。,使用提示进行优化,表连接提示(1),use_nl提示强制对目标表执行嵌套循环连接。与其他连接不同，只需要驱动表的名称。嵌套循环连接是

19、最古老的连接方式，它几乎总是与基于规则的优化方式共同使用。select /*+ use_nl(e) */e.ename,hiredate,mfrom emp e,bonus bwhere e.ename=b.ename;,使用提示进行优化,表连接提示(2),use_hash提示散列连接是Oracle用以从驱动表向RAM区中装载记录的方法。当这两个表都非常大的情况下，散列连接通常与并行查询结合使用！select /*+ use_hash(e,b) parallel(e,4) */e.ename,hiredate,m.from emp e,bonus bwhere e.name=b.ename ;

20、 散列连接通常快于嵌套循环连接，特别是在驱动表已经在查询的where子句中过滤，只乘下少量记录的情况。Use_hash提示非常挑剔，必须满足很多条件，如统计数据。,使用提示进行优化,表连接提示(3),use_merge提示强制执行一个排序合并操作，排序合并操作通常与并行查询结合使用，因为排序合并连接对表执行全表扫描。排序合并连接最适用于生成大型结果集的查询，如日常报表和表细节总结查询，或用来处理不使用连接主键索引的表。select /*+ use_merge(a,b) parallel(e,4) */e.ename,hiredate,mfrom emp e,bonus bwhere e.ena

21、me=b.ename ;,使用提示进行优化,表连接提示(4),star提示 star提示强制使用星型查询计划。前提是查询中存在至少三个表，而且在事实表中存在恰当的索引。比传统的连接方式快得多，传统的连接方式先将最小的引用表与事实表连接，再将其化的引用表与中间生成表连接。,使用提示进行优化,表连接提示(4)-续,星型连接工作过程：Oracle先服务于小维表查询，并将结果连接到笛卡尔生产表中，其中这个表存储Oracle内存中。这个虚拟表包含来自所有相关维表的所有记录。这个虚拟表的主键是来自维表所有主键的组合。如果这个主键与事实表中组合索引相匹配，这个查询将很快处理。一旦确定了引用表的数目，则Ora

22、cle将基于事实表执行中间表的嵌套循环连接。,使用提示进行优化,表连接提示(4)-续,使用star提示必须满足下列条件: 必须存在连接在一起的至少三个表，其中有一个大的事实表和几个小维表。 在事实表的字段上必须存在一个索引，这些字段将是每个表的连接主键。从Oracle8i开始，需要使用位图索引，而不是连接在一起的索引。 必须检验执行计划，以确保在执行连接时使用嵌套循环连接操作。,使用提示进行优化,表反连接提示（1）,merge_aj提示 在使用全表访问比索引访问更好的情况下，可以在NOT IN子查询中使用merge_aj提示以便执行反连接。Select DNAMEFrom DEPTWhereD

23、EPTNO not in(select /*+ merge_aj */ DEPTNOfrom EMPwhere JOB=SALESMAN)；,使用提示进行优化,表反连接提示（1）-续,使用NOT IN的条件: 如果在子查询的字段中确实不存在空值，而又在子查询中使用了提示，外层查询块生成大量记录的情况下，才考虑使用NOT IN子查询。 根据连接的类型，可以考虑使用hash_aj或merge_aj提示执行反连接。而只有在NOT IN子句中要求的字段有非空限制的时候，反连接hash_aj或merge_aj才起作用。,使用提示进行优化,表反连接提示（2）,hash_aj提示 hash_aj提示放置于N

24、OT IN子查询中用来在希望执行散列连接时，执行散列反连接。,使用提示进行优化,Index提示(1),使用Index提示的原则 如果表名或索引名拼写错误，查询将不使用提示。Select /*+ index(erp,dept_idx) */ from emp; 表名在提示中必须指定Select /*+ index(dept_idx) */ from emp; 如果在查询中使用表别名，那么提示必须使用表别名。Select /*+ index(emp,dept_idx) */ from emp e; 索引名称是可选的，如果没有指定，优化器将指定表中“最佳的”索引，但不推荐！Select /*+ in

25、dex(e) */ from emp e;,使用提示进行优化,指定索引提高性能的例子,selectDISTINCT FP_DM fromhtjs.FP_SFD whereFP_DM like %3300%andSWJG_DM = 133010300;-IDX_FP_SFD_FPDM_SFLB_FPQSHM;-INDEX_FP_SFD(SWJG_DM,SFLB,SFRQ);,使用提示进行优化,指定索引前的执行计划,使用提示进行优化,指定索引提高性能的例子,select /*+ index(fp_sfd,index_fp_sfd) */ DISTINCT FP_DMfrom htjs.FP_SFD

26、whereFP_DM like %3300%andSWJG_DM = 133010300;,使用提示进行优化,指定索引后该例的执行计划,使用提示进行优化,Index提示(2),and_equal提示如果表拥有非惟一字段索引，而且你期望使用多个索引服务于该查询，那么可以使用and_equal提示。and_equal提示将合并这些索引，并使这些单独的索引操作时就像单个连锁索引一样。and_equal提示需要指定目标表名和至少两个索引名，但索引名不能超过五个。,使用提示进行优化,Index提示(2)-续,Select /*+ and_equal(emp,job_idx,mgr_idx) */ENAM

27、E,JOB,MGRFromEMPWhereJob=SALESMANAndMgr=7628;,使用提示进行优化,Index提示(3),index_desc提示index_desc提示要求在范围扫描中使用降序索引。在使用MAX内置函数计算字段的最大值时，index_desc提示将提供更好的性能。Select /*+ index_desc(emp,sal_idx) */ENAME,Max(SALARY)FromEMPGroup by ENAME;,使用提示进行优化,Index提示(4),index_combine提示index_combine提示强制使用位图索引作为表的访问路径。index_comb

28、ine提示要求以优化器对两个位图索引都执行ROWID交集操作。实际操作中最好指定表名和两个索引名。Select /*+ rule(emp) index_combine(emp,dept_bit,job_bit) */ENAME,JOB,DEPTNO,MGRFromEMPWhereJOB=SALESMANAndDEPTNO=30; -这种位图合并方法将在很大程度上降低大表的执行时间,使用提示进行优化,Index提示(5),index_ffs提示索引快速完全扫描可以在不访问任何记录的情况下完成查询。优化器使用多块读取扫描索引中的所有数据块。可以同时使用index_ffs提示和parallel提示，

29、使索引快速完全扫描的速度更快。,使用提示进行优化,Index提示(5)续,index_ffs提示create indexDept_job_idxonEmp(job);selectename,job,deptno,mgrfrom empwhere job=SALESMAN;select /*+ index_ffs(emp,dept_job_idx) */ename,job,deptno,mgrfrom empwhere job=SALESMAN;,使用提示进行优化,Index提示(6),use_concat提示use_concat提示要求为查询中所有OR条件使用union all执行计划，并将这

30、个查询重新书写为多个查询。当一个查询对位图索引字段使用多个OR条件时，Oracle将自动使用位图索引。如果有SQL查询的where子句中存在大量的OR条件，那么通常需要使用use_concat提示。,使用提示进行优化,Index提示(6)-续,use_concat提示selectENAMEfromEMPwheredeptno=10orsal5000orjob=CLERK;,供选择的解决办法: 1)有的索引字段基数较低，可以在deptno、sal、job上创建三个位图索引，并生成一个位图合并执行计划。 2）调用use_concat提示，将查询分解为三个独立的B树索引扫描。,使用提示进行优化,Pa

31、rallel提示,对于全表扫描来说，parallel提示要求表查询以并行模式执行，同时，并行查询从动进程的数量由degree参数决定。select /*+ FULL(emp) PARALLEL(emp,) */ename,salfrom emp ;需要同时使用full提示和parallel提示以确保调用全表扫描。如果优化器没有使用全表扫描，则parallel提示被忽略。,使用提示进行优化,表访问提示(1),full提示使用full提示最常见的情况是，已经确定索引范围扫描要读取大量表中数据块，而并行全表扫描将提高查询的速度。,使用提示进行优化,表访问提示(2),簇表提示簇通常用于两个表之间存在着

32、一对多的关系，而大多数查询都是从主表向成员表访问的情况。将主表和成员表的记录存储在相同的数据块，从而一个单独的数据块输入输出可以读取来自两个表的记录。,使用提示进行优化,表访问提示(2)-续,hash提示 select /*+ hash */e.ename,d.deptno fromemp e,dept d wheree.deptno=d.deptno anddeptno=20 ;,cluster提示 select /*+ cluster */e.ename,d.deptnofromemp e,dept dwheree.deptno=d.deptnoanddeptno=20 ;,使用提示进行优

33、化,ordered 提示,在基于成本的优化器中，ordered提示要求按from子句中指定的顺序进行连接，而指定from子句中的第一个表作为驱动表。通常与其他提示结合使用，以确保多个表按照正确的顺序连接。从而免避过多的评估所花费的时间。例如：select /* ordered use_hash(emp,dept) use_nl(sal,bonus */emp.ename,dept.deptnofromemp,dept,salgrade,bonuswhere ,使用提示进行优化,子查询中的提示,SQL语句中的每一个子查询都可以使用提示来改进执行计划。位于外部查询的指定表的提示不会干涉子查询，子查

34、询指定表的提示也不会干涉外部查询。select A.EMPNOfromDEPT B,EMP A,SALGRADE Cwhereexists (select /*+ INDEX(A) */A.EMPNOfrom DEPT B,EMP A,SALGRADE CwhereA.DEPTNO=B.DEPTNO )andA.DEPTNO=B.DEPTNO And A.JOBclerk ;,使用提示进行优化,调用基于规则的优化器,设置init.ora参数optimizer_mode=rule 会话层使用alter session set optimizer_mode=rule 为基于成本的SQL添加rule

35、提示：/*+ rule*/,基于规则优化器调整,使用choose作为默认优化模式时存在的问题,使用choose作为默认优化模式时,根据CBO统计资料的存在与否来评估是使用RBO还是CBO。 使用choose模式的真正问题是，只要任何表或索引包含了统计资料，choose模式就假定你期望使用CBO。,基于规则优化器调整,默认优化器模式,选择具有最优总体性的默认优化器模式的目标是：最小化人工SQL调整的数量。 决定是否使用基于规则的优化模式作为默认值的过程是：首先使用rule模式运行一天生产数据库，接着使用first_rows模式再运行一天，然后根据最终用户的性能反馈和STATSPACK的性能测量，

36、最后决定基于规则的优化是否可以作为合适的默认优化器模式。,基于规则优化器调整,基于规则的优化器,驱动表位置在RBO中，驱动表是from子句中的最后一个表。select /*+ rule */Emp.ename,Emp.deptno,Bmfrom Emp,BonuswhereEmp.ename=bonus.ename；,驱动表和表基数 驱动表应当是返回最小数目记录的表，而不一定是拥有最少记录数的表。selectcustomer_namefromcustomer,orderwherecustomer.cust_nbr=order.cust_nbrandorder_status=backordere

37、d;,基于规则优化器调整,有关调整基于规则查询的提示,在RBO中进行优化是通过重新书写查询更改执行计划来实现的。主要建议是：1）重新排序表名2）重新排序布尔谓词更改where子句中语句的顺序。Oracle将从SQL语句的底层按照与由AND分开的布尔表达式相反的顺序对SQL进行解析。3）添加基于成本的提示4）仔细评估连接方法如果使用RBO，必须处理嵌套循环连接。尽管在RBO中可以使用排序合并连接，但应当将所有执行排序合并连接的RBO查询，替换为使用Oracle并行查询的基于成本的等价访问。,基于规则优化器调整,基于规则的优化器无效的情况,在基于规则的优化模式中，所有的索引都有同等的级别！ 在有些

38、情况下,基于规则的优化器无法选择优化的执行计划。这通常是因为RBO无法使用高级的索引结构和并行查询。即使在新功能的帮助下，RBO有时因为优化器没有意识到表和索引中惟一值的数量，可能选择了非优化的索引为查询服务。 基于规则的优化器不识别位图索引和基于函数的索引，将会导致全表扫描。,基于规则优化器调整,统计数据和基于成本的优化,使用基于成本优化的一个前提条件是，存在表和索引的统计数据。对于表来说，统计数据包括记录数和表中记录平均长度等信息。索引的统计数据包括：有关索引中惟一值的数量、索引中元素的数量，以及在表空间中物理存储等方面的信息。,基于成本优化器调整,基于成本优化和SQL 优化,Oracle

39、8i基于成本的优化器已经可以与基于规则优化相兼容，那么许多SQL调整专业人员就可以使用基于成本的默认优化器模式，再对个体查询进行调整，从而达到最快的执行计划。大多数SQL调整专业人员在遇到可疑的SQL语句时，做的第一件事就是添加rule提示，并察看生成的执行计划是否更加有效。在读取任何记录之前，基于成本的优化器必须创建一个执行计划，这个执行计划告诉Oracle访问目标表和索引的顺序，CBO首先对不同的数据访问路径的成本进行评估，然后选择成本最低的路径。,基于成本优化器调整,基于成本的表连接,通用原则：嵌套循环连接和散列连接应当始终使用所有表上的索引，除了使用排序合并连接以外，在服务于表连接时，

40、一般不要调用全表扫描。,优化步骤： 1）执行计划中查找全表扫描。如果发现了则尝试做的第一件事是，使用index提示或rule提示，从而强制对所有的表使用索引。 2）尝试计算使用不同连接技术的查询的执行时间。,基于成本优化器调整,基于成本的表连接与Oracle版本,在Oracle8i之前版本的基于成本的优化器中存在的一个缺陷是，如果在查询中使用了多于4个表的连接，那么基于成本的优化器几乎总是为其中的一个表调用全表扫描。在Oracle8i及以后版本中，仍然可能有这样的非优化的查询计划。,基于成本优化器调整,带有子查询的SQL语句的优化（1）,首先应该检查where子句，并确定该子查询是非关联子查询

41、还是关联子查询。标准连接通常比子查询快，因此，要仔细评估所有个体SQL查询，以决定是否子查询可以更改为标准连接。,基于成本优化器调整,带有子查询的SQL语句的优化（2）,Select* fromstudent wherestudent_id in(selectstudent_idfrom registrationwhere grade=A) ;,selectstudent.*fromstudent,registrationwherestudent.student_id=registration.student_idandregistration.grade=A;,基于成本优化器调整,优化复杂的

42、布尔查询（1）,布尔谓词中的复合OR条件1：OR中包含相同字段的数值2：OR条件引用不同的字段,基于成本优化器调整,优化复杂的布尔查询（2）,对于所有字段都相同的OR类型，基于成本的执行计划将取决于是否存在索引。如果一个查询的位图索引字段存在多个OR条件，那么Oracle将自动使用位图索引。,Select /*+ first_rows */ENAME FromEMP WhereDeptno in (10,20,30,40) And(job=CLEAKor job=SALESMANorjob=SECRETARY) ；,基于成本优化器调整,布尔谓词中的复合AND条件(1),Select /*+ f

43、irst_rows */ENAME，JOB，DEPTNO FromEMP WhereDeptno = 10 andSal 5000 andJob=CLERK;,三个字段都有B树索引，CBO选择具有选择最具有选择性的索引。 如果以上三个字段上建立三个位图索引，则调用了BITMAP CONVERSION执行计划的变更。,基于成本优化器调整,布尔谓词中的复合AND条件(2),select /*+ index_combine(emp,emp_deptno_bit,emp_JOB_bit,emp_sal_bit) */ENAME，JOB，DEPTNO fromEMP whereDeptno = 10 a

44、ndSal 5000 andJob=CLERK;,有位图索引时使用index_combine 提示将对表强制使用位图访问路径。如果语句中没有这个提示，优化器将选择表访问成本最低的位图索引的布尔组合。使用这个提示指导优化器执行两个位图的ROWID 交集操作。在实际操作中，最好指定表名称和提示中的两个索引名称。,基于成本优化器调整,基于规则的优化器和基于成本的优化器,RBO非常稳定，而且结果可以预测；而CBO更加智能，但是通常不可预测。 随着CBO一个新版本的产生，使用RBO的数据库的数量就会降低。 如果使用基于成本的优化器作为默认值，可通过使用rule提示来覆盖所指定的查询。 如果使用基于规则的

45、优化器作为默认值，可通过添加基于成本提示并分析所选定的表索引来调整SQL查询。 在RBO中，from子句中表的顺序决定表的连接顺序。From子句中最后一个表是驱动表 RBO中最常见的缺陷是，它无法了解表中每个索引的可选择性。 where子句中布尔谓词的顺序也会影响RBO的行为。,基于规则优化器调整,什么是索引,索引是建立在表的一列或多个列上的辅助对象，它有利于快速访问表的数据。 索引由于其内在的结构，它具有某些内在的开销，这些开销依赖于为了检索由索引中ROWID指定的行所访问的表中的块数，并且这个开销可能会超过进行全表扫描的成本。,用索引优化SQL,何时使用索引,建立索引的目的之一是减少I/O

46、操作，如果当一个查询使用索引时相对于全表扫描执行了更多的I/O操作，则使用索引的意义会明显降低。 主键：即主键约束，Oracle会自动建立惟一性索引，因此不要再为主键中的列建立索引了。,用索引优化SQL,具有吸引力的索引,非惟一索引 惟一索引 位图索引 基于函数的索引 反向键索引 降序索引 索引编排表,局部有前缀分区索引 局部无前缀分区索引 全局有前缀分区索引 散列分区索引 组合分区索引,用索引优化SQL,怎样建立最佳索引,建立索引不是一个很容易完成的任务，因为它完全依赖于应用程序的数据查询模式。 如果了解应用程序，则问题不复杂；如果了解数据访问的最常用的方法，则能够建立最佳索引是肯定的；总之

47、，必须确定最常使用列的列表，并且作出关于要建立的索引数量、需要的列组合以及将建立的索引的类型的决定。,用索引优化SQL,建立最佳索引需要回答的问题,特定表中的数据访问的最常用的列组合是什么？ 打算在其上建立索引的一组给定列，其选择性是什么？如果一些列始终有值并且相对惟一，则它们应该是索引的前导列，为建立索引，按照可能具有惟一值的概率，对这些列进行子程序排列。 在where子句中引用的所有列都需要进行索引吗？如果列具有很低的数据基数，并且或者可具有空值的话，则不需要。要有意识地从索引中去掉这样的列。 索引所基于的表用于事务处理还是主要用于查询？如果它是事务处理的表，则需要确定由于给出附加的索引对

48、事务处理的潜在的负面影响。在较好的查询性能和事务处理时的负面影响之间怎样权衡？如果它是主要用于查询的表，则最好建立索引。 是建立单列索引与组合索引？如果了解应用程序中经常使用的列，则建立一个组合索引比分别建立单个索引更好。,用索引优化SQL,索引不当的例子,DELETE FROM HTJS.RZ_WLRZ_FPDKL_MX WHERE NSRSBH = 440301192193934AND(FPDM = 3300043140 AND FPHM = 02436911OR FPMW =4647450*5-70051/188-77171*/4832+39889760-74+5884);- RZ_WL

49、RZ_FPDKL_MX主键索引为FPID、NSRSBH,用索引优化SQL,该例的执行计划,用索引优化SQL,索引不当的例子,SELECT*FROMFX_QY_NSRXXWHERENSRSBH = :b1 AND FX_ZT = 1;-PK_FX_QY_NSRXX(SWJG_DM,NSRSBH);-IDX_FX_QY_NSRXX_SWJG_DM_CSSJ;,用索引优化SQL,该例的执行计划,用索引优化SQL,通过添加索引来消除排序,消除排序的最好的方法之一就是添加索引。当运行在first_rows模式时，Oracle总是会用排序操作场所中的索引来分析order by子句。 有时，Oracle会用全索引扫描来代替磁盘排序操作。Oracle优化器会考虑查询返回的记录数目，而且会在排序成本与通过索引检索记录的成本之间做出权衡。在大多数情况下，Oracle只会在有大量的记录将被检索出来时考虑全索引扫描，而且要满足这个查询还必须进行一次磁盘排序。,用索引优化SQL,推荐使用索引的两个原因,提高从数据表中检索少量的数据集的速度。 预先排序结果集，这样SQL的order by等子句就不会引发内部排序了。,