Go to main content
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 12
c
Release 2 (12.2.0.1)
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
New Features
Other Changes
Changes in Oracle Database 12
c
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
Scripting on this page enhances content navigation, but does not change the content in any way.