Oracle Database 12c: Performance Management and Tuning Training

• Understand the concept of tuning, Performance Tuning Diagnostics, and Performance Tuning Tools and related terms
• Use Enterprise Manager, a tool based on the Automatic Workload Repository to tune the databases
• Use metrics, alerts, and baselines to monitor the performance
• Define use-cases and work on Real-time database operation monitoring
• Control the behavior of the Query Optimizer with parameters
• Tune instance components

• Database Administrators
• Data Warehouse Administrators

• Oracle Database 12c: Install and Upgrade Workshop

1. 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

2. 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

3. Using Automatic Workload Repository

• Automatic Workload Repository Overview
• Automatic Workload Repository Data
• Enterprise Manager Cloud Control and AWR
• Snapshots
• Reports
• Compare Periods

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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

12. 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

13. 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

14. 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

15. Using Database Replay

• Using Database Replay
• The Big Picture
• System Architecture
• Capture Considerations
• Replay Considerations: Preparation
• Replay Considerations
• Replay Options
• Replay Analysis

16. 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

17. 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

18. 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

19. 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

20. Performance Tuning Summary with Waits

• Commonly Observed Wait Events
• Additional Statistics
• Top 10 Mistakes Found in Customer Systems
• Symptoms