Minimizing System Contention - Understanding Response Time

Problem
You want to understand what database response time is, and its relationship with wait time.

Solution
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(DB Time) = processing time + wait time

The foregoing relationship is also frequently represented as R=S + W, where R is the response time, S the service time, and W stands for the wait time. The processing time component is the actual time spent by the database processing the request. Wait time, on the other hand, is time actually wasted by the database—it’s the time the database spends waiting for resources such as a lock on a table's rows, library cache latch, or any of the numerous resources that a query needs to complete its processing. Oracle has hundreds of official wait events, a dozen or so of which are crucial to troubleshooting slow-running queries.

Do You Have a Wait Problem?
It’s easy to find out the percentage of time a database has spent waiting for resources instead of actually executing. Issue the following query to find out the relative percentages of wait times and actual CPU processing in the database:

SQL> select metric_name, value, metric_unit 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);

METRIC_NAME                     VALUE             METRIC_UNIT

-------------------------- ---------- -----------------------

Database Wait Time Ratio    11.371689          % Wait/DB_Time

Database CPU Time Ratio     87.831890           % Cpu/DB_Time

SQL>

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.