Scenario: Identifying costly applications using built-in administrative views

Recent increases in the workload on the ShopMart database have started hindering overall database performance. Jessie, the ShopMart DBA, is trying to identify the larger resource consumers in the daily workload using the following administrative views:

MON_CONNECTION_SUMMARY

This view helps Jessie identify applications that might be performing large table scans:

CONNECT TO SHOPMART;
SELECT APPLICATION_HANDLE, ROWS_READ_PER_ROWS_RETURNED
  FROM SYSIBMADM.MON_CONNECTION_SUMMARY;

The value of ROWS_READ_PER_ROWS_RETURNED shows her the average number of rows accessed from the base tables per rows returned to the application. If this value is a high number, the application might be performing a table scan that could be avoided with the creation of an index. Jessie uses this view to identify potentially troublesome queries, and then she can investigate further by looking at the SQL to see whether there are any ways to reduce the number of rows that are read in the execution of the query.

MON_CURRENT_SQL

Jessie uses the MON_CURRENT_SQL administrative view to identify the longest running queries that are currently being executed:

CONNECT TO SHOPMART;
SELECT ELAPSED_TIME_SEC, ACTIVITY_STATE, ACTIVITY_TYPE, APPLICATION_HANDLE
 FROM SYSIBMADM.MON_CURRENT_SQL
 ORDER BY ELAPSED_TIME_SEC DESC
 FETCH FIRST 5 ROWS ONLY;

Using this view, she can determine the length of time these queries have been running, and the status of these queries. If a query has been executing for a long time and is waiting on a lock, she can issue a query on the MON_LOCKWAITS administrative view specifying an agent id to investigate further. The MON_CURRENT_SQL view can also tell her the statement that is being executed, allowing her to identify potentially problematic SQL.

MON_PKG_CACHE_SUMMARY

Jessie uses the MON_PKG_CACHE_SUMMARY to troubleshoot queries that have been identified as problematic. This view can tell her how frequently a query is run as well as the average execution time for each of these queries:

CONNECT TO SHOPMART;
SELECT SECTION_TYPE, EXECUTABLE_ID, NUM_COORD_EXEC,
  NUM_COORD_EXEC_WITH_METRICS, AVG_STMT_EXEC_TIME, PREP_TIME
 FROM SYSIBMADM.MON_PKG_CACHE_SUMMARY
 ORDER BY NUM_COORD_EXEC DESC;

The value of PREP_TIME tells Jessie what amount of time was spent compiling the queries compared to its execution time. If the time it takes to compile and optimize a query is almost as long as it takes for the query to execute, Jessie might want to advise the owner of the query to change the optimization class used for the query. Lowering the optimization class might make the query complete optimization more rapidly and therefore return a result sooner. However, if a query takes a significant amount of time to prepare but is executed thousands of times (without being prepared again) then changing the optimization class might not benefit query performance.

Jessie also uses the MON_PKG_CACHE_SUMMARY view to identify the most frequently executed and the longest-running SQL statements. Having this information will allow Jessie to focus her SQL tuning efforts on the queries that represent some of the biggest resource consumers

To identify the most frequently run SQL statements, Jessie issues the following statements:

CONNECT TO SHOPMART;
SELECT SECTION_TYPE, NUM_COORD_EXEC, NUM_COORD_EXEC_WITH_METRICS,
  AVG_STMT_EXEC_TIME, PREP_TIME, SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT
 FROM SYSIBMADM.MON_PKG_CACHE_SUMMARY
 ORDER BY NUM_COORD_EXEC DESC
 FETCH FIRST 5 ROWS ONLY;

The output shows all of the details regarding the execution time and the statement text for the five most frequent SQL statements.

To identify the SQL statements with the longest execution times, Jessie examines the queries with the top five values for AVG_STMT_EXEC_TIME:

CONNECT TO SHOPMART;
SELECT SECTION_TYPE, NUM_COORD_EXEC, NUM_COORD_EXEC_WITH_METRICS,
  AVG_STMT_EXEC_TIME, SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT
 FROM SYSIBMADM.MON_PKG_CACHE_SUMMARY
 ORDER BY AVG_STMT_EXEC_TIME DESC
 FETCH FIRST 5 ROWS ONLY;