• • Introduction to SQL performance and tuning
• • Performance issues
• • Simple example
• • Visualizing the problem
• • Summary Performance analysis tools
• • Components of response time
• • Time estimates with VQUBE3
• • SQL EXPLAIN
• • The accounting trace
• • The bubble chart
• • Performance thresholds Index basics
• • Indexes
• • Index structure
• • Estimating index I/Os
• • Clustering index
• • Index page splits Access paths
• • Classification
• • Matching versus Screening
• • Variations
• • Hash access
• • Prefetch
• • Caveat More on indexes
• • Include index
• • Index on expression
• • Random index
• • Partitioned and partitioning, NPSI and DPSI
• • Page range screening
• • Features and limitations Tuning methodology and index cost
• • Methodology
• • Index cost: Disk space
• • Index cost: Maintenance
• • Utilities and indexes
• • Modifying and creating indexes
• • Avoiding sorts Index design
• • Approach
• • Designing indexes Advanced access paths
• • Prefetch
• • List prefetch
• • Multiple index access
• • Runtime adaptive index Multiple table access
• • Join methods
• • Join types
• • Designing indexes for joins
• • Predicting table order Subqueries
• • Correlated subqueries
• • Non-correlated subqueries
• • ORDER BY and FETCH FIRST with subqueries
• • Global query optimization
• • Virtual tables
• • Explain for subqueries Set operations (optional)
• • UNION, EXCEPT, and INTERSECT
• • Rules
• • More about the set operators
• • UNION ALL performance improvements Table design (optional)
• • Number of tables
• • Clustering sequence Denormalization
• • Materialized query tables (MQTs)
• • Temporal tables
• • Archive enabled tables Working with the optimizer
• • Indexable versus non-indexable predicates
• • Boolean versus non-Boolean predicates
• • Stage 1 versus stage 2
• • Filter factors
• • Helping the optimizer
• • Pagination Locking issues
• • The ACID test
• • Reasons for serialization
• • Serialization mechanisms
• • Transaction locking
• • Lock promotion, escalation, and avoidance More locking issues (optional)
• • Skip locked data
• • Currently committed data
• • Optimistic locking
• • Hot spots
• • Application design
• • Analyzing lock waits Massive batch (optional)
• • Batch performance issues
• • Buffer pool operations
• • Improving performance
• • Benefit analysis
• • Massive deletes