Pages

Subscribe:

Ads 468x60px

Showing posts with label sql response time. Show all posts
Showing posts with label sql response time. Show all posts

Wednesday 23 January 2013

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.