AUTOMATIC DEGREE OF PARALLELISM (DOP) – PART – II

    

 In my last article , I discussed Automatic DOP computation in Oracle 11g Release 2.  In this  article, I will demonstrate the oracle behaviour for various values of PARALLEL_DEGREE_POLICY i.e. Manual, Limited and Auto.
—–      PARALLEL_DEGREE_POLICY= MANUAL —————-
SQL>conn / as sysdba
          alter system set parallel_adaptive_multi_user=false;
          conn sh/sh
         alter session set parallel_degree_policy=manual;
         alter session set parallel_min_time_threshold=auto;
— CREATE TABLES —
— P5 – DICTIONARY DOP = 5
– PDEF – DICTIONARY DOP = DEFAULT
SQL>drop table p5 purge;
          create table p5 parallel 5
         as select * from sales
        where rownum < 5000;
SQL>drop table pdef purge;
   
         create table pdef parallel
        as select * from sales
        where rownum < 5000;
SQL> select table_name,degree
from user_tables
          where table_name in (‘P5′, ‘PDEF’);
TABLE_NAME                    DEGREE
———————————————————————-
P5                                             5
PDEF                                DEFAULT
– CHECK THAT PARALLELISM USED = DICTIONARY DOP IN BOTH THE TABLES –
SQL> select count(*) from p5;
 
          select * from v$pq_sesstat
          where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      5            0
SQL> select  count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      2            0
– PRIOR TO 11G R2, TO DECIDE PARALLELISM, THE DBA COULD SPECIFY DIFFERENT
– DICTIONARY DOP’S FOR DIFFERENT OBJECTS
– TO OVERRIDE THE DICTIONARY DOP, HE COULD USE HINTS AT OBJECT LEVEL –
SQL> select /*+ parallel (p5 8) */ count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      8            0
SQL> select /*+ parallel (pdef 8) */ count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      8            0
—–      PARALLEL_DEGREE_POLICY= LIMITED —————-
SQL> alter session set parallel_degree_policy=limited;
– CHECK THAT DOP IS NOT COMPUTED FOR TABLES WITH NON DEFAULT –
– DICTIONARY DOP – FOR TABLE P5, DOP USED = DICTIONARY DOP = 5
SQL> select count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                    5            0
– CHECK THAT DOP IS  COMPUTED FOR TABLES WITH DEFAULT –
– DICTIONARY DOP – FOR TABLE PDEF, DOP IS COMPUTED TO BE 0 I.E. PARALLELIZATION
– IS NOT REQUIRED CONSIDERING THE SMALL SIZE OF THE OBJECT
SQL> select  count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
—–      PARALLEL_DEGREE_POLICY= AUTO —————-
SQL> alter session set parallel_degree_policy=auto;
– EXECUTE QUERY ON P5
– NOTE THAT DOP USED = 0 I.E. QUERY IS EXECUTED SERIALLY
– BECAUSE WITH CURRENT SIZE OF THE TABLE, SERIAL EXECUTION
– CAN EXECUTE THE STATEMENT WITHIN THE TIME SPECIFIED BY
– PARALLEL_MIN_TIME_THRESHOLD

SQL> select count(*) from p5;

 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                    LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
– RECREATE THE TABLE WITH MORE ROWS SO THAT PARALLELIZATION
– IS NEEDED TO EXECUTE THE QUERY
SQL>drop table p5 purge;
    create table p5 parallel 5
    as select * from sh.sales;
– REEXECUTE THE QUERY AND CHECK THAT PARALLELIZATION
– IS USED AND DOP USED IS NOT 5 (DICTIONARY DOP) I.E. AUTOMATIC
– DOP IS COMPUTED EVEN IN CASE OF TABLE WHERE DICTIONARY DOP IS
– NOT DEFAULT
     select count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      2            0
– MODIFY
PARALLEL_MIN_TIME_THRESHOLD TO 20 SECS –
SQL>alter session set parallel_min_time_threshold=20;
EXECUTE THE SAME QUERY AGAIN. CHECK THAT THE QUERY
– IS EXECUTED SERIALLY AS THE TIME REQUIRED TO EXECUTE
– IT SERIALLY IS LESS THAN 20 SECS AND HENCE PARALLELIZATION
– IS NEEDED

SQL>select count(*) from p5;

 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
SQL>alter session set parallel_min_time_threshold=auto;
– DOP IS COMPUTED COMPUTED FOR THOSE TABLES ALSO FOR WHICH
– DICTIONARY DOP IS DEFAULT. NOTE THAT THE FOLLOWING STATEMENT
– EEXCUTES SERIALLY AS DUE TO SMALL SIZE OF THE TABLE, PARALLELIZATION
– IS NOT REQUIRED
SQL>select  count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height’;
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
– WE CAN OVERRIDE AUTOMATIC COMPUTATION OF DOP BY
GIVING OBJECT LEVEL HINT
SQL>select /*+ parallel (p5 8) */ count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      8            0
– IF TABLE SIZE IS SO SMALL THAT PARALLELISM IS NOT REQUIRED
– EVEN SPECIFIED DOP IN THE HINT IS OVERRIDDEN. IF WE EXECUTE
– QUERY ON PDEF WHOSE SIZE IS SMALL, THE QUERY EXECUTES SERIALLY
– EVEN THOUGH WE HAVE SPECIFIED DOP = 8

SQL>select /*+ parallel (pdef 8) */ count(*) from pdef;

 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
References:
———————————————————————————————

Related links :

Home

Database Index
Automatic Degree Of Paralellism – Part-I
Oracle 11g : Automatic DOP – Parallel Threshold
Oracle 11g :  Parallel Statement Queueing
Parallel_Adaptive_Multi_User
Parallel_Min_Percent
Parallel_Min_Servers

                                                                              ——————

Your comments and suggestions are welcome!