- Proactive tuning
- Design, develoment and testing stage
- In production ADDM -link
- Reactive Tuning
- In production after problem has occurred.
Resolving Performance issues
- Tuning outside of database
- O.S.
- Network
- Storage
- Database Tuning
- Design
- SQL Tuning
- Instance Tuning
Tuning Methodology
- Monitor (Locking for Symptoms)
- User Feedback
- Dynamic performance views
- OEM
- Alert log
- Diagnostic (What It's the issue that caused this problem.)
- Reports
- Stackpack (Oracle Standar Edition SE and Enterprise Edition EE)
- AWR (Automatic Workload Repository is comming with EE / Diagnostic Pack)
- ASH (Active Session History is coming with EE & Diagnostic Pack
- Tune
- Advisor (EE Diagnostic & Tuning Pack)
- ADDM (Automatic Diagnostic Monitor)
- STA (SQL Tuning Advisor)
- SAA (SQL Access Advisor)
- SPA (SQL Performance Analyzer)
- Memory Advisor
Dynamic Performance Views
- Instance Activity Statistics.
- all statistics
- V$statname
- Parse Time CPU
- Physical Reads
- User Commits
- Cumulative
- Sessions
- Systems
- Wait Event
- v$event_name
- Instance level wait events v$session_event
- Session level wait events
- v$session_event (Waited in the past)
- v$session_wait (Current wait)
- Metrics (Metrics are rate of change of cumulative statistics)
- v$metric_name
- Instance Level v$sysmetric
- Session Level v$sessmetric
Monitor alert log
- Time to performance archiving
- Instance Recovery
- Deadlock and timeout error
- Incomplete Checkpoint
- Checkpoint start
- Checkpoint start and end times.
- ADRCI
