DOES ASH RECORD INACTIVE AND IDLE SESSI0NS IN ORACLE?
Before diving into this topic, i was trying to find a blocking session using ASH which execute a sql statement waiting on an idle event. I tried every possible way to identify the session details. But failed due to lack of idle and inactive diagnostics collection of samples in ASH.
No, by default, ASH cannot record all the snapshot’s of idle wait’s as in the name itself says active session history! and not inactive session history. But there are options to sample idle inactive session details as well. Let us see how.
Identify the current sid of your session
kIsH@Xhydra<>select sid mysid from v$mystat where rownum <2;
MYSID
----------
312
Check the wait event of current sid. Since there is no activity performed by user, SQL*Net message to client event is posted.
20:54:02 kIsH@Xhydra<>select sid,event from v$session where sid=312;
SID EVENT
---------- ----------------------------------------------------------------
312 SQL*Net message to client
If the same is checked in ASH view, the details are not sampled. To differentiate that the idle events are not recorded, i set the time in one session.
kIsH@Xhydra<>set time on
20:49:50 kIsH@Xhydra<> <====== idle session 312 with SQL*Net message to client at this time
--Another session
kIsH@Xhydra<>col EVENT for a20
kIsH@Xhydra<>select to_char(sample_time,'dd-mm-yy hh24:mi:ss') time,event from v$active_session_history where session_id=312 order by sample_time;
TIME EVENT
----------------- --------------------
11-09-22 18:14:53 control file heartbe
at
11-09-22 18:14:54 control file heartbe
at
11-09-22 18:14:55 db file sequential r
ead
11-09-22 18:14:56 db file sequential r
ead
TIME EVENT
----------------- --------------------
11-09-22 18:14:57 db file sequential r
ead
11-09-22 18:14:58
11-09-22 18:14:59
11-09-22 18:27:44
11-09-22 18:27:45
11-09-22 18:27:47 <== last sampled data on sid 312 is at this time(current data is not sampled from 20:49:50 due to idle wait)
10 rows selected.
Enabling the hidden parameter _ash_sample_all enables the samples for idle wait events. But there is a trade off of enabling the parameter. Lot of space will be consumed into sysaux tablespace due to collection of all inactive or idle session details. Since ASH sample the session details for every second, collecting active session details into ASH can be useful for performance diagnostics. Unless there is a specific requirement to diagnose any unsolved idle wait issues, do not enable the parameter.
kIsH@Xhydra<>set lines 200 pages 1000
col "Hidden_parameter" for a20
col "It's Value" for a20
select x.ksppinm as Hidden_parameter,
y.ksppstvl "It's Value"
from
x$ksppi x
inner join x$ksppcv y on (x.indx = y.indx)
and
x.ksppinm like '%_ash_sample_all%'
order by x.ksppinm;kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<> 2 3 4 5 6 7 8
HIDDEN_PARAMETER It's Value
-------------------- --------------------
_ash_sample_all FALSE
kIsH@Xhydra<>alter system set "_ash_sample_all"=true;
System altered.
kIsH@Xhydra<>select to_char(sample_time,'dd-mm-yy hh24:mi:ss') time,event from v$active_session_history where session_id=312 order by sample_time;
TIME EVENT
----------------- --------------------
11-09-22 18:14:53 control file heartbe
at
11-09-22 18:14:54 control file heartbe
at
11-09-22 18:14:55 db file sequential r
ead
11-09-22 18:14:56 db file sequential r
ead
TIME EVENT
----------------- --------------------
11-09-22 18:14:57 db file sequential r
ead
TIME EVENT
----------------- --------------------
11-09-22 21:17:01 SQL*Net message from <== Idle events are sampled after changes
client
11-09-22 21:17:02 SQL*Net message from
client
11-09-22 21:17:03 SQL*Net message from
client
11-09-22 21:17:04 SQL*Net message from
TIME EVENT
----------------- --------------------
client
11-09-22 21:17:05 SQL*Net message from
client
11-09-22 21:17:06 SQL*Net message from
client
39 rows selected.
kIsH@Xhydra<>alter system set "_ash_sample_all"=false;
System altered.