Oracle: Is There Any Logical Reason Not to Use Parallel Execution with Subqueries in the Select List

Oracle: why doesn't use parallel execution?

You could have found the answer in the documentation:

A SELECT statement can be parallelized only if the following
conditions are satisfied:

• The query includes a parallel hint specification (PARALLEL or
  PARALLEL_INDEX) or the schema objects referred to in the query have a
  PARALLEL declaration associated with them.

• At least one of the tables specified in the query requires one of
  the following:

  • A full table scan

  • An index range scan spanning multiple partitions

• No scalar subqueries are in the SELECT list.

Your query falls at the final hurdle: it has a scalar subquery in its projection. If you want to parallelize the query you need to find another way to write it.

Does using full resources of server ensures better query execution performance?

Using more resources for a parallel Oracle SQL statement will typically result in better performance and run in less time.

In theory, it would make sense that using too high a degree of parallelism (DOP) would eventually become counter-productive. At some point the overhead of managing such a large number of parallel processes could out-weigh the benefit of extra processes.

But in practice I have never seen this happen with Oracle SQL statements. I've bench marked this a few times, on several different platforms, for both CPU and I/O bound tasks. It seems like bigger is always better.

But there are many important disclaimers:

1. Although a higher DOP will help that one statement it may be massively unfair to other statements. There is certainly a point of diminishing returns. It may not be a good idea to increase the performance of one statement by less than 1% if it hurts many other statements.
2. Although I've tested this several times I can't guarantee it's true for every configuration.
3. Testing these scenarios is difficult. You have to pick a large amount of work, on a system that other people aren't using, try to avoid a "hot" system by running things like alter system flush buffer_cache (although that may not disable OS caching), run the tests multiple times, remove outliers, average the results, etc.
4. Watch out for Oracle mechanisms that may reduce the DOP. Just because you ask for 256 doesn't mean you'll get 256. To ensure that parameters or throttling isn't happening, check v$px_process to ensure that all the parallel threads are being used.

In my experience, if you only care about the best performance for a single SQL statement, crank the DOP as high as possible.

Oracle parallel query wait stats not in tkprof's report

When you execute parallel query, each parallel slave is a different session. The main session is the query coordinator, which coordinates the work, but doesn't do any of it. So, you need to trace the parallel query slaves to see those waits.

If you're on at least 11g, you can alter session set events 'sql_trace level 12';

You may also want to set tracefile_identifier, to help you find related trace files.

More details are available here:
https://blogs.oracle.com/db/entry/how_to_get_a_10046_trace_for_a_parallel_query

Hope that helps.

Why does Parallelism affect if my query is successful?

A query language, which SQL is, has nothing to see with a procedural language.
In procedural language, you control the order of each instruction by sequencing the pieces of the code in ordering each parts. This is known as the "how" code...
In a query language, you only specify the "what" code, not the how, and the query is translated into differents sequences of instructions (how code) to do your request, then each different solution to solve your demand are evaluated in terms of cost and finally the optimizer choose the best one that will be executed.

Sometime, the how code is changed because of subtile differences in the query, the session execution parameters, the distribution of the data values...

So you must never presume that a query will be executed the same way ever. You need to secure your query by a logic that eliminate misinterpretation...

---

Related Topics