Find start and end execution time of a sql statement?

[zigz...@yahoo.com]

Find start and end execution time of a sql statement?
I am have databases with 10.2.0.3 and 9.2.0.8 on HP UNIX 11i and
Windows 200x.

I am not in a position to turn on sql tracing in production
environment. Yet, I want to find when a sql statement started
executing and when it ended. When I look at v$sql, it has information
such FIRST_LOAD_TIME, LAST_LOAD_TIME etc. No where it has information
last time statement began execution and when it ended execution.. It
shows no of executions, elapsed time etc, but they are cumulative. Is
there a way to find individual times (time information each time a sql
statement was executed. – its start time, its end time ….)? If I were
to write my own program how will I do it?

Along the same line, when an AWR snapshot is shown, does it only
include statements executed during that snapshot or it can have
statements from the past if they have not been flushed from shared
memory. If it only has statements executed in the snapshot period, how
does it know when statement began execution?

[joel garry]

On May 27, 7:58 pm, zigzag...@yahoo.com wrote:
> Find start and end execution time of a sql statement?
> I am have databases with 10.2.0.3 and 9.2.0.8 on HP UNIX  11i and
> Windows 200x.
>
> I am not in a position to turn on sql tracing in production
> environment. Yet, I want to find when a sql statement started
> executing and when it ended. When I look at v$sql, it has information
> such FIRST_LOAD_TIME, LAST_LOAD_TIME etc. No where it has information
> last time statement began execution and when it ended execution.. It
> shows no of executions, elapsed time etc, but they are cumulative. Is
> there a way to find individual times (time information each time a sql
> statement was executed. – its start time, its end time ….)? If I were
> to write my own program how will I do it?

I haven't run it yet, but check out http://ashmasters.com/ash-simulation/
(found from following link on Doug Burns blog). A brief glance at the
script and I see it keeps time of collection and elapsed time, so if
there isn't something more specific you should be able to figure
start, stop and elapsed times.

jg
--
@home.com is bogus.
http://www.joeydevilla.com/2008/05/27/happy-51st-birthday-siouxsie-sioux/
word: surmorig