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
