Identifying and Resolving SAP/DB2 Access Path Problems

A general rule of thumb for good SAP Performance is that the application should make use of table indices, wherever available (‘SQL/Index Agreement’), in order to ensure the most efficient access to the database. Despite the apparent simplicity of ABAP code being converted to SQL which executes in the database, DB2’s Cost Based Optimizer (CBO) actually considers a number of factors in determining exactly how it will access the database.

About The Cost Based Optimizer

• The CBO considers the SQL that is passed to DB2 from the application, as well as statistics about the table and its indices from the DB2 Catalog
• DB2 Catalog statistics are generated by the DB2 ‘runstats’ utility, which populates the catalog with values for cluster-ratio, cardinality, etc.
• Depending upon the catalog statistics, and the internal algorithms of the DB2 Optimizer, DB2 may choose an access path which differs from ‘SQL/Index Agreement’

SQL/Where-Clause Agreement

What Are The Impacts Of Access Path Problems?

Many times they are characterized by a database read ‘jumping the tracks’ from an indexed to a sequential read, which can manifest itself as an order of magnitude increase in run time

Degradation is at the SQL level – relative infrequency of an SQL statement within a program can mitigate or mask negative effect on overall transaction

On Update statements, degraded access path leads to longer held locks, table contention, and an increase in deadlocks and timeouts when executed in parallel with other statements requiring the same table

How Can We Detect Access Path Problems?

Customer complaint of drastic performance degradation for a specific program or transaction

Trace of long-running SQL during system monitoring (SM66, ST05)

Analysis of most resource-intensive SQL statements in cursor cache (ST04 Transaction for DB2 z/OS systems)

Following any of the above, you determine:

Explain of SQL statement yields an access path which does not match the index corresponding to the ‘where clause’, AND Performance of SQL statement is worse than previous benchmark

Verification Of Access Path Problems

Trace and ‘explain’ long-running SQL

Use ST05 to trace specific user id, program or process (and drill-down and explain the suspect SQL), OR

Use ST04>>Thread Activity, in order to view SQL and explain for a particular process ID (PID)

Check the ‘access path’ from explain for agreement with fields of ‘where clause’

Access path should show sequential read, indexed read, etc

If access path and where clause are not in agreement, check for other available indices in DB02 to see if there is a better match for the where clause

If there is a better match for the where clause, compare the statistics in DB02 for both indices (actual access path and expected access path, to try and determine the delta)

NOTE: In some cases, notably for ‘small’ tables, the DB2 Optimizer will opt for a sequential read or full table scan even when a full index key is provided. This is because for such tables, it is actually more efficient for DB2 to scan the entire table/ tablespace than to use the index.  (This is a potential pitfall, however, if you have a very small table grow to be very big over a short period of time, with no execution of Runstats to ‘inform’ DB2 of the change —  this will likely result in bad response time)

Root Causes Of Access Path Problems

Bad/Inefficient SQL

Code only makes use of partial keys for available indexes, lessening the likelihood that the CBO will choose the desired access path

Use of database joins/views, particularly for large and/or volatile tables (if tables, for instance, grow to require reorg, joins can ‘break’, causing the SQL to scan the product of the tables, leading to ugly and exponential increases)

Poor index design

Key fields are duplicated among several indexes, giving them similar statistics and characteristics, and creating opportunity for ‘index competition’

The key fields chosen do not provide sufficient selectivity to make the index a good choice

Need for maintenance (reorgs, runstats) of tables and/or indices

Data Skew — SQL is written to match key, however, due to the nature of the data in the table, the index is not selective

Coding alone cannot resolve this problem. This potentially requires DB2 Hints, changes to index and/or changes to statistics

‘Fixing’ Access Path Problems

When ‘fixing’ these problems, we tend to go down a path from least to most invasive change.

These items assume that access path problem has been positively identified; verification is required after each of the following actions:

Have DBA execute runstats for the suspect index or table

Reorg the table and/or index if flagged by system, or if there has been significant and uncharacteristic activity on the table (excessive growth, mass deletions, data conversions, etc)

Analyze indexes to determine overlap/competition, dropping unneeded ones

Make changes to ABAP code, if possible/necessary:

Provide additional or all keys for desired index and ensure order of keys matches desired index

‘Code-around’ to avoid select, if it isn’t critical

‘Fake-out’ Optimizer, through manual adjustment of certain catalog stats

Scan table for presence of data skew

Implement DB2 Hints in ABAP code to bias optimizer toward desired index for SQL