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:
- How many parallel servers were requested?
- How many parallel servers were allocated?
- 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:
- 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!”.
- 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. - 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.
- Alter session
alter session [force|enable] parallel [query|dml|ddl];Note that parallel DML is disabled by default. - Table degree
- Index degree
- 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
FULLhint may help solve this issue.) - 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.
- Edition Only Enterprise and Personal Editions allow parallel operations. Except for the package 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 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.
- PARALLEL_MIN_PERCENT
- PARALLEL_MIN_SERVERS
- PARALLEL_MIN_TIME_THRESHOLD
- PARALLEL_SERVERS_TARGET
- PARALLEL_THREADS_PER_CPU
- Number of RAC nodes Another multiplier for default DOP.
- CPU_COUNT If the default DOP is used.
- RECOVERY_PARALLELISM
- FAST_START_PARALLEL_ROLLBACK
- Profile
SESSIONS_PER_USERalso limits parallel servers. - Resource Manager
- System load If parallel_adaptive_multi_user is true. Probably impossible to guess when Oracle will start throttling.
- PROCESSES
- Parallel DML restrictions Parallel DML will not work if any of these cases:
- COMPATIBLE < 9.2 for intra-partition
- INSERT VALUES, tables with triggers
- replication
- self-referential integrity or delete cascade or deferred integrity constraints
- accessing an object column
- non-partitioned table with LOB
- intra-partition parallelism with a LOB
- distributed transaction
- clustered tables
- temporary tables
- 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.
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.