查找运行时间超过5秒的查询

Elapsed_Time是运行SQL语句的所有时间的累计时间。因此,对于频繁执行的查询来说,它将是很高的。

这是一个快速的查询(提示样式注释是为了获取v$sqlarea中的sql_id):

select /*+ fast_running_query */ id
from big_table
where id = 1
/

跑多长时间?这么长:

SQL> select elapsed_time
  2         , executions
  3         , elapsed_time / executions as avg_ela_time
  4  from v$sqlarea
  5  where sql_id = '73c1zqkpp23f0'
  6  /

ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
      235774          1       235774

SQL> 

由于解析时间的原因,这是一个相对较大的O’microsecs块。我们可以看到,多运行几次并不会增加运行时间,而且平均值要低得多:

SQL> r
  1  select elapsed_time,
  2         executions,
  3         elapsed_time / executions as avg_ela_time
  4  from v$sqlarea
  5* where sql_id = '5v4nm7jtq3p2n'

ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
      237570          3        79190

SQL>

再运行100000次后…

SQL> r
  1  select elapsed_time,
  2         executions,
  3         elapsed_time / executions as avg_ela_time
  4  from v$sqlarea
  5* where sql_id = '5v4nm7jtq3p2n'

ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
     1673900     100003   14.3809724

SQL>

现在,你想要的是找到活动的会话,这些会话持续做了超过5秒的事情。因此,您需要会话级别的计时,尤其是V$session上的最后一个“调用”ET,它是会话执行某项操作(如果其状态为“活动”)的秒数,或者是自上次操作(如果其状态为“非活动”)以来经过的总时间。

select sid
       , serial#
       , sql_address
       , last_call_et
from v$session
where status = 'ACTIVE'
and last_call_et > sysdate - (sysdate-(5/86400))
/

所以,考虑这个查询。很慢:

SQL> select /*+ slow_running_query */ *
  2  from big_table
  3  where col2 like '%whatever%'
  4  /

no rows selected

Elapsed: 00:00:07.56
SQL>

它足够长,可以在v$session上使用查询进行监视。这个搜索已经运行3秒以上的语句…

SQL> select sid
  2         , serial#
  3         , sql_id
  4         , last_call_et
  5  from v$session
  6  where status = 'ACTIVE'
  7  and last_call_et > sysdate - (sysdate - (3/86400))
  8  and username is not null
  9  /

       SID    SERIAL# SQL_ID        LAST_CALL_ET
---------- ---------- ------------- ------------
       137          7 096rr4hppg636            4
       170          5 ap3xdndsa05tg            7

SQL>

瞧!

SQL> select sql_text from v$sqlarea where sql_id = 'ap3xdndsa05tg'
  2  /

SQL_TEXT
--------------------------------------------------------------------------------
select /*+ slow_running_query */ * from big_table where col2 like '%whatever%'

SQL>

“它应该给我一份查询列表 目前正在执行或已经 最近执行完毕,我可以 查找查询花费的总时间 完成“

v$sqlarea视图上的最后一次活动时间记录了语句执行的最新时间。但是,没有任何视图显示语句每次执行的度量。如果你想要这样的细节,你需要开始追踪。这是一个单独的问题。

使用跟踪:

# alter session set timed_statistics = true; 
# alter session set sql_trace = true; 
.....
# show parameter user_dump_dest 
$ tkprof <trc-файл> <txt-файл>