#acl merlyn:read,write All:read

==  Oracle elapsed time tips ==
SQL執行經過時間Oracle指標是SQL語句正在執行的經過時間。請注意，對於SQL select語句，這還包括執行查詢結果獲取所花費的時間。

另請參閱有關按時間和Oracle DB時間排序SQL的重要說明。

請記住，Oracle無法知道SQL語句的實際端到端響應時間，因為Oracle無法測量實例外部的網絡延遲。因此，AWR報告的“按經過時間排序的SQL”部分不應實際佔用執行時間。 Oracle經過時間的公式如下：

{{{
elapsed time =
   cpu time +
    user i/o wait time +
    application_wait_time +
   concurrency_wait_time +
   cluster_wait_time +
   plsql_exec_time +
   java_exec_time 
}}}



A SQL trace (10046 trace) will also show the elapsed time, as will the v$sql view.

This query will show the SQL execution elapsed time duration (in hours) for long-running SQL statements:
{{{
col program format a30

select query_runs.*,
                round ( (end_time - start_time) * 24, 2) as duration_hrs
           from (  select u.username,
                          ash.program,
                          ash.sql_id,
                          ash.sql_plan_hash_value as plan_hash_value,
                          ash.session_id as sess#,
                          ash.session_serial# as sess_ser,
                          cast (min (ash.sample_time) as date) as start_time,
                          cast (max (ash.sample_time) as date) as end_time
                     from dba_hist_active_sess_history ash, dba_users u
                    where u.user_id = ash.user_id and ash.sql_id = lower(trim('&sql_id'))
                 group by u.username,
                          ash.program,
                          ash.sql_id,
                          ash.sql_plan_hash_value,
                          ash.session_id,
                          ash.session_serial#) query_runs
order by sql_id, start_time;
}}}

雖然STATSPACK和AWR報告可以輕鬆顯示執行時間最長的排名最高的SQL，但是您可以運行字典查詢以查看運行時間最長的SQL：
{{{
select
   sql_id,
   child_number,
   sql_text,
   elapsed_time
from
   (select
      sql_id_child_number,
      sql_text,
      elaped_time,
      cpu_time,
      disk_reads,
   rank ()
   over
      (order by elapsed_time desc)
   as
      sql_rank
   from
      v$sql)
where
   sql_rank < 10;
}}}
總而言之，重要的是要注意，SQL經過時間指標與SQL語句的實際響應時間不同。