Oracle DB Performance Tuning 19c
Course Duration: 30 Hours
Required Prerequisites
- Good Knowledge of Oracle SQL and PL/SQL Programming
- Good Knowledge of Oracle DBA
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
KEEP and RECYCLE Pool
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