示例：列出例程执行的语句所耗用的时间

在本示例中，数据库管理员 (DBA) 正在调查称为 TEST.PROC1 的关键存储过程的性能。MON_GET_ROUTINE 表函数返回的 TOTAL_ROUTINE_CPU_TIME 监视元素会显示存储过程耗用的执行时间很长。DBA 先前使用 MON_RTN_EXECLIST 数据库配置参数，将数据库配置为跟踪例程的语句信息。DBA 发出下列查询以列出 TEST.PROC1 所执行的语句。

SELECT B.EXECUTABLE_ID, 
   100*B.COORD_STMT_EXEC_TIME / A.TOTAL_ROUTINE_COORD_EXEC_TIME 
AS PERCENT_EXEC_TIME,(SELECT SUBSTR(C.STMT_TEXT,1,120)
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,B.EXECUTABLE_ID,NULL,-2)) AS C) AS STMT_TEXT
FROM TABLE(MON_GET_ROUTINE('P','TEST',NULL,'PROC1', -2)) AS A,
   TABLE(MON_GET_ROUTINE_EXEC_LIST('P','TEST',NULL,'PROC1', -1)) AS B
WHERE A.TOTAL_ROUTINE_COORD_EXEC_TIME<>0
ORDER BY PERCENT_EXEC_TIME DESC

结果按例程运行所耗用时间的百分比来排序。

EXECUTABLE_ID                                                       PERCENT_EXEC_TIME    STMT_TEXT                                                                                                                       
------------------------------------------------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------
x'01000000000000000C0000000000000000000000020020120801145618138490'                    0 SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='MY_TABLE' AND  TABSCHEMA='MYSCHEMA'                            
x'01000000000000000B0000000000000000000000020020120801145618127528'                    0 SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='MY_TABLE_2' AND  TABSCHEMA='MYSCHEMA'                              
x'0100000000000000070000000000000000000000020020120801145618004432'                    0 SELECT COLNAME, TYPENAME FROM  SYSCAT.COLUMNS WHERE TABNAME='MY_TABLE' AND TABSCHEMA='MYSCHEMA'                           

  3 record(s) selected.