Share on Facebook Share on Twitter Share on Digg Share on Stumble Upon Share via e-mail Print

Blinking Your Databases to Health

by Craig S. Mullins

Simplification is important in today’s era of increasing complexity and ever-changing software environments. A key component of simplification is to remember the basics and apply some simple rules and practices to your database environment. Many problems arise because we don’t keep track of the things most of us already know.

I stumbled upon the idea for this month’s column after recalling Malcolm Gladwell’s excellent business book Blink: The Power of Thinking Without Thinking (Little, Brown, ISBN: 0316172324). In this book, Gladwell offers up case studies and examples depicting the benefit of our "adaptive unconscious" – a powerful innate ability that provides us with instant and sophisticated information. Basically, what it boils down to is that our experience can drive us to make quick decisions that are usually correct.

So, how does this apply to database systems? Well, let’s examine some of the primary issues and concepts that need to be addressed in order to keep database applications humming along.

Updating Statistics

Simple negligence can be a cause of performance problems. Relational databases need to have accurate database object statistics in order to create efficient strategies for data access. Many inefficient database access paths are caused by bad or missing statistics. In other words, it is our fault.

To avoid such problems make sure you run the RUNSTATS or UPDATE STATISTICS operation on a regular basis. As the volume and nature of data in your databases changes, statistics on tables, table spaces, partitions, indexes, and columns must be collected or performance will suffer. The statistics are used by the optimizer to formulate efficient query access. Without up-to-date statistics, your database system is operating at a disadvantage.

Reorganizing When Necessary

Now that the statistics are correct you should be using them to schedule reorganizations of your database objects. Reorganization is required periodically to ensure that the data is stored in an optimal manner for subsequent access. Reorganization re-clusters data, resets free space, removes garbage, and potentially redefines underlying files.

Reorganizing indexes can be beneficial, too. When REORG is run on an index, the number of levels may decrease, page splits are removed, fragment space gets reclaimed, free space is reset, and index access performance can improve. The cost of reorganizing an index usually is significantly less when compared to the cost of reorganizing a table space.

Proper planning and scheduling of reorganization requires an examination of the statistics in the system catalog and an understanding of how the object is being used.

Coding Appropriately

Another pervasive problem permeating database applications and systems is the "flat file" development mentality. This occurs when programmers try to access databases the same way they access data from a flat file. SQL databases operate on data a set-at-a-time, instead of the record-at-a-time processing used against flat files. This requires that programmers change the way they think about accessing data.

SQL is designed so that programmers specify what data is needed but they cannot specify how to retrieve it. SQL is coded without embedded data-navigational instructions. The DBMS analyzes SQL and formulates data-navigational instructions "behind the scenes." This is foreign to the programmer who has never accessed data using SQL.

When accessing data, a programmer needs to think about what the end result should be and then code everything possible into the SQL. This means using the native features of SQL – joins and subselects and functions, etc. – instead of coding procedural logic and processing tables like files. Educating programmers how to use SQL properly is probably the single most important thing you can do to optimize performance of your DB2 applications.

Building the Correct Indexes

One final "simple thing" to consider is indexing. This is a job for the DBA and the proper way to do it is by workload, not by database object. Instead of simply creating a table and then creating an index or two, and repeating those steps over and over, focus on indexing based on actual usage.

Indexes should support the predicates in the SQL that is written to access your tables. Building indexes to support predicates of the most frequently executed queries and most important queries should be your first indexing step after building the unique indexes required to support primary keys and unique constraints. Of course, this requires knowledge of how your tables will be accessed.

As you continually monitor and build new indexes, be sure to review the old ones that were created. Remember, although indexes can improve SELECT access, they will degrade the performance of INSERTs and DELETEs (as well as any UPDATEs of indexed columns). So, be sure to drop those unused indexes.

Summary

Although this column focused on simple things that you may have heard of before, keep in mind that that was the point. Database management and administration can be a complex, arduous task. But by paying attention to the basics and making sure you do not take shortcuts around necessary processes, the complex things can be addressed more easily – because you can be sure that the simple things have been handled appropriately.



From Database Trends and Applications, July 2011.

© 2012 Craig S. Mullins,  

July 2011

DBA Corner


For more details on database systems visit: