Performance Tuning considerations for developing new Database system

Over all tuning = Oracle Tuning + OS Tuning (including Network Tuning)
Oracle tuning = DB Instance tuning + Application tuning

DB Instance tuning =Tuning Instance initialization parameters

Application Tuning=SQL Tuning + PL/SQL tuning

OS Tuning= Tuning Kernel Parameters+use of large page size of virtual memory+tuning of swap space+tuning of network kernel parameter+tuning of I/O kernel parameters

 

Whenever we have tuning issues I found to tune at system levevl is best approach efforts to gain higher benefit in short time. This is the area where lies the maximum scope of return of efforts as mostly its ignored. So Let us first begin with this.

 

1. Resources under utilized

 

a) SGA+PGA not configured to make best use of available memory.

 

b) Database files not distributed to balanced I/O. Most of the cases they are not following SAME methods.

 

c) Database code is not using partitioned tables or parallelization benefit

 

So even finest tuned aplication would be slow if they wait/content too much for memory/cpu/IO.

 

2. Poor hardware configuration

 

a) very few CPUs or underpower CPU cores

 

b) less memory so more physical I/O and swapping

 

c) not using appropriate RAID level, for e.g. using RAID for write intensive DB or using same for storing redo log files

 

d) very few diks array or physical drives

 

I found many servers having 4GB RAM, RAID 5 and too with only single logical array drive. To make it more slow I found less RPMS of individual hdds in Disk Array

 

3.Poor indexing

• indices missing
• indices unselective
• indices not used
• In other cases I found over indexing was kill for slow DMLs
• too high clustering factor

 4. Statistics not gathered in way to provide  accurate statistics or they are stale

 

database configuration parameters

 

Poor rdbms version / Poor operating system - One should be wise to choose RDBMS software and OS.

 

5. extra load[unnessary work] I found one database project where there were too much redundant materialized views.

 

6. Too often gathering statistics

 

7. bugs(in case some specific sqls are slow) mostly these re fixed in patches.
not using hidden parameters to counter these

 

8. not using latest upgrade may also be one issue for performance slow down as new versions and patches generally have fixes for the known performance bugs and

 

9. Not using the latest features is also one concern for performance tuning one should go for features such as analytial functions,Bulk loading, Query Subfactoring [with clause ], partitioned tables can be used if required.