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'.

4 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  
Blogger xccc said...

The most in demand online social network site Twitter helps people to realize about ongoing issues and activities or social reactions of their favored superstars. here to buy twitter followers

7:17 AM  
Blogger dipali sharma said...

This solution is the best medium that you can decide for improving internet web traffic on your page as all the folks will certainly be able to view the products and also solutions that you are offering. buy usa facebook fans

7:47 AM  

Post a Comment

<< Home