PARALLEL_MIN_SERVERS specifies the number of slave processes that are started at instance startup. These slave processes are always available and don’t need to be started when a server process requires them. The slave processes exceeding this minimum are dynamically started when required and, once returned to the pool, stay idle for five minutes. If they are not reused in that period, they are shut down. By default, this initialization parameter is set to 0. This means that no slave processes are created at startup. When some SQL statements need slave processes, they wait for the startup of the slave processes. The wait event related to this operation is os thread startup

Let’s demonstrate …

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

SQL> conn / as sysdba
sho parameter parallel_min_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     0

– Let’s restart the database

SQL> shu immediate;
startup

– check that no px processes are started automatically    (Servers Started = 0)

SQL> set line 80
set tab off
col statistic for a30

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

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

– Let’s check the maximum no. of servers that can be started –

SQL> sho parameter parallel_max_servers

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

– Let’s try to set parallel_min_servers to a value > parallel_max_servers
— We immediately get an error message

SQL> alter system set parallel_min_servers=41;

alter system set parallel_min_servers=41
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-12811: PARALLEL_MIN_SERVERS must be less than or equal to
PARALLEL_MAX_SERVERS, 40

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

SQL> alter system set parallel_min_servers=8;

– Verify that 8 Px servers have been spawned (Servers Started =8)
   and all of them are available (Servers Available = 8)

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

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

– Let’s create a table with dictionary DOP = 4 –

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

alter table sh.temp_sales parallel 4;

– Issue a query on temp_sales so that px servers are put to use

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

– Check that the query used 4 Px servers

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

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          4

- check that Server highwater has been modified to 4 as maximum of 4 servers have    been allocated by this instance yet

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

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

– Let’s modify dictionary DOP of temp_sales to 16 and issue the same query again

SQL>alter table sh.temp_sales parallel 16;

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

– check that the query uses 16 px processes

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

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                         16

– check that 8 new px servers have been assigned (servers started has increasesd    from 8 to 16) and all of them are available (servers available = 16).
   Servers highwater has been updated to 16

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

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

– Let’s wait for 5 minutes to check  that px servers idle for 5 minutes are  shut down automatically

SQL> exec dbms_lock.sleep(300);

– check that 8 px servers have been shutdown automatically after 5 minutes
   (Servers Shutdown = 8) as they were idle for that time. Now available
   servers are back to 8)

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

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

– Let’s change the dictionary DOP of temp_sales back to 4

SQL>alter table sh.temp_sales parallel 4;

– Let’s run the earlier query repeatedly in another session and while the query is
   still running, issue the query in the next step

SQL> begin
for i in 1..1000 loop
execute immediate 'select * from sh.temp_sales';
end loop;
end;
/

– It can be seen that 4 servers are in use in another session (Servers in use=4)
   and only 4 servers are available now

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

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

– Let’s check again the status of px servers after the query has completed
   in the other session
– It can be seen that all the 8 servers are available again now

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

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

Reference :

Troubleshooting Oracle Performance by Christian Antognini

—————————————————————————————-

Related Links :

Home