Oracle SQL Tuning 19c

Course Duration: 30 Hours

Required Prerequisites

  • Basic Knowledge of Oracle SQL
  • Basic Knowledge of Oracle database Architecture

Target Audience

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

Course Highlights

  • Oracle Database Architecture Overview
  • Introduction to SQL Tuning
  • Setting Up Oracle DB 19c For Practice
  • Query Optimizer
  • Execution Plans
  • Optimizer Statistics
  • Cursor Sharing
  • Monitoring and Tracing SQL
  • SQL Tuning Sets
  • SQL Tuning Advisor
  • SQL Plan Management
  • SQL Access Advisor
  • Using Indexes / Hints
  • Server Result Cache
  • More SQL Tuning Tools

Course Content

Section 1: 1. Oracle Database Architecture Overview

Memory Architecture

Physical Architecture

Logical Architecture

Section 2: Introduction to SQL Tuning
Section 3: Setting Up Oracle DB 19c For Practice
Section 4: Query Optimizer
Section 5: Execution Plans

How to read Query execution plan

Displaying Query execution plan

SQL Operators

SQL Joins – Nested Loop Joins

Hash Joins

Sort Merge Joins

Section 6: Optimizer Statistics

Optimizer Statistics Concept

Gathering Optimizer Statistics

Setting Optimizer statistics Preferences

Histogram

Extended Statistics

Managing Optimizer statistics

Managing Historical Optimizer Statistics

Optimizer Statistics Advisor

Section 7: Cursor Sharing

Improving Performance through Cursor Sharing

Section 8 : Monitoring and Tracing SQL

Monitoring Database Operations in Real-time using DBMS_MONITOR

Tracing SQL statements using DBMS_MONITOR

TKPROF Utility

More SQL Tracing Methods

Section 9 : SQL Tuning Sets

Managing SQL Tuning Sets ( STS )

Section 10 : SQL Tuning Advisor

Using SQL Tuning Advisor – Automatic Mode

Using SQL Tuning Advisor – Manual Mode

Managing SQL Profiles

Section 11: SQL Plan Management

SQL Plan Baselines

Using SQL Outlines and Migrating them to SQL Plan Baselines

SQL Management Base (SMB)

Section 12: SQL Access Advisor

Using SQL Access Advisor

Section 13: Using Indexes / Hints

Indexes

Start Transformation

Oracle Hints

What is Hint?

Influencing the Optimizer with Hints

Section 14: Server Result Cache
Section 15: More SQL Tuning Tools

SQL Performance Analyzer

SQL Tuning Health – Check scripts (SQLHC)