Oracle中含查询文本与耗时的SQL调用历史查询方案咨询

针对你在Oracle数据库中获取查询执行日期时间、文本和总耗时的需求，结合你试了v$sql（仅记录最后一次调用耗时）和dba_hist_sqltext（未捕获到调用且无耗时信息）遇到的问题，给你几个实用的可行方案：

方案1：用V$SQL_MONITOR监控近期执行的SQL #

V$SQL_MONITOR会自动跟踪执行时间超过5秒（或符合CONTROL_MANAGEMENT_PACK_ACCESS配置规则）的SQL，能提供单条SQL的完整执行细节——包括执行起止时间、总耗时、SQL文本等，非常适合查看近期刚执行完或正在运行的SQL。

你可以用这条查询获取信息：

SELECT
  sql_id,
  sql_text,
  TO_CHAR(sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') exec_start_time,
  elapsed_time/1000000 total_elapsed_seconds
FROM
  v$sql_monitor
WHERE
  sql_exec_start >= SYSDATE - 1 -- 筛选最近1天的记录，可按需调整
ORDER BY
  sql_exec_start DESC;

小提示：这个视图的信息会根据系统内存负载自动清理，如果需要长期保存特定SQL的监控数据，可以调用DBMS_SQL_MONITOR.REPORT_SQL_MONITOR生成报告后存储。

方案2：结合DBA_HIST_SQLTEXT+DBA_HIST_SQLSTAT获取历史SQL数据 #

你之前用DBA_HIST_SQLTEXT没找到记录，大概率是因为AWR（自动工作负载仓库）的快照没捕获到这条SQL。首先确认AWR是否启用，默认快照间隔是1小时一次，如果你的SQL执行间隔不在快照周期内，就不会被记录。

调整后可以用这条查询拉取历史SQL的执行统计：

SELECT
  s.sql_id,
  t.sql_text,
  TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') snapshot_start,
  (s.elapsed_time_total/1000000)/s.executions_total avg_elapsed_seconds,
  s.elapsed_time_total/1000000 total_elapsed_seconds, -- 该快照周期内的总耗时
  s.executions_total total_executions
FROM
  dba_hist_sqlstat s
JOIN
  dba_hist_sqltext t ON s.sql_id = t.sql_id
JOIN
  dba_hist_snapshot sn ON s.snap_id = sn.snap_id
WHERE
  t.sql_text LIKE '%你的查询特征片段%' -- 替换成你要找的SQL的独特标识
ORDER BY
  sn.begin_interval_time DESC;

注意：这个方案的统计是按AWR快照周期汇总的，如果需要单条执行的精确时间，要么调整AWR快照间隔（不建议太频繁，会增加系统负载），要么搭配其他方法使用。

方案3：自定义日志表追踪特定SQL #

如果需要长期追踪某几条特定业务SQL的每次执行细节，手动建一个日志表来记录是最精准的方式，还能避免依赖系统视图的清理机制。

示例步骤：

1. 先创建日志表：

CREATE TABLE sql_exec_log (
  log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  exec_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  sql_text CLOB,
  elapsed_seconds NUMBER
);

2. 执行SQL时自动记录耗时：

DECLARE
  v_start_time TIMESTAMP;
  v_sql_text CLOB := '替换成你要执行的查询语句';
  v_elapsed NUMBER;
BEGIN
  v_start_time := SYSTIMESTAMP;
  -- 执行目标SQL
  EXECUTE IMMEDIATE v_sql_text;
  -- 计算耗时（转成秒）
  v_elapsed := EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)) +
               EXTRACT(MINUTE FROM (SYSTIMESTAMP - v_start_time))*60 +
               EXTRACT(HOUR FROM (SYSTIMESTAMP - v_start_time))*3600;
  -- 插入日志
  INSERT INTO sql_exec_log (sql_text, elapsed_seconds)
  VALUES (v_sql_text, v_elapsed);
  COMMIT;
END;
/

这种方法能精准记录每次执行的时间点和耗时，完全可控，适合业务核心SQL的长期监控。

方案4：用V$SQL快速查看最近一次执行信息 #

你提到V$SQL只有最后一次调用的耗时，它确实只能保留SQL的最新执行统计，但如果只是快速查看某条SQL的最近执行情况，这个视图还是很方便的：

SELECT
  sql_id,
  sql_text,
  TO_CHAR(last_load_time, 'YYYY-MM-DD HH24:MI:SS') last_exec_time,
  elapsed_time/1000000 last_elapsed_seconds
FROM
  v$sql
WHERE
  sql_text LIKE '%你的查询特征片段%'
ORDER BY
  last_load_time DESC;

根据你的实际需求选就行：要监控近期SQL用V$SQL_MONITOR，查历史数据调AWR快照后用DBA_HIST_*视图，特定SQL长期追踪就用自定义日志表。

内容的提问来源于stack exchange，提问作者user1710931