Oracle Performance Views

Oracle Database 10g has introduced several new wait-based performance views and has also added new wait columns to several existing performance views. The most interesting new performance views include

• V$EVENT_HISTOGRAM
• V$SYSTEM_WAIT_CLASS
• V$WAITCLASSMETRIC AND V$WAITCLASSMETRIC_HISTORY
• V$SESSION_WAIT_CLASS
• V$SESSION_WAIT_HISTORY
• V$SESS_TIME_MODEL
• V$SYS_TIME_MODEL
• V$ACTIVE_SESSION_HISTORY

Interesting wait-based enhancements made to existing performance views include

• V$EVENT_NAME
• V$SESSION
• V$SESSION_WAIT
• V$SQLAREA

New Performance Views

The V$EVENT_HISTOGRAM view contains a histogram of system waits over various wait duration intervals. For example, it might show that 100 latch-free events occurred where the wait time was 16 to 32 milliseconds and that 200 latch-free events occurred where the wait time was 32 to 64 milliseconds. You can use the following query (which filters out idle events) to view this type of information:

select  EVENT,
       WAIT_TIME_MILLI,
       WAIT_COUNT
from    V$EVENT_HISTOGRAM
where   EVENT IN
       (select NAME
        from   V$EVENT_NAME
        where  CLASS not in ('Idle'))
order by 1,2

Enhancements to Existing Performance Views

The first notable wait-related change to existing performance views occurs in V$EVENT_NAME, where a wait class column is included to identify which wait events correspond to which wait classes. This helps you see, for example, that a db file scattered read belongs to the User I/O wait class.

The next change can be found in V$SESSION, where several wait event columns have been added so you can see the current wait event being experienced by the session, along with pertinent wait-related information. This often removes the requirement to join V$SESSION and V$SESSION_WAIT. This join gets written a lot in systems preceding Oracle 10g.

In the V$SESSION_WAIT view, you'll find a wait class column that can help you identify which wait class each individual wait event belongs to.

By far the most interesting enhancement I've found is the new wait columns added to the V$SQLAREA view, which houses SQL statement execution information. Six new wait-related and timing columns have been added:

• APPLICATION_WAIT_TIME
• CONCURRENCY_WAIT_TIME
• CLUSTER_WAIT_TIME
• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME
• JAVA_EXEC_TIME

The new columns can definitely help you determine the amount of time, say, that a procedure spends in PL/SQL code versus standard SQL execution and whether the statement experienced any particular user wait time.