SQL Tuning Introduction
  • Why SQL Tuning?
  • Prerequisites for SQL Tuning
  • SQL Processing
  • SOFT parse vs HARD parse
  • Parsing
Optimizer and its Execution Plan
  • Cost based Optimization
  • Gathering Statistics
  • Execution Plan
  • SQL Tuning Tools
  • Running Explain Plan
  • Optimizer statistics
Accessing Tables
  • What is my Address?
  • Types of Table Accesses
  • Table Access FULL
  • Table Access by ROWID
  • Index Unique Scan
  • Index Range Scan
  • Choosing between FULL and INDEX scan
  • Access Paths
Explain Plan
  • Execution Plan
  • What should you look for?
  • What is COST?
  • Rules of Execution Plan Tree
  • Traversing through the Tree
  • Reading Execution Plan
  • Execution Plan Example #1
  • Execution Plan Example #2
  • Execution Plan Example #3
  • Execution Plan Example #4
Simple Tuning Rules
  • SELECT consideration
  • Using Table Aliases
  • Using WHERE rather than HAVING
  • Simple Rules
Index Suppression
  • Index Suppression reasons
  • Use of <> operator
  • Use of SUBSTR function
  • Use of Arithmetic operators
  • Use of TRUNC function on Date columns
  • Use of || operator
  • Comparing a character column to a numeric value
  • Use of IS NULL and IS NOT NULL
  • Function based Indexes
  • Index Suppression SQL
Performance Improvement Considerations
  • Use UNION ALL instead of UNION
  • Minimize Table lookups in a Query
  • EXISTS vs IN
  • Use EXISTS instead of DISTINCT
  • Reading same table multiple times?
  • Use TRUNCATE instead of DELETE
Considerations while using SQL in programming
  • Reduce the number of Trips to the database
  • Issue frequent COMMIT statements
  • Using BULK COLLECT
Join Methods
  • Join Methods
  • Nested Loop Join
  • Hash Join
  • Sort Merge Join
HINTS to the database
  • Why HINTS?
  • Forcing a specific Join Method
  • HINTS list
House Keeping
  • Invalid Optimizer Statistics
  • Checking SQL statements which are performing BAD
Design Considerations
  • Effective Schema Design
  • Separate Tablespace for Data and Index
  • Index Organized Tables
  • Partitioned Tables
  • Bitmap Indexes