Taking Advantage of Relational Technology Part 3

Last time we talked about excess tablespace scans, list prefetch and index scans. Let’s look further into potential application performance issues.

Lack of Data Access Reviews

Having good application and SQL review processes in place for testing, and quality assurance are vital for overall guarantees of application performance. One of the best ways to facilitate these application and SQL reviews is by having all the SQL access plan information within the program compile and bind processes used within all environments.

By having these procedures in place a good history of access plans can be reviewed through the current Explain plan table information.  By reviewing the EXPLAIN table access paths a good profile of the application efficiency can be developed.  At a recent performance review for a government agency we discovered the following access path tuning opportunities in the production application:

  • Hybrid Joins – 3
  • Index Scans – 538
  • List Prefetch – 791
  • Table Scans – 2,721

All programs that are doing these types of access can be found through simple SQL queries against the Explain table. (If you would like the SQL please drop me a note and I will be happy to share.) Identifying the programs and plans executing these different access paths is only the first step toward improving the overall performance of your environment.

Large Number of Invalid Packages and Plans

Within the DB2 system the plans and packages are bound to the tables and other database objects.  When these database table or index objects change, they invalidate their dependent plans and packages.  Researching within the system found 2,136 invalid packages and 6 invalid plans.

These invalid packages and plans indicate that there are many instances where a rebind is not performed after a database object is changed.  This leads to excess plans and packages in the system, slowing response time as DB2 searches a large list to find the appropriate plan or package. It also means that a program which has not been rebound after the object change will need to do a rebind “on the fly,” resulting in slower response time when the affected program is executed.

Also since the rebind will be done when the program is executed a new unknown application access path will be executed against the database with unknown performance, locking and response time implications.

Compression Not Used in Any Tables

Compression offers tremendous savings potential by reducing the row size within DB2, sometimes saving up to 50 to 90% of the disk space used and accessing/caching more rows per physical I/O. By doing less I/O, CPU can be also be cut tremendously.

Implementing compression needs research to make sure that the compression overhead is minimized.  Since the compression cost ratio is 3 to 1 for compression (Insert or Update activity) versus decompression (Select activity), analysis of the I/O characteristics and workload needs to be done.

Analyzing a recent insurance company’s database environment, there were 142 tables holding over 1 terabyte of data that could be potentially compressed.  These 142 tables each had a row size of over 75 bytes and had over 500,000 rows.

Summary

Data performance reviews need to be exhaustive in order to achieve all the possible savings possible. Data access reviews are an important part of these reviews.  Also review:

  • Analyze the types of access paths that are active in your environment.
  • The number of invalid plans and packages
  • Use of table compression for large tables with frequent Select statements run against them.

____________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>