在oracle中查找所有sql id、sql文本、执行次数、两个AWR snap id之间的执行时间

您可以从以下视图中进行选择。

• 历史 SQL 统计：DBA_HIST_SQLSTAT
• 所有 SQL 文本：DBA_HIST_SQLTEXT
• 将 snap_id 映射到实际时间：DBA_HIST_SNAPSHOT

  SELECT T.SQL_ID,
         MIN (SN.BEGIN_INTERVAL_TIME)             BEGIN_INTERVAL_TIME,
         MAX (SN.END_INTERVAL_TIME)             END_INTERVAL_TIME,
         MAX (CAST (DBMS_LOB.SUBSTR (T.SQL_TEXT, 2000) AS NVARCHAR2 (2000)))             SQL_TEXT,
         SUM (S.EXECUTIONS_DELTA)                   EXECUTIONS,
         SUM (S.ELAPSED_TIME_DELTA)                 ELAPSED_TIME
    FROM DBA_HIST_SQLSTAT S, DBA_HIST_SQLTEXT T, DBA_HIST_SNAPSHOT SN
   WHERE     T.SQL_ID = S.SQL_ID
         AND S.SNAP_ID = SN.SNAP_ID
         AND SN.BEGIN_INTERVAL_TIME >= SYSDATE - 1
         AND SN.END_INTERVAL_TIME <= SYSDATE
GROUP BY T.SQL_ID
ORDER BY ELAPSED_TIME DESC

您需要在 and 之间添加一个连接dba_hist_sqlstat，dba_hist_sqltext否则它将永远运行：

select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where t.sql_id=s.sql_id and 
s.snap_id between 9349 and 9351

此外，您需要确保您拥有调整包的许可证，它不是免费的。

这将获得 2 个快照 ID 之间的那些字段，只需替换 where 子句中的快照 ID

select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 1000 and 2000;