Oracle 如何在数据库中查找长时间运行的查询

嗨，

我正在尝试获取数据库的长期运行查询。我也需要这些查询的开始和结束时间。

这些长时间运行的查询在发行版中引起问题。请帮助我找出sql文本以及查询的开始和结束时间。

提前谢谢。

有很多方法可以做到这一点。对于仍在缓存中的语句，您可以从v $ sql * 视图中找到聚合信息。

例如，这是一个需要〜27s的查询:

SQL> with /* slow */ rws as (
  2    select rownum x from dual connect by level <= 1000
  3  )
  4    select count(*) from rws, rws, rws;

  COUNT(*)
----------
1000000000

SQL>
SQL> select sql_id, first_load_time, last_load_time, elapsed_time, cpu_time
  2  from   v$sql
  3  where  sql_text like 'with /* slow */ rws as (%';

SQL_ID        FIRST_LOAD_TIME     LAST_LOAD_TIME      ELAPSED_TIME   CPU_TIME
------------- ------------------- ------------------- ------------ ----------
f3mdy8usdm8j4 2017-02-14/07:01:31 2017-02-14/07:01:31     27976167   27914000

时间以微秒为表示，并且是累积的。因此，如果您多次执行相同的语句，则可以将其除以执行以获得平均值:

SQL> with /* slow */ rws as (
  2    select rownum x from dual connect by level <= 1000
  3  )
  4    select count(*) from rws, rws, rws;

  COUNT(*)
----------
1000000000

SQL>
SQL> select sql_id, first_load_time, last_load_time, elapsed_time, elapsed_time/executions avg_elapsed, cpu_time/executions avg_cpu
  2  from   v$sql
  3  where  sql_text like 'with /* slow */ rws as (%';

SQL_ID        FIRST_LOAD_TIME     LAST_LOAD_TIME      ELAPSED_TIME AVG_ELAPSED    AVG_CPU
------------- ------------------- ------------------- ------------ ----------- ----------
f3mdy8usdm8j4 2017-02-14/07:01:31 2017-02-14/07:01:31     55617239  27808619.5   27775000

如果您获得了诊断和调整包的许可，那么您可以做得更好。SQL monitor记录每个超过5秒的查询实例:

SQL> select to_char(sql_exec_start, 'dd-Mon hh24:mi'), elapsed_time, cpu_time
  2  from   v$sql_monitor
  3  where  sql_id = 'f3mdy8usdm8j4';

TO_CHAR(SQL_ ELAPSED_TIME   CPU_TIME
------------ ------------ ----------
14-Feb 07:01     27963910   27904000
14-Feb 07:02     27635004   27633000

Oracle将这些详细信息保留至少一分钟，甚至更长。这取决于它监控了多少条语句!

你也可以从AWR获得历史细节。您可以使用它来查找给定时间段内最慢的语句:

select sql_id, elapsed_time_delta/executions_delta avg_elapsed
from   sys.dba_hist_sqlstat
where  snap_id = :snap;