Elapsed time or exeution time for Oracle sqls in 11g

Oracle is perhaps the best database available out there when it comes to diagnostics and performance monitoring tools. The captured data can tell us the number of times events were called and the time waited for each event.

To find the elapsed time of a sql in 10g timeframe, we usually look at v$sqlstats or dba_hist_sqlstats. The average elapsed time of a sql is equal to sum( elspsed_time) /sum(executions) from v$sqlstats or
sum(elapsed_time_delta)/sum(executions_delta) from dba_hist_sqlstat.
When a sql rans in parallel, there was a problem with the accuracy of elapsed_time.

The elapsed time adds the time spent by the individual parallel slaves for a sql. So for simple sql quries, where all the parallel slaves were busy until the end of the sql, we could still get the elapsed time by dividing the total_elapsed_time by number of parallel slaves. i.e sum (elapsed_time_delta)/sum(px_servers_executions). But what if not all slaves were busy , then the above formulate would not work.

The solution to this problem is in v$active_session_history and dba_hist_active_sess_history and the introduction of a new column SQL_EXEC_START in 11g.

Oracle background process looks at v$session information and updates v$active_session_history every second. A new column introduced in 11g called sql_exe_start now logs the time the sql started its execution.

So now you can calculate the elapsed time by looking for the maximum sampled time the sql was running with the same start time ( sql_exec_start). Sample sql script below.

Since the precision of v$active_session_history is in seconds, The precisions of the result is one second. And for sql queries taking less than 1 second, the traditional way of looking at v$sqlstat should be good enough as in most cases, those queries may not run in parallel.


I have observer that dba_hist_active_sess_history records sample data from v$active_session_history every 10 seconds. Now that would mean that the precision of elapsed_time for sqls  would be 10 seconds from dba_hist_active_sess_history table.

select sql_id,round(avg(exe_sec1),3) ela_per_exe from (

select sql_exec_start, sql_id,

  to_char(max(sample_time),'SSSSS')

- to_char(sql_exec_start,'SSSSS') exe_sec1
from v$active_session_history
where
sql_id is not null and
sql_exec_start is not null
group by sql_exec_start, sql_id
having

  to_char(max(sample_time),'SSSSS')

  -to_char(min(sample_time),'SSSSS') > 1 )
group by sql_id;