Contents

List of Figures

List of Tables

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
    • Creating Snapshots
    • Modifying Snapshot Settings
• 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 I/O by Function
    • Monitoring I/O by Type
    • Monitoring I/O by Consumer Group
  • Monitoring Parallel Execution
  • Monitoring Services
• Monitoring Host Activity
  • Monitoring CPU Utilization
  • Monitoring Memory Utilization
  • Monitoring Disk I/O Utilization
• Determining the Cause of Spikes in Database Activity
• 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
    • Top User Events
    • Top Background Events
  • Load Profile
  • Top SQL
  • Top Sessions
  • Top DB Objects
  • Top DB Files
  • Activity Over Time
• Diagnosing Malfunctioning Systems in an Emergency

8 Resolving Performance Degradation Over Time

• Managing Baselines
  • Creating a Baseline
    • Creating a Single Baseline
    • Creating a Repeating Baseline
  • Deleting a Baseline
  • Computing Threshold Statistics for Baselines
  • Setting Metric Thresholds for Baselines
    • Setting Metric Thresholds for the Default Moving Baseline
    • Setting Metric Thresholds for Selected Baselines
• Running the AWR Compare Periods Reports
  • Comparing a Baseline to Another Baseline or Pair of Snapshots
  • Comparing Current System Performance to a Baseline Period
  • Comparing Two Pairs of Snapshots
• Using the AWR Compare Periods Reports
  • Summary of the AWR Compare Periods Report
    • Snapshot Sets
    • Load Profile
    • Top Timed Events
    • Host Configuration Comparison
    • System Configuration Comparison
  • 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
    • Viewing SQL Statistics
    • Viewing Session Activity
    • Viewing the SQL Execution Plan
    • Viewing the Plan Control
    • Viewing the Tuning History

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
    • Creating a SQL Tuning Set: Options
    • Creating a SQL Tuning Set: Load Method
    • Creating a SQL Tuning Set: Filter Options
    • Creating a SQL Tuning Set: Schedule
  • Dropping a SQL Tuning Set
  • Transporting SQL Tuning Sets
    • Exporting a SQL Tuning Set
    • Importing a SQL Tuning Set
• 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
    • Using SQL Statements from the Cache
    • Using an Existing SQL Tuning Set
    • Using a Hypothetical Workload
  • Running SQL Access Advisor: Filter Options
    • Defining Filters for Resource Consumption
    • Defining Filters for Users
    • Defining Filters for Tables
    • Defining Filters for SQL Text
    • Defining Filters for Modules
    • Defining Filters for Actions
  • 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