Oracle统计分析某个SQL的执行频率

有时候因为业务增长或应用程序配置问题会导致一些SQL语句的执行频率出现飚增，下面这两个SQL语句就是用来统计某个SQL的执行频率/单位时间内的执行次数，分析SQL的执行频率是否出现异常。

按天统计SQL的执行次数

SET LINESIZE 255
SET PAGESIZE 60
COL SQL_ID FOR A20
COL DATE_TIME FOR A20
SELECT M.SQL_ID ,
       TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD')   "DATE_TIME",
       SUM(M.EXECUTIONS_DELTA)  EXECUTIONS
FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N
WHERE M.SNAP_ID  = N.SNAP_ID
  AND M.DBID = N.DBID
  AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER
  AND M.INSTANCE_NUMBER=1
  AND M.SQL_ID='&SQL_ID'
GROUP BY M.SQL_ID , TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD')
ORDER BY "DATE_TIME";

按小时统计SQL的执行次数

SET LINESIZE 255
SET PAGESIZE 60
COL SQL_ID FOR A20
COL DATE_TIME FOR A20
SELECT M.SQL_ID ,
       TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24')   "DATE_TIME",
       SUM(M.EXECUTIONS_DELTA)  EXECUTIONS
FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N
WHERE M.SNAP_ID  = N.SNAP_ID
  AND M.DBID = N.DBID
  AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER
  AND M.INSTANCE_NUMBER=1
  AND M.SQL_ID='&SQL_ID'
GROUP BY M.SQL_ID , TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24')
ORDER BY "DATE_TIME";