Db2 12 for z/OS SQL Performance and Tuning (CV964G) – Outline

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