Checking DB Response Time

Understanding Response Time

The most crucial performance indicator in a database is response time. Response time is the time it
takes to get a response from the database for a query that a client sends to the database. Response time
is simply the sum of two components:

response time = processing time + wait time

R=S+W
RESPONCE = SERVICE + WAIT

**********************************************
Issue the following query on V$SYSMETRIC to find out the relative percentages of wait times and actual CPU
processing in the database:

select metric_name, value
from v$sysmetric
where metric_name in ('Database CPU Time Ratio',
'Database Wait Time Ratio') and
intsize_csec =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);

If the query shows a very high value for the Database Wait Time Ratio, or if the Database Wait Time
Ratio is much greater than the Database CPU Time Ratio, the database is spending more time waiting
than processing and you must dig deeper into the Oracle wait events to identify the specific wait events
causing this.

**********************************************
DETAILED INFORMATION OF WAITS
**********************************************
You can use the following Oracle views to find out detailed information of what a wait event is actually
waiting for and how long it has waited for each resource.

V$SESSION
V$SESSION_WAIT
V$SESSION_WAIT_HISTORY
V$SESSION_EVENT
V$SYSTEM_EVENT
V$SYSTEM_WAIT_CLASS

***********************************************

V$SESSION: This view shows the event currently being waited for as well as the
event last waited for in each session.

V$SESSION_WAIT: This view lists either the event currently being waited for or the
event last waited on for each session. It also shows the wait state and the wait
time.

V$SESSION_WAIT_HISTORY: This view shows the last ten wait events for each
current session.

V$SESSION_EVENT: This view shows the cumulative history of events waited on
for each session. The data in this view is available only so long as a session is
active.

V$SYSTEM_EVENT: This view shows each wait event and the time the entire
instance waited for that event since you started the instance.

V$SYSTEM_WAIT_CLASS: This view shows wait event statistics by wait classes.