Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Baki.

Asked: July 14, 2017 - 11:37 am UTC

Last updated: July 23, 2017 - 4:46 am UTC

Version: Oracle 11.2.0.4 EE

Viewed 1000+ times

You Asked

Hi AskTOM team,
I want to generate a detailed performance report for a sql. This report should be like Wait Event Histogram on AWR report

Time Slice : 2017-07-14 12:00:00-12:05:00

Time Slice | sql_id | total_exec | <8ms | < 16ms | < 32ms | <1s | > 1s
2017-07-14 12:00:00-12:05:00 |adfeafdak563 | 12000 | 6000 | 3000 | 1000 | 1000 | 1000
2017-07-14 12:00:00-12:05:00 |34fsfdsdf345 | 4000 | 1000 | 1000 | 500 | 500 | 1000

Is it possible to get such a report from v$active_session_history?
Thanks

and Connor said...

Not really...well not anything that you could consider truly accurate.

Dont forget, v$active_session_history is sampled once *per second*, so an SQL statement could run once, twice or hundreds of times in that one second. For the long running SQL's, would be able to pick up some info, eg

select sql_id, sql_exec_id, count(*) approx_elapsed_secs
from v$active_session_history
group by sql_id, sql_exec_id

but the faster an SQL runs, the more chance the above both

a) misses some data
b) over-estimates the data (ie, count(*) will return '1' even if the SQL took 0.1ms to run but was just captured at that particular instant)

I'd recommend with the same approach as what AWR does

- executions per time period
- total elapsed per time period

which gives an average value, and you can use the estimate query above for those really long running SQL's (which are typically the ones we most often need to target)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database