Oracle_优化器使⽤（oracle11g）
⼀：优化器介绍
  优化器（optimizer）是oracle数据库内置的⼀个核⼼⼦系统。优化器的⽬的是按照⼀定的判断原则来得到它认为的⽬标SQL在当前的情形下的最⾼效的执⾏路径，也就是为了得到⽬标SQL的最佳执⾏计划。依据所选择执⾏计划时所⽤的判断原则，oracle数据库⾥的优化器⼜分为RBO（基于原则的优化器）和CBO（基于成本的优化器，SQL的成本根据统计信息算出）两种。
RBO
  Oracle会在代码⾥事先为各种类型的执⾏路径定⼀个等级，⼀共15个等级，从等级1到等级15，oracle认为等级1的执⾏路径是效率最⾼的，等级15是执⾏效率最差的。对于等级相同的执⾏计划，oracle根据⽬标对象的在数据字典中缓存的顺序判断选择哪⼀种执⾏计划。RBO 是⼀种适合于OLTP类型SQL语句的优化器。相对于CBO⽽⾔，RBO有着先天的缺陷，⼀旦SQL语句的执⾏计划出现问题，将很难调整。那么RBO执⾏计划出现问题，怎么调整⽬标SQL的执⾏计划呢？⼀般有如下⽅法：等价改写⽬标SQL，⽐如在where条件对number和date类型的列添加0（deptno+0>100），varchar2或char类型的列可以添加⼀个“空字符”，例如“||”。对于多表连接的SQL，可以改变from表的连接顺序（RBO会按照从右往左的顺序决定谁是驱动表，谁是被驱动表。）来达到改变⽬标SQL执⾏计划的⽬的。我们也可以改变相关对象在数据字典
中缓存的顺序（创建顺序），来改变执⾏计划。RBO最⼤的缺点是以oracle内置代码的规则作为判断标准，⽽并没有考虑到实际⽬标表的数据量以及数据分布情况。
CBOoracle11g 创建数据库
  CBO选择执⾏计划时，以⽬标SQL成本为判断原则，CBO会选择⼀条执⾏成本最⼩的执⾏计划作为SQL的执⾏计划，各条执⾏路径的成本通过⽬标SQL语句所涉及的表、索引、列等的统计信息算出。这⾥的成本是oracle通过相关对象的统计信息计算出来的⼀个值，它实际上代表⽬标SQL对应执⾏步骤所消耗的IO、CPU、⽹络资源（针对于dblink下的分布式数据库系统⽽⾔）的消耗量，oracle会把⽹络资源的消耗量计算在IO成本内，实际上你看到的成本为IO、CPU资源，另外需要注意的是，oracle在未引⼊系统统计信息之前，CBO所计算的成本值实际全是基于IO计算的。
  1、集的势（cardinality）
  Cardinality是CBO特有的概念，指集合所包含的记录数，即结果集⾏数。Cardinality实际上表⽰对⽬标SQL某个具体执⾏步骤的执⾏结果所包含的记录数的估算，当然，如果针对整个⽬标SQL，那么此时的cardinality就表⽰对该SQL最终执⾏结果所包含的记录数的估算。Cardinality和成本值得估算息息相关，因为oracle得到的制定结果集所需要消耗的IO资源可以近似的看成随着结果集所包含的记录数递增⽽递增。所以，SQL编写的⼀个原则就是“尽早的过滤更多的数据”。
  2、可选择率（Selectivity）
  Selectivity也是CBO特有的概念，它是指“施加指定谓语条件后返回的结果集的记录数占未施加任何谓语条件的原始结果集的记录数的⽐率”，取值范围为0~1，其值越⼩，代表可选择性越好。Selectivity也可成本值得估算息息相关，可选择率越⼤，意味着所返回的结果集的cardinality越⼤，所以估算的成本就越⼤。实际上CBO就是利⽤selectivity来计算对应结果集的cardinality的，即：Computed
cardinality=original*selectivity
  Cardinility和selectivity的值会直接影响CBO对于相关执⾏步骤成本的估算，进⽽影响CBO对于⽬标SQL的执⾏计划的选择。
  3、可传递性
  可传递性也是CBO的特有属性，它是查询转换中所做的第⼀件事情，其含义是CBO会对⽬标SQL做等价改写，进⽽提供更多的执⾏路径给⽬标CBO，增加得到最佳执⾏计划的可能性。RBO不会对⽬标SQL做等价改写。Oracle⾥可传递性分为以下3种情况：
  1）简单谓语传递
  ⽐如原⽬标SQL中的谓语条件是“t1.c1=t2.c1 and t1.c1=10”，则CBO可能会给谓语条件额外加上“t2.c1=10”。
  2）连接谓语传递
  ⽐如原⽬标SQL中的谓语条件是“t1.c1=t2.c1 and t2.c1=t3.c1”，则CBO可能会给谓语条件额外加上“t1.c1=t3.c1”。
  3）外链接谓语传递
  ⽐如原⽬标SQL中的谓语条件是“t1.c1=t2.c1(+) and t1.c1=10”，则CBO可能会给谓语条件额外加上“t2.c1(+)=10”。
  4、CBO的局限性
  1）CBO会默认⽬标SQL语句where条件中出现的各个列之间出现是独⽴的，没有任何关联。并且CBO会根据这个前提条件来计算selectivity和cardinality，进⽽估算成本并选择执⾏计划。但是这种假设并不全是正确的，⽣产中列与列之间存在关联的现象并不罕见。⽬前可以⽤来缓解上述负⾯影响的⽅法是使⽤动态采样和多列统计信息。但动态采样的准确性取决于采样数据的质量以及数量，⽽多列统计信息并不适合⽤于多表之间有关联的情形，所以这两种⽅法只能算是缓解，并不算是完美的解决⽅案。
  2）CBO会假设所有的⽬标SQL都是独⽴运⾏的，并且互不⼲扰，但实际情况却不完全是这样。
  3）CBO对直⽅图统计信息有多⽅限制。主要体现在如下2个⽅⾯：
    （1）在oracle 12c之前，frequency类型的直⽅图所对应的bucket的数量不能超过254，这样如果列的distinct数量超过254，oracle 就会使⽤height balanced类型的直⽅图。对于height balanced类型的直⽅图⽽⾔，oracle不会记录所有的nonpopular value的值，所以此种情况下CBO选错执⾏计划的概率会⽐frequency类型的情形要⾼。
    （2）在oracle数据库⾥，如果针对⽂本类型的字段⼿机直⽅图统计信息，则oracle只会将⽂本的前32个字符（实际只取前15个）取出来并将其转换为浮点数，然后将浮点数作为上述⽂本字段的直⽅图统计信息记录在数据字典⾥。
  4）CBO在解析多表关联的⽬标SQL时，可能会漏选正确的执⾏计划。在oracle 11gR2中，CBO在解析这种多表关联的⽬标SQL时，所考虑的各个表的连接顺序的总和受隐含参数_OPTIMIZER_MAX_PERMUTATIONS的限制。这意味着⽬标SQL不管有多少种连接顺
序，CBO最多只考虑其中根据_OPTIMIZER_MAX_PERMUTATIONS计算出来的有限种可能性。
⼆:oracle 11g优化器
  Oracle 11g使⽤基于代价的优化⽅式(CBO)，这⾥的代价主要指CPU和内存，优化器只要参照的是表及索引的统计信息。
创建表（plan_table）
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql;-----创建oracle EXPLNAN PLAN计划使⽤的表(plan_table)，在sqlplus环境下执⾏UTLXPLAN.SQL脚本：设置语句的执⾏计划
explain plan [set statement_id [=] <string literal>] [into <table_name>] for <sql_statement>;-----把某条sql语句执⾏计划分析数据插⼊plan_table表plan_table字段解释

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。