Ferhat's Blog

There will be only one database

Archive for February, 2016

Calculating TPS from ASH via SQL_EXEC_ID

Posted by fsengonul on February 7, 2016

After a problem happened in an application server  running to a 11.2.0.4 database , we were arguing about how many queries per second had been running at the time of the problem for a sqlid . The dba_hist_sqlstat was actually not satisfactory because the snapshot period was 1 hour. The problem started at 23:45 and peaked at 23:55.

PLAN_HASH_VALUE PARSING_SCHEMA_NAME            BEGIN_INTERVAL_TIME                      EXECUTIONS_DELTA BUFFER_GETS DISK_READS ELAPSED_SN     CPU_SN

————— —————————— —————————————- —————- ———– ———- ———- ———- ——-

2579161228 TIMS                           02/02/2016 20:00:21,028                            358698     18,9402    10,5922      ,0435      ,0027

2579161228 TIMS                           02/02/2016 21:00:28,454                            350244      17,587     9,5744      ,0425      ,0022

2579161228 TIMS                           02/02/2016 22:00:33,139                            278166     11,1771     7,0515      ,0294      ,0016

2579161228 TIMS                           02/02/2016 23:00:37,352                            512135     15,4872    11,3624      ,0507      ,0021

2579161228 TIMS                           03/02/2016 00:00:41,781                            381670     22,1958    17,1788      ,0784      ,0029

And the visualization via excel:

from_dba_hist_sqlstat

So the 500K per hour (138 tps)  does not seem as an outlier when compared to the other hours statistics. But when we looked it closely from the ASH :


SELECT TO_CHAR (sample_time, 'HH24:MI'),
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
  FROM v$active_Session_history
 WHERE     sample_time BETWEEN TO_DATE ('2016-02-02 23:00',
                                        'YYYY-MM-DD HH24:MI')
                           AND TO_DATE ('2016-02-02 23:59',
                                        'YYYY-MM-DD HH24:MI')
       AND sql_id = 'd9q0btbtvr5bv'
group by TO_CHAR (sample_time, 'HH24:MI')
order by 1 asc;

So the tps has reached up to 600  when calculated from minutely averages.

And the visualization via excel :

from_ash

For more info about SQL_EXEC_ID  you may check Tanel’s post.

Advertisements

Posted in oracle | 1 Comment »