Sunday, October 12, 2008

Service-related statistics

V$SERVICE_WAIT_CLASS: aggregated wait counts and wait times for each service on a wait class basis, shows all wait statistics for each service, broken down by wait class.
V$SERVICE_EVENT: aggregated wait counts and wait times for each service on a per wait class basis, shows the same information as V$SERVICE_WAIT_CLASS, except that it is further broken down by event ID.

Oracle data dictionary views
Oracle dynamic performance views

Session-related statistics

V$SESSION: stores current session information for each user logged on.
V$SESSION or V$SESSION_WAIT: used to determine the resources or events for which active sessions are waiting.
V$SESSTAT: displays user session statistics.
V$SESSION_EVENT: list information on waits for an event by a session.
V$SESSTAT and V$SYSSTAT: cumulative values for statistics are generally available through these two views.
V$MYSTAT: displays the statistics for the current session.
V$SESSMETRIC: display the performance metric values for all active sessions. The view lists performance metrics such as CPU usage, number of physical reads, number of hard parses, and the logical read ratio.

Oracle data dictionary views
Oracle dynamic performance views

Oracle system statistics

To effectively diagonse performance problems, statistics must be available. Oracle generates many types of cumulative statistics for the system, sessions, and individual SQL statements. The Oracle database also tracks cumulative statistics oln segments and services.

Waite events statistics
V$EVENT_NAME: all the possible wait events are cataloged here.
V$SYSTEM_EVENT: Cumulative statistics for all sessions are stored here and showthe total waits for a particular event since instance startup.

Systemwide statistics
V$STATNAME: all the systemwide statistics are cataloged here and Oracle 10g has about 330 statistics.
V$SYSSTAT: The server displays all calculated system statistics here. The view can be used to find cumulative totals since the instance started.
V$SYSTEM_WAIT_CLASS: all wait events for a particular wait class class.

SGA global statistics
V$SGASTAT: all caculated memory statistics. The view can be queried to find cumulative totals of detailed SGA usage since the instance started.

Oracle data dictionary views
Oracle dynamic performance views