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

问题描述

我有一些在巨大的表上运行的庞大查询.这些查询似乎是 CPU 瓶颈，并且运行了数小时.我知道 Oracle 11g 第 2 版具有许多新特性，可以在内部并行化查询的执行.然而，无论我在查询中放入什么样的提示，我似乎都不能在数据库框上使用超过 1 个 CPU.我有一台非常不错的 Solaris 机器，有 8 个 CPU，但是每次我运行这个查询时，我最终只是将一个 CPU 推到 100%，然后在那里坐了几个小时.

我尝试过的提示是:

SELECT/*+ 并行 */...选择/*+ 并行 (5) */...选择/*+ 并行 (10) */...

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

SQL 查询文本:

除外.

这个列表肯定不完整，也不包括 12c 特性.它没有解决操作系统和硬件问题.它没有回答这个极其困难的问题，什么是最好的并行度?"(简短的回答:更多通常更好，但会牺牲其他流程.)希望它至少能让您了解这些问题的难度，并提供一个开始寻找的好地方.

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.

The hints I have tried are:

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

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 Query Text:

http://pastie.org/8634380

System Parameters:

http://pastie.org/8634383

Edit:

Detailed Explain Plan - No Parallelism:

http://pastebin.com/HkZgbPpf

Optimizer related system parameters:

http://pastie.org/8639841

Further EDIT: 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.

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.

Ask the Right Questions

Parallel problems really involve three different questions:

1. How many parallel servers were requested?
2. How many parallel servers were allocated?
3. How many parallel servers were meaningfully used?

Use the Best Tools

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:

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 Monitoring also includes the DOP requested and the DOP allocated:

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.

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.

But WHY did I get that number of parallel servers?

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. 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. 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

32. 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.
33. ENQUEUE_RESOURCES Hidden parameter in 10g, is this relevant any more?
34. Index-organized tables Cannot direct-path insert to IOTs in parallel? (Is this still true?)
35. Parallel pipelined function requirements Must use a CURSOR(?). TODO.
36. Functions must be PARALLEL_ENABLE
37. 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.
38. Number of partitions Only for partition-wise joins on older versions.(?)
39. 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.

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！