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;

amerar (AT) zacks (DOT) com wrote:
trunc(2840/1000000) might very well return 0, no?