Category: Performance Tunning

PARALLEL_MIN_SERVERS Parameter Behaviors

Set PARALLEL_MIN_SERVERS = 0 to reduce unnecessary parallel processes when instance up

By Default, when 12.2.0.1 instance starts up, there are more than necessary parallel processes created.

In this test case, there are 133 parallel processes created when instance starts up.

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


STATISTIC                      VALUE      CON_ID
------------------------------ ---------- ----------
Servers In Use                    0          0
Servers Available               133          0
Servers Started                 133          0
Servers Shutdown                  0          0
Servers Highwater                10          0
Servers Cleaned Up                0          0
Server Sessions               12577          0

7 rows selected.

Look at the default parameter values related to parallel process:

SQL> show parameter parallel_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
parallel_max_servers                 integer     352
parallel_min_servers                 integer     128
parallel_servers_target              integer     512
parallel_threads_per_cpu             integer       2
...
.

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
cpu_count                            integer     32

According to Oracle Doc, the default value for :

PARALLEL_MAX_SERVERS = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
PARALLEL_MIN_SERVERS = CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2
PARALLEL_SERVERS_TARGET = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2

If MEMORY_TARGET or SGA_TARGET parameter is set, then the number of concurrent_parallel_users = 4.
If neither MEMORY_TARGET or SGA_TARGET is set, then if a value is set for PGA_AGGREGATE_TARGET, then concurrent_parallel_users = 2. If a value is not set for PGA_AGGREGATE_TARGET, then concurrent_parallel_users = 1.

Let’s change PARALLEL_MIN_SERVERS to a lower than default value from 128 to 32. It does not work as specified. There are still 131 parallel processes started when instance bounced.

SQL> show parameter PARALLEL_MIN_SERVERS

NAME                                 TYPE         VALUE
------------------------------------ ----------- -----------
parallel_min_servers                  integer     32

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

STATISTIC                      VALUE CON_   ID
------------------------------ ---------- ----------
Servers In Use                    0           0
Servers Available               131           0
Servers Started                 131           0
Servers Shutdown                  0           0
Servers Highwater                 6           0
Servers Cleaned Up                0           0
Server Sessions                 183           0

7 rows selected.

Let’s change  PARALLEL_MIN_SERVERS to 0. it works perfectly because there are only 5 parallel processes started when instance bounced.

SQL> show parameter PARALLEL_MIN_SERVERS

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

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

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

7 rows selected.

So in order to reduce the number of parallel processes when instance starts up, make PARALLEL_MIN_SERVERS = 0, then the number of parallel processes will be down to smaller. Bouncing instance is not required in 12.2.0.1.

High Swap Usage On Oracle Database Server

SITUATION

When investigating into client’s Oracle database performance issue, we found the swap space usage is constantly very high on this Linux server.

OS: RHEL 7.3
DB: Oracle 12.2.0.1

FINDINGS

1)top
Tasks: 352 total, 2 running, 350 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.4%us, 4.1%sy, 0.0%ni, 79.3%id, 2.2%wa, 0.3%hi, 0.8%si, 0.0%st
Mem: 32172820k total, 32015956k used, 156864k free, 14528k buffers
Swap: 16777208k total, 7435428k used, 9341780k free, 11129844k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
137049 oracle 15 0 16.2g 5.3g 5.3g S 20.6 17.4 10:14.17 oracle
 72457 oracle 15 0 16.2g 4.7g 4.7g S 15.3 15.3 10:50.15 oracle
...
..
.
2) pmap
$ pmap -x 137049
137049: oracleRACTEST1 (LOCAL=NO)
Address          Kbytes   RSS     Dirty Mode Mapping
0000000000400000 96356    11704   0     r-x-- oracle
0000000006419000 444      140     4     rwx-- oracle
0000000006488000 148      100     80    rwx-- [ anon ]
000000001966e000 532      176     92    rwx-- [ anon ]
0000000060000000 16779264 5444888 1485768 rwxs- [ shmid=0x670005 ]
00000032b6a00000 112      108     0     r-x-- ld-2.5.so
00000032b6c1c000 4        0       0     r-x-- ld-2.5.so
...
..
.
00007fff4c504000 160 136 132 rwx-- [ stack ]
00007fff4c5d2000 12 4 0 r-x-- [ anon ]
ffffffffff600000 8192 0 0 ----- [ anon ]
---------------- ------ ------ ------
total kB 16954204 5477952 1490448
3) swappiness
$ cat /proc/sys/vm/swappiness
10

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

“SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Takes Long Time and Uses Huge Temp Space

It is a good practice for DBA to periodically collect stats of fixed objects, data dictionary, SYS and SYSTEM schemas,

The following query from OEM agent keeps failing, after upgrading Enterprise Manager Cloud Control and a monitoring agent to 13.2, this query is run in the 11.2.0.4 target database:

SELECT MEDIA
 FROM V$BACKUP_PIECE_DETAILS
 WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 
   AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1

Also it uses huge temp space :

SQL> select USERNAME,TABLESPACE,BLOCKS*8/1024/1024 
      from V$TEMPSEG_USAGE 
  order by BLOCKS desc ;

USERNAME   TABLESPACE                       BLOCKS*8/1024/1024
---------- ------------------------------- ------------------
DBSNMP     TEMP                             30.649414

It is the exact same issue as per (Doc ID 2201982.1). so the following actions are taken :

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Finally flush the cursor out of memory for next running :

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, 
            loads, version_count, invalidations, parse_calls 
      from v$sqlarea 
     where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS    
---------------- ---------- --------------- ---------- -----
VERSION_COUNT INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
00000001418FA048 4087355773 1896464546      213        5          
1             1             213

SQL> exec dbms_shared_pool.purge('00000001418FA048,4087355773','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, 
            version_count, invalidations, parse_calls 
       from v$sqlarea 
      where sql_id='dx4nqvbtu06bx';

no rows selected

Have a check of the new execution time. We can see the new execution plan is created, and the execution time is only 0.25 second.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions,
            ELAPSED_TIME,ELAPSED_TIME/executions/1000000 TimePerExecution
      from v$sqlarea 
     where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE  PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME 
---------------- ----------- --------------- ---------- ------------ 
TIMEPEREXECUTION
----------------
00000001418FA048   4087355773   75529090       2          504858       .252429

How To Flush an Object Out the Library Cache

We know how to flash all shared pool by :

SQL>alter system flush shared_pool;

Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

CREATE INDEX COMPUTE STATISTICS

“COMPUTE STATISTICS” is the default behavior for “CREATE INDEX”.

When creating an index, “COMPUTE STATISTICS” option is no longer required for 10g onwards databases. Here is a quick demonstration:

Create a table and insert a couple of sample records:

SQL> create table test ( id number, name varchar(20));
Table created.

SQL> insert into test values (1,'james1');
1 row created.

SQL> insert into test values (2,'james2');
1 row created.

SQL> insert into test values (3, 'james3');
1 row created.

SQL> commit;
Commit complete.

No statistics for newly created table:

SQL> select table_name,num_rows, last_analyzed 
       from user_tables 
      where table_name='TEST';

TABLE_NAME      NUM_ROWS      LAST_ANAL
-----------     ----------     ---------
TEST

Create a new index :

SQL> create index ind_test_id on test(id);
Index created.

Check the index statistics:

SQL> select INDEX_NAME,TABLE_NAME,NUM_ROWS,LAST_ANALYZED 
      from user_indexes 
     where INDEX_NAME='IND_TEST_ID';

INDEX_NAME    TABLE_NAME   NUM_ROWS  LAST_ANAL
-----------   ------------ --------- --------
IND_TEST_ID    TEST         3        05-JAN-17

From the example, we can see even there is no statics for the table, but the statistics is collected for the index when it is created.