检索活动级别的耗用时间信息的一个示例是查看特定 SQL 语句的“耗用时间”监视元素。可以使用 MON_GET_PKG_CACHE_STMT 表函数来检索此信息。
关于此任务
此任务显示了一个示例，用来说明如何检索有关程序包高速缓存中的 SQL 语句的所选耗用时间详细信息。注：
- 为程序包高速缓存中的给定语句报告的耗用时间指标，是执行该语句的所有次数的耗用时间指标的总计。
- 这些查询的输出中所显示的值只是为了便于说明，不应将它们理解为表示您在自己的系统中可能看到的内容。
过程
- 明确阐述一个 SQL 语句，它使用 MON_GET_PKG_CACHE_STMT 表函数来检索有关程序包高速缓存中的语句的信息。
例如，假定您想确定相对于语句的执行总时间的等待总时间。用于检索此信息的查询可能为如下所示：
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
- 运行此查询。
结果可能与下列输出相似：
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.
结果
此时，您可以再次使用 MON_GET_PKG_CACHE_STMT 表函数来检索您特别感兴趣的任何语句的语句文本。例如，可以使用下列查询来确定以上所显示的具有最长等待时间的语句：SELECT VARCHAR(STMT_TEXT, 80) AS STMT_TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,x'0100000000000000320000000000000000000000020020091111120320140000',NULL,-2))
AS T
上述查询的输出将类似于以下内容：STMT_TEXT
---------------------------------------------------------------------------------
UPDATE EMPLOYEE SET BONUS=10000 WHERE PERF_RATING=1
1 record(s) selected.