Oracle中监控SQL执行时间的常用方法_MsSqlserver

在 oracle 数据库中，监控 sql 语句的执行时间是性能调优和问题排查的关键环节。oracle 提供了多种机制来捕获和分析 sql 的执行耗时，以下是 常用且实用的方法，按使用场景分类说明：

一、实时监控：查看当前正在执行的 sql 及其已运行时间

方法：查询v$session+v$sql

select 
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    q.sql_text,
    s.last_call_et as "seconds_running",  -- 当前操作已运行秒数
    q.elapsed_time / 1000000 as "elapsed_sec_total",  -- 该sql总耗时（秒）
    q.cpu_time / 1000000 as "cpu_sec"
from 
    v$session s
join 
    v$sql q on s.sql_id = q.sql_id
where 
    s.status = 'active'
    and s.username is not null
    and s.type != 'background';

说明：

last_call_et：当前调用已持续的秒数（单位：秒），对活跃会话非常有用。
elapsed_time：该 sql 自首次执行以来累计总耗时（微秒），除以 1e6 转为秒。
此方法适用于实时抓取慢查询。

二、历史监控：查看已执行 sql 的性能统计（awr / v$sql）

oracle 会自动在共享池中缓存 sql 的执行统计信息（通过 v$sql 视图）。

查询最近执行过的 sql 及其平均/总耗时：

select 
    sql_id,
    substr(sql_text, 1, 100) as sql_preview,
    executions as execs,
    round(elapsed_time / 1000000, 2) as total_sec,
    round(elapsed_time / nullif(executions, 0) / 1000000, 4) as avg_sec,
    round(cpu_time / nullif(executions, 0) / 1000000, 4) as avg_cpu_sec,
    last_active_time
from 
    v$sql
where 
    executions > 0
    and last_active_time > sysdate - 1  -- 过去24小时
order by 
    elapsed_time desc;

注意：

v$sql 中的数据是内存中的缓存，实例重启或 sql 被老化（aged out）后会丢失。
适合查看近期高频或高耗时 sql。

三、长期监控：使用 awr（automatic workload repository）

awr 是 oracle 企业版（enterprise edition）提供的自动性能数据仓库，默认每小时快照一次，保留 8 天。

查看某段时间内最耗时的 sql（top sql by elapsed time）：

-- 需要知道快照 id（snap_id）
select * from table(
    dbms_workload_repository.select_sql(
        begin_snap => 12345,
        end_snap   => 12346,
        sql_id     => null  -- null 表示返回 top sql
    )
);

或者使用标准 awr 报告：

-- 生成 html 或文本格式的 awr 报告（需 dba 权限）
@$oracle_home/rdbms/admin/awrrpt.sql

awr 优势：

持久化存储，不受内存老化影响；
包含等待事件、i/o、cpu 等完整上下文；
支持跨时间段对比。

缺点：仅限 enterprise edition，且需额外许可（diagnostics pack）。

四、会话级跟踪：启用 sql trace（10046 event）

如果要精确分析某条 sql 的执行细节（包括各阶段耗时、等待事件），可开启 sql trace。

步骤：

1. 开启当前会话跟踪：

alter session set sql_trace = true;
-- 或更详细的 10046 trace（level 12 包含绑定变量和等待事件）
alter session set events '10046 trace name context forever, level 12';

2. 执行你的 sql

select * from large_table where ...;

3. 关闭跟踪

alter session set sql_trace = false;
-- 或
alter session set events '10046 trace name context off';

4. 找到 trace 文件位置

select value from v$diag_info where name = 'default trace file';

5. 使用tkprof格式化分析

tkprof tracefile.trc output.txt explain=user/pass sort=exeela

输出包含：

parse / execute / fetch 各阶段耗时
cpu time、elapsed time、disk reads
执行计划

适用场景：深度性能诊断，但不适用于生产环境大规模开启。

五、使用dbms_monitor跟踪特定会话或客户端

可针对某个用户、服务名或会话开启跟踪：

-- 跟踪某个客户端程序（如 jdbc 应用）
begin
  dbms_monitor.client_id_trace_enable(
    client_id => 'my_app_user',
    waits     => true,
    binds     => true
  );
end;
/

后续用 tkprof 分析生成的 trace 文件。

六、监控长时间运行的操作（long operations）

对于 dml、ddl、备份等长时间操作，可用：

select 
    opname,
    target,
    sofar,
    totalwork,
    round(sofar/totalwork*100, 2) as pct_done,
    time_remaining,
    elapsed_seconds
from 
    v$session_longops
where 
    totalwork > 0 
    and sofar != totalwork;

权限说明

操作	所需权限
查询 `v$session`, `v$sql`	`select any dictionary` 或 dba 授予 `v_$session` 等
生成 awr 报告	`select_catalog_role` 或 `dba`
开启 sql trace	`alter session`（普通用户可开自己的），全局跟踪需 dba

总结：如何选择？

场景	推荐方法
实时查看谁在跑慢 sql	`v$session + v$sql`（查 `last_call_et`）
分析最近哪些 sql 最耗时	`v$sql` 按 `elapsed_time` 排序
长期性能趋势分析	awr 报告
深度诊断单条 sql	`10046 trace + tkprof`
监控大事务/导入导出进度	`v$session_longops`