Database Tuning overview – Access Path Tuning

04 January,2012 by Jack Vamvas

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  

 

Author: Jack Vamvas(http://www.dba-db2.com)
Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Database Tuning overview – Access Path Tuning

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright