Viewing metrics monitor elements from XML documents as rows in a table

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.

1. First, convert the unformatted event (UE) table that the package cache event monitor writes to into relational tables using the EVMON_FORMAT_UE_TO_TABLES procedure

   call EVMON_FORMAT_UE_TO_TABLES ('PkgCache',NULL,NULL,NULL,NULL,NULL,
        NULL,0,'SELECT * FROM PKGCACHEEVENTS')

   This procedure creates two tables:

   • One is called PKGCACHE_EVENT, which contains a column called METRICS. This column, in turn, contains XML documents with metrics monitor elements.
   • The other is called PKGCACHE_METRICS.
     Note: You could view the metrics directly from the columns in PKGCACHE_METRICS, rather than extract metrics from the METRICS column of the PKGCACHE_EVENT table. However, when you examine PKGCACHE_METRICS, the metrics appear in columns, rather than rows; it is not as easy to get a ranking of, say, the metrics with the highest values.
2. Query the two tables produced in the preceding step to determine which statement is the most expensive in terms of execution times:

   SELECT EVENTS.EXECUTABLE_ID, 
          SUM(METRICS.STMT_EXEC_TIME) AS TOTAL_STMT_EXEC_TIME
   FROM   PKGCACHE_EVENT AS EVENTS, 
          PKGCACHE_METRICS AS METRICS 
   WHERE  EVENTS.XMLID = METRICS.XMLID 
   GROUP  BY EVENTS.EXECUTABLE_ID
   ORDER  BY TOTAL_STMT_EXEC_TIME DESC
   FETCH  FIRST 5 ROWS ONLY

   In the preceding query, the two tables produced in step 1 are joined so that the statement IDs from the PKGCACHE_EVENT table can be associated with their execution times in the PKGCACHE_METRICS table:

   EXECUTABLE_ID                                                       TOTAL_STMT_EXEC_TIME
   ------------------------------------------------------------------- --------------------
   x'01000000000000001A0300000000000000000000020020091215115933859000'                  250
   x'0100000000000000150300000000000000000000020020091215115850328000'                  191
   x'0100000000000000210200000000000000000000020020091215115818343001'                  129
   x'0100000000000000C40200000000000000000000020020091215115838578000'                   41
   x'0100000000000000B00200000000000000000000020020091215115838203000'                   38
   
     5 record(s) selected.

   The first item in the results represents the statement with the largest overall execution time.
3. Optional: If you like, you can display the text for the statement using the following SQL:

   SELECT SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT 
   FROM PKGCACHE_EVENT 
   WHERE EXECUTABLE_ID = x'01000000000000001A0300000000000000000000020020091215115933859000'

   Results:

   STMT_TEXT
   ------------------------------------------------------------
   DROP XSROBJECT MYSCHEMA.EVMON_PKGCACHE_SCHEMA_SQL09070
   
     1 record(s) selected.

4. Use the MON_FORMAT_XML_TIMES_BY_ROW table function to view a listing of the time-spent monitor elements for the statement you identified in step 2:

   SELECT SUBSTR(XMLMETRICS.METRIC_NAME,1,30) AS METRIC_NAME,
          XMLMETRICS.TOTAL_TIME_VALUE, 
          SUBSTR(XMLMETRICS.PARENT_METRIC_NAME,1,30) AS PARENT_METRIC_NAME
   FROM PKGCACHE_EVENT AS EVENTS, 
        TABLE(MON_FORMAT_XML_TIMES_BY_ROW(EVENTS.METRICS)) AS XMLMETRICS 
   WHERE EVENTS.EXECUTABLE_ID=
   x'01000000000000001A0300000000000000000000020020091215115933859000' 
    AND PARENT_METRIC_NAME='STMT_EXEC_TIME'
   ORDER BY XMLMETRICS.TOTAL_TIME_VALUE DESC
   

   Notes:

   • Remember that time-spent monitor elements are organized into hierarchies. To eliminate double-counting, only those metrics that roll-up to stmt_exec_time are included in the results. Otherwise, stmt_exec_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_TIMES_BY_ROW is included for illustrative purposes.
   When run, the following results are returned by this query:

   METRIC_NAME                    TOTAL_TIME_VALUE     PARENT_METRIC_NAME
   ------------------------------ -------------------- ------------------------------
   TOTAL_ACT_WAIT_TIME                             234 STMT_EXEC_TIME
   TOTAL_SECTION_PROC_TIME                          15 STMT_EXEC_TIME

   Here, you can see that the total processing time adds up to 249 ms. Compare this time to the total time of 250 shown in step 2; the extra millisecond is accounted for by other times (for example, waits) not included in stmt_exec_time.