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 :
Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II
——————————————————————————————–