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語句的實際響應時間不同。