Automatic Performance Diagnosis and Tuning in Oracle

TLDR

Performance tuning in modern databases is time‑consuming, expertise‑heavy, and often misdirected due to a lack of holistic methodology and historical diagnostic data. The paper presents Oracle’s solution for automatic performance diagnosis and tuning. Oracle introduces a “Database Time” metric and the Automatic Database Diagnostic Monitor (ADDM) to automatically identify throughput bottlenecks and recommend actions, based on specific performance measurements. ADDM serves as a core component of Oracle 10g’s self‑management framework, delivering a comprehensive tuning solution.

Abstract

Performance tuning in modern database systems requires a lot of expertise, is very time consuming and often misdirected. Tuning attempts often lack a methodology that has a holistic view of the database. The absence of historical diagnostic information to investigate performance issues at first occurrence exacerbates the whole tuning process often requiring that problems be reproduced before they can be correctly diagnosed. In this paper we describe how Oracle overcomes these challenges and provides a way to perform automatic performance diagnosis and tuning. We define a new measure called ‘Database Time’ that provides a common currency to gauge the performance impact of any resource or activity in the database. We explain how the Automatic Database Diagnostic Monitor (ADDM) automatically diagnoses the bottlenecks affecting the total database throughput and provides actionable recommendations to alleviate them. We also describe the types of performance measurements that are required to perform an ADDM analysis. Finally we show how ADDM plays a central role within Oracle 10g’s manageability framework to self-manage a database and provide a comprehensive tuning solution.