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 the Automatic Database Diagnostic Monitor
• Configuring the Automatic Database Diagnostics Monitor
  • Setting the STATISTICS_LEVEL parameter
  • Setting the DBIO_EXPECTED parameter
  • Managing Snapshots
    • Creating Snapshots
    • Modifying Snapshot Settings
• Reviewing the Automatic Database Diagnostics Monitor Analysis
• Interpreting the Automatic Database Diagnostics Monitor Findings
• Implementing ADDM 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 Instance Activity
• Monitoring Host Activity
  • Monitoring CPU Utilization
  • Monitoring Memory Utilization
  • Monitoring Disk I/O Utilization

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
• Using Active Session History Reports
  • Top Events
    • Top User Events
    • Top Background Events
    • Top Event P1/P2/P3 Values
  • Load Profiles
    • Top Service/Module
    • Top Client IDs
    • Top SQL Command Types
  • Top SQL
    • Top SQL Statements
    • Top SQL Using Literals
    • Complete List of SQL Text
  • Top Sessions
    • Top Sessions
    • Top Blocking Sessions
    • Top Sessions Running PQs
  • Top Objects/Files/Latches
    • Top DB Objects
    • Top DB Files
    • Top Latches
  • Activity Over Time

8 Resolving Performance Degradation Over Time

• Creating Baselines
• Running the Automatic Workload Repository Compare Periods Reports
  • Comparing a Baseline to Another Baseline or Pair of Snapshots
  • Comparing Two Pairs of Snapshots
• Using the Automatic Workload Repository Compare Periods Reports
  • Report Summary
    • Snapshot Sets
    • Configuration Comparison
    • Load Profile
    • Top 5 Timed Events
  • Wait Events
  • Time Model Statistics
  • Operating System Statistics
  • Service Statistics
  • SQL Statistics
    • Top 10 SQL Comparison by Execution Time
    • Top 10 SQL Comparison by CPU Time
    • Top 10 SQL Comparison by Buffer Gets
    • Top 10 SQL Comparison by Physical Reads
    • Top 10 SQL Comparison by Executions
    • Top 10 SQL Comparisons by Parse Calls
    • Complete List of SQL Text
  • Instance Activity Statistics
    • Key Instance Activity Statistics
    • Other Instance Activity Statistics
  • I/O Statistics
    • Tablespace I/O Statistics
    • Top 10 File Comparison by I/O
    • Top 10 File Comparison by Read Time
    • Top 10 File Comparison by Buffer Waits
  • Advisory Statistics
    • PGA Aggregate Summary
    • PGA Aggregate Target Statistics
  • Wait Statistics
    • Buffer Wait Statistics
    • Enqueue Activity
  • Latch Statistics
  • Segment Statistics
    • Top 5 Segments Comparison by Logical Reads
    • Top 5 Segments Comparison by Physical Reads
    • Top 5 Segments by Row Lock Waits
    • Top 5 Segments by ITL Waits
    • Top 5 Segments by Buffer Busy Waits
  • Dictionary Cache Statistics
  • Library Cache Statistics
  • SGA Statistics
    • SGA Memory Summary
    • SGA Breakdown Difference
  • init.ora Parameters

Part IV SQL Tuning

9 Identifying High-Load SQL Statements

• Identifying 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 SQL Execution Plan
    • Viewing SQL Tuning Information

10 Tuning SQL Statements

• Tuning SQL Statements Using the SQL Tuning Advisor
• 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
  • Deleting a SQL Tuning Set
  • Transporting a SQL Tuning Set
• Managing SQL Profiles

11 Optimizing Data Access Paths

• Running the SQL Access Advisor
  • Running the SQL Access Advisor: Initial Options
  • Running the SQL Access Advisor: Workload Source
    • Using SQL Statements from the Cache
    • Using an Existing SQL Tuning Set
    • Using a User-Defined Workload
    • Using a Hypothetical Workload
  • Running the 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 Module ID
    • Defining Filters for Actions
  • Running the SQL Access Advisor: Recommendation Options
  • Running the 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