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
The CREATE EVENT MONITOR statement requires
a table space with a page size of at least 8 K to store the unformatted
event (UE) table produced by the event monitor. Unless a table space
is explicitly named in the CREATE EVENT MONITOR statement, the default
table space for the database is used.
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.
Restrictions
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.
Procedure
- 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 1
Note: 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
monitor:
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:
- PKGCACHE_EVENT
- PCKCACHE_METRICS
The first table contains the most frequently used monitor elements
and metrics associated with each event captured. The second contains
detailed metrics for each event.
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
cost:
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_CPU_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.