[翻译]Oracle 12c优化器白皮书(完整版WORD文档已上传)

初识orcl · 发表于 2014-4-17 11:45

寻根求源，追本逐末

newkid · 发表于 2014-4-17 21:38

初识orcl 发表于 2014-4-17 11:45
寻根求源，追本逐末

乱用成语，这两个意思是反的。

oracle_cj · 发表于 2014-4-17 21:52

newkid 发表于 2014-4-17 21:38
乱用成语，这两个意思是反的。

他语文是英语老师教的。。。

newkid · 发表于 2014-4-18 05:42

本帖最后由 newkid 于 2014-4-23 04:29 编辑

对现有功能的强化
在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数据库中，随着新的查询优化自适应方法的引入，还有对可用的统计信息的强化，优化器实现了一个巨大的飞跃。

新的查询优化自适应方法使得优化器能够对执行计划作出实时调整，并且发现能够导致更佳的统计信息的额外信息。利用这些信息，和已有的统计信息一起，能够使得优化器对环境有更多的了解，并且允许它每次都选择一个优化的执行计划。

如同以往，我们希望通过详细描述在这一版本中对优化器和统计信息的修改，围绕着它们的迷雾将会被去除，而这一知识将会使得你的升级过程更加顺利，先知先戒备，凡事预则立!

dingjun123 · 发表于 2014-4-18 09:31

我说嘛，标量子查询这玩意是12c才能展开的，以前看黄玮那书上写11g就可以了。。。
当时没有仔细看，现在一看，他说的有点问题。。。只是标量子查询计划比较特殊，不能用普通计划的规则来看，其实他那书上的表还是由主表驱动查询很多次，不是直接与外面先关联的。。。

dingjun123 · 发表于 2014-4-18 09:47

类似这个，其实不是2与3直接做关联，标量子查询不能用普通计划最右最上最先执行的规则，红色部分是标量子查询，t_tables和t_users的标量子查询最后的结果做成了view,与t_tablespaces做关联

SQL>  SELECT COUNT(DISTINCT owner) FROM t_tables;

COUNT(DISTINCTOWNER)
--------------------

               28

t_tables的不同行(不考虑hash碰撞）是28行，正好驱动t_users查询28次，符合实际情况

SQL>  SELECT COUNT(DISTINCT x)
  2 FROM (
  3 SELECT (SELECT u.default_tablespace FROM t_users u
  4 WHERE t.owner=u.username
  5 ) x
  6 FROM t_tables t
  7 )
  8  /

COUNT(DISTINCTX)
----------------
            3

标量子查询的结果做成view,正好3行，符合实际情况。

所以这种计划得注意，它的顺序是t_tablespaces与标量子查询做JOIN，标量子查询中t_tables的每一条（distinct)驱动t_users一次，最后的查询结果做成view,并且hash unique。

不是t_tablespaces与t_users直接join,所以我说他在那书上表述不是太准确。

SELECT/*+LEADING(@"SEL$5F35D591" "TS"@"SEL$1"
"VW_NSO_1"@"SEL$5F35D591")  USE_HASH(@"SEL$5F35D591"
"VW_NSO_1"@"SEL$5F35D591") */ * FROM t_tablespaces ts WHERE
ts.tablespace_name IN (SELECT/*+qb_name(inner)*/ (SELECT
u.default_tablespace FROM t_users u WHERE t.owner=u.username  )  FROM
t_tables t  )

Plan hash value: 2410028812

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |             |    1 |       |    3 |00:00:00.01 |    22 |
|*  1 |  HASH JOIN                |             |    1 | 3402 |    3 |00:00:00.01 |    22 |
| 2 | TABLE ACCESS FULL       | T_TABLESPACES |    1 |    6 |    6 |00:00:00.01 |    3 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_USERS    | 28 |    1 |    28 |00:00:00.01 |    4 |
|*  4 | INDEX RANGE SCAN       | IDX_T_USERS | 28 |    1 |    28 |00:00:00.01 |    3 |
| 5 | VIEW                      | VW_NSO_1    |    1 | 3402 |   3 |00:00:00.01 |    19 |
| 6 | HASH UNIQUE             |             |    1 | 3402 | 3 |00:00:00.01 |    19 |
| 7 |    INDEX FAST FULL SCAN    | IDX_T_TABLES  |    1 | 3402 | 3402 |00:00:00.01 |    15 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("TS"."TABLESPACE_NAME"="$kkqu_col_1")
   4 - access("U"."USERNAME"=:B1)