Query Execution Time

From: <amerar_at_zacks.com>
Date: Fri, 7 Jan 2011 10:04:33 -0800 (PST)
Message-ID: <41ca8c89-0fe7-4fd4-a773-e789a0c9431f_at_i18g2000yqn.googlegroups.com>

Ok, I'm lost. I was trying to calculate the query execution time for queries in the shared pool. I used this type of sql.

Now, when I look at a given statement I ran using "set timing on" it gave me 6 seconds. ELAPSED_TIME gave me 2840. The query above gives me 0 seconds.

Is there something wrong with how I am calculating the time?

   select username, sql_id, EXECUTIONS, ROWS_PROCESSED, elapsed_time, last_active_time,
(case

     when trunc(elapsed_time/1000000)<60 then to_char(trunc(elapsed_time/1000000))||' Sec(s)'

     when trunc(elapsed_time/1000000/60)<60 then to_char(trunc(elapsed_time/1000000/60))||' Min(s)'

     when trunc(elapsed_time/1000000/60/60)<24 then to_char(trunc(elapsed_time/1000000/60/60))||'Hour(s)'

     when trunc(elapsed_time/1000000/60/60/24)>=1 then to_char(trunc(elapsed_time/1000000/60/60/24))||' Day(s)'  end) as time, disk_reads, sql_text, executions,

          to_char((((disk_reads+buffer_gets)/executions) * 8192)/ 1048576,'9,999,999,990.00') as total_gets_per_exec_mb,

          to_char((( disk_reads /executions) * 8192)/ 1048576,'9,999,999,990.00') as disk_reads_per_exec_mb,

          to_char((( buffer_gets /executions) * 8192)/ 1048576,'9,999,999,990.00') as buffer_gets_per_exec_mb

   from v$sqlarea s, all_users u
   where parsing_user_id = user_id
   and executions > 0
   and username = 'SYS'
   and sql_id = 'fsggy2qb1mmy0'
   order by 5 desc; Received on Fri Jan 07 2011 - 12:04:33 CST