PARALLEL_MIN_PERCENT

The Oracle server maintains a “pool” of parallel slave processes available for parallel operations. The Database configuration parameters PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS determine the initial and maximum size of the pool.  The number of slave processes provided to a query coordinator may be less than requested by it, due to system load or other parallel processes which are using the parallel server pool. For example, if the maximum number of slave processes is set to 40 and an  execution plan requires eight slave processes, only 5 concurrent SQL statements (40/8) can be executed with the required degree of parallelism. When the limit is reached, there are two possibilities:

- either the degree of parallelism is downgraded (in other words, reduced) or

- an error (ORA-12827: insufficient parallel query slaves available) is returned to the server process.

To configure which one of these two possibilities is used, you have to set the initialization parameter parallel_min_percent. It can be set to an integer value ranging from 0 to 100. There are three main situations:

0: This value (which is the default) specifies that if requested no. of slave processes are not available,  the degree of parallelism of the statement can be silently downgraded.  If less than two slave processes are available, the statement might even execute serially. The error ORA-12827 is never raised .

1–99: The values ranging from 1 to 99 define a limit for the downgrade. If no. of available slave processes is less than the specified percentage of the requested slave processes , the error ORA-12827 is raised.
For example, if it is set to 25 and 16 slave processes are requested, at least 4 (16*25/100) must be provided to avoid the error.

100: With this value, If no. of available slave processes is less than the no. of  requested slave processes , the error ORA-12827 is raised.

Let’s demonstrate …

– Find out the value of parallel_max_servers

SQL> sho parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
parallel_max_servers                 integer     40

– Let’s  set parallel_min_servers to a value < parallel_max_servers i.e. 8

SQL> alter system set parallel_min_servers=8;

– Restart the database

SQL>shu immediate;
startup

– Initially only 8 px servers (= parallel_min_servers) are started

SQL>  select * from v$px_process_sysstat
           where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         0
Servers Shutdown                        0
Servers Highwater                       0
Servers Cleaned Up                      0
Server Sessions                         0

– Initially all the statistics related to parallel execution show values
   as 0

SQL>select name, value from v$sysstat
where upper(name) like '%PARALLEL OPERATIONS%'
or upper(name) like '%PARALLELIZED%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                      0
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    0
Parallel operations not downgraded                                        0
Parallel operations downgraded to serial                                  0
Parallel operations downgraded 75 to 99 pct                               0
Parallel operations downgraded 50 to 75 pct                               0
Parallel operations downgraded 25 to 50 pct                               0
Parallel operations downgraded 1 to 25 pct                                0

-—————- PARALLEL_MIN_PERCENT = 0 ————————

– Check that currently, parallel_min_percent is set to its default value of 0

SQL> sho parameter parallel_min_p

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     0

– Let’s create a test table  –

SQL> drop table sh.temp_sales purge;
create table sh.temp_sales as select *    from sh.sales;

– Let’s modify dictionary DOP of temp_sales to a value > parallel_max_servers (40)

SQL>alter table sh.temp_sales parallel 50;

– Let’s execute a query on temp_sales

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

– check that the query uses 40 px processes = parallel_max_servers
   i.e. DOP of the statement has been silently downgraded from requested value
   of 50 to 40 as parallel_min_percent= 0

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                         40

– Note that 1 query has been parallelized so far and its parallelization
   operation has been downgraded (1 to 25) % (50 to 40 i.e. 20%)

SQL>select name, value from v$sysstat
where upper(name) like '%PARALLEL OPERATIONS%'
or upper(name) like '%PARALLELIZED%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                      1
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    1
Parallel operations not downgraded                                        0
Parallel operations downgraded to serial                                  0
Parallel operations downgraded 75 to 99 pct                               0
Parallel operations downgraded 50 to 75 pct                               0
Parallel operations downgraded 25 to 50 pct                               0
Parallel operations downgraded 1 to 25 pct                                1

– check that 32 more  px servers were started in addition earlier 8 (servers started has increasesd    from 8 to 32)  and 40 of them are available (servers available = 40).    Servers highwater has been updated to 40

SQL>  select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      40
Servers Started                        32
Servers Shutdown                        0
Servers Highwater                      40
Servers Cleaned Up                      0
Server Sessions                        40

----------------- PARALLEL_MIN_PERCENT = 50 ------------------------

– Let’s set parallel_min_percent = 50

SQL> alter system set parallel_min_percent=50 scope=spfile;

shu immediate;
startup

SQL> sho parameter parallel_min_percent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     50

– check that query executes with DOP = 40 as
   requested DOP * (parallel_min_percent/100) = 50 *(50/100) = 25
   and upto maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

– Let’s change dictionary DOP of temp_sales to 82

SQL> alter table sh.temp_sales parallel 82;

– Let’s re execute the earlier query
– It can be seen that we get error message ORA-12827 as
   requested DOP * (parallel_min_percent/100) = 82 *(50/100) = 41
   but maximum of only 40 servers  (parallel_max_servers)   are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
begin
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at line 2

– Let’s change dictionary DOP of temp_sales to 80

SQL> alter table sh.temp_sales parallel 80;

– Let’s re execute the earlier query
– It can be seen that query executes with DOP of 40  as
requested DOP * (parallel_min_percent/100) = 80 *(50/100) = 40
and maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

Thus PARALLEL_MIN_PERCENT = 1 to 99  ensures that
if specified percent of the requested px servers are available
        a SQL statement will be parallelized  
Else,
   ORA-12827 will be returned

-—————- PARALLEL_MIN_PERCENT = 100 ————————
– Let’s set parallel_min_percent to 100 so that query will be parallelized only if available px servers >= requested no. of px servers

SQL>alter system set parallel_min_percent=100 scope=spfile;
shu immediate;
startup;

– Currently  Dictionary DOP = 80, parallel_max_servers = 40

– Let’s execute the same query again

– Since maximum no. of available px servers (40) < requested no. (80), we get ORA-12827

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
begin
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at line 2

– Let’s change dictionary DOP of temp_sales to 40 (=parallel_max_servers) so that available servers = requested

SQL> alter table sh.temp_sales parallel 40;

– Let’s re execute the earlier query
– It can be seen that the query executes with DOP = 40
   requested DOP * (parallel_min_percent/100) = 40 *(100/100) = 40
   and upto maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

References:

Troubleshooting Oracle Performance by Christian Antognini

—————————————————————————————

Related Links :

Home

Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II

——————————————————————————————–

 

Your comments and suggestions are welcome!