By Franck Pachot

.
When you don’t have Enterprise Edition + Diagnostic Pack, you cannot use AWR but you can, and should, install Statspack. Statspack is still there, but unfortunately do not evolve a lot. The most important section, the ‘Top 5 Timed Events’ shows only foreground events, or is supposed to do so. When a user process waits on a background process, this section must count only the foreground wait and not the activity of the background process or we will have double accounting. The background activity is included in ‘Idle’ events in order to be excluded from this section.
But unfortunately, new versions come with new wait events, and the list of Statspack idle events is not up to date anymore.

Here is the ‘Top 5 Timed Events’ I got from a database between 22:00 and 23:00 where there is no application activity:


Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle                               6      22,049 ######   65.2
AQPC idle                                          120       3,602  30014   10.7
heartbeat redo informer                          3,568       3,601   1009   10.7
lreg timer                                       1,195       3,598   3011   10.6
direct path read                                31,221         466     15    1.4
          -------------------------------------------------------------

Humm. What can you do with that? Idle events and timers are at the top. Direct path read seem to be minimal. And no CPU usage?
Obviously, something is wrong here.

Statspack uses a fixed list of wait events that are considered as ‘idle’ events and it is stored at Statspack installation into STATS$IDLE_EVENT.
This comes from an age where wait classes were not there. In current version, a more realistic list of wait events is in V$EVENT_NAME where class_name=’Idle’

Let’s compare them (that’s in 12.1.0.1)

First, are there some non-idle events that are considered as ‘idle’ by Statspack?


SQL> select s.event statspack_idle_event,v.name,v.wait_class from STATS$IDLE_EVENT s left outer join V$EVENT_NAME v on s.event=v.name where wait_class!='Idle';
 
STATSPACK_IDLE_EVENT                    NAME                                    WAIT_CLASS
--------------------------------------- --------------------------------------- ----------
null event                              null event                              Other
SQL*Net message to client               SQL*Net message to client               Network
SQL*Net more data from client           SQL*Net more data from client           Network
KSV master wait                         KSV master wait                         Other
parallel recovery slave wait for change parallel recovery slave wait for change Other

The goal of this blog post is not to detail the meaning of each of those events (search for them on tanelpoder.com as a good start for that), but if they are now considered as non-idle, Statspack should obey the same rule.

Then we can check which idle events are not in Statspack list:


SQL> select s.event statspack_idle_event,v.name,v.wait_class from STATS$IDLE_EVENT s right outer join V$EVENT_NAME v on s.event=v.name where wait_class='Idle' and s.event is null;
 
STATSPACK_IDLE_EVENT NAME                                       WAIT_CLASS
-------------------- ------------------------------------------ ----------
                     OFS idle                                   Idle
                     heartbeat redo informer                    Idle
                     LGWR worker group idle                     Idle
                     Recovery Server waiting for work           Idle
                     Recovery Server waiting restore start      Idle
                     Recovery Server Surrogate wait             Idle
                     Recovery Server Servlet wait               Idle
                     Recovery Server Comm SGA setup wait        Idle
                     parallel recovery coordinator idle wait    Idle
                     recovery sender idle wait                  Idle
                     recovery receiver idle wait                Idle
                     recovery merger idle wait                  Idle
                     virtual circuit next request               Idle
                     lreg timer                                 Idle
                     REPL Apply: txns                           Idle
                     REPL Capture/Apply: messages               Idle
                     REPL Capture: archive log                  Idle
                     PL/SQL lock timer                          Idle
                     Emon coordinator main loop                 Idle
                     Emon slave main loop                       Idle
                     AQ: 12c message cache init wait            Idle
                     AQ Cross Master idle                       Idle
                     AQPC idle                                  Idle
                     Streams AQ: load balancer idle             Idle
                     Sharded  Queues : Part Maintenance idle    Idle
                     REPL Capture/Apply: RAC AQ qmn coordinator Idle
                     iowp msg                                   Idle
                     iowp file id                               Idle
                     netp network                               Idle
                     gopp msg                                   Idle

There are a lot of them. We can see lot of idle events that have been introduced in recent versions.

The Statspack list is an old list. Here is how to refresh it:


delete from STATS$IDLE_EVENT;
insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';
commit;

Once I did that and run a new Statspack report on the same snapshots as above, I get a more realistic ‘Top 5 Timed Events’:


Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
direct path read                                31,221         466     15   48.7
CPU time                                                       310          32.4
db file sequential read                         49,137          77      2    8.0
SQL*Net vector data to client                   15,189          31      2    3.3
enq: TM - contention                                 1          24  23937    2.5

Actually, this 22:00 to 23:00 time period is where the maintenance jobs are running. dbms_space.auto_space_advisor_job_proc likes to read your tables in bulk in order to see if there is some free space. And I don’t like this 24 seconds TM lock wait at a time where I though the database was not in use. This was hidden from the original report.

Patch?

Statspack is still supported and there’s a patch to add the following events as idle:

"virtual circuit next request" "AQ Cross Master idle" "AQ: 12c message cache init wait" "AQPC idle" "Emon coordinator main loop" "Emon slave main loop" "LGWR worker group idle" "OFS idle" "REPL Apply: txns" "REPL Capture/Apply: RAC AQ qmn coordinator" "REPL Capture/Apply: messages" "REPL Capture: archive log" "Recovery Server Comm SGA setup wait" "Recovery Server Servlet wait" "Recovery Server Surrogate wait" "Recovery Server waiting for work" "Recovery Server waiting restore start" "Sharded Queues : Part Maintenance idle" "Streams AQ: load balancer idle" "gopp msggopp msg" "heartbeat redo informer" "iowp file id" "iowp msg" "lreg timer" "netp network" "parallel recovery coordinator idle wait" "recovery merger idle wait" "recovery receiver idle wait" "recovery sender idle wait" "imco timer" "process in prespawned state"

(Nice way to be referenced by google for all those improbable wait events, isn’t it?)

However, I think that filing STATS$IDLE_EVENT from V$EVENTNAME, or maybe even replacing it as a view can be a better long term solution. Each version comes with new wait events and it seems that Statspack evolves only through patches.

Update later

I think that this page will become very dynamic as I’ll add idle events when I encounter them.
‘log file parallel write’ is background process by log writer (or rather by log writer slave since 12c, and ‘target log write size’ is the time spend by log writer activity)


insert into STATS$IDLE_EVENT values('log file parallel write');
insert into STATS$IDLE_EVENT values('target log write size');