CheckTuning.sql:

This script provides an easy-to-understand "quick check" of instance-level performance measurements and their related tuning parameters. It selects raw statistics from V$ dynamic tables, performs the necessary calculations, and returns suggested target values along with the calculated statistics. When the performance statistics are not aligned with the suggested target values, the initialization parameter or configuration element needing change or further inspection is suggested. Information regarding the following measurements is returned:

• Library cache hit ratio
• Dictionary cache hit ratio
• Buffer cache hit ratio
• Sort area hit ratio
• Redo log space requests
• Enqueue waits
• Checkpoint degradation
• Rollback contention
• Redo allocation latch contention (willing-to-wait and immediate latches)
• Redo copy latch contention (willing-to-wait and immediate latches)
• Freelist contention

Download checktuning.sql (10K)