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

Color your tables with a DEFAULT degree of parallelism using the logic below: 3.

SYS @ visx1> select 'alter table '||owner||'.'||table_name||' parallel (degree default);'
2 from dba_tables
3 where owner='INV'
4 /
alter table INV.MTL_TXNS_HISTORY parallel (degree default);
alter table INV.MTL_MATERIAL_TRANSACTIONS_HCC parallel (degree default);
alter table INV.MTL_LSP_ONHAND_BALANCE_TMP parallel (degree default);
alter table INV.MTL_CLIENT_PARAMETERS parallel (degree default);
... output omitted for brevity
SYS @ visx1> alter table INV.MTL_TXNS_HISTORY parallel (degree default);
Table altered.
SYS @ visx1> alter table INV.MTL_MATERIAL_TRANSACTIONS_HCC parallel (degree default);
Table altered.
SYS @ visx1> alter table INV.MTL_LSP_ONHAND_BALANCE_TMP parallel (degree default);
Table altered.
SYS @ visx1>
... output omitted for brevity

How It Works
Parallel query works the same on Exadata as it does on non-Exadata 11gR2. With Exadata, which you've cost-justified
based on your database's performance requirements, business criticality, or some other reason, you want to get the
most performance out of your investment, so exploiting the benefits of parallel query is important.
Some of the historical challenges with parallel query have included the following questions:

• How many parallel query slaves does my query need?

• How many concurrent parallel operations will saturate my system?

• How can I ensure that parallelism will scale up to the point of saturation but not exceed it?

• How can I ensure that my query will only run if it can establish a minimum amount of parallel

query slaves?

Prior to 11gR2, finding this balance has been difficult. With 11gR2, Oracle has introduced Automatic Degree of
Darallelism, or Auto DOP. Auto DOP automatically calculates the degree of parallelism on a per SQL statement basis
and is implemented using the parallel_degree_policy initialization parameter. When parallel_degree_policy
is set to AUTO , Oracle will automatically parallelize SQL statements that the optimizer estimates will take longer than
parallel_min_time_pct_threshold seconds, which defaults to ten seconds. This is true regardless of how the table's
parallel degree is configured.
Depending on which size rack you have, the Exadata X2-2 compute grid has 24 cores, 48 cores, or 96 CPU cores,
and your storage grid has even more. When SQL statements are executed that qualify to be parallelized, Oracle will
establish parallel query slaves based on the degree of parallelism capable, specified, or calculated. These parallel
query slaves will “divide and conquer” the work (I/O), typically returning results faster.

■ the X2-8, X3-2, and X3-8 configurations have different numbers of processor cores than the exadata X2-2
models. please refer to Chapter 1 or oracle's documentation for additional information.

Note