Configuring a Database for Performance

Published by Ahmad on

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


24 Comments

rajashekar · October 12, 2018 at 11:55 am

vERY NICE BLOG

Arvind kumar · October 12, 2018 at 12:21 pm

Great sir jee

Nematullah · October 12, 2018 at 1:56 pm

Thanks for sharing your valuable experience with us. Keep posting such a great experience.

Erick · October 12, 2018 at 3:38 pm

great stuff.

Suroor · October 12, 2018 at 4:22 pm

Great doc

Nassyam Basha · October 12, 2018 at 4:44 pm

Excellent start. 👍🏼

Zaheer · October 12, 2018 at 7:43 pm

Hi Ahmed,
Thank you for your Valuable Knowledge and experience share with us … which increase our knowledge
Pls keep post … 🙂

Manas · October 13, 2018 at 1:39 am

Great Sir.. Really Nice

Abdul Haseeb Ansari · October 13, 2018 at 10:10 am

Nice post…

kavit Bhatt · October 13, 2018 at 2:31 pm

Very Nice Sir.. Knowledge is very helpful.

ankit · October 13, 2018 at 5:55 pm

Excellent

ankit · October 13, 2018 at 5:57 pm

it really helps

Rajesh devineni · October 13, 2018 at 6:15 pm

Great to find this blog sir.. thanks for sharing knowledge

ankit · October 13, 2018 at 6:28 pm

more than superb

Munshi · October 13, 2018 at 7:54 pm

Good information for beginners

Justin Raju · October 14, 2018 at 10:36 am

The sir blog is awesome and no doubt it will turn out to be of great help for many.god bless you and your work

satish · October 14, 2018 at 11:33 am

very nice explanation sir

Rashmeet · October 15, 2018 at 7:33 pm

Very Useful Post….

Belal · October 15, 2018 at 8:26 pm

Nice blog! Should be very useful for others trying to tune the database for enhanced performance…

Afeer · October 16, 2018 at 7:06 pm

Nice post.Thanks for sharing

Rakesh · October 16, 2018 at 8:29 pm

Good article, keep on update.

ajeetkumardixit · October 17, 2018 at 5:26 pm

Nice suggestion

Showket ali · November 18, 2018 at 3:56 pm

Nice work Riyaz bhai

Leave a Reply to Nematullah Cancel reply

Your email address will not be published. Required fields are marked *