Tag Archives: PARALLEL_ADAPTIVE_MULTI_USER

PARALLEL_ADAPTIVE_MULTI_USER

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

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

AUTOMATIC DEGREE OF PARALLELISM (DOP) – PART – I

In a serial – non-parallel – execution environment, a single process or thread undertakes  the operations required to process your SQL statement and each action must complete before  the succeeding action can commence.Parallel SQL allows a SQL statement to be processed by multiple threads or processes simultaneously.Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:
  •  Queries that involve table or index range scans.
  •  Bulk insert, update or delete operations.
  •  Table and index creation.
In earlier versions of the Oracle Database, we had to determine the DOP more or less  manually, either with a parallel hint or by setting a parallel degree with alter table. 
There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula:
 CPU_COUNT * PARALLEL_THREADS_PER_CPU. 
If there were insufficient parallel servers to satisfy the requested DOP, one of three things could occur:
  • The SQL would be run at a reduced DOP (be downgraded)
  • The SQL would run in serial mode (be serialized)
  • If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate with “ORA-12827: insufficient parallel query slaves available”.
Oracle 11g release 2 introduced Automatic computation of DOP to parallelize
SQL. Automatic DOP whichis – as the name says – a way of having Oracle determine the degree of parallelism depending on the nature of the operations to be performed and the sizes of the objects involved. The decision is made based on various initialization parameter settings.
1. PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO.
  • MANUAL – This is the default. Disables Auto DOP, statement queuing and in-memoryparallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2)..
  • LIMITED- will only assign a system computed  degree of parallelism (DOP) for tables decorated with a parallel degree of
  • DEFAULT
  1. Tables and indexes that have a specific DOP specified use that specified DOP.   
  2.  Parallel statement queuing and in-memory parallel execution are disabled.
  3. The optimizer computes the optimal degree-of-parallelism value based on estimated  execution statistics..
  • AUTO
  1. will consider to assign a system computed DOP to all tables
  2. Enables parallel statement queuing :If the requested or required DOP is not possible because parallel servers are busy, then Oracle will defer statement execution rather than downgrading or  serializing the SQL until enough parallel slaves become available.
  3. Enables in-memory parallel execution.

2. PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query’s estimated execution time is likely to run longer than the acceptable value (in seconds) for PARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at the cost of other non-parallelizable operations.The default of this parameter is 10 seconds.

3. PARALLEL_ADAPTIVE_MULTI_USER : If it is set to TRUE, then Oracle will adjust the degree of parallel based on the overall load on the system. When the system is more heavily loaded, then the degree of parallelism will be reduced.

4. If PARALLEL_IO_CAP is set to TRUE in 11g or higher, then Oracle will limit the Degree of  Parallelism to that which the IO subsystem can support. The IO subsystem limits can be calculated by using the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

5. PARALLEL_MAX_SERVERS : To avoid an arbitrary number of parallel processes to be running on a system, which may overload that system, the parameter parallel_max_servers provides a hard upper boundary. Regardless of any other setting, the degree of parallelism cannot exceed that which can be supported by PARALLEL_MAX_SERVERS. For most SQL statements, the number of servers required will be twice the Degree of Parallelism.

6. PARALLEL_DEGREE_LIMIT : It is the maximum DOP that can be used. It can take various values :

  •  CPU (Default) : the total CPU count of the system
  •  IO : the limit depends on the I/O load on the database host
  •  any number greater than  0.

Note that even if you increase the parameter value, the automatically computed degree of parallelism may not increase, because the database may determine that the computed degree of parallelism is sufficient.The DOP that we run the statement with is the minimum value of the computed DOP (or ideal DOP) and that parallel_degree_limit parameter.

• A degree of parallelism can be specified at the table or index level by using the PARALLEL clause of CREATE TABLE, CREATE INDEX, ALTER TABLE or ALTER INDEX.

• The PARALLEL hint can be used to specify the degree of parallelism for a specific table within a query.

In my next article in this series , I will practically demonstrate oracle behaviour for various values of the parameter PARALLEL_DEGREE_POLICY i.e. MANUAL, LIMITED AND AUTO

References:
http://searchitchannel.techtarget.com/feature/Using-parallel-SQL-to-improve-Oracle-database-performance
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

http://kerryosborne.oracle-guy.com/2014/01/does-parallel_degree_limit-work-with-parallel_degree_policymanual/
http://www.databasejournal.com/features/oracle/oracle-parallel-processing-new-and-improved.html
http://www.pythian.com/blog/secrets-of-oracles-automatic-degree-of-parallelism/
http://hemantoracledba.blogspot.in/2015/02/parallel-execution-1-parallel-hint-and.html?utm_source=feedburner&utm_medium=email&utm_campaign=Feed:+blogspot/JcnHK+(Hemant%27s+Oracle+DBA+Blog)
https://hourim.wordpress.com/2015/02/24/parallel-query-broadcast-distribution-and-temp-space/
http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1657271217898
What Is Auto DOP?
Configuring And Controlling Auto DOP
https://blogs.oracle.com/datawarehousing/entry/parallel_degree_limit_parallel_max
In Memory Parallel execution in Oracle Database 11g R2
Parameter Changes For Parallel Execution In Oracel Database 12cR2

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

Related links :


Home

Database Index
Automatic Degree Of Paralellism – Part-II
Oracle 11g : Automatic DOP – Parallel Threshold
Oracle 11g :  Parallel Statement Queueing
Parallel_Adaptive_Multi_User
Parallel_Min_Percent
Parallel_Min_Servers

                                           —————-