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