Thursday, February 5, 2009

Performance Tuning in Oracle Database

Tuning performance is a difficult task to perform. One should know the area on which the tuning is required before performing the real tuning. This column is added to know the database parameters to tune to meet the usage of the hardware requirement during the installation of oracle database.

Parameters affecting the real time transaction processing system in oracle database are as below:

For instance the server is of 1GB memory. The size of the parameters is specified in bytes.

sga_max_size=536870912
reset this parameter upto 50% of the total memrory (in bytes) -512m
shared_pool_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
large_pool_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m
db_cache_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
pga_aggregate_target=214748364
reset this parameter upto 20% of the total memrory (in bytes) -204m
sort_area_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m


Create the pfile from the current spfile and change or add the parameters neccessary parameters in the parameter file. Rename
the spfile to take the effect of the pfile in the next startup of the database or create spfile from the changed pfile.

These parameters helps in processing the DML statements during Online Transaction Processing System.

No comments: