Re: Query Execution Time

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Thu, 13 Jan 2011 10:03:24 +0100
Message-ID: <4d2ebfdc$0$14257$ba620e4c_at_news.skynet.be>

amerar_at_zacks.com wrote:
> 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;
>
>

trunc(2840/1000000) might very well return 0, no? Received on Thu Jan 13 2011 - 03:03:24 CST