When running this type of report, always remember statistics are gathered from startup, and the numbers could be skewed.
For example, for a system that has been running for six months, the data dictionary hit ratio will be a running average over six months. Consequently, data from the V$ structures is meaningless if you want to measure today's statistics.
As objects such as stored procedures and triggers become more popular, more application code will move away from external programs and into the database engine. Oracle is committed to providing technology and in Oracle 10g we can store and deploy applications with all of the process logic stored either in
- Java or
- PL/SQL,
all stored inside the database. However, the Oracle DBA must be conscious of the increasing memory demands of stored procedures, and carefully plan for the days when all of the database access code resides within the database.
Database statistics provide information on the type of load on the database and the internal and external resources used by the database. This section describes some of the more important statistics.
Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing. Wait event data reveals various symptoms of problems that might be impacting performance, such as latch contention, buffer contention, and I/O contention. To enable easier high-level analysis of the wait events, events are grouped into classes.
The classes include: Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O, and User I/O. The wait classes are based on a common solution that usually applies to fixing a problem
with the wait event. For example, exclusive TX locks are generally an application level issue and HW locks
are generally a configuration issue. The following list includes common examples of the waits in some of the classes:
- Application: locks waits caused by row level locking or explicit lock commands
- Commit: waits for redo log write confirmation after a commit
- Idle: wait events that signify the session is inactive, such as SQL*Net message from client
- Network: waits for data to be sent over the network
- User I/O: wait for blocks to be read off a disk
Wait event statistics for an instance include statistics for both background and foreground processes. Because you would typically focus your effort in tuning foreground activities,
overall instance activity is broken down into foreground and background statistics in the relevant V$ views to facilitate tuning.
The V$SYSTEM_EVENT view shows wait event statistics for the foreground activities of an instance and the wait event statistics for the instance. The V$SYSTEM_WAIT_CLASS view shows these foreground and wait event instance statistics after aggregating to wait classes.
V$SESSION_EVENT and V$SESSION_WAIT_CLASS show wait event and wait class statistics at the session level.
