Mining AWR to Find Unreported Performance Problems

Wayne Sharp
Union Gas

REQUIREMENT: Diagnostics pack


Problem description

• Three high-profile production applications were experiencing severe performance degradation
• Response time for other 49 applications was either 
• normal
• degraded, but not reported
• I/O Latency appeared to be the culprit
• SAN Admin blamed I/O on DB

Looked at AWR for information regarding issue for all 54 databases to collect information about problem

THERE HAS TO BE A BETTER WAY. 

• DBA_HIST_SYSSTAT
• dbms_workload_repository(to generate AWR report)

Walkthrough of scripts(!!!!!)

• Dropbox link to scripts and presentation

Solution: OEM query taking forever on a quiet system (3m reads) on DBMS_SCHEDULER_TABLE