Using package cache information to identify statements that are candidates for performance tuning
You can use the package cache event monitor along with in-memory metrics to identify which statements from the package cache are costly to run. Once you know which statements take a long time to run, you can do performance tuning on them.
Before you begin
About this task
This task shows how you can examine all work done on the system between two points in time to find the costliest statements in terms of total CPU time. Using the package cache event monitor together with package cache information reflected in in-memory monitor elements (as returned by the MON_GET_PKG_CACHE_STMT or MON_GET_PKG_CACHE_STMT_DETAILS table functions) is useful because you can see both statements in the cache as well as statements that have been evicted from the cache. Once the costly statements have been identified, you can then do performance tuning on these statements.
In this particular example, the length of the analyzed statements is limited to 3000 characters. This limitation is due to the use of the GROUP BY clause used in the statement, which cannot be used with LOB values, such as the stmt_text monitor element.
- Create a package cache event monitor to capture statements
as they are removed (evicted) from the package cache. For example, to create an event monitor called EXPENSIVESTMTS, you could use the following SQL:
CREATE EVENT MONITOR EXPENSIVESTMTS FOR PACKAGE CACHE WRITE TO UNFORMATTED EVENT TABLE
This statement creates a package cache event monitor that writes to a UE table with the same name as the event monitor, EXPENSIVESTMTS, in the default table space for the database. You can override the default name for the UE table using the TABLE table-name clause. You can also override the table space used for the UE table by using the IN tablespace-name clause.
By default, all statements evicted from the package cache are captured by the package cache event monitor. To limit the amount of information collected, you can specify options as part of the CREATE EVENT MONITOR statement that restrict the information collected. See the documentation for the CREATE EVENT MONITOR (package cache) statement for more information.
- Next, activate the event monitor:
SET EVENT MONITOR EXPENSIVESTMTS STATE 1Note: By default, this event monitor starts automatically upon database activation, because the AUTOSTART option is applied by default. However, because this event monitor is being created in an already-active database, you must use the SET EVENT MONITOR command to start it manually.
- Connect to the database and run whichever statements, workload, or applications for which you are interested in doing performance analysis. You can collect as much information as you like. However, this type of performance tuning works best when you have applications or workloads that run on a regular basis; otherwise adjustments you make for previously executed statements might not have any impact on statements that run in the future.
- When you are finished collecting data, deactivate the event
SET EVENT MONITOR EXPENSIVESTMTS STATE 0
- Extract the data from the UE table that was populated by
the event monitor using the EVMON_FORMAT_UE_TO_TABLES procedure.
CALL EVMON_FORMAT_UE_TO_TABLES ('PKGCACHE', NULL, NULL, NULL, NULL, NULL, NULL, -1, 'SELECT * FROM EXPENSIVESTMTS')This procedure examines the UE table TRACKSTMTS produced by the event monitor. It selects all of the records from the UE table, and from them, creates two relational tables from the data collected by the package cache event monitor:
Note: The values in the columns of PKGCACHE_METRICS can also be found in the XML document contained in the METRICS column of the PKGCACHE_EVENT table. They are provided in the PKGCACHE_METRICS table for more convenient, column-oriented access.
- Query the output
from the event monitor to determine which statements took the longest
time to run. In this example, total CPU time (total_cpu_time - Total CPU time monitor element) is the time-spent monitor element used to determine overall
WITH STMTS AS ( ┌ SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT 1 │ FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T └ GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) UNION ALL ┌ SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT 2 │ FROM PKGCACHE_EVENT E, PKGCACHE_METRICS M WHERE E.XMLID = M.XMLID └ GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) ) SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, STMT_TEXT, EXECUTABLE_ID FROM STMTS GROUP BY EXECUTABLE_ID, STMT_TEXT ORDER BY TOTAL_EXEC_TIME DESC FETCH FIRST 10 ROWS ONLY;
In the preceding example, both the data returned from by the MON_GET_PKG_CACHE_STMT table function (see 1 ) and the package cache event monitor (see 2 ) are retrieved. Looking at both data sets lets you see data for statements that still exist in the package cache, as well as data for statements that have been evicted from the package cache. Doing so assures that when you evaluate which statements are costly to run that all the statements run between two points in time are considered.The preceding query returns the following results:
TOTAL_CPU_TIME STMT_TEXT EXECUTABLE_ID -------------------- ------------------------------ ------------------------------------------------------------------- 656250 CALL EVMON_FORMAT_UE_TO_TABLES x'0100000000000000070000000000000000000000020020101207125759221000' SQL0445W Value "WITH STMTS AS ( SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME" has been truncated. SQLSTATE=01004 500000 CALL XSR_COMPLETE(?,?,NULL,1) x'0100000000000000160000000000000000000000020020101207125801112004' 156250 CALL XSR_ADDSCHEMADOC(?,?,?,?, x'0100000000000000090000000000000000000000020020101207125759877000' 156250 CREATE INDEX PKGCACHE_EVENT_IN x'0100000000000000120000000000000000000000020020101207125800565003' 93750 CALL XSR_REGISTER(?,?,?,?, NUL x'0100000000000000080000000000000000000000020020101207125759643000' 93750 CALL XDB_DECOMP_XML_FROM_QUERY x'0100000000000000180000000000000000000000020020101207125801862001' 78125 CREATE INDEX PKGCACHE_METRICS_ x'0100000000000000140000000000000000000000020020101207125800924000' 46875 CREATE EVENT MONITOR EXPENSIVE x'0100000000000000010000000000000000000000020020101207125758299000' 46875 SET EVENT MONITOR EXPENSIVESTM x'0100000000000000050000000000000000000000020020101207125758768001' 46875 CALL SYSPROC.SYSINSTALLOBJECTS x'0100000000000000240000000000000000000000020020101207125936286002' 10 record(s) selected with 1 warning messages printed.Note: The STMT_TEXT column has been truncated for presentation purposes.
What to do next
Use the output from the query shown in step 6 to determine which statements to tune.