Oracle Database 19c – 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
• 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

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?