Description
This training provides an in-depth exploration of concepts and techniques for improving Oracle SQL performance. It covers SQL tuning principles, optimizer fundamentals, execution plan analysis, table and index access methods, join types, optimizer statistics, parameterized SQL usage, and SQL plan management. Participants will gain hands-on experience through practical exercises on real database scenarios.
Audience
- Database Administrators
- Software Developers
- Performance Analysts
- All professionals interested in Oracle SQL performance
Learning Outcomes
- Understand SQL tuning methodology
- Identify and analyze inefficient SQL queries
- Comprehend the core logic of the optimizer
- Read and interpret SQL execution plans
- Resolve performance issues using hints and plan management
Outline
Introduction to SQL Tuning
- What is SQL Tuning
- Recognize: What is Bad SQL
- Clarify: Understand the Current Issue
- Verify: Collect Data
- Verify: Is Bad SQL a Real Problem (Top-Down Analysis)
- Sanity Check
- Advanced SQL Tuning Analysis
- Parse Time Reduction
- Plan Comparison
- Query Analysis
Optimizer Fundamentals
- SQL Statement Processing
- Why Do You Need an Optimizer
- Query Transformer
- Estimator: Selectivity and Cardinality
- Plan Generator
- Adaptive Query Optimization
- Quarantined SQL Plans
- Controlling the Behavior of the Optimizer
Creating and Viewing SQL Plans
- What is an Execution Plan
- Reading an Execution Plan
- Reviewing an Execution Plan
- Viewing Execution Plans
- The EXPLAIN_PLAN Command
- PLAN_TABLE
- AUTOTRACE
- Using the V$SQL_PLAN View
- Automatic Workload Repository (AWR)
- SQL Monitoring
Table Partition Types and Operations
- List Partition
- Range Partition
- Hash Partition
- Composite Partition
- Interval Partition
- CREATE TABLE PARTITION BY
Understanding SQL Plans and Their Characteristics
- Execution Order of Plan Steps
- Adaptive Plans
- SQL Advisors
Table and Index Access Methods
- Full Table Scan
- ROWID Scan
- Sample Table Scan
- Index Scan (Unique)
- Index Scan (Range)
- Index Scan (Full)
- Index Scan (Fast Full)
- Index Scan (Skip)
- Index Scan (Index Join)
- Bitmap Index
- Bitmap Index Combine
Join Types
- Nested Loop Join
- Sort-Merge Join
- Hash Join
- Cartesian Join
- Equijoin / Natural / Nonequijoin
- Outer Join (Full, Left, Right)
- Semi Join (EXISTS)
- Anti Join (NOT IN)
Optimizer Statistics Concepts
- Table Statistics
- Index Statistics
- Column Statistics (Histogram)
- Column Statistics (Extended Statistics)
- Global Temporary Tables
- System Statistics
Parameterized SQL Usage and Benefits
- Cursor Sharing and Bind Variables
- Bind Variable Peeking
- Adaptive Cursor Sharing
Prerequisites
Basic knowledge of Database and SQL