Skip to content

Calculating TPS from ASH via SQL_EXEC_ID

Calculating TPS from ASH via SQL_EXEC_ID published on Комментариев к записи Calculating TPS from ASH via SQL_EXEC_ID нет

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;

Screen Shot 2016-02-08 at 15.50.27

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;

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика