Contenuti dettagliati del Corso
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