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

In addition to determining the ideal degree of parallelism for a SQL statement, Auto DOP also introduces the
concepts of parallel statement queuing and In-Memory Parallel Execution. Parallel statement queuing performs
what the name suggests; it causes parallel query operations to be queued when the calculated DOP is higher than the
maximum number of parallel processes in the system. Recipe 21-6 provides a deeper discussion on this topic.
In-Memory Parallel Execution is a feature that calculates the working set size of a parallel operation and
determines if it will fit in the aggregate Oracle database buffer cache. If so, blocks requested via parallel query will
be read into the buffer cache and not satisfied via direct read mechanisms. With potentially large buffer cache sizes,
on Exadata this could lead to fewer parallel direct reads and by consequence, prevent Smart Scan and cell offload
features. To combat this, you could do the following:

• Set

parallel_degree_policy=LIMITED , which will enable Auto DOP to determine the right
number of parallel query processes but disable In-Memory Parallel Execution and parallel
statement queuing

• Set

"_parallel_statement_queuing"=TRUE , which will enable parallel statement queuing

SYS @ visx1> alter system set parallel_degree_policy=LIMITED scope=both sid='*';
System altered.
SYS @ visx1> alter system set "_parallel_statement_queuing"=TRUE scope=both sid='*';
System altered.
SYS @ visx1>

With an approach like this, you can achieve the best of both worlds with Auto DOP and Exadata.

21-5. Setting I/O Calibration on Exadata
Problem
You wish to use Auto DOP on Exadata and you are required to perform I/O calibration in order for Auto DOP
functionality to work.

Solution
In this recipe, we will demonstrate how to identify when I/O calibration is required and how to implement I/O
calibration.
First, log in to your Exadata 11gR2 database and configure Automatic Degree of Parallelism:

SYS @ dwprd1> alter system set parallel_degree_policy=LIMITED scope=both sid='*';
System altered.
SYS @ dwprd1> alter system set "_parallel_statement_queuing"=TRUE scope=both sid='*';
System altered.
SYS @ dwprd1>

Next, color a table or tables with the default degree of parallelism to enable Auto DOP functionality to work:

SYS @ dwprd1> alter table myfact.my_fact_detail
2 parallel (degree default);
Table altered.
SYS @ dwprd1>