Steps in Oracle Tning Process

1. Verify that there are no network slowdowns or transmission bottlenecks.
2. Verify that the CPU and memory for the Oracle database server are not overloaded. Also have the System Administrator check for I/O bottlenecks on each disk.
3. Once the external check is complete, we should now know if the Oracle database is responsible for the performance slowdown. If this is the case, we need to perform the following tasks.
4. Check library cache hit ratio and pin hit ratio for the following library cache areas:
   • SQL area
   • Table./procedure
   • Body
   • Trigger
   • Index
   • Cluster
   • Object
   • Pipe
   • Check for high redo log space requests
   • Check for the amount of SGA free memory
   • Check for held locks within the storage pool
   • Check lock summaries of DDL locks
   • Check lock summaries of DML locks
   • Check redo copy latch contention
   • Check rollback segment contention
   • Check session event waits
   • Check wait queues
   • Check buffer busy waits

• Check buffer hit ratio
• Check data dictionary hit ratio
• Check library cache hit ratio
• Check the number of reads/writes for each Oracle data file