Once we knew which application we wanted to study, we had the DBA create a statement cache table and issue an EXPLAIN STMTCACHE ALL statement. That same DBA granted us SELECT authority to the table. Our goal was to query the table for the authid of the leading CPU consumer (DMDMGMTE) and sort the result by CPU descending. That way, we saw that the overall most-expensive statement for this application happened to be in the statement cache at the time the EXPLAIN statement ran. Figure 3 is one example of a common query we executed against the statement cache table. We filter by the authid and sort by CPU descending. Since the elapsed time and CPU time columns have a floating point data type, we convert to a decimal data type to make the output easier to read. We also apply a built-in function to the statement text so very long statements don’t cause trouble with our query tool’s limitations.
Figure 4 contains the output from the SQL statement in Figure 3. Our application that uses the most CPU has one statement that’s responsible for a large percentage of the total CPU the application consumes. Additional investigation showed the elapsed time for this query was about the same as the CPU time, meaning the query ran quickly and, without this information, the poor performance may have gone unnoticed.
Now that we’ve discovered a single statement using a significant amount of CPU, we can isolate that statement, EXPLAIN it, and see if it can be tuned, cached, or avoided! The following statement can be used to run a normal explain against that high CPU-consuming statement so we can expose the access path:
EXPLAIN STMTCACHE STMTID 1083216;
In this case, the statement was easily tuned, and significant CPU savings realized.
You’ll want to maintain the amount of data in your statement cache table as it can quickly get quite large, depending on how many dynamic statements are cached. You’ll need to plan to delete or archive the information because a large statement cache table can impact the performance of the EXPLAIN STMTCACHE ALL statement execution. Also, the statement text column (STMT_TEXT) is a Binary Large Object (BLOB), which could make it time-consuming or impossible to execute certain functions. Consider writing the statement cache table to a flat file, then performing extensive searches of that flat file to find interesting cached statements.
While we found the relationship between the authid and an application critical to determining who owned the poor performing statements, we had trouble relating the text of a statement to the method in the application where the statement was embedded. If a comment is added to the statement text, then that comment could be the perfect tool for identifying the piece of code that contains the statement. The following example shows how simple this is to do, and that embedded comment will show up in the statement text column of the statement cache table:
SELECT LASTNAME –GetEmpLastName method
WHERE EMPNO = ?
Getting the performance metrics out of the dynamic statement cache can be extremely valuable for performance tuning your dynamic remote applications. Remember to make this work part of a comprehensive performance reporting and tuning effort. Also, ensure you report the impact of tuning so management understands and supports the work you do!