Skip to main content
Oracle Help Center

Sign Out

Sign In

  • Table of Contents
  • Search
  • Print
  • Download
  1. Database
  2. Oracle
  3. Oracle Database
  4. Release 19

Database 2 Day + Performance Tuning Guide

  • Table of Contents
  • Search
  • Print
  • Download

Table of Contents

Expand AllCollapse All
  • 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 19c, Version 19.1
      • New Features
      • Desupported Features
    • 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.1.4 ADDM for a Multitenant Environment
      • 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

Search

Print

Download

PDF for offline and print

  • Previous
  • Next
  1. Database 2 Day + Performance Tuning Guide
  2. Getting Started

Part I Getting Started

Part I provides an introduction to this guide and explains the Oracle Database performance method. This part contains the following chapters:

  • Introduction

  • Oracle Database Performance Method

  • Previous
  • Next
Back to main content
  • About Oracle
  • Contact Us
  • Legal Notices
  • Terms of Use
  • Your Privacy Rights
  • Cookie Preferences
Copyright © 2007, 2019, Oracle and/or its affiliates. All rights reserved.
  • Previous
  • Next
Video