1/22

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database 2 Day + Performance Tuning Guide

• Changes in Oracle Database 12c Release 1 (12.1.0.2)
• Changes in Oracle Database 12c Release 1 (12.1.0.1)

Part I Getting Started

1 Introduction

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

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 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 Real-Time Database Operations

• About Monitoring Database Operations
  • Types of Database Operations
  • Purposes of Monitoring Database Operations
  • Enabling Monitoring of Database Operations
  • Attributes of Database Operations
• Creating a Database Operation
• Monitoring Database Operations in Cloud Control
  • Viewing SQL Execution Details for a Composite Database Operation
  • Viewing SQL Execution Details for a SQL Statement
  • Viewing SQL Execution Details for a PL/SQL Statement

6 Monitoring Performance Alerts

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

Part III Reactive Database Tuning

7 Manual Database Performance Monitoring

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

8 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/Files/Latches
    • Top DB Objects
    • Top DB Files
    • Top Latches
  • Activity Over Time
• Diagnosing Serious Performance Problems in Real Time

9 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
    • Host Configuration Comparison
    • Cache Sizes
    • Load Profile
    • Top Timed Events
  • Details of the AWR Compare Periods Report
  • Supplemental Information in the AWR Compare Periods Report

10 Using Automatic Workload Repository Warehouse for Generating Performance Reports

• Setting Up the AWR Warehouse
• Working with Source Databases
• Uploading Snapshots to the AWR Warehouse
• Using Performance Pages with the AWR Warehouse
• Monitoring and Researching Incidents and Errors
• AWR Warehouse Best Practices
  • Database Best Practices
    • Memory Management
    • Storage Requirements
    • Backup
    • Redo Log Size
    • Stats Collection
    • The job_queue_processes Parameter
    • Access Control
  • Enterprise Manager Best Practices
    • AWR Warehouse Credentials
    • Source Database Credentials
    • Staging Location on AWR Warehouse
    • Network Latency

Part IV SQL Tuning

11 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

12 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 Methods
    • 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 Plan Baselines
  • Capturing SQL Plan Baselines Automatically
  • Loading SQL Plan Baselines Manually
  • Evolving SQL Plans

13 Optimizing Data Access Paths

• Running SQL Access Advisor
  • Selecting the Initial Options
  • Selecting the Workload Source
    • Using SQL Statements from the Cache
    • Using an Existing SQL Tuning Set
    • Using a Hypothetical Workload
  • Applying 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
  • Specifying Recommendation Options
  • Specifying Task and Scheduling Options
• 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