Database Tuning overview – Access Path Tuning

Part of the Performance Tuning series – Performance Stack , this section looks at Access  Path Tuning.  How  does the user access the data and how can the access  be maintained. The purpose of the series is a general approach to Performance Tuning – independent of database server platform.   A large proportion of DBAs support more than one type of database server and a methodology allows them to apply to all platforms.

Access Path Tuning is an important factor in database performance.   Access Path is the operational part of the Database system. Think of concurrency, isolation, index access, data sets heavily used…

What options exist for the DB2 DBA to tune Access path ?

The main focus on this section is about improving query response time. Create a query response benchmark of every database server.  This assists greatly in defining the requirements.  

Partitioning

1)  Table partitioning – Which tables should be partitioned ? Think of logical access

2)  Create an access profile of the database server. An access profile defines the type of access activity , such as Read-Only data , data updated overnight by an ETL.  

Match the appropriate logic access to the partitions created in the Storage Tuning section.

3)  Analysis is required to decide which tables to partition.  Decide which tables to partition after analysis of logical partitioning. For example, if a 100 million row table exists , 50 % of the data won’t be updated again, and there some logical way of partitioning –  this is a candidate for table partitioning

 Index tuning

1)  In the Storage Tuning section the focus was on designing data structures for faster access for specific data. Monitor which indexes are used? How often are they used?  Maintaining  an index requires careful access analysis.

2)  Which DB2 indexes should the DBA maintain? What index should be maintained and how should they be maintained?

 Materialized Query Tables (MQT)

 1)     Is there much Aggregated Data ?

2)      List Aggregated data over multiple tables.

3)      List queries using multiple JOINS over multiple tables

4)      Is there a data set – from a partition – which is heavily used? Such as the last months sales data?

 Related Posts

 Performance Tuning series – Performance Stack

Database Tuning  overview – Hardware Tuning

OLTP checklist for DB2 configurations.

Database tuning and Operating System Tuning