Idle Waits

Idle Waits

One problem with Oracle's wait interface when it was introduced in version 7 was that there was no documentation on the wait events and to make matters worse, many of the wait events were "idle", ie the meant that the processes had no work to do and were just waiting. For example if I run SQL*PLUS but don't actually run any queries, then my session in the database will report that it's waiting for "SQL*Net message to client", meaning the session is ready and waiting to execute queries but I'm not giving it any. Once I submit a query then my state would change to either running on the CPU or some actual non-idle wait event like waiting for IO. But once my query finishes executing and the results are given back to the user in SQL*Plus then the session state in the database goes back to "SQL*Net message to client". The wait event "SQL*Net message to client" is only one of many idle wait events that don't signify bottlenecks but if I don't know they are idle events, then it might look like my database has huge bottlenecks. This confusion between real wait events and idle wait events and the lack of documentation slowed the adoption of the wait interface by DBAs and performance analysts. Luckily now it's easy to get the list of idle wait events and create a list of events to ignore with the query:

select name

from v$event_name

where wait_class='Idle';

58 Rows

If on Oracle 9i or below, you can install statspack which has a table STATS$IDLE_EVENT that lists the idle events. These are events we can ignore when looking at overall database load. They could be relevant when looking at one session. For example, if a session is suppose to be running and we see lots of idle waits, its a signal that the application is inefficiently using the database. For example if the application is inserting 1000s of rows, but inserting one at a time, then a lot of time will be spent on communication between the application and the database session. In this case we will see the session spending a lot of time waiting on idle events when we'd expect it to be on CPU. The solution in that case would be to use batch processing. For example

Batching vs Single row operations

Single row operations, lots of communication , slowest

-- slowest : single row inserts with commit

insert into foo values ...;

insert into foo values ...;

insert into foo values ...;

insert into foo values ...;

Send all the info over the wire to the database, one communication

-- For Loop

FOR i IN 1..i LOOP

INSERT INTO foo VALUES (id(i), data(i));

END LOOP;

COMMIT;

Send all the info over the wire to the database, one communication, bulk insert

-- FORALL

FORALL i IN 1..i

INSERT INTO foo VALUES (id(i), data(i));

COMMIT;

see http://www.di.unipi.it/~ghelli/didattica/bdl/B19306_01/appdev.102/b14261/tuning.htm#i48876