Detailed Course Outline
Introduction
- Course Objectives
- Course Organization
- Course Agenda
- Topics Not Included in the Course
- Who Tunes?
- What Does the DBA Tune?
- How to Tune
- Tuning Methodology
Basic Tuning Diagnostics
- Performance Tuning Diagnostics
- Performance Tuning Tools
- Tuning Objectives
- Top Timed Events
- DB Time
- CPU and Wait Time Tuning Dimensions
- Time Model
- Dynamic Performance Views
Using Automatic Workload Repository
- Automatic Workload Repository Overview
- Automatic Workload Repository Data
- Enterprise Manager Cloud Control and AWR
- Snapshots
- Reports
- Compare Periods
Defining the Scope of Performance Issues
- Defining the Problem
- Limiting the Scope
- Setting the Priority
- Top SQL Reports
- Common Tuning Problems
- Tuning During the Life Cycle
- ADDM Tuning Session
- Performance Versus Business Requirements
Using Metrics and Alerts
- Metrics and Alerts Overview
- Limitation of Base Statistics
- Benefits of Metrics
- Viewing Metric History Information
- Viewing Histograms
- Server-Generated Alerts
- Setting Thresholds
- Metrics and Alerts Views
Using Baselines
- Comparative Performance Analysis with AWR Baselines
- Automatic Workload Repository Baselines
- Moving Window Baseline
- Baselines in Performance Page Settings
- Baseline Templates
- AWR Baseslines
- Creating AWR Baselines
- Managing Baselines with PL/SQL
Using AWR-Based Tools
- Automatic Maintenance Tasks
- ADDM Performance Monitoring
- Using Compare Periods ADDM
- Active Session History
- New or Enhanced Automatic Workload Repository Views
- Emergency Monitoring
- Real-time ADDM
Real-Time Database Operation Monitoring
- Overview
- Use Cases
- Defining a Database Operation
- Scope of a Composite Database Operation
- Database Operation Concepts
- Identifying a Database Operation
- Enabling Monitoring of Database Operations
- Identifying, Starting, and Completing a Database Operation
Monitoring Applications
- What is a Service?
- Service Attributes
- Service Types
- Creating Services
- Managing Services in a Single-Instance Environment
- Where are Services Used?
- Using Services with Client Applications
- Services and Pluggable Databases
Identifying Problem SQL Statements
- SQL Statement Processing Phases
- Role of the Oracle Optimizer
- Identifying Bad SQL
- Top SQL Reports
- SQL Monitoring
- What is an Execution Plan?
- Methods for Viewing Execution Plans
- Uses of Execution Plans
Influencing the Optimizer
- Functions of the Query Optimizer
- Selectivity
- Cardinality and Cost
- Changing Optimizer Behavior
- Optimizer Statistics
- Extended Statistics
- Controlling the Behavior of the Optimizer with Parameters
- Enabling Query Optimizer Features
Reducing the Cost of SQL Operations
- Reducing the Cost
- Index Maintenance
- SQL Access Advisor
- Table Maintenance for Performance
- Table Reorganization Methods
- Space Management
- Extent Management
- Data Storage
Using SQL Performance Analyzer
- Real Application Testing: Overview
- Real Application Testing: Use Cases
- SQL Performance Analyzer: Process
- Capturing the SQL Workload
- Creating a SQL Performance Analyzer Task
- SQL Performance Analyzer: Tasks
- Parameter Change
- SQL Performance Analyzer Task Page
SQL Performance Management
- Maintaining SQL Performance
- Maintaining Optimizer Statistics
- Automated Maintenance Tasks
- Statistic Gathering Options
- Setting Statistic Preferences
- Restore Statistics
- Deferred Statistics Publishing
- Automatic SQL Tuning
Using Database Replay
- Using Database Replay
- The Big Picture
- System Architecture
- Capture Considerations
- Replay Considerations: Preparation
- Replay Considerations
- Replay Options
- Replay Analysis
Tuning the Shared Pool
- Shared Pool Architecture
- Shared Pool Operation
- The Library Cache
- Latch and Mutex
- Diagnostic Tools for Tuning the Shared Pool
- Avoiding Hard Parses
- Reducing the Cost of Soft Parses
- Sizing the Shared Pool
Tuning the Buffer Cache
- Oracle Database Architecture: Buffer Cache
- Buffer Cache: Highlights
- Database Buffers
- Buffer Hash Table for Lookups
- Working Sets
- Buffer Cache Tuning Goals and Techniques
- Buffer Cache Performance Symptoms
- Buffer Cache Performance Solutions
Tuning PGA and Temporary Space
- SQL Memory Usage
- Performance Impact
- Automatic PGA Memory
- SQL Memory Manager
- Configuring Automatic PGA Memory
- Setting PGA_AGGREGATE_TARGET Initially
- Limiting the size of the Program Global Area (PGA)
- SQL Memory Usage
Automatic Memory
- Oracle Database Architecture
- Dynamic SGA
- Granule
- Memory Advisories
- Manually Adding Granules to Components
- Increasing the Size of an SGA Component
- Automatic Shared Memory Management: Overview
- SGA Sizing Parameters: Overview
Performance Tuning Summary with Waits
- Commonly Observed Wait Events
- Additional Statistics
- Top 10 Mistakes Found in Customer Systems
- Symptoms