Mohammad Sharaf, February 25, 2018 - 9:34 pm UTC
Hi,
do u agree on this query
select t1.sql_id,t1.exec_count,t2.avg_cpu, t2.avg_cpu*t1.exec_count CPU_PER_SQL from (select sql_id , count(sql_id) exec_count from v$active_session_history
where SQL_EXEC_START > sysdate-30/(24*60)
group by sql_id) t1, (select sql_id , executions, cpu_time, decode(executions,0,'ZERO',(cpu_time/executions)/1000000) avg_cpu, sql_text from v$sqlstats) t2
where t1.sql_id = t2.sql_id
order by t1.sql_id;
February 27, 2018 - 1:47 am UTC
select sql_id , count(sql_id) exec_count from v$active_session_history
wont be valid, because I might run the same SQL 50 times, and each one takes 2 seconds. You would get a "score" of ~100.
Don't forget that ASH is a sampling activity, so something like
select sql_id , sql_exec_id, count(*) duration from v$active_session_history
group by sql_id , sql_exec_id
having count(*) >= 3
is not the *execution* count, but those sql executions that took longer than 3 seconds to run.