Tuesday, May 30, 2006

event 'events in waitclass Other' in Oracle 10.2

From release to release Oracle constantly increases the number of events (select count(*) from v$event_name). For example, Oracle 9i has 405 events, Oracle 10.1 has 811 events and Oracle 10.2 has 874 ones.

We can monitor session's wait events via v$session_event and system's ones via v$system_event.

The tricky point here is that in 10.2 a number of events (588, to be exact) are grouped into one event in v$session_event. It's almost all events of wait class (v$event_name.wait_class) 'Other'. In Oracle 10.2 there are 590 events of wait class 'Other' and 284 remaining events:

SQL> select count(*) from v$event_name where wait_class='Other';

COUNT(*)
----------
590

SQL> select count(*) from v$event_name where wait_class!='Other';

COUNT(*)
----------
284

And v$session_event can store information only for 286 events out of 874 ones. Here's some kind of proof:

v$session_event is based on fixed table x$ksles. Below is the definition of gv$session_event taken from v$fixed_view_definition:

select
s.inst_id, -- INST_ID NUMBER
s.kslessid, -- SID NUMBER
d.kslednam, -- EVENT VARCHAR2(64)
s.ksleswts, -- TOTAL_WAITS NUMBER
s.kslestmo, -- TOTAL_TIMEOUTS NUMBER
round -- TIME_WAITED NUMBER
(s.kslestim / 10000),
round -- AVERAGE_WAIT NUMBER
(s.kslestim / (10000 * s.ksleswts), 2),
round -- MAX_WAIT NUMBER
(s.kslesmxt / 10000),
s.kslestim, -- TIME_WAITED_MICRO NUMBER
d.ksledhash, -- EVENT_ID NUMBER
d.ksledclassid, -- WAIT_CLASS_ID NUMBER
d.ksledclass#, -- WAIT_CLASS# NUMBER
d.ksledclass -- WAIT_CLASS VARCHAR2(64)
from
x$ksles s,
x$ksled d
where
s.ksleswts != 0 and s.kslesenm = d.indx

Note that x$ksles is a fixed table. It means that it resides in fixed area of SGA and it can't be changed (grow/shrink) when instance is running. In other words, it's just a fixed piece of memory which changes its size only after you change 'sessions' parameter.

The field x$ksles.kslesenm is an event# from v$event_name.

Let's see how many events indexes we have in x$ksles:

SQL> select count(distinct kslesenm) from x$ksles;

COUNT(DISTINCTKSLESENM)
-----------------------
286

286! If we select events from v$session_event ordered by event# then first 286 events will be exactly these events.

SQL> select event#,name,wait_class from v$event_name order by event#;
0 null event Other
1 pmon timer Idle
2 rdbms ipc message Idle
...
283 JS kill job wait Administrative
284 JS coord start wait Administrative
285 events in waitclass Other Other

See that last event with event#=285 ? Named as 'events in waitclass Other'. It's some kind of pseudo-event. When session generates any events with event# more than 285 (all of such events are in class 'Other') then they are reflected in v$session_events as one event 'events in waitclass Other'.

2 Comments:

Blogger Alex Gorbachev said...

Интересное замечание.
At least V$SYSTEM_EVENT (based on x$kslei) tracks all 875 waits as "select count(distinct indx) from x$kslei" returns 875.
It also contains wait id 285 "events in waitclass Other" so some kind of duplicate accounting - be careful.

11:26 AM  
Blogger steve7876 said...

Nice info about Oracle last couple of days I am searching this post. At last we find that I think It's provide me a lot of benefits in future. Mobile wallet

11:36 PM  

Post a Comment

<< Home