Calculating Response Time and Statistics

Calculating Response Time and Statistics for Oracle Performance Tuning

According to so many books, the response time R correspond to a SQL trace file is identified as the sum of the elapsed time exhausted in database calls (e values) at recursive call depth 0 (dep=0) plus the sum of all elapsed values from inter and recursive database call wait events. The wait time (elapsed values) collected while routing a database call is rolled up into the parameter e of the database call that stimulated the wait. The classification of wait events is pertained in the calculation of R. Time used up waiting for intra database call wait events (all calls including recursive) must not be added to R, since this would outcome in double calculating. The e values of a database call already include the wait time of all intra database call wait events. Database calls are produced to trace files upon conclusion. This is why WAIT entries for intra database call wait events become visible before the PARSE, EXEC, and FETCH entries that engendered them. Runtime statistics, such as consistent reads, physical writes, and db block gets at recursive call depths other than zero are rolled up into PARSE, EXEC, and FETCH calls at recursive call depth 0. Just like ela values of intra database call wait events, these must not be double calculated. To promote a thorough understanding of how an extended SQL trace profiler calculates a resource, we should need to understand properly its mechanism. Then and only we are able to calculate total response time matrix as well as root cause of SQL statement for performing poor.

Database call statistics at recursive call (internal calls implicit cursors) deepness other than zero are revolved up into the statistics at recursive call depth 0. To determine the total number of db block gets in the trace file, we must think only cu parameter values of PARSE, EXEC, and FETCH entries with dep=0. The database call parameter cu (for current read) keep in touches to the statistic db block gets. The fact that the total number of db block gets as determined by querying V$SESSTAT was nine confirms that database call statistics at lower levels are rolled up into statistics at recursive call depth 0. This is methodology of calculation of response time from v$sesstat and if you get trace file then you can get whole idea about response time matrix of problematic SQL.

 

How to Get Success in Oracle Performance Tuning?

This topic has been discussing so many times in different blog and different books. Every Oracle DBA is interesting to get success in Oracle performance tuning. I have published so many articles on same topic. Still I am asked same query by so many Oracle DBAs. Those Oracle DBAs are very keen to get answer to solve this mystery.

Oracle performance tuning doesn’t like mystery. Performance slowness would start in your database server and slowly it would be impacting all active sessions. Performance bottleneck doesn’t occur immediately and reflect drastically. But it started from very beginning and increasing rapidly sometimes. If you are well experienced Oracle DBA then you can judge for future impacts of some incidents. These symptoms and characteristics can be judged using constant monitoring of databases. If you are constantly monitoring your databases, then you can able to mark some incidents like buffer cache hit ratio suddenly dropped, temp segment usage suddenly improving, physical read is increasing, etc.

If you are keen in system administration then you would get these all characteristics of performance bottleneck very quickly and easily. Because in Linux and Unix environment so many utilities available to monitor Oracle server accurately about usage and load. As per my own opinion, I always use system administration tools for server monitoring like "top" command or "vmstat" command. Those are best monitoring tools.

Oracle installs on operating system. We need to concentrate on server performance and system load. If server has too much load then obviously Oracle database performance would affect. There is no question about it. So many performance issues are starting from Oracle server consumption only. It is just like health of people. If your stomach is not healthy then you would get so many health problems at any part of body.

I got one very good article on performance tuning of Oracle server from a resource. Oracle Performance Tuning Troubleshooting is art and techniques. To get expertise in tuning, you need to get various kind of knowledge like system administration, network administration, hardware, and applications. Without that knowledge, it is very difficult to understand main reason behind server slowness. In another word we can say it "without knowledge of application, system, network, hardware, Oracle server tuning becomes mystery for you".