One of the initialization parameters that influence the number of slave processes assigned to a server process is parallel_adaptive_multi_user.
It accepts two values:
• FALSE (default value in Oracle9i): If the pool is not exhausted, the server process is assigned the requested number of slave processes .
• TRUE (default value from Oracle Database 10g onwards): As the number of slave processes already in use increases, the requested degree of parallelism is automatically reduced, even if there are still enough servers in the pool to satisfy the required degree of parallelism.
Let’s demonstrate ..
Overview:
– parallel_max_servers = 40
– Set parallel_adaptive_multi_user = false
– Execute a query requesting DOP of 8 concurrently in 1, 2, 3, 4, 5 and 6 sessions and note down the no. of slave processes allocated in each session for each concurrent execution.
– Verify that
. for no. of concurrent sessions <= 5, each session gets requested no. of slave processes i.e. 8
. for no. of concurrent sessions > 5, first 5 sessions get 8 slave processes each but query in any subsequent session is executed serially i.e. it is silently downgraded.
– Set parallel_adaptive_multi_user = true
– Execute a query requesting DOP of 8 concurrently in 1, 2, 3, and 4 sessions and note down the no. of slave processes allocated in each session for each concurrent
– Verify that
. for no. of concurrent sessions <= 2, each session gets requested no. of slave processes i.e. 8
. for no. of concurrent sessions = 3,
– first 2 sessions get 8 slave processes each
-Third session gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
. for no. of concurrent sessions > 3,
– first 2 sessions get 8 slave processes each
-Third session gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
– The query in any subsequent session is executed serially i.e. it is silently downgraded.
Implementation :
———— PARALLEL_ADAPTIVE_MULTIUSER = FALSE —————————
– Create test table temp_sales 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;
– Set parallel_min_percent=0 and restart the database
SQL> alter system set parallel_min_percent=0 scope=spfile;
shu immediate; startup 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
– Check that parallel_max_servers = 40
SQL> sho parameter parallel_max
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
parallel_max_servers integer 40
– check that parallel_adaptive_multi_user = FALSE
SQL> sho parameter parallel_adaptive
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
parallel_adaptive_multi_user boolean FALSE
– Issue the following query requesting DOP = 8 in one session --
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
– Check that the query gets requested no. of slave processes (8)
SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
-- check the highwater of parallel processes started = 8–
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 8
Servers Cleaned Up 0
Server Sessions 8
-- Issue the following query requesting DOP = 8 in two sessions –
-- Check that both the sessions get requested no. of slave processes (8)
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
– check that
. 8 more parallel processes have started (Servers Started = 8)
. 16 parallel processes are available now (Servers Available = 16)
. Servers highwater has been updated to 16
select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 0
Servers Available 16
Servers Started 8
Servers Shutdown 0
Servers Highwater 16
Servers Cleaned Up 0
Server Sessions 24
– Issue the following query requesting DOP = 8 in three sessions –
– Check that all the sessions get requested no. of slave processes (8)
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
– check that
.8 more parallel processes have started (Servers Started = 16)
. 24 parallel processes are available now (Servers Available = 24)
. Servers highwater has been updated to 24
SQL>select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 0
Servers Available 24
Servers Started 16
Servers Shutdown 0
Servers Highwater 24
Servers Cleaned Up 0
Server Sessions 48
– Issue the following query requesting DOP = 8 in four sessions –
– Check that all the sessions get requested no. of slave processes (8)
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
– check that
.8 more parallel processes have started (Servers Started = 24)
. 32 parallel processes are available now (Servers Available = 32)
. Servers highwater has been updated to 32
SQL>select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 0
Servers Available 32
Servers Started 24
Servers Shutdown 0
Servers Highwater 32
Servers Cleaned Up 0
Server Sessions 80
– Issue the following query requesting DOP = 8 in five sessions –
– Check that all the sessions get requested no. of slave processes (8)
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
– check that
. 8 more parallel processes have started (Servers Started = 32)
. 40 parallel processes are available now (Servers Available = 40)
. Servers highwater has been updated to40
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 120
– Issue the following query requesting DOP = 8 in five sessions –
– In first 5 sessions Server process gets requested no. of slaves (8):
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
-- In 6th session, the query gets executed serially
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 0
– check that
. No more parallel processes have started (Servers Started = 32 as earlier)
. 40 parallel processes are available as earlier (Servers Available = 40)
. Servers highwater is at the same value as earlier i.e. 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 160
The observations for PARALLEL_DAPTIVE_MULTI_USER = FALSE can be tabulated as follows:
PARALLEL_ADAPTIVE_MULTI_USER = FALSE
|————– No. of slave processes assigned ————–|
No. of sessions sess1 sess2 sess3 sess4 sess5 sess6
———————- ——— ——– ——— ——– ——– ——–
1 8 – – – – -
2 8 8 – – – -
3 8 8 8 – – -
4 8 8 8 8 – -
5 8 8 8 8 8 -
6 8 8 8 8 8 0
Hence, it can be seen that
. for no. of concurrent sessions <= 5, each session gets requested no. of slave processes i.e. 8 so that total px servers allocated = parallel_max_servers = 40
. for no. of concurrent sessions > 5, first 5 sessions get 8 slave processes each but query in any subsequent session is executed serially i.e. it is silently downgraded as maximum no. of px slaves have already been used.
Hence, when PARALLEL_ADAPTIVE_MULTI_USER = FALSE, as many px slaves are allocated as are requested as long as they are available.
————- PARALLEL_ADAPTIVE_MULTI_USER = TRUE —————————
– Set parallel_adaptive_multi_user = true and restart the database
SQL>Alter system set parallel_adaptive_multi_user = true;
shu immediate;
startup
sho parameter parallel_adaptive NAME TYPE VALUE ------------------------------- ----------- -----------------
parallel_adaptive_multi_user boolean TRUE
– Issue the following query requesting 8 px slaves in one session –
– Check that the Server process gets requested no. of slaves (8)
– While the query is executing , go to next step and check that Servers in use = 8 and servers highwater = 8
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
SQL>select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 8
Servers Available 0
Servers Started 0
Servers Shutdown 0
Servers Highwater 8
Servers Cleaned Up 0
Server Sessions 8
– Issue the following query requesting 8 px slaves in two sessions –
– Check that the both the sessions get requested no. of slaves (8)
– While the query is executing , go to next step and check that Servers in use = 16 and servers highwater = 16
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
SQL>select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 16
Servers Available 0
Servers Started 8
Servers Shutdown 0
Servers Highwater 16
Servers Cleaned Up 0
Server Sessions 24
– Issue the following query requesting 8 px slaves in three sessions –
– Check that the first two sessions get requested no. of slaves (8)
the third session get 6 slaves only
– While the query is executing , go to next step and check that Servers in use = 22 and servers highwater = 22 (8 + 8 + 6)
– In first 2 sessions :
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
In third session :
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 6
SQL>select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 22
Servers Available 0
Servers Started 14
Servers Shutdown 0
Servers Highwater 22
Servers Cleaned Up 0
Server Sessions 46
– Issue the following query requesting 8 px slaves in four sessions –
– Check that the first two sessions get requested no. of slaves (8)
the third session get 6 slaves only
the fourth session does not get any slaves
– While the query is executing , go to next step and check that Servers in use = 22 and servers highwater = 22 (8 + 8 + 6) as earlier
– In first 2 sessions :
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 8
– in 3rd session:
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 6
– In 4th session:
SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;
select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';
STATISTIC LAST_QUERY
------------------------------ ----------
Server Threads 0
SQL>select * from v$px_process_sysstat
where statistic like '%Server%';
STATISTIC VALUE
------------------------------ ----------
Servers In Use 22
Servers Available 0
Servers Started 14
Servers Shutdown 0
Servers Highwater 22
Servers Cleaned Up 0
Server Sessions 68
The observations for PARALLEL_DAPTIVE_MULTI_USER = FALSE can be tabulated as follows:
PARALLEL_ADAPTIVE_MULTI_USER = FALSE
|—- No. of slave processes assigned –|
No. of sessions sess1 sess2 sess3 sess4
———————- ——— ——– ——— ——–
1 8 – – –
2 8 8 – –
3 8 8 6 –
4 8 8 6 0
Hence, it can be seen that if same query is issued in all the sessions,
. for no. of concurrent sessions <= 2, each session gets requested no. of slave processes i.e. 8
. for no. of concurrent sessions = 3,
– first 2 sessions get 8 slave processes each
-Third session gets 6 slave processes i.e DOP gets silently downgraded even requested no. of slaves can be provided
. for no. of concurrent sessions > 3,
– first 2 sessions get 8 slave processes each
-Third session gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
– The query in any subsequent session is executed serially i.e. it is silently downgraded.
Hence, when PARALLEL_ADAPTIVE_MULTI_USER = true, DOP of the a SQL statements is downgraded automatically so that px slaves can be assigned to other SQL statements which might be issued in other sessions.
References:
Troubleshooting Oracle Performance by Christian Antognini
—————————————————————————————
Related Links :
Home
Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II
——————————————————————————————–
Like this:
Like Loading...