Table of 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 Release 18c, Version 18.1
  • Changes in Oracle Database 12c Release 2 (12.2)
  • Changes in Oracle Database 12c Release 1 (12.1.0.2)
    • New Features
    • Other Changes
  • Changes in Oracle Database 12c Release 1 (12.1.0.1)
    • New Features
    • Desupported Features
    • Other Changes
• Part I Getting Started
  • 1 Introduction
    • 1.1 About This Guide
    • 1.2 Common Oracle DBA Tasks
    • 1.3 Tools for Tuning the Database
    • 1.4 Accessing the Database Home Page
  • 2 Oracle Database Performance Method
    • 2.1 Gathering Database Statistics Using the Automatic Workload Repository
      • 2.1.1 Time Model Statistics
      • 2.1.2 Wait Event Statistics
      • 2.1.3 Session and System Statistics
      • 2.1.4 Active Session History Statistics
      • 2.1.5 High-Load SQL Statistics
    • 2.2 Using the Oracle Performance Method
      • 2.2.1 Preparing the Database for Tuning
      • 2.2.2 Tuning the Database Proactively
      • 2.2.3 Tuning the Database Reactively
      • 2.2.4 Tuning SQL Statements
    • 2.3 Common Performance Problems Found in Databases
• Part II Proactive Database Tuning
  • 3 Automatic Database Performance Monitoring
    • 3.1 Overview of Automatic Database Diagnostic Monitor
      • 3.1.1 ADDM Analysis
      • 3.1.2 ADDM Recommendations
      • 3.1.3 ADDM for Oracle Real Application Clusters
    • 3.2 Configuring Automatic Database Diagnostic Monitor
      • 3.2.1 Setting Initialization Parameters to Enable ADDM
      • 3.2.2 Setting the DBIO_EXPECTED Parameter
      • 3.2.3 Managing AWR Snapshots
        • 3.2.3.1 Creating Snapshots
        • 3.2.3.2 Modifying Snapshot Settings
    • 3.3 Reviewing the Automatic Database Diagnostic Monitor Analysis
    • 3.4 Interpretation of Automatic Database Diagnostic Monitor Findings
    • 3.5 Implementing Automatic Database Diagnostic Monitor Recommendations
    • 3.6 Viewing Snapshot Statistics
  • 4 Monitoring Real-Time Database Performance
    • 4.1 Monitoring User Activity
      • 4.1.1 Monitoring Top SQL
      • 4.1.2 Monitoring Top Sessions
      • 4.1.3 Monitoring Top Services
      • 4.1.4 Monitoring Top Modules
      • 4.1.5 Monitoring Top Actions
      • 4.1.6 Monitoring Top Clients
      • 4.1.7 Monitoring Top PL/SQL
      • 4.1.8 Monitoring Top Files
      • 4.1.9 Monitoring Top Objects
    • 4.2 Monitoring Instance Activity
      • 4.2.1 Monitoring Throughput
      • 4.2.2 Monitoring I/O
        • 4.2.2.1 Monitoring I/O by Function
        • 4.2.2.2 Monitoring I/O by Type
        • 4.2.2.3 Monitoring I/O by Consumer Group
      • 4.2.3 Monitoring Parallel Execution
      • 4.2.4 Monitoring Services
    • 4.3 Monitoring Host Activity
      • 4.3.1 Monitoring CPU Utilization
      • 4.3.2 Monitoring Memory Utilization
      • 4.3.3 Monitoring Disk I/O Utilization
    • 4.4 Determining the Cause of Spikes in Database Activity
    • 4.5 Customizing the Database Performance page
  • 5 Monitoring Real-Time Database Operations
    • 5.1 About Monitoring Database Operations
      • 5.1.1 Types of Database Operations
      • 5.1.2 Purposes of Monitoring Database Operations
      • 5.1.3 Enabling Monitoring of Database Operations
      • 5.1.4 Attributes of Database Operations
    • 5.2 Creating a Database Operation
    • 5.3 Monitoring Database Operations in Cloud Control
      • 5.3.1 Viewing SQL Execution Details for a Composite Database Operation
      • 5.3.2 Viewing SQL Execution Details for a SQL Statement
      • 5.3.3 Viewing SQL Execution Details for a PL/SQL Statement
  • 6 Monitoring Performance Alerts
    • 6.1 Setting Metric Thresholds for Performance Alerts
    • 6.2 Responding to Alerts
    • 6.3 Clearing Alerts
• Part III Reactive Database Tuning
  • 7 Manual Database Performance Monitoring
    • 7.1 Manually Running ADDM to Analyze Current Database Performance
    • 7.2 Manually Running ADDM to Analyze Historical Database Performance
    • 7.3 Accessing Previous ADDM Results
  • 8 Resolving Transient Performance Problems
    • 8.1 Overview of Active Session History
    • 8.2 Running Active Session History Reports
    • 8.3 Active Session History Reports
      • 8.3.1 Top Events
        • 8.3.1.1 Top User Events
        • 8.3.1.2 Top Background Events
      • 8.3.2 Load Profile
      • 8.3.3 Top SQL
      • 8.3.4 Top Sessions
      • 8.3.5 Top DB Objects/Files/Latches
        • 8.3.5.1 Top DB Objects
        • 8.3.5.2 Top DB Files
        • 8.3.5.3 Top Latches
      • 8.3.6 Activity Over Time
    • 8.4 Diagnosing Serious Performance Problems in Real Time
  • 9 Resolving Performance Degradation Over Time
    • 9.1 Managing Baselines
      • 9.1.1 Creating a Baseline
        • 9.1.1.1 Creating a Single Baseline
        • 9.1.1.2 Creating a Repeating Baseline
      • 9.1.2 Deleting a Baseline
      • 9.1.3 Computing Threshold Statistics for Baselines
      • 9.1.4 Setting Metric Thresholds for Baselines
        • 9.1.4.1 Setting Metric Thresholds for the Default Moving Baseline
        • 9.1.4.2 Setting Metric Thresholds for Selected Baselines
    • 9.2 Running the AWR Compare Periods Reports
      • 9.2.1 Comparing a Baseline to Another Baseline or Pair of Snapshots
      • 9.2.2 Comparing Current System Performance to a Baseline Period
      • 9.2.3 Comparing Two Pairs of Snapshots
    • 9.3 Using the AWR Compare Periods Reports
      • 9.3.1 Summary of the AWR Compare Periods Report
        • 9.3.1.1 Snapshot Sets
        • 9.3.1.2 Host Configuration Comparison
        • 9.3.1.3 Cache Sizes
        • 9.3.1.4 Load Profile
        • 9.3.1.5 Top Timed Events
      • 9.3.2 Details of the AWR Compare Periods Report
      • 9.3.3 Supplemental Information in the AWR Compare Periods Report
  • 10 Using Automatic Workload Repository Warehouse for Generating Performance Reports
    • 10.1 Setting Up the AWR Warehouse
    • 10.2 Working with Source Databases
    • 10.3 Uploading Snapshots to the AWR Warehouse
    • 10.4 Using Performance Pages with the AWR Warehouse
    • 10.5 Monitoring and Researching Incidents and Errors
    • 10.6 AWR Warehouse Best Practices
      • 10.6.1 Database Best Practices
        • 10.6.1.1 Memory Management
        • 10.6.1.2 Storage Requirements
        • 10.6.1.3 Backup
        • 10.6.1.4 Redo Log Size
        • 10.6.1.5 Stats Collection
        • 10.6.1.6 The job_queue_processes Parameter
        • 10.6.1.7 Access Control
      • 10.6.2 Enterprise Manager Best Practices
        • 10.6.2.1 AWR Warehouse Credentials
        • 10.6.2.2 Source Database Credentials
        • 10.6.2.3 Staging Location on AWR Warehouse
        • 10.6.2.4 Network Latency
• Part IV SQL Tuning
  • 11 Identifying High-Load SQL Statements
    • 11.1 Identification of High-Load SQL Statements Using ADDM Findings
    • 11.2 Identifying High-Load SQL Statements Using Top SQL
      • 11.2.1 Viewing SQL Statements by Wait Class
      • 11.2.2 Viewing Details of SQL Statements
        • 11.2.2.1 Viewing SQL Statistics
          • 11.2.2.1.1 SQL Statistics Summary
          • 11.2.2.1.2 General SQL Statistics
          • 11.2.2.1.3 Activity by Waits Statistics and Activity by Time Statistics
          • 11.2.2.1.4 Elapsed Time Breakdown Statistics
          • 11.2.2.1.5 Shared Cursors Statistics and Execution Statistics
          • 11.2.2.1.6 Other SQL Statistics
        • 11.2.2.2 Viewing Session Activity
        • 11.2.2.3 Viewing the SQL Execution Plan
        • 11.2.2.4 Viewing the Plan Control
        • 11.2.2.5 Viewing the Tuning History
  • 12 Tuning SQL Statements
    • 12.1 Tuning SQL Statements Using SQL Tuning Advisor
      • 12.1.1 Tuning SQL Manually Using SQL Tuning Advisor
      • 12.1.2 Viewing Automatic SQL Tuning Results
    • 12.2 Managing SQL Tuning Sets
      • 12.2.1 Creating a SQL Tuning Set
        • 12.2.1.1 Creating a SQL Tuning Set: Options
        • 12.2.1.2 Creating a SQL Tuning Set: Load Methods
          • 12.2.1.2.1 Loading Active SQL Statements Incrementally from the Cursor Cache
          • 12.2.1.2.2 Loading SQL Statements from the Cursor Cache
          • 12.2.1.2.3 Loading SQL Statements from AWR Snapshots
          • 12.2.1.2.4 Loading SQL Statements from AWR Baselines
          • 12.2.1.2.5 Loading SQL Statements from a User-Defined Workload
        • 12.2.1.3 Creating a SQL Tuning Set: Filter Options
        • 12.2.1.4 Creating a SQL Tuning Set: Schedule
      • 12.2.2 Dropping a SQL Tuning Set
      • 12.2.3 Transporting SQL Tuning Sets
        • 12.2.3.1 Exporting a SQL Tuning Set
        • 12.2.3.2 Importing a SQL Tuning Set
    • 12.3 Managing SQL Profiles
    • 12.4 Managing SQL Plan Baselines
      • 12.4.1 Capturing SQL Plan Baselines Automatically
      • 12.4.2 Loading SQL Plan Baselines Manually
      • 12.4.3 Evolving SQL Plans
  • 13 Optimizing Data Access Paths
    • 13.1 Running SQL Access Advisor
      • 13.1.1 Selecting the Initial Options
      • 13.1.2 Selecting the Workload Source
        • 13.1.2.1 Using SQL Statements from the Cache
        • 13.1.2.2 Using an Existing SQL Tuning Set
        • 13.1.2.3 Using a Hypothetical Workload
      • 13.1.3 Applying Filter Options
        • 13.1.3.1 Defining Filters for Resource Consumption
        • 13.1.3.2 Defining Filters for Users
        • 13.1.3.3 Defining Filters for Tables
        • 13.1.3.4 Defining Filters for SQL Text
        • 13.1.3.5 Defining Filters for Modules
        • 13.1.3.6 Defining Filters for Actions
      • 13.1.4 Specifying Recommendation Options
      • 13.1.5 Specifying Task and Scheduling Options
    • 13.2 Reviewing the SQL Access Advisor Recommendations
      • 13.2.1 Reviewing the SQL Access Advisor Recommendations: Summary
      • 13.2.2 Reviewing the SQL Access Advisor Recommendations: Recommendations
      • 13.2.3 Reviewing the SQL Access Advisor Recommendations: SQL Statements
      • 13.2.4 Reviewing the SQL Access Advisor Recommendations: Details
    • 13.3 Implementing the SQL Access Advisor Recommendations
• Index