Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Part I Getting Started

1 Introduction

• About This Guide
• Common Oracle DBA Tasks
• Tools for Tuning the Database

2 Oracle Database Performance Method

• Gathering Database Statistics Using the Automatic Workload Repository
  • Time Model Statistics
  • Wait Event Statistics
  • Session and System Statistics
  • Active Session History Statistics
  • High-Load SQL Statistics
• Using the Oracle Performance Method
  • Preparing the Database for Tuning
  • Tuning the Database Proactively
  • Tuning the Database Reactively
  • Tuning SQL Statements
• Common Performance Problems Found in Oracle Databases

Part II Proactive Database Tuning

3 Automatic Database Performance Monitoring

• Overview of Automatic Database Diagnostic Monitor
  • ADDM Analysis
  • ADDM Recommendations
  • ADDM for Oracle Real Application Clusters
• Configuring Automatic Database Diagnostic Monitor
  • Setting Initialization Parameters to Enable ADDM
  • Setting the DBIO_EXPECTED Parameter
  • Managing AWR Snapshots
• Reviewing the Automatic Database Diagnostic Monitor Analysis
• Interpretation of Automatic Database Diagnostic Monitor Findings
• Implementing Automatic Database Diagnostic Monitor Recommendations
• Viewing Snapshot Statistics

4 Monitoring Real-Time Database Performance

• Monitoring User Activity
  • Monitoring Top SQL
  • Monitoring Top Sessions
  • Monitoring Top Services
  • Monitoring Top Modules
  • Monitoring Top Actions
  • Monitoring Top Clients
  • Monitoring Top PL/SQL
  • Monitoring Top Files
  • Monitoring Top Objects
• Monitoring Instance Activity
  • Monitoring Throughput
  • Monitoring I/O
  • Monitoring Parallel Execution
  • Monitoring Services
• Monitoring Host Activity
  • Monitoring CPU Utilization
  • Monitoring Memory Utilization
  • Monitoring Disk I/O Utilization
• Customizing the Database Performance Page

5 Monitoring Performance Alerts

• Setting Metric Thresholds for Performance Alerts
• Responding to Alerts
• Clearing Alerts

Part III Reactive Database Tuning

6 Manual Database Performance Monitoring

• Manually Running ADDM to Analyze Current Database Performance
• Manually Running ADDM to Analyze Historical Database Performance
• Accessing Previous ADDM Results

7 Resolving Transient Performance Problems

• Overview of Active Session History
• Running Active Session History Reports
• Active Session History Reports
  • Top Events
  • Load Profile
  • Top SQL
  • Top Sessions
  • Top DB Objects
  • Top DB Files
  • Activity Over Time

8 Resolving Performance Degradation Over Time

• Managing Baselines
  • Creating a Baseline
  • Deleting a Baseline
  • Computing Threshold Statistics for Baselines
  • Setting Metric Thresholds for Baselines
• Running the AWR Compare Periods Reports
  • Comparing a Baseline to Another Baseline or Pair of Snapshots
  • Comparing Two Pairs of Snapshots
• Using the AWR Compare Periods Reports
  • Summary of the AWR Compare Periods Report
  • Details of the AWR Compare Periods Report
  • Supplemental Information in the AWR Compare Periods Report

Part IV SQL Tuning

9 Identifying High-Load SQL Statements

• Identification of High-Load SQL Statements Using ADDM Findings
• Identifying High-Load SQL Statements Using Top SQL
  • Viewing SQL Statements by Wait Class
  • Viewing Details of SQL Statements

10 Tuning SQL Statements

• Tuning SQL Statements Using SQL Tuning Advisor
  • Tuning SQL Manually Using SQL Tuning Advisor
  • Viewing Automatic SQL Tuning Results
• Managing SQL Tuning Sets
  • Creating a SQL Tuning Set
  • Dropping a SQL Tuning Set
  • Transporting SQL Tuning Sets
• Managing SQL Profiles
• Managing SQL Execution Plans

11 Optimizing Data Access Paths

• Running SQL Access Advisor
  • Running SQL Access Advisor: Initial Options
  • Running SQL Access Advisor: Workload Source
  • Running SQL Access Advisor: Filter Options
  • Running SQL Access Advisor: Recommendation Options
  • Running SQL Access Advisor: Schedule
• Reviewing the SQL Access Advisor Recommendations
  • Reviewing the SQL Access Advisor Recommendations: Summary
  • Reviewing the SQL Access Advisor Recommendations: Recommendations
  • Reviewing the SQL Access Advisor Recommendations: SQL Statements
  • Reviewing the SQL Access Advisor Recommendations: Details
• Implementing the SQL Access Advisor Recommendations

Index