DB2 optimizer makes DB2 the best option for database performance

IBM Champion Lloyd Matthews, Principal Software Specialist (DBA), US Senate SAA, explains how the DB2 optimizer makes DB2 a database performance leader.  Enter Lloyd:

———————

DB2 Optimizer

Database performance matters because if the user community cannot perform their jobs in a timely manner, they won’t use the system or worse, may opt to get a new system. No one wants to work on a system that is slow or where they cannot rely on the integrity of the data.  As a database administrator, data integrity, performance, security and recovery are critical to any DBMS and we must be able to meet each and every one of these critical tasks.

I think DB2 is the best option for performance because of the DB2 optimizer.   The optimizer is the heart and soul of DB2. This set of algorithms is what makes this Database Management Solution (DBMS) so unique.  No other DBMS can match DB2’s ability to pick the correct access path 99% of the time given the correct statistics.

How do you get the correct statistics?

Statistics are information about objects, like tables or indexes, which DB2 needs to know in order to access the data efficiently.  An example of a key statistic is how large a table is or how many unique records it has.  The more information you can provide to DB2 the better. Statistics collects this information for the optimizer.  There’s a product from IBM called InfoSphere Optim Query Workload Tuner (OQWT) whose primary design is provide the best statistics to DB2’s optimizer for given SQL statements.   OQWT provides key statistics for the DB2 optimizer for both static and dynamic SQL.

Benefits and uses of IBM Optim Query Workload Tuner

OQWT is unique to DB2 z/OS and LUW.   Having been a database administrator (DBA) for 25 years, OQWT is the best tool for tuning SQL by identifying statistics that DBAs have never been able to fully capture manually before.   The DBA now has the ability to give the DB2 optimizer the best available statistics so it can do what it does best— pick the correct access path. I’m not aware of any other DBMS optimizer that can match the precision and accuracy of the DB2 optimizer.

OQWT also provides a rich set of reports, including a “what-if” analysis.  With the “what-if” analysis, I can compare the delta between the before and after of an SQL statement without actually having to create the index to see whether or not it worked as planned.  This feature alone saves me many hours of analysis.

Oracle’s offering is less robust and takes more time to use

Oracle has a feature called Management Packs that provides some of the functionality of OQWT.  However, Oracle’s offering is not feature rich as OQWT and the process is more manual.  With OQWT, the process of getting statistics recommendations is very automated, saving even more time and effort.

Using OQWT to tune a production database

When a developer comes to me with SQL that is performing poorly and I’m able to reduce the run time by as much as 80% via OQWT, I can honestly say this tool is without a doubt worth its weight in gold!

Recently, we had a report running against our production database.  This report ran for several hours before the user community started to complain about performance of the database.  By this time, the system was spiking @ 100%. I was called to determine what was causing the performance slowdown.   As it turns out, the report was executing dynamic SQL that was taking much of the CPU while scanning millions of rows of data against our largest tables.

I identified the poorly performing SQL via our monitors.  Monitors are graphical user interfaces that tell me about transactions within DB2. I can get information about the SQL statement, length of time executed, pages of data read, CPU utilization and other relavent information for the SQL. This information allows me to identify the query that’s causing the problem.

Once I had the queries that were causing the problem, I ran them via OQWT to get recommendations on the SQL, statistics and indexes.  What came of this analysis was astonishing to me.   Incomplete statistics on our largest objects, SQL predicates and joins not being supported by appropriate indexes as well as “stage 2” predicates.

After I got the statistics and index recommendations from OQWT, I applied them to our database.   This long running report that was taking 2+ hours to complete now was taking < 20 seconds.   Amazing said the developer, great work very nicely done said our user community.  Now reports that were critical to our users didn’t have to run off hours but could now run on an ad-hoc basis without impacting production.

There are many, many more stories like this one.  Queries that were taking 5 minutes, now only took 1 second and all because the DB2 optimizer now had the correct statistics to access the data.  This is what I’ve been able to achieve with OQWT!!