Infrastructure at your Service

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');

5 Comments

  • Pang says:

    Thanks, it saved my time today. It is exactly I saw in 12c statspack before apply that oracle statspack patch you mentioned. Thumbs up.

  • Uwe Küchler says:

    I like the idea of using a View on V$EVENTNAME to replace STATS$IDLE_EVENT. Guess I’ll update my deployment scripts for Statspack with that.

    Shameless plug ahead:
    Alternatively, If you don’t want to fiddle with the original Tables, you might consider using my Statspack Reports for SQL Developer instead, e.g. https://oraculix.com/2015/03/24/visualizing-statspack-average-active-sessions-in-sql-developer/

    Have a nice weekend!
    Uwe

  • I am not sure it is always good idea to make such direct replacements of records.
    It is always good idea to understand what we are doing and why!
    Let’s compare list of events:
    W/O patch applied Statspack list of Idle events is bigger then ‘Idle’ class events from V$EVENT_NAME.

    select count(1) from PERFSTAT.STATS$IDLE_EVENT;

    COUNT(1)
    ———-
    135

    select count(1) from V$EVENT_NAME where wait_class=’Idle’;

    COUNT(1)
    ———-
    121
    Delta: 14 rows! Looks like Statspack’s list is longer. But…. what is the difference?
    select EVENT from PERFSTAT.STATS$IDLE_EVENT where EVENT not in (select NAME from V$EVENT_NAME where wait_class=’Idle’ );
    ….
    46 rows selected.
    Wow! This is much bigger then 14! Next query will surprise much more:
    select EVENT from PERFSTAT.STATS$IDLE_EVENT where EVENT not in (select NAME from V$EVENT_NAME);
    41 rows selected.
    So, 41 event names are not known to data dictionaly! Are they from older version or there are some typing errors – is subject for deeper investigation.

    Let’s compare from other side – what do we have in V$EVENT_NAME not in Statspack:
    select NAME from V$EVENT_NAME where wait_class=’Idle’ and name not in (select EVENT from PERFSTAT.STATS$IDLE_EVENT);
    32 rows selected.

    Hope this was useful note.

  • Gerrit Haase says:

    Hello!
    Very useful posting!

    In the 12.2 waits list from MOS there are also idle events included which are already visible in 12.1.0.2:

    select name from v$event_name where wait_class=’Idle’
    minus
    select event from perfstat.stats$idle_event
    order by 1;
    Backup Appliance Comm SGA setup wait
    Backup Appliance Servlet wait
    Backup Appliance Surrogate wait
    Backup Appliance waiting for work
    Backup Appliance waiting restore start
    PL/SQL lock timer
    gopp msg
    recovery merger idle wait

    Also note this event which is not at all covered by any patches or MOS documents, where the names are not matching:

    select ‘#’|| name ||’#’ from v$event_name where name like ‘recovery merger idle wait%’
    union
    select ‘#’|| event ||’#’ from perfstat.stats$idle_event
    where event like ‘recovery merger idle wait%’;

    ‘#’||NAME||’#’
    ——————————————————————
    #recovery merger idle wait #
    #recovery merger idle wait#

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist