Oracle Database 12c: Performance Management and Tuning (Duration: 5 Days)

What you will learn

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.

Learn To:

Use the Oracle tuning methodology.

Use Oracle-supplied tools for monitoring and diagnosing SQL and instance performance issues.

Use database advisors to proactively correct performance problems.

Identify and tune problem SQL statements.

Monitor instance performance by using Enterprise Manager.

Tune instance components.

Benefits To You:

The DBA will analyze the SQL performance with available tools. The DBA will be introduced to various methods of identifying the SQL statements that require tuning and the diagnostic tools used to find ways to improve performance.

This will include the use of statistics, profiles to influence the optimizer, and using the SQL Advisors.

Maintain SQL Performance

A major task of DBAs is to maintain SQL performance across changes. This course introduces Database Replay and SQL Performance Analyzer which help the DBA test and minimize the impact of change.

Influence Instance Behavior Instance tuning uses the same general method of observing a problem, diagnosing the problem, and implementing a solution. The instance tuning lessons cover the details of major tunable components and describe how you can influence the instance behavior. For each lesson, we will examine the relevant components of the architecture.

The course only discusses the architecture to the level required to understand the symptoms and solutions. More detailed explanations are left to other courses, reference material, and the Oracle document ation.

Audience

Data Warehouse Administrator

Database Administrators

Related Training

Suggested Prerequisites

Oracle Database 12c: Install and Upgrade Workshop

Course Objectives

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 Topics

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