Re: Capturing sql statement

There's gv$sqlstats, and dba_hist_sqlstat (for historical sqls).

On gv$sqlstats there's ELAPSED_TIME which is micro seconds so you can convert that to milliseconds and divide ELAPSED_TIME / EXECUTIONS to get time per execution. For example, I use (ELAPSED_TIME/1000000)/EXECUTIONS to get seconds_per_execution.

On dba_hist_sqlstat, there EXECUTIONS_DELTA and ELAPSED_TIME_DELTA. Elap Time Delta is again microseconds , so divide to get your conversion. Then divide that by executions_delta to get your time per execution.

Thanks,
Chris Taylor

On Tue, Sep 25, 2018 at 12:02 PM Arun Chugh <arun.chugh1610_at_gmail.com> wrote:

>
> All,
>
> I have one scenario related to capturing of sql statement in history
> table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or
> more.. How do I get the sql stmt which took less time say in
> millisecond.. Any idea pleae share.
>
> Regards,
> Arun Chugh
>

--
http://www.freelists.org/webpage/oracle-l