(Online Delivery) Oracle SQL Tuning Workshop
Description
Oracle Database 19c – SQL Tuning Workshop (4-Day Agenda) :
🔹 Day 1: SQL Tuning Concepts & Tools
- Course Introduction & Objectives
- SQL Tuning Overview
- Identifying Problematic SQL
- Performance Issues
- Optimizer Basics
- AUTOTRACE & EXPLAIN PLAN usage
- Application Tracing
- Understanding Execution Plans
- Hands-on Lab 1: Identifying and tracing slow SQL
🔹 Day 2: Optimizer & Execution Plans
- Join Methods and Access Paths
(Nested Loops, Hash Joins, Merge Joins) - Index Usage & Access Paths
(Index Range Scan, Full Scan, Skip Scan, etc.) - Optimizer Statistics
(Table, Column, Index Stats – Gathering Stats) - Histograms: Frequency, Top-N, Hybrid
- Hands-on Lab 2: Join analysis, table access paths, and index tuning
🔹 Day 3: Bind Variables & Plan Control
- Bind Variables & Cursors
- Adaptive Query Optimization
- SQL Plan Directives & Real-Time Statistics
- SQL Plan Baselines & Plan Management
(Capture, Load, Fix Plans) - Hands-on Lab 3: Bind variables, adaptive plans, plan baselines
🔹 Day 4: Advanced Tuning Strategies
- Automatic Workload Repository (AWR) & ADDM
- SQL Tuning Advisor & SQL Access Advisor
(Using DBMS_SQLTUNE) - Tuning in SQL Developer
- Case Study & Hands-on Lab 4: Tuning a real-world SQL workload
- Q&A, Summary, and Wrap-up