About this Course

In the Oracle Database 12c: Performance Management and Tuning course, learn about the performance analysis and tuning tasks expected of a DBA: proactive management through built-in performance analysis features and tools, diagnosis and tuning of the Oracle Database instance components, and diagnosis and tuning of SQL-related performance issues.

Audience Profile

• Data Warehouse Administrator
• Database Administrators

At Course Completion

Upon completing this course, students will be able to:

• Use the Oracle Database tuning methodology appropriate to the available tools
• Utilize database advisors to proactively tune an Oracle Database Instance
• Use the tools based on the Automatic Workload Repository to tune the database
• Diagnose and tune common SQL related performance problems
• Diagnose and tune common Instance related performance problems
• Use Enterprise Manager performance-related pages to monitor an Oracle Database

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

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