CV964G Db2 12 for z/OS SQL Performance and Tuning
Duração: 3 Dias
Nível: Intermédio
Audiência: Systems Administrator
PRÓXIMAS SESSÕES
| Início (AAAA-MM-DD) | Fim (AAAA-MM-DD) | Língua | Preço | |
|---|---|---|---|---|
| 2023-11-06 | 2023-11-08 | Português | 2175 EUR | |
| 2023-11-20 | 2023-11-22 | Português | 2175 EUR | |
| 2023-12-04 | 2023-12-06 | Português | 2175 EUR | |
| 2023-12-18 | 2023-12-20 | Português | 2175 EUR | |
| 2024-01-15 | 2024-01-17 | Português | 2175 EUR | |
| 2024-01-29 | 2024-01-31 | Português | 2175 EUR | |
| 2024-02-12 | 2024-02-14 | Português | 2175 EUR | |
| 2024-02-26 | 2024-02-28 | Português | 2175 EUR | |
| 2024-03-11 | 2024-03-13 | Português | 2175 EUR | |
| 2024-03-25 | 2024-03-27 | Português | 2175 EUR | |
| 2024-04-08 | 2024-04-10 | Português | 2175 EUR | |
| 2024-04-22 | 2024-04-24 | Português | 2175 EUR | |
| 2024-05-06 | 2024-05-08 | Português | 2175 EUR | |
| 2024-05-20 | 2024-05-22 | Português | 2175 EUR | |
| 2024-06-03 | 2024-06-05 | Português | 2175 EUR | |
| 2024-06-17 | 2024-06-19 | Português | 2175 EUR |
SÍNTESE
This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.
PREREQUISITOS
- Familiarity with SQL
- Familiarity with Db2 12 for z/OS
- Familiarity with Db2 12 for z/OS application programming
After completing this course, students will be able to:
- Understand and design better indexes
- Determine how to work with the optimizer (avoid pitfalls, provide guidence)
- Optimize multi-table access
- Work with subqueries
- Avoid locking problems
- Use accounting traces and other tools to locate performance problems in existing SQL
- and more
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