save to my blog ( original )

to calculate tps by sql_id we can use sql_exec_id , ash and exel

SELECT TO_CHAR (sample_time, 'HH24:MI'),inst_id,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = 'b6asbgkghps5h' and sample_time > sysdate-5/24
group by TO_CHAR (sample_time, 'HH24:MI'),inst_id
order by 1 asc;

or to split results it frame by 10 minutes:

select date#,"'1'" as first_node,"'2'" as second_node from (
SELECT TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60) as date#,instance_number,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_10_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = '77qx41mkwcm92' 
group by TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60),instance_number
order by 1 asc )
pivot 
(
   sum(EXECUTIONS_PER_10_MINUTE)
   for instance_number in ('1'  ,'2' )
) order by date# asc;

one more type of view

select * from (
select s.INSTANCE_NUMBER,s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and st.sql_id=s.sql_id
and s.sql_id='8xjwqbfwwppuf'
-- and st.sql_text not like '/* SQL Analyze%'
--and s.sql_id in ( select p.sql_id from dba_hist_sql_plan p where p.object_name=’OPN_HIS’)
and ss.begin_interval_time > sysdate-14
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id,s.INSTANCE_NUMBER )
pivot ( sum(execs) for instance_number in (1,2 )
) order by 1;

and result

or even that way

select * from (
select s.sql_id,
sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
and st.sql_id=s.sql_id
-- and st.sql_text not like '/* SQL Analyze%'-- and s.sql_id in ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS')
and ss.begin_interval_time > sysdate-7
group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
pivot ( sum(execs) for sql_id in (
'8xjwqbfwwppuf' ,'14crnjtpxh9aa')
) order by 1;