为什么我似乎无法强制 Oracle 11g 为单个 SQL 查询消耗更多 CPU

我有一些在巨大的表上运行的巨大查询。这些查询似乎存在 CPU 瓶颈，并且运行了数小时。据我所知，Oracle 在 11g 第 2 版中有很多新功能，可以在内部并行化查询的执行。然而，无论我在查询中添加何种提示，我似乎都无法在数据库框中使用超过 1 个 CPU。我有一台非常不错的 Solaris 机器，有 8 个 CPU，但是每次运行这个查询时，我最终只是将一个 CPU 调到 100%，然后就坐在那里几个小时。

我尝试过的提示是：

SELECT /*+ PARALLEL */ ...
SELECT /*+ PARALLEL(5) */ ...
SELECT /*+ PARALLEL(10) */ ...

从盒子上的总体 CPU 消耗来看，这些似乎都不起作用。它似乎总是将一个 CPU 固定在 100%。不幸的是，即使解释计划似乎也需要永远运行。我会尝试通过不同的提示获得不同的解释计划，看看是否有帮助。是否有可能某些查询根本无法并行，即使它们的运行时间在几个小时内？！！？ 该查询中的主表有 3.35 亿行。

SQL 查询文本：

http://pastie.org/8634380

系统参数：

http://pastie.org/8634383

Edit:

详细解释计划 - 无并行性：

http://pastebin.com/HkZgbPpf

优化器相关系统参数：

http://pastie.org/8639841

进一步编辑：我们已联系 Oracle 以了解为什么 EXPLAIN PLAN 需要 2 个多小时。我们尝试运行各种解释计划时超时了。

了解 Oracle 并行性最重要的一点是它很复杂。优化并行性需要大量的 Oracle 知识、阅读手册、检查许多参数、测试长时间运行的查询以及大量的怀疑精神。

提出正确的问题

并行问题实际上涉及三个不同的问题：

1. 请求了多少个并行服务器？
2. 分配了多少个并行服务器？
3. 有多少并行服务器被有效使用？

使用最好的工具

Go straight to the best tool - SQL Monitoring with active reports. Find your SQL_ID and generate the HTML report: select dbms_sqltune.report_sql_monitor(sql_id => 'your_sql_id', type => 'active') from dual;. This is the only way to know how much time was spent on each step in the execution plan. And it will tell you how much parallelism was effectively used, and where. For example:

另一个不错的选择是type => 'text'。它没有那么多信息，但查看起来更快，也更容易共享。

SQL Monitoring also includes the DOP requested and the DOP allocated:

100线平行select可能会运行得很好，但是由于未缓存的序列，一切都会在一步中停止。您可以盯着解释计划、跟踪或 AWR 报告几个小时，却看不到问题。活跃的报告使得缓慢的步骤变得几乎微不足道。不要浪费时间猜测问题出在哪里。

然而，仍然需要其他工具。生成的解释计划explain plan for ... and select * from table(dbms_xplan.display);将提供一些关键信息。具体来说Notes部分可以包含查询不请求并行性的许多原因。

但为什么我要获得这么多并行服务器呢？

相关信息分布在几个不同的手册中，这些手册非常有用，但有时不准确或具有误导性。关于并行性有很多误解和很多不好的建议。每个版本的技术都会发生显着变化。

当您将所有信誉良好的来源放在一起时，影响并行服务器数量的因素列表非常大。下面的列表大致按我认为最重要的因素排序：

1. 操作间并行性任何使用排序或分组的查询将分配两倍于 DOP 的并行服务器。这可能就是“Oracle 分配尽可能多的并行服务器！”这一神话的原因。
2. 查询提示最好是语句级提示，例如/*+ parallel */，或者可能是对象级提示，例如/*+ noparallel(table1) */。如果计划的特定步骤连续运行，通常是因为仅对部分查询进行了对象级提示。
3. 递归SQL某些操作可能并行运行，但可以通过递归 SQL 有效地序列化。例如，大型插入上的未缓存序列。生成解析语句的递归SQL也将是串行的；例如动态采样查询。
4. 改变会话 alter session [force|enable] parallel [query|dml|ddl];请注意，默认情况下禁用并行 DML。
5. 表度
6. 指标度
7. 指数更便宜并行提示仅告诉优化器考虑使用特定 DOP 进行全表扫描。它们实际上并不强制并行。如果优化器认为串行索引访问更便宜，它仍然可以自由地使用它。 （这FULL提示可能有助于解决这个问题。）
8. 计划管理SQL 计划基线、大纲、概要文件、高级重写和 SQL 翻译器都可以在您背后改变并行度。检查计划的注释部分。
9. Edition只有企业版和个人版允许并行操作。除了包裹DBMS_PARALLEL_EXECUTE.
10. PARALLEL_ADAPTIVE_MULTI_USER
11. 并行自动调整
12. PARALLEL_DEGREE_LIMIT
13. PARALLEL_DEGREE_POLICY
14. PARALLEL_FORCE_LOCAL
15. 并行实例组
16. PARALLEL_IO_CAP_ENABLED
17. PARALLEL_MAX_SERVERS这是整个系统的上限。这里有一个权衡。同时运行太多并行服务器对系统不利。但是对于某些查询来说，将查询降级为串行查询可能会带来灾难性的后果。
18. PARALLEL_MIN_PERCENT
19. PARALLEL_MIN_SERVERS 个
20. PARALLEL_MIN_TIME_THRESHOLD
21. PARALLEL_SERVERS_TARGET
22. 每个 CPU 并行线程数
23. RAC节点数量默认 DOP 的另一个乘数。
24. CPU_COUNT如果使用默认的 DOP。
25. 恢复并行
26. FAST_START_PARALLEL_ROLLBACK
27. Profile SESSIONS_PER_USER还限制并行服务器。
28. 资源管理器
29. 系统负载如果parallel_adaptive_multi_user为true。可能无法猜测 Oracle 何时会开始限制。
30. 流程
31. Parallel DML restrictions Parallel DML will not work if any of these cases:
    1. 对于分区内兼容
    2. INSERT VALUES，带有触发器的表
    3. 复制
    4. 自引用完整性或删除级联或延迟完整性约束
    5. 访问对象列
    6. 带有 LOB 的非分区表
    7. 具有 LOB 的分区内并行性
    8. 分布式事务
    9. 聚簇表
    10. 临时表
32. 标量子查询不并行运行？这是手册上的，我希望这个was确实如此，但我的测试表明并行性在 11g 中有效。
33. 排队资源10g 中的隐藏参数，这还有关系吗？
34. 索引组织表无法并行直接路径插入到物联网吗？ （这仍然是真的吗？）
35. 并行流水线功能需求必须使用一个CURSOR（？）。去做。
36. 函数必须是 PARALLEL_ENABLE
37. 声明类型旧版本根据分区限制 DML 的并行性。当前的一些手册仍然包含这一点，但它肯定不再正确了。
38. 分区数量仅适用于旧版本上的分区明智连接。（？）
39. Bugs具体来说，我看到了很多解析错误。 Oracle 将分配正确数量的并行服务器，但什么也不会发生，因为它们都在等待类似的事件cursor: pin s wait on x.

这个列表当然不完整，并且不包括 12c 功能。而且它不解决操作系统和硬件问题。而且它没有回答这个极其困难的问题：“最好的并行度是多少？” （简短的回答：通常越多越好，但会牺牲其他流程。）希望它至少能让您了解这些问题有多么困难，并且是一个开始寻找的好地方。