Post-Install Database Tasks - Oracle Exadata Recipes: A Problem-Solution Approach - page 606

• The per-SQL statement limit is calculated by the

parallel_degree_limit initialization
parameter. On Exadata, this typically is set to CPU, which means that the parallel degree
limit is calculated based on the number of processors on the Exadata compute grid. In the
examples above, we saw a parallel limit of 96; the formula that Oracle uses to set this parallel
limit when parallel_degree_limit=CPU is calculated based on the query in Listing 21-12:

Listing 21-12. lst21-12-parallel-limit.sql

SQL> select decode(p1.value,'CPU',
p2.value*p3.value*p4.value,
p1.value) parallel_limit
from v$parameter p1, v$parameter p2,
v$parameter p3, v$parameter p4
where p1.name='parallel_degree_limit'
and p2.name='cpu_count'
and p3.name='parallel_threads_per_cpu'
and p4.name='parallel_server_instances'
/

Parallel Degree Limit
------------------------
96

parallel_servers_target minus
the number of active parallel query servers, the statement is queued. On Exadata, parallel_
servers_target is typically set to 128.

• If the requested parallel query degree exceeds the setting of

• If a SQL statement is queued with parallel statement queuing, it will post a wait on Oracle's

resmgr:pq queued wait event and be queued until sufficient parallel query servers are
available to meet the query's calculated degree of parallelism. Oracle then de-queues these
SQL statements in the order in which they were queued.

To measure parallel statement queuing with Auto DOP, use SQL Monitoring to show your queuing behavior
and look for resmgr:pq queued wait events from ASH, Oracle AWR, or other wait-interface-related dynamic
performance views.

Not ■ one item worth mentioning is the sQl hint NO_STATEMENT_QUEUING . With this hint, you can disable parallel
statement queuing on a per-statement basis, if required, and allow un-hinted sQl statements to operate with parallel
statement queuing.

21-7. Measuring Auto DOP and In-Memory Parallel Execution
Problem
You have enabled Auto DOP on Exadata and wish to measure the impact of In-Memory Parallel Execution, or
In-Memory PX.