Tuning an application to run efficiently against any version of the Oracle RDBMS is a recursive process which requires continuous monitoring and adjustments.    There are multiple Oracle tools and process to aid in both the monitoring and adjustment process.    This article covers the basic process of Application Tuning.

1. Tune to a problem or performance level – Due not go about changing settings just to see what happens. Rather try to find a problem or bottle-neck in the database and tune to reduce the problem or performance slow down.
2. Gather Data/statistics – Use diagnostics tools to gather info about problem/slow down. Normally this tools will be related to gathering statistics based against a known baseline.
3. Make Discrete Changes – Make small exact changes and observe response. Try to make one change at a time, thus you will be able to determine what change helped solve the issue.
4. Stop – Stop making changes once the problem or bottle-neck has been addressed.
5. Monitor – Monitor the database and observe things that can be associated with performance problems:
   1. Long waits.
   2. Long held latches and locks.
   3. Long running processes.
   4. High CPU usage.
   5. High I/O
6. Create new baselines for performance with known workloads. These baselines can be used to truly determine that a perceived database issue is truly abnormal and needs to be addressed.

Larry Catt

OCP