Tuning of the database is very challenging work for the DBA. For this DBA should use Proactive strategy for the database. In Proactive strategy dba should work on few things when they are creating database.

We know that for database creation parameter file is required which contains parameters & its values.

We know that parameters and its values plays an important role in terms of the performance of the database.

Oracle Expert recommend that do not change parameter’s value when you are facing performance issues in the database.

In this post I will tell you what are those parameters which will create performance impact in your database.

These are 8 parameters in the database which DBA should check first when facing performance issues in the database.

These parameters are–:

• PROCESSES

• SESSIONS

• DB_BLOCK_SIZE

• SGA_TARGET

• PGA_AGGREGATE_TARGET

• UNDO_MANAGEMENT

• UNDO_TABLESPACE

• COMPATIBLE

1- PROCESSES

PROCESSES=70

This parameter specifies the maximum number of operating

System user processes that can be started by that instance or

that can be connected to Oracle Database concurrently.

The Default value is derived, and it typically depends on

The number of cores reported in the alert log.

It is not Modifiable in non-CDB & in a PDB also.

Range of values 6 to operating system dependent.

In Oracle RAC Multiple instances must have the different

values.

A good estimate would be to set the PROCESSES

Initialization parameter to 70.

2- SESSIONS

If processes = 1000 then sessions = 1522

SESSIONS=1522

SESSIONS specifies the maximum number of sessions that can

be created in the system.

The Default value Derived: (1.5 * PROCESSES) + 22. It is

Modifiable in PDB also.

ALTER SYSTEM can be used in a PDB only to change the value

of the SESSIONS parameter for that PDB.

ALTER SYSTEM cannot be used to change the value of the

SESSIONS parameter in a non-CDB or in a CDB$ROOT.

Range of values is 1 to 65536.

3- DB_BLOCK_SIZE

DB_BLOCK_SIZE=8192

DB_BLOCK_SIZE specifies (in bytes) the size of Oracle

database blocks.

Typical values for DB_BLOCK_SIZE are 4096 and 8192.

The Default value is 8192.

It is not Modifiable in non-CDB & in a PDB also.

Range of values 2048 to 32768.

In Oracle RAC You must set this parameter for every

instance, and multiple instances must have the same

value.

The value of this parameter must be a multiple of the physical

Block size at the device level. It means the range of values

depends on the operating system

4- SGA_TARGET

SGA_TARGET=720M

SGA_TARGET specifies the total size of all SGA components.

The Default value is 0 (SGA auto tuning is disabled for

DEFERRED mode auto tuning requests, but allowed for

IMMEDIATE mode auto tuning requests).

It is Modifiable by ALTER SYSTEM. It is Modifiable in a

PDB.

Range of values 64 MB to operating system-dependent.

If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)

• Shared pool (SHARED_POOL_SIZE)

• Large pool (LARGE_POOL_SIZE)

• Java pool (JAVA_POOL_SIZE)

• Streams pool (STREAMS_POOL_SIZE)

• Data transfer cache (DATA_TRANSFER_CACHE_SIZE)

If these automatically tuned memory pools are set to nonzero

values, then those values are used as minimum levels by

Automatic Shared Memory Management.

You would set minimum values if an application component

needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not

affected by Automatic Shared Memory Management:

• Log buffer

• Other buffer caches, such as KEEP, RECYCLE, and other block

Sizes.

• Fixed SGA and other internal allocations.

The memory allocated to these pools is deducted from the total

Available for SGA_TARGET when Automatic Shared Memory

Management computes the values of the automatically tuned

Memory pools.

In the Default value field, IMMEDIATE mode auto tuning

Requests are necessary to avoid ORA-04031 errors.

The DEFERRED and IMMEDIATE modes are reflected in the

OPER_MODE column of the V$MEMORY_RESIZE_OPS view.

If Automatic Memory Management is enabled

(MEMORY_TARGET is set to a positive value) and SGA_TARGET

Is also set to a positive value, the SGA_TARGET value acts as

The minimum value for the size of the SGA.

5- PGA_AGGREGATE_TARGET

PGA_AGGREGATE_TARGET=700mb

OLTP: PGA_AGGREGATE_TARGET =

(Total memory * 80%) * 20%

DSS: PGA_AGGREGATE_TARGET =

(Total memory * 80%) * 70%

PGA_AGGREGATE_TARGET specifies the target aggregate PGA

memory available to all server processes attached to the

instance.

The Default value10 MB or 20% of the size of the SGA,

whichever is greater.

It is Modifiable by ALTER SYSTEM. It is Modifiable in a

PDB.

Range of values is Minimum: 10 MB &

Maximum: 4096 GB – 1.

To set a hard limit for aggregate PGA memory, use the

PGA_AGGREGATE_LIMIT parameter.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the

effect of automatically setting the WORKAREA_SIZE_POLICY

parameter to AUTO.

With this setting, SQL working areas used by memory-intensive

SQL operators (such as sort, group-by, hash-join, bitmap

merge, and bitmap create) will be automatically sized.

A nonzero value for this parameter is the default since, unless

you specify otherwise, Oracle sets it to 20% of the SGA or 10

MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the

WORKAREA_SIZE_POLICY parameter to MANUAL.

With this setting, SQL working areas are sized using the

*_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below

the target specified by this parameter by adapting the size of

the working areas to private memory.

When increasing the value of this parameter, you indirectly

increase the memory allotted to working areas.

Consequently, more memory-intensive operations are able to

run fully in memory and fewer will work their way over to disk.

If Automatic Memory Management is enabled

(MEMORY_TARGET is set to a positive value) and

PGA_AGGREGATE_TARGET is also set to a positive value, the

PGA_AGGREGATE_TARGET value acts as the minimum value

for the size of the instance PGA.

6-UNDO_MANAGEMENT

UNDO_MANAGEMENT=AUTO

 

UNDO_MANAGEMENT specifies which undo space management

mode the system should use.

The default is AUTO. If unspecified, the database uses

AUTO.

It is not Modifiable in non-CDB & Modifiable in a PDB.

In Oracle RAC Multiple instances must have the

same value.

When UNDO_MANAGEMENT is set to AUTO, the instance starts

in automatic undo management mode.

In manual undo management mode, undo space is allocated

externally as rollback segments.

7-UNDO_TABLESPACE

UNDO_TABLESPACE=unotbs01.dbf

 

UNDO_TABLESPACE specifies the undo tablespace to be used

when an instance starts. If this parameter is specified when the

instance is in manual undo management mode, then an error

will occur and startup will fail.

The Default value is the first available undo tablespace in

the database.

It is Modifiable by ALTER SYSTEM & in a PDB also.

Range of values is Legal name of an existing undo

tablespace.

In Oracle RAC Each instance must have a unique value

for this parameter, when it is set.

8- COMPATIBLE

COMPATIBLE = 12.2.0

 

Specifies the release with which the Oracle database must

maintain compatibility.

The Default value is 12.2.0.

It is not Modifiable in non-CDB & in a PDB also.

Range of values is 11.2.0 to default release.

In Oracle RAC Multiple instances must have the same

value.

The COMPATIBLE parameter specifies the Oracle version

number that the database disk format must be compatible

with.

Check regularly my post and give feedback & comments!!!!.

Thanks

Ahmad (Riaz Ahmad Ansari)

OCE In (11g R2 RAC)

OCP In (11g/10g/9i)