Detailed Course Outline
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
- Tandom 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