19.1 Idle Events

---

An idle event is idle because nothing is happening. An idle event is a result of a process busy waiting for something else to respond. Idle events are generally insignificant as far as performance is concerned although they can sometimes show a distinct lack of activity, a lack of use of resources, or simply over configuration. High idle times in specific areas such as SQL*Net network usage is quite normal.

However, high idle times on SQL*Net configuration could indicate over-configuration of shared servers for instance or a client process waiting for submission of a command to the database. Over-configuration is not really a problem except that hardware resources are wasted and could be better utilized elsewhere.

All possible database events are listed in the V$EVENT_NAME view. This view contains a lot of entries. Events in this view are applicable to all Oracle software options such as Oracle Replication and Oracle Real Application Clusters. This book covers only Oracle Database tuning and does not delve into Oracle optional applications and architectures other than Oracle Partitioning. My concurrently active OLTP database has specific events in its simulation. These following events using the displayed query are generally idle and irrelevant for anything but information. I can isolate idle events with a query such as this.

SELECT name FROM v$event_name WHERE name LIKE '%null%' OR name LIKE '%timer%' OR name LIKE '%SQL*Net%' OR name LIKE '%rdbms ipc%' OR name LIKE '%ispatcher%' OR name LIKE '%virtual circuit%' OR name LIKE '%PX%' OR name LIKE '%pipe%' OR name LIKE     '%message%' OR name LIKE 'jobq%';

Note 

 Oracle Database 10 Grid   The number of idle events is expanded.

There are a lot of rows returned as idle events so I will not display the results here. The query is placed into a function as shown below for ease of use in queries in the rest of this book against the V$SYSTEM_EVENT performance view. This is the function.

CREATE OR REPLACE FUNCTION IdleEvent(pEvent IN VARCHAR2    DEFAULT NULL) RETURN VARCHAR2 IS       CURSOR cIdleEvents IS             SELECT name FROM v$event_name             WHERE name LIKE '%null%' OR name LIKE                 '%timer%'             OR name LIKE '%SQL*Net%' OR name LIKE '%rdbms                 ipc%'             OR name LIKE '%ispatcher%' OR name LIKE                 '%virtual circuit%'             OR name LIKE '%PX%' OR name LIKE '%pipe%'                 OR name LIKE '%message%' OR name LIKE 'jobq%'; BEGIN       FOR rIdleEvent in cIdleEvents LOOP             IF pEvent = rIdleEvent.name THEN                   RETURN NULL;             END IF;       END LOOP;       RETURN pEvent; EXCEPTION WHEN OTHERS THEN       DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE)); END; / ALTER FUNCTION IdleEvent COMPILE; /

Tip 

If using STATSPACK the STATS$IDLE_EVENTS table can be used.

The only idle events that warrant examination are those to do with Oracle networking software. On rare occasions excessive occurrence of SQL*Net idle events could possibly indicate bottleneck issues with the network. However, as already stated, excessive network activity is often normal. Other chapters in this book cover Oracle network usage tuning.

Another idle event occurring often in my database is the jobq slave wait event. This wait event does not indicate a problem. It occurs because I am executing multiple jobs simultaneously on the same data to simulate a highly active concurrent OLTP database. The intention is to produce tunable problems, which it most certainly does. When the background processes that are used to service jobs find that there are no queued jobs to be processed they will enter into this wait state.

Let's examine significant database and wait events.

---