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

6jvkghxmqkx4n library cache: mutex X 18.00
6jvkghxmqkx4n os thread startup 9.00
6jvkghxmqkx4n DFS lock handle 6.00
6jvkghxmqkx4n enq: XL - fault extent map 2.00
6jvkghxmqkx4n PX Nsq: PQ load info query 2.00
6jvkghxmqkx4n PX Deq: reap credit 1.00

If we examine the test times for the five executions, similar to the previous test without parallel statement
queuing, we see that the lower-bound of SQL execution completion times was between 340 and 375 seconds; these
were the first two SQL statements that operated with a non-downgraded 96 way degree of parallelism. The remaining
three SQL statements took longer to complete due to being queued, but in comparison with the initial test case, the
overall elapsed time was 22% less than without parallel statement queuing (2,973 seconds vs. 3,810 seconds):

[oracle@cm01dbm01 autodop]$ grep seconds *lst
autodop_1.lst:375.95 seconds
autodop_2.lst:659.27 seconds
autodop_3.lst:736.17 seconds
autodop_4.lst:862.5 seconds
autodop_5.lst:339.59 seconds
[oracle@cm01dbm01 autodop]$

This demonstrates that parallel statement queuing with Auto DOP can in some cases improve overall parallel
query response times by ensuring that SQL statements do not start until they are guaranteed their ideal degree of
parallelism.

How It Works
Understanding parallel query functionality with Auto DOP on Exadata is important for the Exadata DMA and Exadata
performance engineer. While Auto DOP functionality is generic to Oracle 11gR2, it often plays a significant role on
Exadata systems because many Exadata database workloads are characterized by large parallel full-scan SQL queries.
Getting the most mileage from parallel query operations on Exadata is a crucial element to ensuring predictably fast
query response time.
Parallel statement queuing was introduced with Auto DOP as a means to prevent parallel-enabled SQL
statements from executing unless and until enough parallel query servers are available to satisfy the request. Parallel
statement queuing will be enabled under the following conditions:

• When

parallel_degree_policy = AUTO , or

• When

parallel_degree_policy = LIMITED and "_parallel_statement_queuing ”= TRUE , as
demonstrated in the solution of this recipe

Under these conditions, when a SQL statement is issued, the following occurs:

• Oracle determines whether the statement qualifies for parallel execution based on its parallel

degree setting (see dba_tables.degree ).
Oracle's Auto DOP algorithms calculate the degree of parallelism based on the table or table's •

block counts; the larger the segment, the higher the automatically calculated DOP.