Consider
the following areas for tuning.
The
order in which steps are listed needs to be maintained to prevent tuning side
effects.
For
example, it is not good increasing the buffer cache if you can reduce I/O by
rewriting a SQL statement. Database Design (if it's not too late)
Poor system performance usually results from a poor database design.
One should generally normalize to the 3rd Normalization Format.
Selective denormalization can provide valuable performance improvements.
When designing, always keep the "data access path" in mind.
Also look at proper data partitioning, data replication and aggregation tables for decision support systems etc.
Application Tuning
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning
Poor system performance usually results from a poor database design.
One should generally normalize to the 3rd Normalization Format.
Selective denormalization can provide valuable performance improvements.
When designing, always keep the "data access path" in mind.
Also look at proper data partitioning, data replication and aggregation tables for decision support systems etc.
Application Tuning
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning
Properly
size your database buffers (shared pool, buffer cache, log buffer etc.) by
looking at your buffer hit ratios. Pin large objects into memory to prevent
frequent reloads.
Disk I/O Tuning
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation etc
Eliminate Database Contention
Study database locks, latches and wait events carefully and eliminate where possible.
Tune
the Operating System
Disk I/O Tuning
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation etc
Eliminate Database Contention
Study database locks, latches and wait events carefully and eliminate where possible.
Monitor and tune operating system CPU, I/O and memory utilization. For more
information, read the related Oracle FAQ dealing with your specific operating
system.
