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

Solution
In this recipe, we will show you how to do the following tasks:

• Configure Auto DOP with In-Memory Parallel Execution enabled

• Identify SQL statements that take advantage of Auto DOP's In-Memory Parallel Query feature

using data from Oracle AWR

• Compare query performance of In-Memory PX parallel queries vs. traditional parallel direct

reads with Smart Scan

• Perform a simple test to measure the impact of In-Memory PX vs. Exadata Smart Scan with

concurrent query executions
First, enable Auto DOP by setting parallel_degree_policy =AUTO:

SQL> alter system set parallel_degree_policy=AUTO scope=both sid='*';
System altered.
SQL>

Confirm that your database is ready for Auto DOP by checking your parallel query initialization parameters and
I/O calibration status using the script in Listing 21-13:

Listing 21-13. lst21-13-autodop-check.sql

SQL> select name,value,decode(value,'AUTO','Auto DOP is enabled',
'LIMITED','Auto DOP is implemented in limited fashion',
'MANUAL','Auto DOP is not implemented') descr
from v$parameter
where name='parallel_degree_policy'
/

Parameter Value Notes
------------------------ ------------ --------------------------
parallel_degree_policy AUTO Auto DOP is enabled

SQL>
SQL> select a.ksppinm name, b.ksppstvl value
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
and a.ksppinm in ('_parallel_cluster_cache_pct','_parallel_cluster_cache_policy','_parallel_
statement_queuing')
order by 1,2
/
Parameter Value
---------------------------------- ---------------
_parallel_cluster_cache_pct 80
_parallel_cluster_cache_policy CACHED
_parallel_statement_queuing TRUE
SQL>

The preceding output shows us that Auto DOP is enabled because parallel_degree_policy = AUTO and
In-Memory PX are enabled because the underscore parameter _parallel_cluster_cache_policy is set to CACHED .