Ferhat's Blog

There will be only one database

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.

One Response to “Calculating TPS from ASH via SQL_EXEC_ID”

  1. […] to my blog ( original […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: