Go to main content
1/23
Contents
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What's New in Oracle Performance?
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
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
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 Two Pairs of Snapshots
Using the AWR Compare Periods Reports
Summary of the AWR Compare Periods Report
Snapshot Sets
Host Configuration Comparison
System Configuration Comparison
Load Profile
Top Timed Events
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 SQL Tuning Information
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
12
Analyzing SQL Performance Impact
SQL Performance Analyzer Usage
SQL Performance Analyzer Methodology
Capturing and Transporting a SQL Workload
Setting Up the Database Environment on the Test System
Executing a SQL Workload
Running SQL Performance Analyzer
Testing an Initialization Parameter Change with SQL Performance Analyzer
Following a Guided Workflow with SQL Performance Analyzer
Creating a SQL Performance Analyzer Task Based on a SQL Tuning Set
Establishing the Initial Environment
Collecting SQL Performance Data Before the Change
Making the System Change
Collecting SQL Performance Data After the Change
Comparing SQL Performance Before and After the Change
Reviewing the SQL Performance Analyzer Report
Reviewing the SQL Performance Analyzer Report: General Information
Reviewing the SQL Performance Analyzer Report: Global Statistics
Reviewing the SQL Performance Analyzer Report: Global Statistics Details
Index
Scripting on this page enhances content navigation, but does not change the content in any way.