Example: Identifying the most expensive routines by CPU consumption

You can use routine monitoring to identify your most expensive routines.

Scenario

In this example, a database administrator (DBA) wants to identify the database routines that are consuming the most total CPU. The DBA issues the following query which displays all routines that have executed since database activation time:

SELECT ROUTINESCHEMA, ROUTINEMODULENAME, ROUTINENAME,
   SPECIFICNAME, SUM(TOTAL_CPU_TIME) AS TOTAL_CPU
FROM TABLE(MON_GET_ROUTINE(NULL,NULL,NULL,NULL,-2)) AS T
GROUP BY ROUTINESCHEMA, ROUTINEMODULENAME, ROUTINENAME, SPECIFICNAME
ORDER BY TOTAL_CPU DESC

The result is ordered by total routine CPU consumption.

ROUTINESCHEMA     ROUTINEMODULENAME  ROUTINENAME                 SPECIFICNAME        TOTAL_CPU
----------------- ------------------ --------------------------- ------------------- --------------------
SYSIBMINTERNAL    -                  COMPILED_ANON_BLOCK_INVOKE  SQL120801135416210               8942414
DRICARD           -                  PROC1                       PROC1                              23444
SYSIBMSUBROUTINE  -                  PROC1_66613_101877843       -                                   4213
DRICARD           -                  MYPROC                      SQL120801135351900                  1838
DRICARD           -                  TRIG1                       SQL120801135519200                   467

  5 record(s) selected.

The DBA can now focus the tuning efforts on the routines that consume the most total CPU.