How many of you were using Oracle 9i, Oracle 8i, or even Oracle 7 back in the old days? And how many of you have tried to tune things with these versions? You have to admit life is so much easier this days when v$active_session_history is around (it has burst to our lives in Oracle 10g and was much improved in 11g).
Saying that, I still find myself surprised again and again to realize how unfamiliar v$active_session_history is among DBAs. And it shouldn’t be. Every now and then I bump into a DBA who is not using it, and it keep surprising me. I am saying to myself: “No way!” How come this is even possible when v$active_session_history has been around for so many years? Oracle 10g has been around since 2003 and working with v$active_session_history truly makes DBA’s life much more easier and simpler.
Oracle is sampling the database every second (more or less), and then, in v$active_session_history, it holds extremely valuable information for each session that was active at the time. Each row contains sample_id and a timestamp, it holds all session identifiers (i.e sid, serial#, program, module, action, machine), sql_id that was executed with its plan_hash_value and the step in the plan that was active at that second, which object was being accessed, from which plsql program (in case it was executed from a package, procedure, function etc), what was the wait_class and wait event, what was the blocking session id (in case there was a block) and more.
So all you need to know suddenly exists in one multi-dimension flat cube. And from now on it’s up to you. You can play with it anyway you like, anyway you need. Select from it, filter it, group it, join it, pivot it, whatever. When you know your data, the sky is the limit. Take my word for it: anything becomes reachable with a SELECT.
So, for the sake of strange, weird, magnificent, peaceful, scary CORONA (aka Covid-19) times , here are 19 great things you can do with v$active_session_history (and its dba_hist_active_sess_history “twin sister”):
https://www.linkedin.com/pulse/19-great-things-you-can-do-vactivesessionhistory-part-merav-kedem/