Automatic Parallel Execution

In 11g release 2, Oracle introduced the following new parameters:

 

parallel_degree_policy

parallel_min_time_threshold

 

The default setting of parallel_degree_policy is manual. With this value, the automatic parallel execution of SQL statements is disabled. This is how Oracle worked before 11g release 2.

 

Setting this parameter to auto enables this functionality. Oracle then parses an SQL statement, works out its execution plan and estimates how long it will take. If this value is greater than  parallel_min_time_threshold seconds, the statement runs in parallel. You can see what difference this makes in the example below, which runs the same query twice.

 

Automatic parallel execution is turned off for the first run:

 

SQL> select value from v$parameter

  2  where name = 'parallel_min_time_threshold'

  3  /

 

VALUE

--------------------

AUTO

 

SQL> select value from v$parameter

  2  where name = 'parallel_degree_policy'

  3  /

 

VALUE

--------------------

MANUAL

 

SQL> set timing on

SQL> select count(*) from

  2  (select a.table_name from

  3   dba_tables a, dba_tables b)

 4  /

 

  COUNT(*)

----------

   9247681

 

Elapsed: 00:02:00.41

SQL> set timing off

 

And the query takes just over 2 minutes. Automatic parallel execution is then turned on for the second run:

 

SQL> alter session set

  2  parallel_min_time_threshold = 1

  3  /

 

Session altered.

 

SQL> alter session set

  2  parallel_degree_policy = 'AUTO'

  3  /

 

Session altered.

 

SQL> set timing on

SQL> select count(*) from

  2  (select a.table_name from

  3   dba_tables a, dba_tables b)

  4  /

 

  COUNT(*)

----------

   9247681

 

Elapsed: 00:00:37.15

SQL> set timing off

 

And the query takes less than 40 seconds.