Go to main content
1/21
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
Scripting on this page enhances content navigation, but does not change the content in any way.