Database Response Time Tuning Model

If you ask your users to describe performance, they will either describe it in terms of response time or throughput. When your users call you about performance problems, it is because they are unhappy with their process ‚ s response time or throughput. They don ‚ t call you because certain database ratio numbers are bad or some SQL statements are taking a lot of CPU time, having high buffer gets or causing a lot of physical disk reads. Frankly, they don ‚ t care.

OWI plays a significant role in determining the database response time. The database response time consists of the ‚“service time ‚½ and ‚“wait time ‚½ as follows :

Response Time = ServiceTime + WaitTime

The service time is the amount of time a process spends on the CPU. The wait time is the amount of time a process waits for specific resources to be available before continuing with processing. This formula is based on the notion that at any point, a process is either actively servicing a request on the CPU or is off the CPU and in a wait state. You can improve database response time by shortening the service time, the wait time, or both. This is not hard to understand as you also practice this formula in your daily chores. Take your last trip to the grocery store. When you were getting ready to pay, your checkout response time was how long you had to wait in line plus how fast the clerk scanned your items. You normally get bad checkout response time if you shop during peak hours (high wait time), or if you are unlucky enough to get a rookie clerk who needs to look up the code numbers on most of your produce purchases (high service time). So you improve your checkout response time by shopping during off hours (low wait time) and picking an experienced clerk with whom you are familiar (low service time).

However, you should note that a total (or end- user or end-to-end) response time goes beyond the database response time. End-to-end response time includes server latencies such as queue time, context switch, memory management, and so on, as well as network and middle tier latencies, if applicable . But for our purposes, we will look exclusively at the database response time, and we may rewrite the formula in the database terminology as follows:

Response Time = CPU used when call started + S TIME_WAITED

At the session level, service time refers to the CPU used when call started statistic in the V$SESSTAT view, and wait time is the sum of TIME_WAITED for all foreground- related wait events in the V$SESSION_EVENT view for the particular session. The CPU used when call started statistic comprises three categories: CPU used for parsing, CPU used for recursive calls, and CPU used for normal work. The CPU used for parsing is tracked by the statistic parse time CPU , while the CPU used for recursive calls is tracked by the statistic recursive cpu usage . The CPU used for normal work (CPU_W) can be calculated as follows:

CPU_W = CPU used when call started ‚ parse time CPU ‚ recursive cpu usage

Note ‚  

There are statistics for CPU used by this session and C PU used when call started in the V$SYSSTAT and V$SESSTAT views. We recommend using the latter because CPU used by this session is buggy and the CPU used when call started is much more stable. Both statistics show approximately the same value
in the absence of the bug, so there is no benefit to using the CPU used by this session .

Note ‚  

CPU statistics are only updated at the end of a call, but wait event statistics happen in real time. So a long-running SQL statement will not increment the CPU statistics until it returns from its call. What this means is that the accuracy of the database response-time calculation is highly dependent on when you sample the stats.

The true measure of a session-level database response time should be obtained right before the session disconnects. Obviously, this is quite impossible to do manually. In Chapter 4, we will show you how this can be achieved using the database LOGOFF trigger. For sessions that do not disconnect, it is possible to take snapshots of ongoing session-level database response times using the following query, bearing in mind the nasty trait of CPU time reporting just discussed.

 select event, time_waited as time_spent 
 from   v$session_event 
 where  sid = &sid 
 and    event not in ( 
 'Null event', 
 'client message', 
 'KXFX: Execution Message Dequeue - Slave', 
 'PX Deq: Execution Msg', 
 'KXFQ: kxfqdeq - normal deqeue', 
 'PX Deq: Table Q Normal', 
 'Wait for credit - send blocked', 
 'PX Deq Credit: send blkd', 
 'Wait for credit - need buffer to send', 
 'PX Deq Credit: need buffer', 
 'Wait for credit - free buffer', 
 'PX Deq Credit: free buffer', 
 'parallel query dequeue wait', 
 'PX Deque wait', 
 'Parallel Query Idle Wait - Slaves', 
 'PX Idle Wait', 
 'slave wait', 
 'dispatcher timer', 
 'virtual circuit status', 
 'pipe get', 
 'rdbms ipc message', 
 'rdbms ipc reply', 
 'pmon timer', 
 'smon timer', 
 'PL/SQL lock timer', 
 'SQL*Net message from client', 
 'WMON goes to sleep') 
 union all 
 select b.name, a.value 
 from   v$sesstat a, v$statname b 
 where  a.statistic# = b.statistic# 
 and    b.name       = 'CPU used when call started' 
 and    a.sid        = &sid; 

Note ‚  

Oracle Database 10 g Release 1 classifies wait events into several classes, one of which is the Idle class. Rather than listing the unwanted idle events as shown above, you may simply exclude the Idle class. Please see Chapter 2 for more information on idle events and wait event classification.

Following is an example output from the preceding query. If you add up all the numbers in the TIME_SPENT column, you get the process ‚ s snapshot response time. In this case, it is 3,199,836 centiseconds or about 8.89 hours.

 EVENT                           TIME_SPENT 
 ------------------------------ ----------- 
 CPU used when call started      1,358,119 
 db file sequential read         1,518,787 
 SQL*Net message from dblink       191,907 
 db file scattered read             54,949 
 SQL*Net more data from dblink      44,075 
 latch free                         12,687 
 free buffer waits                   9,567 
 write complete waits                8,970 
 log file switch completion            553 
 direct path read                       97 
 local write wait                       33 
 log file sync                          32 
 SQL*Net message to dblink              24 
 db file parallel read                  14 
 direct path write                      13 
 buffer busy waits                       7 
 file open                               2 

The Database Response Time tuning model takes performance tuning to new heights by taking you closer to the real end-user performance experience. You should always have response time in mind when you sift through the bottlenecks.