sql - 为什么我似乎不能强制Oracle 11g为单个SQL查询消耗更多的CPU

Question

我在巨型表上运行了一些庞大的查询。这些查询似乎是CPU的瓶颈，并且运行了几个小时。我了解到Oracle在11g第2版中具有许多新功能，可以在内部并行执行查询的执行。但是，无论我在查询中添加什么样的提示，我似乎都无法在数据库框中使用超过1个CPU。我有一台非常受人尊敬的Solaris计算机，带有8个CPU，但是每次运行此查询时，最终我只能将一个CPU推到100％，然后在那儿坐了几个小时。

我尝试过的提示是：

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

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

SQL查询文字：

http://pastie.org/8634380

系统参数：

http://pastie.org/8634383

编辑：

详细的解释计划-没有并行性：

http://pastebin.com/HkZgbPpf

优化器相关的系统参数：

http://pastie.org/8639841

进一步编辑：
我们已经与Oracle联系，以了解为何EXPLAIN PLAN需要两个多小时。我们超时尝试运行各种说明计划。

Answer 1

关于Oracle并行性，要了解的最重要的事情是它很复杂。优化并行性需要大量的Oracle知识，阅读手册，检查许多参数，测试长期运行的查询以及许多怀疑态度。

提出正确的问题

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


请求了多少个并行服务器？
分配了多少台并行服务器？
有意义地使用了多少个并行服务器？


使用最佳工具

直接转到最佳工具-具有活动报告的SQL监视。找到您的SQL_ID并生成HTML报告：select dbms_sqltune.report_sql_monitor(sql_id => 'your_sql_id', type => 'active') from dual;。这是知道执行计划中每个步骤花费了多少时间的唯一方法。它会告诉您有效使用了多少并行性以及在何处使用。例如：


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

SQL监视还包括请求的DOP和分配的DOP：


100行并行的select可能运行得很漂亮，但是由于未缓存的序列，所有操作都在单个步骤中停止。您可以盯着一个小时的解释计划，跟踪或AWR报告，而看不到问题所在。活动报告使缓慢的步骤几乎不容易找到。不要浪费时间猜测问题所在。

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

但是为什么我得到了那么多并行服务器？

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

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


互操作并行性使用排序或分组的任何查询将分配的并行服务器数量是DOP的两倍。这可能是神话“ Oracle分配尽可能多的并行服务器！”的原因。
查询提示最好是像/*+ parallel */这样的语句级别的提示，或者像/*+ noparallel(table1) */这样的对象级别的提示。如果计划的特定步骤是串行运行的，通常是因为仅在查询的一部分上存在对象级提示。
递归SQL某些操作可能并行运行，但可以通过递归SQL有效地序列化。例如，大插入上的未缓存序列。生成的用于解析语句的递归SQL也将是串行的；例如动态抽样查询。
Alter session alter session [force|enable] parallel [query|dml|ddl];请注意，默认情况下禁用并行DML。
表度
指标度
索引更便宜并行提示仅告诉优化器考虑使用特定DOP进行全表扫描。它们实际上并没有强制并行化。如果优化器认为便宜，则仍然可以自由使用串行索引访问。 （FULL提示可能有助于解决此问题。）
计划管理SQL计划基准，大纲，配置文件，高级重写和SQL转换器都可以改变您背后的并行度。检查计划的注释部分。
仅企业版和个人版允许并行操作。软件包DBMS_PARALLEL_EXECUTE除外。
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_AUTOMATIC_TUNING
PARALLEL_DEGREE_LIMIT
PARALLEL_DEGREE_POLICY
PARALLEL_FORCE_LOCAL
PARALLEL_INSTANCE_GROUP
PARALLEL_IO_CAP_ENABLED
PARALLEL_MAX_SERVERS这是整个系统的上限。这里需要权衡。一次运行太多并行服务器对系统不利。但是将查询降级为串行查询可能对某些查询造成灾难性的影响。
PARALLEL_MIN_PERCENT
PARALLEL_MIN_SERVERS
PARALLEL_MIN_TIME_THRESHOLD
PARALLEL_SERVERS_TARGET
PARALLEL_THREADS_PER_CPU
RAC节点数默认DOP的另一个乘数。
CPU_COUNT如果使用默认DOP。
RECOVERY_PARALLELISM
FAST_START_PARALLEL_ROLLBACK
配置文件SESSIONS_PER_USER还限制了并行服务器。
资源经理
系统负载如果parallel_adaptive_multi_user为true。大概无法猜测Oracle何时开始节流。
工艺流程
Parallel DML restrictions在以下任何情况下，并行DML将不起作用：


分区内兼容<9.2
插入值，带有触发器的表
复制
自引用完整性或删除级联或延迟的完整性约束
访问对象列
带LOB的非分区表
LOB的分区内并行
分布式交易
聚簇表
临时表

标量子查询不并行运行？这已经在手册中了，我希望这是真的，但是我的测试表明并行性在11g中有效。
ENQUEUE_RESOURCES 10g中的隐藏参数，这是否又有意义？
索引组织表无法将路径直接并行插入IOT？ （这仍然是真的吗？）
并行管道功能要求必须使用CURSOR（？）。去做。
函数必须为PARALLEL_ENABLE
语句类型较旧的版本根据分区在DML上限制并行性。当前的一些手册仍然包含此内容，但现在已经不正确了。
分区数仅适用于旧版本上的分区联接。（？）
错误具体来说，我已经看到很多解析错误。 Oracle将分配适当数量的并行服务器，但是什么也不会发生，因为它们都等待诸如cursor: pin s wait on x之类的事件。


该列表当然是不完整的，并且不包括12c功能。它不能解决操作系统和硬件问题。而且它没有回答可怕的难题：“最佳并行度是什么？” （简短的回答：更多通常更好，但以其他过程为代价。）希望它至少使您了解这些问题的难度，以及一个开始寻找的好地方。