Configuring a Database for Performance

Tuning of the database is very challenging work for the DBA. These days client wants everything should be fast on the database. It is possible if DBA will use Proactive strategy for the database. For Proactive strategy, DBA should work on a few things when they are creating a database.

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

Because we know that parameters and its values play an important role in terms of the performance.

There are 5 parameters in the database which will not create any performance impact in your database.

These 5 parameters are–:

1)-DB_NAME-

Name of the database.

db_name=teprod1

DB_NAME specifies a database identifier of up to 8 characters.

There is no default value. It is not modifiable in non-CDB &

it is not modifiable in a PDB also. In Oracle RAC You must set

this parameter for every instance. Multiple instances must have

the same value.

The value of DB_NAME must be the same in both the standby

and production initialization parameter files.

This should match the ORACLE_SID environment variable.

2)-DB_DOMAIN-

Specifies the logical location of the database within the network

structure.

db_domain=india.oracleride.com

Location of the database in Internet dot notation. There is no

default value. It is modifiable in a PDB. In Oracle RAC You

must set this parameter for every instance,

and multiple instances must have the same value.

3)-OPEN_CURSORS-

OPEN_CURSORS specifies the maximum number of open

cursors (handles to private SQL areas) a session can

have at once. You can use this parameter to prevent a

session from opening an excessive number of cursors.

open_cursors=500

Limit on the maximum number of cursors (active SQL

statements) for each session. The setting is application

dependent; 500 is recommended.

The default value is 0 to 65535. It is modifiable by an ALTER SYSTEM.

It is modifiable in a PDB.

4)-CONTROL_FILES-

CONTROL_FILES specifies one or more names of control files,

separated by commas.

Control_files=’/u01/app/oracle/product/12.2/database/datafile/control01.ctl’

Set to contain at least two files on different disk drives to

prevent failures from control file loss.

The Default value Operating system-dependent. It is not

Modifiable in non-CDB & in a PDB also.Range of values 1 to 8

Filenames. In Oracle RAC Multiple instances must have the

same value.

5)-DB_FILES-

DB_FILES specifies the maximum number of database files that can be opened for this database.

Set to the maximum number of files that can be assigned to the database.

db_files=1000

The Default value is 200. It is not Modifiable in non-CDB & in a

PDB also. In Oracle RAC Multiple instances must have the

same value.

If you increase the value of DB_FILES, then you must shut

down and restart all instances accessing the database before

the new value can take effect. If you have a

primary and standby database, then they should have the

same value for this parameter.

Check regularly my post and give feedback & comments.

Thanks

Ahmad(Riaz Ahmad Ansari)

OCE & OCP