Oracle 12C优化器的巨大变化，上生产必读（下）
在Oracle 12c数据库中，随着新的查询优化自适应方法的引入，还有对可用的统计信息的强化，优化器实现了一个巨大的飞跃。在《Oracle 12C优化器的巨大变化，上生产必读（上）》一文中，已经为大家介绍了优化器和其统计信息的一些新的特性，今天就让我们继续这个话题，一起来揭晓Oracle 12C其他的强大功能吧。
全局临时表通常用于存储应用程序上下文中的中间结果。一个全局临时表的定义，是全系统中拥有适当权限的所有用户所共享的，但其数据永远是会话私有的。在全局临时表（必须是会话级临时表，PRESERVE ROWS ON COMMIT，提交时保留数据）上收集统计信息是行得通的；然而，在前一个版本中，收集到的统计信息会被所有访问这张表的会话所使用。如果不同会话之间在临时表中存储的数据量或者数据的性质有巨大差异，这样就不太理想了。
现在，每个使用临时表的会话都可以有一份单独的统计信息。临时表上共享的统计信息可以通过一个新的DBMS_STATS选项GLOBAL_TEMP_TABLE_STATS来进行控制。在缺省情况下，这个选项被设置为SESSION，意思是每个访问临时表的回航都会有自己的一套统计信息。优化器会试图先使用会话的统计信息，但是如果会话统计信息不存在，那么优化器会使用共享统计信息。如果你想回到以前的表现，即仅有一份统计信息被所有会话所使用，可以将GLOBAL_TEMP_TABLE_STATS选项设为SHARED。
(图25. 将全局临时表不共享统计信息的缺省表现修改为强制共享统计信息)
当通过直接路径操作往一张全局临时表中填充数据（提交时保留数据的那种），会话级的统计信息会被自动创建，这是由于统计信息在线收集的作用，它免除了额外运行统计信息收集指令的必要性，并且不会影响其他会话使用的统计信息。
(图26. 使用直接路径加载对全局临时表填充数据会导致会话级别的统计数据被自动收集)
对于任何一个系统，为了维持一个可接受的性能水平，知道何时收集、如何收集及时的统计信息是至关重要的。想要确定一个环境中正在执行的是什么样的统计信息收集操作，对统计方法的变动将会如何影响系统，这可能是一项困难且耗时的任务。在Oracle 12c数据库中, 新的报告子程序被加入到DBMS_STATS包中，使得我们更易于监控何种统计收集活动正在进行，以及对这些操作的参数进行修改会有什么影响。
REPORT_STATS_OPERATIONS
这个新函数生成一个报表，显示的是关于在特定的时间窗口内发生了何种统计收集活动的详细信息。报表给出的细节包括每种操作何时发生，状态如何，覆盖了多少个对象。报表可以输出为文本或者HTML格式。
(图27. 生成一个新的 DBMS_STATS.REPORT_STATS_OPERATIONS 报表)
REPORT_SINGLE_STATS_OPERATION
这个新函数生成一个关于指定的统计信息收集操作的详细报表。
REPORT_GATHER_*_STATS
这组新的函数实际上并不会收集统计信息，但是会报告一个对象的列表：假如对应的GATHER_*_STATS过程被以相同的参数设定所调用，哪些对象将会被处理。REPORT_GATHER_*_STATS接受的输入参数和对应的GATHER_*_STATS过程相同，多了两个额外参数：详细级别(detail_level)以及格式(format)。
(图28. 生成新的DBMS_STATS.REPORT_GATHER_SCHEMA_STATS 报表)
在查询优化的过程中，Oracle会使用一系列复杂的技术对SQL语句进行变换。查询优化的这个阶段的目标是为了将原来的SQL语句变换为一个语义上等价、但是处理起来更加高效的SQL语句。Oracle 12c数据库引入了几种新的查询优化方法。
部分连接取值是这样一种优化技术，它是在连接顺序的生成过程中被使用的。这种技术的目标是为了避免产生重复的行，如果不用这种技术，这些重复只能在计划中随后用一个DISTINCT操作符来去除。通过早些用一个内连接(INNER JOIN)或者半连接(SEMI-JOIN)来取代DISTINCT操作符，这一步骤产生的行数将会减少。这应该会使得计划的总体性能得到改善，因为随后的步骤只需在缩小的行的集合上进行操作。
这种优化可以应用在如下类型的查询块：MAX(),MIN(), SUM(distinct), AVG (distinct), COUNT (distinct), 以及UNION, MINUS, INTERSECT 操作符的分支, [NOT] EXISTS 子查询等等。
考虑如下的DISTINCT查询：
(译者注：这个例子举得不好。order_id在orders中应该是唯一的，customer_id在customers表中也应该是唯一的，所以连接之后order_id在结果中也应该是唯一的，DISTINCT完全多余)
在Oracle 11g数据库中，ORDERSh CUSTOMERS之间的连接是一个哈希连接(HASH JOIN)，必须在去除重复行的排序发生前被完全取值。
(图29. Oracle 11g数据库要求在ORDERS 和 CUSTOMERS之间完全的连接，然后用unique sort去除重复)
有了部分连接取值，ORDERS 和 CUSTOMERS之间的连接被转换为一个半连接，这意味着一旦在CUSTOMERS表中找到一个匹配的CUSTOMER_ID，查询就会转移到下一个CUSTOMER_ID。通过将哈希连接转换为半连接，流入SORT UNIQUE 的行数大大减少，因为重复的行已经被去除了。变换过的SQL的计划如图30所示。(译者注：变换之后是HASH UNIQUE不是SORT UNIQUE)
(图 30. Oracle 12c数据库的计划显示了ORDERS 和 CUSTOMERS之间完全的半连接，没有产生重复的行)
应用开发者在包含EXISTS子查询的SQL语句中加入IS NULL谓词是不罕见的。加入额外的IS NULL谓词是因为 EXISTS子查询产生的半连接结果会去除具有空值的行，正如内连接(INNER JOIN)所做的一样。
考虑如下的查询：
这里的假定是在s.cust_id 列上可能有空值，而我们想要返回那些行。在Oracle 12c数据库之前，EXISTS子查询无法被展开，因为它出现在一个带有IS NULL谓词的OR谓词（析取谓词）中。因为无法被展开，导致不理想的计划被产生，子查询被作为过滤操作应用在SALES和PRODUCTS表的连接之后。
(图 31. Oracle 11g数据库的计划显示EXISTS 自查询被作为过滤操作应用于连接之后)
在Oracle 12c数据库中，一种新型的半连接被引入，称为接受空值的半连接。这种新连接扩展了半连接的算法，在连接的左边的表的连接列上检查空值。在这个例子中检查会发生在s.cust_id。如果列包含空值，那么相应的SALES表上的行被返回，否则半连接被执行以确定该行是否满足连接条件。接受空值的半连接计划如下图32所示。
(图32. Oracle 12c数据库的计划显示 EXISTS子查询被展开，在customers和sales之间使用了接受空值的半连接)
标量子查询是出现在SQL语句的SELECT子句的子查询。因为标量子查询不能被展开，所以一个相关的标量子查询（它引用了子查询之外的列）必须为外层查询产生的每一行被取值。考虑下面的查询：
在Oracle 11g数据库中，对于CUSTOMERS 表中 CUST_CREDIT_LIMIT大于50000的每一行，在sales表上的标量子查询都必须被执行。SALES表是大表，将它扫描很多次是非常耗费资源的。
(图 33.Oracle 11g数据库的计划显示，对于customers表返回的每一行，标量子查询都必须被取值)
将标量子查询展开并且将其转换为一个连接，就免除了为外层查询的每一行都进行取值的必要性。在Oracle 12c数据库中，标量子查询能够被展开，在这个例子中，SALES表上的标量子查询被转换成一个group-by视图。group-by视图确保每组会返回一行，正如标量子查询一样。查询中同样加入了一个外连接，这是为了确保即使当视图的结果为空时，CUSTOMERS的数据仍然会被返回。转换后的查询如下:
(图34. Oracle 12c数据库的计划显示标量子查询已经被展开成外连接和GROUP BY视图)
在Oracle 12c数据库之前，如果在外连接的左边有多个表是不合法的，会导致ORA-01417错误。
(图 35. Oracle 11g数据库不支持多表左外连接)
执行这样一个查询的方法是将其翻译为ANSI语法。可是，实现这样的ANSI语法会导致一个横向视图被使用。Oracle无法合并横向视图，所以优化器的计划在连接顺序和连接方法上的选择就受到了限制，这可能导致不理想的计划。
（注：横向视图指的是这样的视图：它引用了不在视图中的表的列）
(图36. ANSI 语法导致带有横向视图的计划，它无法被合并，因此限制了连接顺序)
在Oracle 12c数据库，用Oracle的(+)指定的多表左外连接现已被支持。你也能够合并一个外连接的左边的多表视图。能够合并视图就能允许更多的连接顺序和连接方法被考虑，结果是更优的计划会被选中。
(图37. 对新的多表作连接的支持允许视图合并，并且会导致更优的计划)
有几个新的初始化参数可以管理Oracle 12c数据库的优化器及其新特性。下面是关于这些新参数的详细介绍。
OPTIMIZER_ADAPTIVE_FEATURES
对于新的自适应查询优化功能的使用，包括自适应连接，SQL计划指令的创建和使用，是受OPTIMIZER_ADAPTIVE_FEATURES参数控制的。这个参数的缺省值和OPTIMIZER_FEATURES_ENABLE(OFE)相关。如果OFE被设置为12.1.0.1或者更高，那么OPTIMIZER_ADAPTIVE_FEATURES被设为TRUE, 所有的自适应查询优化功能将会启用。如果OFE被设为比12.1.0.1更低，那么OPTIMIZER_ADAPTIVE_FEATURES将会被设为FALSE, 所有的自适应查询优化功能都不会启用。
OPTIMIZER_ADAPTIVE_REPORTING_ONLY
乍一看，执行计划的自适应或者中途变动有些吓人。为了更好地理解多少SQL语句会受到新的自适应计划的影响，你可以将自适应计划开启为只报告模式，方法是将OPTIMIZER_ADAPTIVE_REPORTING_ONLY设置为TRUE(缺省值是FALSE)。在这个模式下，启用自适应连接方法所需的信息被收集，但是不会有任何修改计划的行动。这意味着缺省的计划总是会被使用，但计划在非报告模式下会如何调整的信息也会被收集。
OPTIMIZER_DYNAMIC_SAMPLING
虽然OPTIMIZER_DYNAMIC_SAMPLING参数不是新的，但它确实有了新的级别11, 这个级别控制动态统计信息的生成。当设置为级别11时，优化器会自动确定哪些语句会受益于动态统计信息，即使所有的对象已经有了统计信息。
在Oracle 12c数据库的开发过程中，大量的时间是被花在改善现有的优化器特性和功能，使得它更易于获得最佳的统计信息，确保最优的执行计划被选中。这个章节将会详细描述对现有优化器以及输送给它的统计信息的各方面的强化。
SQL计划的管理
SQL计划的管理 (SPM)是Oracle 11g数据库中最受欢迎的优化器新特性之一，因为它确保性能不会因为执行计划的变动而下降。为了确保这一点，只有被接受的执行计划才会被使用；任何实际发生了的计划的演变都会被跟踪并且在随后的一个时间点进行评估，并且只有当新计划在运行时间上显示出可观的改善，才会被接受。SQL计划的管理有三个主要部件：
1. 计划的捕获：
SQL计划基线的创建，它们存储了所有相关的SQL语句的执行计划。SQL计划基线存储在SYSAUX表空间的SQL管理库中。
2. 计划的选择：
对于具备SQL计划基线的语句，确保仅有被接受的执行计划才会被使用，并且把为一个语句找到的新的执行计划，记录为未接受计划并存储在SQL计划基线中。
3. 计划的演变：
为一个给定的语句评估所有未接受计划，只有那些显示出性能改善的计划才会变成SQL计划基线中被接受的计划。
在Oracle 12c数据库中，SPM的计划演变已被强化，以允许计划的自动演变。
计划的自动演变是由SPM的演变顾问完成的。演变顾问是一项自动任务(SYS_AUTO_SPM_EVOLVE_TASK)，它在夜晚的维护窗口进行操作，自动为SPM中的未接受计划执行演变过程。自动任务将所有SPM中的未接受计划排等级（新发现的计划排在最高级），然后在维护窗口关闭之前为尽可能多的计划执行演变过程。
所有那些未接受计划，如果性能比计划基线中现有的计划更好，则自动被接受。然而，如果未接受计划未能达到性能标准，则仍然保持为未接受状态，并且它们的 LAST_VERIFIED会被修改为当前时间戳。至少在30天内，自动任务不会试图将一个未接受计划再次进行演变, 在此之后也仅当SQL为激活状态(LAST_EXECUTED被修改过)才会演变。
夜间的演变任务的结果可以通过DBMS_SPM.REPORT_AUTO_EVOLVE_TASK函数查看。SPM演变顾问的任何方面都可以通过企业管理器或者PL/SQL包DBMS_AUTO_TASK_ADMIN进行管理。
此外，你也可以利用ORACLE企业管理器或者提供的DBMS_SPM包对未接受计划手动进行演变。从Oracle 12c开始，原有的SPM演变函数(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE)被废弃，代之以新的调用演变顾问的API。图38展示了调用演变顾问的一系列步骤。典型的过程为三个步骤的处理，从演变任务的创建开始。每个任务都被给予一个唯一的名字，这使得它可以被多次执行。一旦任务被执行，你就可以查看演变报告，方法是为DBMS_SPM.REPORT_EVOLVE_TASK函数提供TASK_ID和EXEC_ID。
(图38. 手动调用演变顾问所需的步骤)
当演变顾问被手动调用，未接受的计划不会被自动接受，即使它达到了性能标准。计划必须使用DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE 过程被手动接受。演变报告含有详细的指导信息，包括关于接受计划所需的特定的语法。
(图39. 手动接受一个SQL基线中的计划)
在原先，只有SQL语句的纲要(即outline, 是为了重现一个特定计划而必需的一套完整的提示集合)被捕获并作为SQL计划基线的一部分。从Oracle 12c数据库开始，当一个计划被添加到SQL计划基线，实际的执行计划也会被记录下来。对于那些在以前的版本被添加到SQL计划基线中的执行计划，当它们第一次在Oracle 12c数据库中被执行，实际的执行计划也会被添加到SQL计划基线中。
捕获实际执行计划是很重要的，这是为了确保一个SQL计划基线从一个系统被转移到另一系统时，SQL计划基线中的计划仍然能被显示，即使它所用到的某些对象或者解析模式本身在新系统中已经不存在了。注意，显示一个来自SQL计划基线中的计划，和能够重现这个计划，这是两件不同的事情。
对于SQL计划基线的任何计划，其详细的执行计划可以被显示，方法是在企业管理器的SQL计划基线页面点击计划的名称，或者使用DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE过程。图40显示了被接受的计划(SQL_PLAN_6zsnd8f6zsd9g54bc8843)的执行计划。所显示的计划，是当这个计划被添加到SQL计划基线所捕获到的实际计划，因为"plan rows"属性被设置为"From dictionary"(来自数据字典)。对于那些基于纲要而显示的计划，"plan rows"属性被设置为"From outline"(来自纲要)。
(图40. 显示来自SQL计划基线中的一个计划)
强化统计信息
连同优化器统计信息这一领域的许多新功能一起，现有的统计信息收集技术也得到了几种强化。
增量统计信息
在分区表上收集统计信息，包括在表级(全局统计信息)和分区(子分区)级别的收集。如果一个分区表的INCREMENTAL属性被设为TRUE, DBMS_STATS.GATHER_*_STATS过程的GRANULARITY（粒度）参数包含GLOBAL，并且ESTIMATE_PERCENT被设为AUTO_SAMPLE_SIZE，那么ORACLE就会通过将分区级的统计信息进行聚合，精确地推导出所有全局级别的统计信息。
增量统计信息的工作原理是为表中的每个分区存储一份纲要(synopsis)。纲要是一种关于分区以及分区中的列的统计元数据。通过将每个分区的分区级统计信息及纲要进行聚合，可以精确地生成全局级别的统计信息，从而不必扫描整个表。
增量统计信息及其陈化(staleness)
在Oracle 11g数据库中，如果一张表的增量统计被打开，当其中的一个分区的一行数据被修改时，那个分区的统计信息就被认为已经过于陈旧，必需被重新收集才能用于生成全局级别的统计信息。
在Oracle 12c数据库中，一个称为INCREMENTAL_STALENESS的新属性允许你控制分区统计信息何时被认为已陈化，并且不能胜任生成全局统计信息。在缺省情况下，INCREMENTAL_STALENESS被设为NULL, 这意味着一旦有一行数据被修改，分区级统计就被认为已陈化（和11g相同）。
或者，它也可被设置为USE_STALE_PERCENT 或 USE_LOCKED_STATS。USE_STALE_PERCENT的意思是只要被修改行数的百分比低于STALE_PRECENTAGE属性的值(缺省是10%)，分区级统计就会被使用。USE_LOCKED_STATS的意思是如果一个分区上的统计信息被锁定，就会被用于生成全局统计信息，不管这个分区中自从上次收集以来有多少行数据被修改。
增量统计信息以及分区交换加载
分区的好处之一是可以通过分区交换命令轻易地、迅速地加载数据，对业务用户造成最小的影响。分区交换命令允许一张非分区表中的数据被切换到分区表的指定分区中。这个命令并不会物理地移动数据；相反，它只是修改了数据字典，将指针从分区交换到表上，反之亦然。
在以前的版本中，在分区交换的操作过程中，你无法在非分区表上生成必要的统计信息。相反，统计信息只有在交换发生后才能被收集，这是为了确保全局统计信息能够被增量维护。
在Oracle 12c数据库中，必要的统计信息(纲要)可以在非分区表上创建，从而使得在分区交换中被交换的统计信息能够自动被用于增量维护全局统计信息。新的DBMS_STATS表属性INCREMENTAL_LEVEL可以被用来识别那些即将用于分区交换加载的非分区表。通过将INCREMENTAL_LEVEL设置为TABLE(缺省值是PARTITION)，Oracle会在收集统计信息的时候自动为这张表创建一个纲要(synopsis)。这个表级的纲要，在交换加载结束后会变成分区级的纲要。
并发统计信息
在Oracle 11.2.0.2数据库中，统计信息的并发收集被引入。当全局统计信息的收集属性CONCURRENT被设置，Oracle会利用作业调度器和高级队列部件来为每一个对象（表或者分区）创建和管理一个并发的作业。
在Oracle 12c数据库中，统计信息的并发收集被强化以更好地利用每个调度的作业。如果一个表，或分区，或者子分区很小或者是空的，数据库可能自动将这个对象以及其他的小对象整批放进一个单独的作业来降低作业维护的开销。
通过将CONCURRENT选项设置为ALL或者AUTOMATIC，统计信息的并发收集现在可以被夜间统计信息收集作业所利用。新的ORA$AUTOTASK消费者群组被加入到资源管理器，在维护窗口期间被激活使用，以确保统计信息的并发收集不会占用太多的系统资源。
列群组的自动侦测
扩展统计信息有助于优化器改善SQL语句的基数估算的精确性，如果这个语句涉及到包含函数的列(例如UPPER(LastName))，或者在过滤谓词、连接条件、分组键中用到的来自同一个表的多个列。虽然扩展统计信息极其有用，但要得知何种扩展信息应该被创建是很困难的，如果你对应用或者数据集不熟悉的话。
列群组的自动侦测，会自动根据给定的工作负载确定一张表上需要哪些列群组。请注意这个功能不会为带有函数的列创建扩展统计信息，它只能用于列群组。列群组的自动侦测是一个简单的三步骤的过程：
1.启动列的使用情况
为了确定适当的列群组，Oracle必需对具有代表性的工作负载进行观察。工作负载可以在SQL优化集(SQL Tuning Set)中提供, 或者通过对运行中的系统进行监控而获得。新的过程DBMS_STATS.SEED_COL_USAGE应该被使用，它指明了工作负载，并且告诉Oracle应该对工作负载观察多长时间。下列例子为当前系统打开了5分钟或者300秒的监控。
(图41. 开启列群组的自动侦测)
监控过程记录不同的信息，来自于你在sys.col_usage$可看到的传统的列使用信息，并且把它存放在sys.col_group_usage$。对于在监控窗口过程中被执行或者解释的任何SQL, 这个信息都会被存储。一旦监控窗口结束，你就可以使用新函数DBMS_STATS.REPORT_COL_USAGE来查看为特定的一个表记录的列使用信息。这个函数产生一个报表，它列出了在工作负载中这个表的哪些列被用于过滤谓词，连接谓词和分组子句。你也可以运行DBMS_STATS.REPORT_COL_USAGE来查看特定模式下的所有表的报告，只需提供模式名称，并将表名设为NULL。
(图42. 查看列使用情况)
2.创建列群组
为每个表调用DBMS_STATS.CREATE_EXTENDED_STATS函数，它就会自动根据监控窗口过程中捕获到的列使用信息自动创建必要的列群组。一旦扩展信息被创建，不管什么时候表统计信息被收集，它们就会被自动维护。
此外，列群组也可被手动创建，方法是将群组指定为DBMS_STATS.CREATE_EXTENDED_STATS的第三个参数。
(图43. 创建自动侦测到的列群组)
3.再度收集统计信息
最后的步骤是在受影响的表上再度收集统计信息，使得新创建的列群组拥有为它们创建的统计信息。
(图44.每当统计信息被收集，列群组统计就会被自动维护)
结论
优化器是Oracle数据库最吸引人的部件之一，这是因为它的复杂性。它的目的是为每个SQL语句确定最高效的执行计划。它是基于查询的结构，它所具有的可用的关于数据的统计信息，以及所有与优化器和执行相关的特性来作出这些决定的。在Oracle 12c数据库中，随着新的查询优化自适应方法的引入，还有对可用的统计信息的强化，优化器实现了一个巨大的飞跃。
新的查询优化自适应方法使得优化器能够对执行计划作出实时调整，并且发现能够导致更佳的统计信息的额外信息。利用这些信息，和已有的统计信息一起，能够使得优化器对环境有更多的了解，并且允许它每次都选择一个优化的执行计划。
如同以往，我们希望通过详细描述在这一版本中对优化器和统计信息的修改，围绕着它们的迷雾将会被去除，而这一知识将会使得你的升级过程更加顺利，先知先戒备，凡事预则立！
译者介绍 苏旭辉
网络ID：newkid，从事IT行业20余年，现定居加拿大多伦多；
Oracle资深开发大师，帮助过无数网友解决过ORACLE疑难问题；
《剑破冰山-Oracle开发艺术》副主编。
作者: Maria Colgan
原文：
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
原价169元的门票限时免费
原价599元的VIP票限时199元(优惠码：dbavip)