Determining where time is spent during SQL statement execution

One example of retrieving time-spent information at the activity level is viewing time spent monitor elements for specific SQL statements. You can use the MON_GET_PKG_CACHE_STMT table function to retrieve this information.

About this task

This task shows an example of how to retrieve selected time-spent details for SQL statements in the package cache.

Note:

The time-spent metrics reported for a given statement in the package cache are aggregates of the time-spent metrics for all executions of that statement.
The values shown in the output for queries are for illustrative purposes only, and should not be construed as representative of what you might see in your own system.

Procedure

Formulate an SQL statement that uses the MON_GET_PKG_CACHE_STMT table function to retrieve information about statements in the package cache.
For example, assume that you want to determine the total wait time relative to the total statement execution time. A query to retrieve this might look like this:
```
SELECT SUM(STMT_EXEC_TIME) AS TOTAL_EXEC_TIME,    
       SUM(TOTAL_ACT_WAIT_TIME) AS TOTAL_WAIT_TIME,
       EXECUTABLE_ID
FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) AS T         
WHERE STMT_EXEC_TIME <> 0  
GROUP BY EXECUTABLE_ID
ORDER BY TOTAL_EXEC_TIME DESC
```

Run the query.

The results might look like the following output:

TOTAL_EXEC_TIME      TOTAL_WAIT_TIME      EXECUTABLE_ID
-------------------- -------------------- -------------------------------------------------------------------
                9021                 9021 x'0100000000000000320000000000000000000000020020091111120320140000'
                3017                  372 x'0100000000000000030000000000000000000000020020091111115438062000'
                 591                    0 x'0100000000000000010000000000000000000000020020091111115252265000'
                 203                  192 x'0100000000000000270000000000000000000000020020091111115936750000'
                 142                    0 x'01000000000000002B0000000000000000000000020020091111115944000000'
                 111                   48 x'0100000000000000070000000000000000000000020020091111115441359002'
                 108                   35 x'01000000000000000B0000000000000000000000020020091111115441750000'
                  55                    0 x'01000000000000000D0000000000000000000000020020091111115442062000'
                  50                    0 x'01000000000000000C0000000000000000000000020020091111115441921000'
                  38                    0 x'0100000000000000260000000000000000000000020020091111115936609003'
                  35                    2 x'01000000000000000A0000000000000000000000020020091111115441609000'
                  35                   35 x'0100000000000000130000000000000000000000020020091111115442593001'
                  33                    0 x'0100000000000000120000000000000000000000020020091111115442531000'
                  32                    0 x'0100000000000000240000000000000000000000020020091111115936578000'
                  29                    0 x'01000000000000000E0000000000000000000000020020091111115442203000'
                  24                   23 x'0100000000000000040000000000000000000000020020091111115440640000'
                  24                    0 x'0100000000000000110000000000000000000000020020091111115442484003'
                  20                    0 x'0100000000000000300000000000000000000000020020091111120241828000'
                  15                    0 x'0100000000000000050000000000000000000000020020091111115440984000'
                  14                    0 x'0100000000000000080000000000000000000000020020091111115441437000'
                  13                   13 x'01000000000000000F0000000000000000000000020020091111115442406001'
                   4                    0 x'0100000000000000100000000000000000000000020020091111115442484001'
                   3                    0 x'0100000000000000180000000000000000000000020020091111115442828000'
                   3                    3 x'01000000000000001F0000000000000000000000020020091111115936515000'
                   3                    0 x'0100000000000000290000000000000000000000020020091111115943968001'
                   2                    0 x'0100000000000000150000000000000000000000020020091111115442656001'
                   2                    0 x'0100000000000000170000000000000000000000020020091111115442750000'
                   1                    0 x'0100000000000000160000000000000000000000020020091111115442734000'
                   1                    0 x'0100000000000000280000000000000000000000020020091111115937000001'
                   1                    0 x'01000000000000002A0000000000000000000000020020091111115943984000'

  30 record(s) selected.

Results

At this point, you could use the MON_GET_PKG_CACHE_STMT table function again to retrieve the statement text for any statement you are particularly interested in. For example, the statement with the highest wait time previously shown could be determined using the following query:

SELECT VARCHAR(STMT_TEXT, 80) AS STMT_TEXT 
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,x'0100000000000000320000000000000000000000020020091111120320140000',NULL,-2)) 
     AS T

The output of the preceding query would look something like this:

STMT_TEXT
---------------------------------------------------------------------------------
UPDATE EMPLOYEE SET BONUS=10000 WHERE PERF_RATING=1
  1 record(s) selected.