Oracle® Database 2 Day + Performance Tuning Guide
Oracle® Database 2 Day + Performance Tuning Guide
Introduction
“Introduction”
Tools for Tuning the Database
“Tools for Tuning the Database”
Oracle Database Performance Method
“Oracle Database Performance Method”
Gathering Database Statistics Using the Automatic Workload Repository
“Gathering Database Statistics Using the Automatic Workload Repository”
Time Model Statistics
“Time Model Statistics”
DB Time in Overall User Response Time
“DB Time in Overall User Response Time”
DB Time in User Transaction
“DB Time in User Transaction”
Wait Event Statistics
“Wait Event Statistics”
Session and System Statistics
“Session and System Statistics”
Active Session History Statistics
“Active Session History Statistics”
Active Session History
“Active Session History”
Active Session History
“Active Session History”
High-Load SQL Statistics
“High-Load SQL Statistics”
Using the Oracle Performance Method
“Using the Oracle Performance Method”
Preparing the Database for Tuning
“Preparing the Database for Tuning”
Tuning the Database Proactively
“Tuning the Database Proactively”
Tuning the Database Reactively
“Tuning the Database Reactively”
Tuning SQL Statements
“Tuning SQL Statements”
Common Performance Problems Found in Oracle Databases
“Common Performance Problems Found in Oracle Databases”
Automatic Database Performance Monitoring
“Automatic Database Performance Monitoring”
Overview of Automatic Database Diagnostic Monitor
“Overview of Automatic Database Diagnostic Monitor”
ADDM Analysis
“ADDM Analysis”
ADDM Recommendations
“ADDM Recommendations”
ADDM for Oracle Real Application Clusters
“ADDM for Oracle Real Application Clusters”
Configuring Automatic Database Diagnostic Monitor
“Configuring Automatic Database Diagnostic Monitor”
Setting Initialization Parameters to Enable ADDM
“Setting Initialization Parameters to Enable ADDM”
Setting the DBIO_EXPECTED Parameter
“Setting the DBIO_EXPECTED Parameter”
Managing AWR Snapshots
“Managing AWR Snapshots”
Creating Snapshots
“Creating Snapshots”
Modifying Snapshot Settings
“Modifying Snapshot Settings”
Reviewing the Automatic Database Diagnostic Monitor Analysis
“Reviewing the Automatic Database Diagnostic Monitor Analysis”
Interpretation of Automatic Database Diagnostic Monitor Findings
“Interpretation of Automatic Database Diagnostic Monitor Findings”
Implementing Automatic Database Diagnostic Monitor Recommendations
“Implementing Automatic Database Diagnostic Monitor Recommendations”
Viewing Snapshot Statistics
“Viewing Snapshot Statistics”
Monitoring Real-Time Database Performance
“Monitoring Real-Time Database Performance”
Monitoring User Activity
“Monitoring User Activity”
Monitoring Top SQL
“Monitoring Top SQL”
Monitoring Top Sessions
“Monitoring Top Sessions”
Monitoring Top Services
“Monitoring Top Services”
Monitoring Top Modules
“Monitoring Top Modules”
Monitoring Top Actions
“Monitoring Top Actions”
Monitoring Top Clients
“Monitoring Top Clients”
Monitoring Top PL/SQL
“Monitoring Top PL/SQL”
Monitoring Top Files
“Monitoring Top Files”
Monitoring Top Objects
“Monitoring Top Objects”
Monitoring Instance Activity
“Monitoring Instance Activity”
Monitoring Throughput
“Monitoring Throughput”
Monitoring I/O
“Monitoring I/O”
Monitoring I/O by Function
“Monitoring I/O by Function”
Monitoring I/O by Type
“Monitoring I/O by Type”
Monitoring I/O by Consumer Group
“Monitoring I/O by Consumer Group”
Monitoring Parallel Execution
“Monitoring Parallel Execution”
Monitoring Services
“Monitoring Services”
Monitoring Host Activity
“Monitoring Host Activity”
Monitoring CPU Utilization
“Monitoring CPU Utilization”
Monitoring Memory Utilization
“Monitoring Memory Utilization”
Monitoring Disk I/O Utilization
“Monitoring Disk I/O Utilization”
Customizing the Database Performance Page
“Customizing the Database Performance Page”
Monitoring Performance Alerts
“Monitoring Performance Alerts”
Setting Metric Thresholds for Performance Alerts
“Setting Metric Thresholds for Performance Alerts”
Responding to Alerts
“Responding to Alerts”
Clearing Alerts
“Clearing Alerts”
Manual Database Performance Monitoring
“Manual Database Performance Monitoring”
Manually Running ADDM to Analyze Current Database Performance
“Manually Running ADDM to Analyze Current Database Performance”
Manually Running ADDM to Analyze Historical Database Performance
“Manually Running ADDM to Analyze Historical Database Performance”
Accessing Previous ADDM Results
“Accessing Previous ADDM Results”
Resolving Transient Performance Problems
“Resolving Transient Performance Problems”
Overview of Active Session History
“Overview of Active Session History”
Running Active Session History Reports
“Running Active Session History Reports”
Active Session History Reports
“Active Session History Reports”
Top Events
“Top Events”
Top User Events
“Top User Events”
Top Background Events
“Top Background Events”
Load Profile
“Load Profile”
Top SQL
“Top SQL”
Top Sessions
“Top Sessions”
Top DB Objects
“Top DB Objects”
Top DB Files
“Top DB Files”
Activity Over Time
“Activity Over Time”
Activity Over Time
“Activity Over Time”
Resolving Performance Degradation Over Time
“Resolving Performance Degradation Over Time”
Managing Baselines
“Managing Baselines”
Creating a Baseline
“Creating a Baseline”
Creating a Single Baseline
“Creating a Single Baseline”
Creating a Repeating Baseline
“Creating a Repeating Baseline”
Deleting a Baseline
“Deleting a Baseline”
Computing Threshold Statistics for Baselines
“Computing Threshold Statistics for Baselines”
Setting Metric Thresholds for Baselines
“Setting Metric Thresholds for Baselines”
Setting Metric Thresholds for the Default Moving Baseline
“Setting Metric Thresholds for the Default Moving Baseline”
Setting Metric Thresholds for Selected Baselines
“Setting Metric Thresholds for Selected Baselines”
Running the AWR Compare Periods Reports
“Running the AWR Compare Periods Reports”
Comparing a Baseline to Another Baseline or Pair of Snapshots
“Comparing a Baseline to Another Baseline or Pair of Snapshots”
Comparing Two Pairs of Snapshots
“Comparing Two Pairs of Snapshots”
Using the AWR Compare Periods Reports
“Using the AWR Compare Periods Reports”
Summary of the AWR Compare Periods Report
“Summary of the AWR Compare Periods Report”
Snapshot Sets
“Snapshot Sets”
Load Profile
“Load Profile”
Top Timed Events
“Top Timed Events”
Host Configuration Comparison
“Host Configuration Comparison”
System Configuration Comparison
“System Configuration Comparison”
Details of the AWR Compare Periods Report
“Details of the AWR Compare Periods Report”
Supplemental Information in the AWR Compare Periods Report
“Supplemental Information in the AWR Compare Periods Report”
Identifying High-Load SQL Statements
“Identifying High-Load SQL Statements”
Identification of High-Load SQL Statements Using ADDM Findings
“Identification of High-Load SQL Statements Using ADDM Findings”
Identifying High-Load SQL Statements Using Top SQL
“Identifying High-Load SQL Statements Using Top SQL”
Viewing SQL Statements by Wait Class
“Viewing SQL Statements by Wait Class”
Viewing Details of SQL Statements
“Viewing Details of SQL Statements”
Viewing SQL Statistics
“Viewing SQL Statistics”
SQL Statistics Summary
“SQL Statistics Summary”
General SQL Statistics
“General SQL Statistics”
Activity by Wait Statistics and Activity by Time Statistics
“Activity by Wait Statistics and Activity by Time Statistics”
Elapsed Time Breakdown Statistics
“Elapsed Time Breakdown Statistics”
Shared Cursors Statistics and Execution Statistics
“Shared Cursors Statistics and Execution Statistics”
Other SQL Statistics
“Other SQL Statistics”
Viewing Session Activity
“Viewing Session Activity”
Viewing the SQL Execution Plan
“Viewing the SQL Execution Plan”
Viewing the Plan Control
“Viewing the Plan Control”
Viewing the Tuning History
“Viewing the Tuning History”
Tuning SQL Statements
“Tuning SQL Statements”
Tuning SQL Statements Using SQL Tuning Advisor
“Tuning SQL Statements Using SQL Tuning Advisor”
Tuning SQL Manually Using SQL Tuning Advisor
“Tuning SQL Manually Using SQL Tuning Advisor”
Viewing Automatic SQL Tuning Results
“Viewing Automatic SQL Tuning Results”
Managing SQL Tuning Sets
“Managing SQL Tuning Sets”
Creating a SQL Tuning Set
“Creating a SQL Tuning Set”
Creating a SQL Tuning Set: Options
“Creating a SQL Tuning Set: Options”
Creating a SQL Tuning Set: Load Method
“Creating a SQL Tuning Set: Load Method”
Loading Active SQL Statements Incrementally from the Cursor Cache
“Loading Active SQL Statements Incrementally from the Cursor Cache”
Loading SQL Statements from the Cursor Cache
“Loading SQL Statements from the Cursor Cache”
Loading SQL Statements from AWR Snapshots
“Loading SQL Statements from AWR Snapshots”
Loading SQL Statements from AWR Baselines
“Loading SQL Statements from AWR Baselines”
Loading SQL Statements from a User-Defined Workload
“Loading SQL Statements from a User-Defined Workload”
Creating a SQL Tuning Set: Filter Options
“Creating a SQL Tuning Set: Filter Options”
Creating a SQL Tuning Set: Schedule
“Creating a SQL Tuning Set: Schedule”
Dropping a SQL Tuning Set
“Dropping a SQL Tuning Set”
Transporting SQL Tuning Sets
“Transporting SQL Tuning Sets”
Exporting a SQL Tuning Set
“Exporting a SQL Tuning Set”
Importing a SQL Tuning Set
“Importing a SQL Tuning Set”
Managing SQL Profiles
“Managing SQL Profiles”
Managing SQL Execution Plans
“Managing SQL Execution Plans”
Optimizing Data Access Paths
“Optimizing Data Access Paths”
Running SQL Access Advisor
“Running SQL Access Advisor”
Running SQL Access Advisor: Initial Options
“Running SQL Access Advisor: Initial Options”
Running SQL Access Advisor: Workload Source
“Running SQL Access Advisor: Workload Source”
Using SQL Statements from the Cache
“Using SQL Statements from the Cache”
Using an Existing SQL Tuning Set
“Using an Existing SQL Tuning Set”
Using a Hypothetical Workload
“Using a Hypothetical Workload”
Running SQL Access Advisor: Filter Options
“Running SQL Access Advisor: Filter Options”
Defining Filters for Resource Consumption
“Defining Filters for Resource Consumption”
Defining Filters for Users
“Defining Filters for Users”
Defining Filters for Tables
“Defining Filters for Tables”
Defining Filters for SQL Text
“Defining Filters for SQL Text”
Defining Filters for Modules
“Defining Filters for Modules”
Defining Filters for Actions
“Defining Filters for Actions”
Running SQL Access Advisor: Recommendation Options
“Running SQL Access Advisor: Recommendation Options”
Running SQL Access Advisor: Schedule
“Running SQL Access Advisor: Schedule”
Reviewing the SQL Access Advisor Recommendations
“Reviewing the SQL Access Advisor Recommendations”
Reviewing the SQL Access Advisor Recommendations: Summary
“Reviewing the SQL Access Advisor Recommendations: Summary”
Reviewing the SQL Access Advisor Recommendations: Recommendations
“Reviewing the SQL Access Advisor Recommendations: Recommendations”
Reviewing the SQL Access Advisor Recommendations: SQL Statements
“Reviewing the SQL Access Advisor Recommendations: SQL Statements”
Reviewing the SQL Access Advisor Recommendations: Details
“Reviewing the SQL Access Advisor Recommendations: Details”
Implementing the SQL Access Advisor Recommendations
“Implementing the SQL Access Advisor Recommendations”