使用程序包高速缓存信息确定用于进行性能调整的候选语句

可以将程序包高速缓存事件监视器与内存中指标配合使用以确定程序包高速缓存中运行成本较高的语句。一旦知道运行花费时间较长的语句后，就可对其进行性能调整。

开始之前

CREATE EVENT MONITOR 语句需要页大小至少为 8K 的表空间来存储事件监视器生成的未格式化事件 (UE) 表。除非在 CREATE EVENT MONITOR 语句中显式命名了表空间，否则使用数据库的缺省表空间。

关于此任务

此任务向您显示如何检查在两个时间点内系统上执行的所有工作，从而查找成本最高的语句（就 CPU 时间总计方面而言）。将程序包高速缓存事件监视器与内存中监视元素中反映的信息（如 MON_GET_PKG_CACHE_STMT 或 MON_GET_PKG_CACHE_STMT_DETAILS 表函数返回的信息）配合使用将很有用，因为您可同时查看高速缓存中语句以及从高速缓存逐出的语句。在确定高成本的语句后，然后可对这些语句执行性能调整。

注：您可选择一些确定高运行成本的语句时要使用的监视元素。在此示例中，使用 CPU 时间 (total_cpu_time -“CPU 时间总计”监视元素)。此度量显示所消耗的实际 CPU 资源；它不反映诸如锁定等待时间或语句执行期间消耗的其他时间之类的内容。您可能改为选择使用语句执行时间 (stmt_exec_time -“语句执行时间”监视元素)，其包含段中所有代理程序所耗用的时间并包括等待时间等等。您还可从程序包高速缓存事件监视器返回的众多其他时间耗用元素中进行选择。有关您可从中选择的监视元素的更多信息，请参阅EVMON_FORMAT_UE_TO_TABLES 为程序包高速缓存事件监视器写至关系表的信息或为程序包高速缓存事件监视器写入 XML 的信息。

限制

在这一特定示例中，被分析的语句长度限制为 3000 个字符。这种限制是由于语句中使用了 GROUP BY 子句，而 GROUP BY 子句无法与 LOB 值（如 stmt_text 监视元素）配合使用。

过程

创建一个程序包高速缓存事件监视器以在语句从程序包高速缓存中被移除（逐出）时将其捕获。
例如，要创建一个名为 EXPENSIVESTMTS 的事件监视器，您可使用以下 SQL：
```
CREATE EVENT MONITOR EXPENSIVESTMTS FOR PACKAGE CACHE WRITE TO UNFORMATTED EVENT TABLE
```
此语句创建一个程序包高速缓存事件监视器，用于写入到与数据库的缺省表空间中的事件监视器 EXPENSIVESTMTS 同名的 UE 表中。可使用 TABLE table-name 子句覆盖 UE 表的缺省名称。还可使用 IN tablespace-name 子句覆盖用于 UE 表的表空间。

缺省情况下，程序包高速缓存事件监视器将捕获从程序包高速缓存中逐出的所有语句。要限制所收集的信息量，可将用于限制收集的信息的某些选项指定为 CREATE EVENT MONITOR 语句的一部分。有关更多信息，请参阅 CREATE EVENT MONITOR (程序包高速缓存) 语句的文档。
接着，激活事件监视器：
```
SET EVENT MONITOR EXPENSIVESTMTS STATE 1
```
注：缺省情况下，事件监视器在数据库激活时自动启动，因为缺省情况下应用 AUTOSTART 选项。但是，由于此事件监视器是在已经活动的数据库中创建的，因此您必须使用 SET EVENT MONITOR 命令手动启动此事件监视器。
连接到数据库，并运行您有兴趣为其执行性能分析的任意语句、工作负载或应用程序。您可收集所需要的任意数量信息。但是，在您具有定期运行的应用程序或工作负载的情况下，这种类型的性能调整具有最佳效果；否则您为先前执行的语句进行的调整对于将来运行的语句可能没有任何影响。
完成数据收集后，停用事件监视器：
```
SET EVENT MONITOR EXPENSIVESTMTS STATE 0
```
使用 EVMON_FORMAT_UE_TO_TABLES 过程从事件监视器所填充的 UE 表中提取数据。
```
CALL EVMON_FORMAT_UE_TO_TABLES ('PKGCACHE', NULL, NULL, NULL, NULL, NULL,
	NULL, -1, 'SELECT * FROM EXPENSIVESTMTS')
```
此过程检查由事件监视器生成的 UE 表 TRACKSTMTS。此过程从 UE 表中选择所有记录，并且在这些记录中，通过程序包高速缓存事件监视器所收集的数据创建两个关系表：
- PKGCACHE_EVENT
- PCKCACHE_METRICS
第一个表包含最常用的监视元素以及与捕获的每个事件相关联的指标。第二个表包含每个事件的详细度量值。
注： PKGCACHE_METRICS 列中的值还可在 PKGCACHE_EVENT 表的 METRICS 列中包含的 XML 文档中找到。为了更加方便以及面向列的访问，这些值在 PKGCACHE_METRICS 表中提供。

对事件监视器中的输出进行查询以确定哪些语句的运行耗用时间最长。在此示例中，CPU 时间总计 (total_cpu_time -“CPU 时间总计”监视元素) 是用于确定总体开销的时间耗用监视元素：

WITH STMTS AS 
     (
    ┌ SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT
 1  │ FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T
    └ GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000)
     UNION ALL
    ┌ SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) AS STMT_TEXT
 2  │ FROM PKGCACHE_EVENT E, PKGCACHE_METRICS M WHERE E.XMLID = M.XMLID
    └ GROUP BY EXECUTABLE_ID, VARCHAR(STMT_TEXT, 3000) 
     )
SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME, STMT_TEXT, EXECUTABLE_ID
FROM STMTS
GROUP BY EXECUTABLE_ID, STMT_TEXT
ORDER BY TOTAL_EXEC_TIME DESC
FETCH FIRST 10 ROWS ONLY;

在上述示例中，将同时检索从 MON_GET_PKG_CACHE_STMT 表函数（请参见 1 ）以及程序包高速缓存事件监视器（请参见 2 ）返回的数据。通过同时查看这两个数据集，您可查看仍在程序包高速缓存中的语句的数据以及从程序包高速缓存逐出的语句的数据。这样做可确保您在计算高运行成本语句时，考虑了在两个时间点之间运行的所有语句。

上述查询将返回下列结果：

TOTAL_CPU_TIME       STMT_TEXT                      EXECUTABLE_ID
-------------------- ------------------------------ -------------------------------------------------------------------
              656250 CALL EVMON_FORMAT_UE_TO_TABLES x'0100000000000000070000000000000000000000020020101207125759221000'
SQL0445W  Value "WITH STMTS AS ( SELECT SUM(TOTAL_CPU_TIME) AS TOTAL_CPU_TIME" has been truncated.  SQLSTATE=01004
              500000 CALL XSR_COMPLETE(?,?,NULL,1)  x'0100000000000000160000000000000000000000020020101207125801112004'
              156250 CALL XSR_ADDSCHEMADOC(?,?,?,?, x'0100000000000000090000000000000000000000020020101207125759877000'
              156250 CREATE INDEX PKGCACHE_EVENT_IN x'0100000000000000120000000000000000000000020020101207125800565003'
               93750 CALL XSR_REGISTER(?,?,?,?, NUL x'0100000000000000080000000000000000000000020020101207125759643000'
               93750 CALL XDB_DECOMP_XML_FROM_QUERY x'0100000000000000180000000000000000000000020020101207125801862001'
               78125 CREATE INDEX PKGCACHE_METRICS_ x'0100000000000000140000000000000000000000020020101207125800924000'
               46875 CREATE EVENT MONITOR EXPENSIVE x'0100000000000000010000000000000000000000020020101207125758299000'
               46875 SET EVENT MONITOR EXPENSIVESTM x'0100000000000000050000000000000000000000020020101207125758768001'
               46875 CALL SYSPROC.SYSINSTALLOBJECTS x'0100000000000000240000000000000000000000020020101207125936286002'

  10 record(s) selected with 1 warning messages printed.

注：为了便于表示，已截断了 STMT_TEXT 列。

下一步做什么

使用步骤 6 中显示的查询的输出来确定要调整的语句。