Trafodion 查看历史SQL执行情况

默认情况下，Trafodion会保存SQL执行的历史信息到”REPOS“下，”REPOS“下目前有4张表，保存相应的统计信息，具体描述如下 ，

Table Name	Table Desc
METRIC_QUERY_AGGR_TABLE	This table contains the statistics of short-running queries, which have been aggregated into one record and written to the repository at the end of each statistics aggregation interval. For descriptions of the fields in the table, see METRIC_QUERY_AGGR_TABLE (Release 1.1). If you are using Trafodion Release 1.0, see METRIC_QUERY_AGGR_TABLE (Release 1.0).
METRIC_QUERY_TABLE	This table stores query statistics information gathered during the compilation and execution of queries that run longer than the configured interval. For descriptions of the fields in the table, see METRIC_QUERY_TABLE (Release 1.1). If you are using Trafodion Release 1.0, see METRIC_QUERY_TABLE (Release 1.0).
METRIC_SESSION_TABLE	This table provides access to ODBC and JDBC session statistics, which are gathered when clients connect to a Trafodion instance. These statistics include the data that is collected at the start and end of each client session. For descriptions of the fields in the table, see METRIC_SESSION_TABLE (Release 1.1). If you are using Trafodion Release 1.0, see METRIC_SESSION_TABLE (Release 1.0).
METRIC_TEXT_TABLE	This table is reserved for future use. For descriptions of the fields in the table, see METRIC_TEXT_TABLE (Release 1.1). This table does not exist in Trafodion Release 1.0.

关于”REPOS“相应的详细内容，可参考： https://cwiki.apache.org/confluence/display/TRAFODION/Trafodion+Manageability

以下列出几个较常用的SQL对数据库SQL情况做统计分析，类似SQL可依据以下样例进行相应修改即可实现。
注：默认情况下，EsgynDB只会保存执行时间超过60秒的SQL的详细信息保存到“REPOS”下，若希望修改默认配置，可以通过修改dcs-site.xml配置文件添加以下配置，并重启dcs实现。

<property>
<name>dcs.server.user.program.statistics.limit.time</name>
<value>0</value>
</property>

1 查询最新执行的10 条语句执行情况（不论是否执行完成）

SELECT
[first 10] EXEC_START_UTC_TS AS EXEC_START_UTC_TS,
QUERY_ELAPSED_TIME AS QUERY_ELAPSED_TIME,
CLIENT_NAME AS CLIENT_NAME
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
ORDER BY EXEC_START_UTC_TS DESC;

2 查询最新已完成的10 条语句执行情况

SELECT
[first 10] EXEC_START_UTC_TS AS EXEC_START_UTC_TS,
EXEC_END_UTC_TS AS EXEC_END_UTC_TS,
QUERY_ELAPSED_TIME AS QUERY_ELAPSED_TIME,
CLIENT_NAME AS CLIENT_NAME
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_END_UTC_TS IS NOT NULL
ORDER BY EXEC_START_UTC_TS DESC;

3 统计过去24 小时内已完成SQL 个数

SELECT
DISTINCT (CAST(EXEC_START_UTC_TS AS DATE)) AS START_DATE,
COUNT(*) AS NUM_OF_QUERIES,
MAX(CAST(EXEC_START_UTC_TS AS TIME(6))) AS MAX_START_TIME,
MIN(CAST(EXEC_START_UTC_TS AS TIME(6))) AS MIN_START_TIME
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= CURRENT - INTERVAL '1' DAY
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY 1;

4 按客户端分组统计过去24 小时执行SQL 个数

SELECT
CLIENT_NAME,
COUNT(*) AS NUM_OF_QUERIES
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= CURRENT - INTERVAL '1' DAY
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY CLIENT_NAME
ORDER BY CLIENT_NAME;

5 按客户端分组统计过去24小时执行SQL个数、平均执行时间及最长执行时间

SELECT
CLIENT_NAME,
COUNT(*) AS NUM_OF_QUERIES,
AVG(QUERY_ELAPSED_TIME)/1000 AS AVG_QRY_ELAPSED_MSEC,
MAX(QUERY_ELAPSED_TIME)/1000 AS MAX_QRY_ELAPSED_MSEC
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= CURRENT - INTERVAL '1' DAY
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY CLIENT_NAME
ORDER BY CLIENT_NAME;