Viewing metrics monitor elements from XML documents as rows in a table
About this task
Procedure
The example shown in this task uses the MON_FORMAT_XML_TIMES_BY_ROW table function to view component times for a statement as tracked by the package cache event monitor. It assumes that a package cache event monitor called PKGCACHEEVENTS has been created and activated. The package cache event monitor writes its output to an unformatted event (UE) table. Before it can be used, the data in the UE table must be converted to either relational tables using the EVMON_FORMAT_UE_TO_TABLES stored procedure, or to XML using the EVMON_FORMAT_UE_TO_XML table function. This task shows the first of these two approaches.
Results
Example
-
Viewing the contents of the DETAILS column produced by a MON_GET_*_DETAILS
table function
You can also use the MON_FORMAT_XML_*_BY_ROW functions to view the contents of the DETAILS column returned by any of the MON_GET_*_DETAILS functions. For example, MON_GET_CONNECTION_DETAILS returns a DETAILS column that contains an XML document with metrics that pertain to a database connection.
For example, to view the non-zero component times for each connection across all members, you could use the following query:SELECT CONDETAILS.APPLICATION_HANDLE, SUBSTR(XMLMETRICS.METRIC_NAME,1,30) AS METRIC_NAME, SUM(XMLMETRICS.TOTAL_TIME_VALUE) AS TOTAL_TIME_VALUE, SUBSTR(XMLMETRICS.PARENT_METRIC_NAME,1,30) AS PARENT_METRIC_NAME FROM TABLE(MON_GET_CONNECTION_DETAILS(NULL,-1)) AS CONDETAILS, TABLE(MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW(CONDETAILS.DETAILS))AS XMLMETRICS WHERE TOTAL_TIME_VALUE > 0 AND XMLMETRICS.PARENT_METRIC_NAME='TOTAL_RQST_TIME' GROUP BY CONDETAILS.APPLICATION_HANDLE, XMLMETRICS.PARENT_METRIC_NAME, XMLMETRICS.METRIC_NAME ORDER BY CONDETAILS.APPLICATION_HANDLE ASC, TOTAL_TIME_VALUE DESC
Notes:- To eliminate
double-counting, only those metrics that roll-up to total_rqst_time are
included in the results (
WHERE .... XMLMETRICS.PARENT_METRIC_NAME='TOTAL_RQST_TIME'
). Otherwise, total_rqst_time itself would be included in the results, which includes several individual component times. - PARENT_METRIC_NAME, one of the columns returned by MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW is included for illustrative purposes.
The preceding query returns the following results:
As this example shows, only metrics that comprise total_rqst_time are included. Had theAPPLICATION_HANDLE METRIC_NAME TOTAL_TIME_VALUE PARENT_METRIC_NAME -------------------- ------------------------------ -------------------- ------------------------------ 52 TOTAL_SECTION_TIME 3936 TOTAL_RQST_TIME 52 TOTAL_COMPILE_TIME 482 TOTAL_RQST_TIME 52 TOTAL_COMMIT_TIME 15 TOTAL_RQST_TIME 52 TOTAL_ROLLBACK_TIME 1 TOTAL_RQST_TIME 496 TOTAL_COMPILE_TIME 251 TOTAL_RQST_TIME 496 TOTAL_SECTION_TIME 46 TOTAL_RQST_TIME 496 TOTAL_IMPLICIT_COMPILE_TIME 5 TOTAL_RQST_TIME 7 record(s) selected.
WHERE .... XMLMETRICS.PARENT_METRIC_NAME='TOTAL_RQST_TIME'
clause not been included in the query, the results would look like those that follow:
In this case, the values for total_rqst_time for each connection are included in the results, which includes the values for all other elements for which it is the parent. Similarly, the values for items in italics roll up to the total_section_time. Had they not been excluded in the WHERE clause, they would have been triple-counted in the results, as total_section_time itself rolls up to total_rqst_time.APPLICATION_HANDLE METRIC_NAME TOTAL_TIME_VALUE PARENT_METRIC_NAME -------------------- ------------------------------ -------------------- ------------------------------ 52 TOTAL_RQST_TIME 4603 - 52 TOTAL_SECTION_TIME 3942 TOTAL_RQST_TIME 52 TOTAL_COMPILE_TIME 537 TOTAL_RQST_TIME 52 TOTAL_SECTION_SORT_TIME 299 TOTAL_SECTION_TIME 52 TOTAL_COMMIT_TIME 15 TOTAL_RQST_TIME 52 TOTAL_ROLLBACK_TIME 1 TOTAL_RQST_TIME 496 TOTAL_RQST_TIME 341 - 496 TOTAL_COMPILE_TIME 251 TOTAL_RQST_TIME 496 TOTAL_SECTION_TIME 46 TOTAL_RQST_TIME 496 TOTAL_IMPLICIT_COMPILE_TIME 5 TOTAL_RQST_TIME 496 TOTAL_SECTION_SORT_TIME 2 TOTAL_SECTION_TIME 11 record(s) selected.
- To eliminate
double-counting, only those metrics that roll-up to total_rqst_time are
included in the results (