Monthly Archives: December 2013

RECOVER STANDBY DATAFILE FROM PRIMARY

In this post, I will demonstrate how we can recover a lost/corrupted/inaccessible datafile on standby from primary.

Overview:

— Simulate loss of a datafile on standby database by renaming it.
— Restart standby database – Stops at mount stage as datafile is inaccessible.
— Check that redo apply to standby is stopped.
— Connect to primary database as target and standby as auxiliary.
— Take backup of the affected datafile from primary  so that backup file is created on standby.
— Recover standby database.
— Open standby database.
— check that redo apply has been restrated on standby and configuration is successful again.

– Implementation –

– Check that dataguard configuration is running properly

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

– Find out names of datafiles on standby

SBY>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dg02/system01.dbf
/u01/app/oracle/oradata/dg02/sysaux01.dbf
/u01/app/oracle/oradata/dg02/undotbs01.dbf
/u01/app/oracle/oradata/dg02/users01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

– To simulate loss of the datafile of example tablespace , rename it

SBY>ho mv /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example.dbf

– Restart standby database
— Stops at mount stage due to missing datafile

SBY>startup force;
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/dg02/system01.dbf'

-- Switch logs on primary and verify that redo apply has stopped on standby

PRI>alter system switch logfile;

DGMGRL>  show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database
 Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

– Using RMAN, connect to primary as target and standby as auxiliary

[oracle@node1 ~]$ . oraenv
ORACLE_SID = [dg01] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@node1 ~]$ rman target / auxiliary sys/oracle@dg02

connected to target database: DG01 (DBID=434142737)
connected to auxiliary database: DG01 (DBID=434142737, not open)

RMAN>

– Try to take backupset type of backup of example tablespace on primary so that backup file is created on standby
— Fails as only image copies can be transported over network using RMAN

RMAN> backup tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/26/2013 14:37:04
RMAN-06955: Network copies are only supported for image copies.

– Take image copy backup of example tablespace on primary so that backup file is created on standby

RMAN> backup as copy  tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/dg01/example01.dbf
output file name=/u01/app/oracle/oradata/dg02/example01.dbf tag=TAG20131226T143727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 26-DEC-13

– check that image copy copy has been created on standby

SBY>ho ls /u01/app/oracle/oradata/dg02/example01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

– Recover standby database and open it

SBY>recover managed standby database disconnect;
alter database open;

– Check that redo apply is resumed again and configuration is successful

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note:  In 12c datafiles can be restored over network using backup sets/ compressed backupsets as shown in this post.

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

Related links:

Home
11g Dataguard Index

11g  DataGuard: Flashback Standby After Resetlogs On Primary11g  DataGuard Setup Using Active DataGuard on RHEL/OEL- 5 
Flashback Through Role Transition For Physical Standby
How to reinstate the old primary as a standby after failover
12c Dataguard: Restore Datafile From Service

—————

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

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

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

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

 

PARALLEL_MIN_SERVERS

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

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