Db2 12 for z/OS SQL Performance and Tuning (CV964G)     » zur vollständigen Seminarliste

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