sql duration 和 elapsed_time 的区别

1nodis

今天在处理一个long run sql的时候发现了这个问题，sql duration和elapsed_time的区别貌似有时候非常大.
oracle doc上关于 elaspsed_time :
ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
而sql_exec_start :
SQL_EXEC_START DATE Time when the execution started

按我的理解sql duration(当前时间或者sql执行结束时间 - sql_exec_start) 应该会和elapsed_time大体上一致。但是在做了一个查询之后发现两者区别有时候很大，请问一条sql语句中间执行会暂停下来吗（当然不是指cpu wait, I/O wait之类已经包含在elapsed_time内的那种等待）？如果有可能那么可能是什么原因呢，或者该如何解释duration远大于elapsed_time？谢谢大家！
环境是dataware house 11.2.0.3.0 非RAC, table是一个比较大的fact table。
（这下面的查询虽然我用的是sysdate-sql_exec_start,不过这条查询是刚刚结束的，OEM的sql monitor显示的duration是2:38:08，请无须深究这一点）

SQL> select sql_id,ELAPSED_TIME/1000000/3600 elapsed,(sysdate-sql_exec_start)*24 duration,status from v$sql_monitor where sql_id='18jj1716x09u4';

SQL_ID           ELAPSED   DURATION STATUS
------------- ---------- ---------- -------------------
18jj1716x09u4 .079098069 2.42805556 DONE (ALL ROWS)


SELECT  A.DOCUMENT_NO        ,
        A.LINE_NO            ,
        A.ORDER_TYPE_SRCCD   ,
        A.ORDER_COMPANY_SRCCD,
        A.CUSTOMER_SHIPPED_ID
FROM    FACT_SALES_LINE A
WHERE   NOT EXISTS
        (
                SELECT 'X'
                FROM    FACT_SALES_LINE_JDE_KEYS B
                WHERE   A.DOCUMENT_NO                           = B.DOCUMENT_NO
                        AND A.LINE_NO                           = B.LINE_NO
                        AND ltrim(rtrim(A.ORDER_TYPE_SRCCD))    = Ltrim(rtrim(B.ORDER_TYPE_SRCCD))
                        AND ltrim(rtrim(A.ORDER_COMPANY_SRCCD)) = ltrim(rtrim(B.ORDER_COMPANY_SRCCD))
        )

kevinlin.ora

我的环境下sql duration 总是大于elapsed_time。像sql duration>elapsed_time 很难理解的事情。


11:40:45 SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

1nodis

kevinlin.ora 发表于 2013-7-5 11:52
我的环境下sql duration 总是大于elapsed_time。像sql duration>elapsed_time 很难理解的事情。

是的，是总是大于。我的理解elapsed_time基本上就是包括了总的执行/等待时间，而duration的话从sql进入sga就开始算起所以应该是大于等于的关系。但是不应该会有这么大的差距吧？但是就这么一条简单的select而且也就执行了一次，我唯一能想到的只有中途有过暂停……有这种可能吗？

1nodis

1nodis 发表于 2013-7-6 10:41
是的，是总是大于。我的理解elapsed_time基本上就是包括了总的执行/等待时间，而duration的话从sql进入sg ...

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Maclean Liu(刘相兵

我打开一个cursor 游标 不代表我一直在执行它

SQL> set timing on;
SQL> declare
  2  cursor C is select /*+ monitor Maclean */ 1  from dba_tables;
  3  v_int int;
  4  begin
  5  for i in C loop
  6  dbms_lock.sleep(0.01) ;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:31.61


SQL> select (last_refresh_time-sql_exec_start)*24*3600 ,ELAPSED_TIME/1000000 from v$SQL_MONITOR where sql_text like '%Maclean%';

(LAST_REFRESH_TIME-SQL_EXEC_START)*24*3600 ELAPSED_TIME/1000000
------------------------------------------ --------------------
                                        31              .114518

Elapsed: 00:00:00.00

Maclean Liu(刘相兵

对比：

SQL> declare
  2  cursor C is select /*+ monitor Maclean1 */ 1  from dba_tables;
  3  v_int int;
  4  begin
  5  for i in C loop
  6  null;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
SQL>  select (last_refresh_time-sql_exec_start)*24*3600 ,ELAPSED_TIME/1000000 from v$SQL_MONITOR where sql_text like '%Maclean1%';

(LAST_REFRESH_TIME-SQL_EXEC_START)*24*3600 ELAPSED_TIME/1000000
------------------------------------------ --------------------
                                         0              .101893

Elapsed: 00:00:00.01

1nodis

Maclean Liu(刘相兵 发表于 2013-7-7 01:49
对比：

SQL> declare

感谢刘大的解释！这么说起来确实cursor打开的时间并不等于执行时间。那通常有什么情况会使得游标打开但是较长时间不执行（暂停或等待）呢，尤其是对于隐式游标来说的话？