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
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 –
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 :
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
——————