为什么我似乎不能强迫Oracle 11g为一个SQL查询消耗更多的CPU [英] Why can't I seem to force Oracle 11g to consume more CPUs for a single SQL query

问题描述

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

I have some humongous queries that run on gigantic tables. These queries seem to be CPU bottlenecked, and run for hours. I understand that Oracle has a lot of new features with 11g, release 2 to internally paralellize the execution of a query. However no matter what kinds of hints I put in the query, I cant seem to use more than 1 CPU on the database box. I have a very respectable Solaris machine with 8 CPUs, however everytime I run this query, I end up just pushing one CPU to 100% and then sit there for hours.

我尝试过的提示是:

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

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

None of that appeared to work when looking at overall CPU consumption on the box. It always seemed to peg one CPU at 100%. Unfortunately even explain plan seems to take forever to run. I will try and get different explain plans with the different hints and see if that helps. Is it possible that some queries are simply un-paralleable, even if their runtime is in the hours?!!? The main table in this query has 335 million rows.

SQL查询文本:

http://pastie.org/8634380

系统参数:

http://pastie.org/8634383

修改:

详细的说明计划-没有并行性:

Detailed Explain Plan - No Parallelism:

http://pastebin.com/HkZgbPpf

与优化程序相关的系统参数:

Optimizer related system parameters:

http://pastie.org/8639841

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

Further We have reached out to Oracle to understand why EXPLAIN PLAN takes more than 2 hours. We are timing out trying to run the various explain plans.

推荐答案

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

The most important thing to understand about Oracle parallelism is that it's complicated. Optimizing parallelism requires a lot of Oracle knowledge, reading the manuals, checking many parameters, testing long-running queries, and a lot of skepticism.

提出正确的问题

平行问题确实涉及三个不同的问题:

Parallel problems really involve three different questions:

1. 请求了多少台并行服务器?
2. 分配了多少台并行服务器?
3. 有意义地使用了多少个并行服务器?

使用最佳工具

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

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'.它没有太多的信息，但查看起来更快，更易于共享.

Another good options is type => 'text'. It doesn't have quite as much information but it's a quicker to look at and easier to share.

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

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

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

A 100-line parallel select may run beautifully, but then everything halts at a single step because of an uncached sequence. You can stare at an explain plan, a trace, or an AWR report for hours and not see the problem. The active report makes the slow steps almost trivial to find. Do not waste time guessing where the problem lies.

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

However, other tools are still required. An explain plan generated with explain plan for ... and select * from table(dbms_xplan.display); will provide a few key pieces of information. Specifically the Notes section can include many reasons why the query did not request parallelism.

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

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

The relevant information is spread over several different manuals, which are very useful but occasionally inaccurate or misleading. There are many myths and much bad advice about parallelism. And the technology changes significantly with each release.

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

When you put together all of the reputable sources, the list of factors influencing the number of parallel servers is astonishingly large. The list below is ordered roughly by what I think are the most important factors:

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. 版本，仅企业版和个人版允许并行操作.除了软件包 DBMS_PARALLEL_EXECUTE .
>
10. PARALLEL_ADAPTIVE_MULTI_USER
11. PARALLEL_AUTOMATIC_TUNING
12. PARALLEL_DEGREE_LIMIT
13. PARALLEL_DEGREE_POLICY
14. PARALLEL_FORCE_LOCAL
15. PARALLEL_INSTANCE_GROUP
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. PARALLEL_THREADS_PER_CPU
23. RAC节点数是默认DOP的另一个乘数.
24. CPU_COUNT (如果使用默认DOP).
25. RECOVERY_PARALLELISM
26. FAST_START_PARALLEL_ROLLBACK
27. 配置文件 SESSIONS_PER_USER还限制了并行服务器.
28. 资源管理器
29. 系统负载:如果parallel_adaptive_multi_user为true.大概无法猜测Oracle何时开始节流.
30. 过程
31. 并行DML限制 在以下任何一种情况下，并行DML将不起作用:

1. Inter-operation parallelism Any query using sorting or grouping will allocate twice as many parallel servers as the DOP. This is probably responsible for the myth "Oracle allocates as many parallel servers as possible!".
2. Query hint Preferably a statement-level hint like /*+ parallel */, or possibly an object-level hint like /*+ noparallel(table1) */. If a specific step of a plan is running in serial it is usually because of object-level hints on only part of the query.
3. Recursive SQL Some operations may run in parallel but can be effectively serialized by recursive SQL. For example, an uncached sequence on a large insert. Recursive SQL generated to parse the statement will also be serial; for example dynamic sampling queries.
4. Alter session alter session [force|enable] parallel [query|dml|ddl]; Note that parallel DML is disabled by default.
5. Table degree
6. Index degree
7. Index was cheaper Parallel hints only tell the optimizer to consider a full table scan with a certain DOP. They do not actually force parallelism. The optimizer is still free to use a serial index-access if it think it's cheaper. (The FULL hint may help solve this issue.)
8. Plan management SQL Plan Baselines, outlines, profiles, advanced rewrite, and SQL Translators can all change the degree of parallelism behind your back. Check the Note section of the plan.
9. Edition Only Enterprise and Personal Editions allow parallel operations. Except for the package DBMS_PARALLEL_EXECUTE.
10. PARALLEL_ADAPTIVE_MULTI_USER
11. PARALLEL_AUTOMATIC_TUNING
12. PARALLEL_DEGREE_LIMIT
13. PARALLEL_DEGREE_POLICY
14. PARALLEL_FORCE_LOCAL
15. PARALLEL_INSTANCE_GROUP
16. PARALLEL_IO_CAP_ENABLED
17. PARALLEL_MAX_SERVERS This is the upper limit for the whole system. There's a trade-off here. Running too many parallel servers at once is bad for the system. But downgrading a query to serial can be disastrous for some queries.
18. PARALLEL_MIN_PERCENT
19. PARALLEL_MIN_SERVERS
20. PARALLEL_MIN_TIME_THRESHOLD
21. PARALLEL_SERVERS_TARGET
22. PARALLEL_THREADS_PER_CPU
23. Number of RAC nodes Another multiplier for default DOP.
24. CPU_COUNT If the default DOP is used.
25. RECOVERY_PARALLELISM
26. FAST_START_PARALLEL_ROLLBACK
27. Profile SESSIONS_PER_USER also limits parallel servers.
28. Resource Manager
29. System load If parallel_adaptive_multi_user is true. Probably impossible to guess when Oracle will start throttling.
30. PROCESSES
31. Parallel DML restrictions Parallel DML will not work if any of these cases:

1. 兼容< 9.2用于分区内
2. 插入值，带有触发器的表
3. 复制
4. 自我参照完整性或删除级联或延迟的完整性约束
5. 访问对象列
6. 带有LOB的非分区表
7. 具有LOB的分区内并行性
8. 分布式交易
9. 集群表
10. 临时表

1. COMPATIBLE < 9.2 for intra-partition
2. INSERT VALUES, tables with triggers
3. replication
4. self-referential integrity or delete cascade or deferred integrity constraints
5. accessing an object column
6. non-partitioned table with LOB
7. intra-partition parallelism with a LOB
8. distributed transaction
9. clustered tables
10. temporary tables

标量子查询不能并行运行吗?这在手册中，我希望这个是正确，但是我的测试表明并行性在11g中可以正常工作. /li>

ENQUEUE_RESOURCES 10g中的隐藏参数，这是否又有意义?

索引组织表无法将路径直接并行插入IOT吗? (这仍然是真的吗?)

并行流水线功能要求必须使用CURSOR(?).待办事项.

功能必须为PARALLEL_ENABLE

语句类型.较早的版本根据分区在DML上限制了并行性.当前的一些手册中仍包含此内容，但是肯定不再适用.

分区数仅适用于旧版本上的分区联接.(?)

错误具体来说，我已经看到很多解析错误. Oracle将分配适当数量的并行服务器，但是什么都不会发生，因为它们都等待诸如cursor: pin s wait on x之类的事件.

Scalar subqueries do not run in parallel? This is in the manual, and I wish this was true, but my tests indicate that parallelism works here in 11g.

ENQUEUE_RESOURCES Hidden parameter in 10g, is this relevant any more?

Index-organized tables Cannot direct-path insert to IOTs in parallel? (Is this still true?)

Parallel pipelined function requirements Must use a CURSOR(?). TODO.

Functions must be PARALLEL_ENABLE

Type of statement Older versions restricted parallelism on DML depending on partitioning. Some of the current manuals still include this but it is certainly not true anymore.

Number of partitions Only for partition-wise joins on older versions.(?)

Bugs Specifically I've seen a lot of bugs with parsing. Oracle will allocate the right number of parallel servers but nothing will happen as they all wait for events like cursor: pin s wait on x.

此列表肯定不完整，并且不包括12c功能.它不能解决操作系统和硬件问题.它没有回答可怕的难题:最好的并行度是多少?" (简短的回答:更多通常更好，但是以其他过程为代价.)希望它至少使您了解这些问题有多难，并且是一个开始寻找的好地方.

This list is certainly not complete, and does not include 12c features. And it doesn't address operating system and hardware issues. And it doesn't answer the horribly difficult question, "what is the best degree of parallelism?" (Short answer: more is usually better, but at the expense of other processes.) Hopefully it at least gives you a sense of how difficult these problems can be, and a good place to start looking.

这篇关于为什么我似乎不能强迫Oracle 11g为一个SQL查询消耗更多的CPU的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！