DOES ASH RECORD INACTIVE AND IDLE SESSI0NS IN ORACLE?

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.

Leave a Reply