Oracle DB Performance Tuning 19c

Course Duration: 30 Hours

Required Prerequisites

Target Audience

  • Oracle DBA who to learn Oracle Database Performance Tuning in detail

Course Highlights

  • Oracle Database Architecture
  • Performance Tuning Tools
  • Oracle Optimizer
  • SQL Tuning with Activities
  • Data Loading and Monitoring
  • AWR,ADDM,ASH Report Analysis
  • Database Storage Objects
  • Advisories
  • Instance / memory tuning
  • Best Architectural Practices

Course Content

Section 1: Oracle Database Architecture

Memory Architecture

Physical Architecture

Logical Architecture

Installation of Oracle Database 19c on OEL

Section 2: Introduction to Oracle Performance Tuning

Oracle Database Performance Tuning Areas

Performance Tuning Methodology

Section 3: Oracle Performance Tuning Tool

Oracle Database Performance Tuning Tools

Types of Performance Tuning Statistics

Statistics Level

Section 4: Time Model Statistics

About Time Model Statistics

Time Model Views and their benefits

Time Model – Practice session

Section 5: Instance Activity

About Instance Activity

Instance Activity Views

System Statistics Classes

Instance Activity – Practice Session

Section 6: Wait Events

About Wait Events

Wait Events Classes

Obtain Information about the wait events from the wait event V$ views

Common Wait Events

Handling Wait Events

Wait Event – Practice Session

Section 7: Automatic Workload Repository – AWR

About AWR 

AWR Snapshots

AWR Reports and Comparison Report

Managing AWR Snapshots

Managing AWR – Practice Session

Generate AWR Report

Producing AWR SQL Report

Reading AWR Report

AWR Report Summary Section

AWR – SQL Statistics

Generating AWR Comparison Report

Reading AWR Comparison Report

Managing AWR Baselines

Managing AWR Baselines Templates

Section 8 : ADDM

About Automatic Database Diagnostic Monitor – ADDM

ADDM Comparison Report

ADDM – Practice Session 

Section 9 : ASH Report

About Active Session History – ASH

Querying ASH 

Active Session History – Practice Session

Section 10 : Latches and Mutexes

Handling Enqueue Waits

About Latches and Mutex

Latches Mechanism

Mutex Mechanism

Handling Latches and Mutex Contention

Latches in AWR Reports

Latches Wait Events

Mutex Wait Events

Cache Buffer Chain Latch

Common Latches

Section 11: Shared Pool and Buffer Cache Tuning

About Shared Pool

Shared Pool Common Performance Issue

Shared Pool Issue in AWR Report

High Hard Parses

Avoiding Hard Parses

High Object Reloading

Large Object in Shared Pool

Sizing Shared Pool

Shared Pool Advisory

Shared Pool Tuning – Practice Session

About Oracle Database Buffer Cache

Buffer Cache Performance Issue

Sizing Buffer Cache

Buffer Pool Advisory

Caching Tables


Multiple DBW Processes

Section 12: Tuning PGA

Program Global Area – PGA

PGA Configuration

Sizing PGA Guidelines

PGA Operations – V$ Views

PGA Wait Events

PGA Stats

PGA Performance Issue

PGA Advisory

PGA Tuning – Practice Session

Section 13: CPU Bottlenecks

CPU Time in AWR Report

Total CPU Consumed 

CPU Time taken by the database

Tuning CPU Events

CPU Bottlenecks – Practice Session

Section 14: Tuning Disk IO Operation

I/O Performance Tuning Hierarchy

I/O Issue Example

Find current I/O Events

I/O Stats in AWR Reports

Handling I/O Bottlenecks

I/O Mode

I/O Calibration from Oracle Database

Practice Session