Oracle Database 12c: Performance Management and Tuning

Prerequisites

• Oracle Database 12c: Install and Upgrade Workshop Ed 1

Audience

• Administrator
• Database Administrator

Course Topics

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

21 – Oracle Database Cloud Service: Overview

• Database as a Service Architecture, Features and Tooling
• Software Editions: Included Database Options and Management Packs
• Accessing the Oracle Database Cloud Service Console Automated Database Provisioning
• Managing the Compute Node Associated With a Database Deployment
• Managing Network Access to Database as a Service Scaling a Database Deployment
• Performance Management in the Database Cloud Environment
• Performance Monitoring and Tuning
• What Can be Tuned in a DBCS Environment?