Thursday, December 29, 2011

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.

No comments: