These courses are being delivered by an IBM Global Training Provider
This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.
After completing this course, students will be able to:
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
Join our public courses in our New Zealand facilities. Private class trainings will be organized at the location of your preference, according to your schedule.