Oracle Database 12c Performance Management and Tuning

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, Features, and Tools
• DB Time
• CPU and Wait Time Tuning Dimensions
• Time Model
• Dynamic Performance Views
• Statistics
• Wait Events
• Log Files and Trace Files

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 and Limiting the Scope
• Setting the Priority
• Top SQL Reports
• Common Tuning Problems
• Tuning During the Life Cycle
• ADDM Tuning Session
• Performance Tuning Resource
• Monitoring and Tuning Tools Overview

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
• Moving Window Baseline
• Baseline Templates
• Creating AWR Baselines
• Baselines Views
• Performance Monitoring and Baselines
• Defining Alert Thresholds Using a Static Baseline
• Configuring Adaptive Thresholds

Using AWR-Based Tools

• Automatic Maintenance Tasks
• ADDM Performance Monitoring
• Active Session History
• Additional Automatic Workload Repository Views
• Real-time ADDM

Real-Time Database Operation Monitoring

• Overview and Use Cases
• Defining a Database Operation
• Database Operation Concepts
• Enabling Monitoring of Database Operations
• Identifying, Starting, and Completing a Database Operation
• Monitoring the Progress of a Database Operation
• Database Operation Views
• Database Operation Tuning

Monitoring Applications

• Service Attributes and Types
• Creating Services
• Using Services with Client Applications
• Using Services with the Resource Manager
• Services and Oracle Scheduler
• Services and Metric Thresholds
• Service Aggregation and Tracing
• Top Services Performance Page

Identifying Problem SQL Statements

• SQL Statement Processing Phases
• SQL Monitoring
• Execution Plans
• DBMS_XPLAN Package
• EXPLAIN PLAN Command
• Reading an Execution Plan
• Using the SQL Trace Facility
• Generating an Optimizer Trace

Influencing the Optimizer

• Functions of the Query Optimizer
• Optimizer Statistics
• Controlling the Behavior of the Optimizer by Using Parameters
• Enabling Query Optimizer Features
• Using Hints
• Access Paths
• Join Operations
• Sort Operations

Reducing the Cost of SQL Operations

• Index Maintenance
• SQL Access Advisor
• Table Maintenance and Reorganization
• Extent Management
• Data Storage
• Migration and Chaining
• Shrinking Segments
• Table Compression

Using SQL Performance Analyzer

• SQL Performance Analyzer Overview
• Real Application Testing Overview and Use Cases
• Capturing the SQL Workload
• Creating a SQL Performance Analyzer Task
• Comparison Reports
• Tuning Regressing Statements
• Guided Workflow Analysis
• SQL Performance Analyzer Views

SQL Performance Management

• Maintaining Optimizer Statistics
• Automated Maintenance Tasks
• Statistics Gathering Options and Preferences
• Deferred Statistics Publishing
• Automatic SQL Tuning
• SQL Tuning Advisor
• SQL Access Advisor
• SQL Plan Management

Using Database Replay

• Database Replay Architecture
• Capture Considerations
• Replay Options
• Replay Analysis
• Database Replay Workflow in Enterprise Manager
• Database Replay Packages and Procedures
• Database Replay Views
• Calibrating Replay Clients

Tuning the Shared Pool

• Shared Pool Architecture
• Latch and Mutex
• Diagnostic Tools for Tuning the Shared Pool
• Avoiding Hard Parses
• Sizing the Shared Pool
• Avoiding Fragmentation
• Data Dictionary Cache
• SQL Query Result Cache

Tuning the Buffer Cache

• Database Buffer Cache Architecture
• Working Sets
• Buffer Cache Tuning Goals and Techniques
• Buffer Cache Performance Symptoms
• Buffer Cache Performance Solutions
• Database Smart Flash Cache
• Flushing the Buffer Cache

Tuning PGA and Temporary Space

• SQL Memory Usage
• Configuring Automatic PGA Memory
• PGA Target Advice Statistics and Histograms
• Automatic PGA and AWR Reports
• Temporary Tablespace Management
• Temporary Tablespace Group
• Monitoring Temporary Tablespaces
• Temporary Tablespace Shrink

Automatic Memory

• Dynamic SGA
• Automatic Shared Memory Management Overview
• SGA Sizing Parameters
• Enabling and Disabling Automatic Shared Memory Management
• SGA Advisor
• Automatic Memory Management Overview
• Enabling Automatic Memory Management
• Monitoring Automatic Memory Management

Tuning I/O

• I/O Architecture
• I/O Modes
• Important I/O Metrics for Oracle Databases
• I/O Calibration
• I/O Statistics
• I/O Diagnostics
• Database I/O Tuning
• Automatic Storage Management (ASM)

Performance Tuning Summary

• Initialization Parameters and their Impact on Performance
• Initial Memory Sizing
• Tuning the Large Pool
• Best Practices for Different Types of Tablespaces
• Block Sizes
• Sizing the Redo Log Buffer and Redo Log Files
• Automatic Statistics Gathering
• Commonly Observed Wait Events