Skip to Main Content
  • Questions
  • count the number of execution for a specific SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mohammad.

Asked: February 24, 2018 - 2:09 am UTC

Last updated: February 27, 2018 - 1:47 am UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hello,

I need a query to count the number of executions and the CPU time for those executions for a specific SQL_ID in the last hour.

background:
we have a simple query which executed very frequently by our customers and it takes 0.3 sec (usually). somehow its behavior changed and started to take like 5 sec which in turn caused a concurrency issues, and we could not detect it as a long runner (5 sec for each execution).

why we need this query:
we a have a separate monitoring system to be fed by this query to detect the issue early before we have the concurrency.

if you have a better way to detect such issues (without using EM) please add it.

Best Regards

and Connor said...

v$sqlstats will show you cumulative information for the particular SQL. If you want to get each individual execution time, then you can

1) Query v$active_session_history, because if it ran for longer than 1 second, you can pick up those occurrences that have multiple rows for the same SQL_ID/SQL_EXEC_ID

2) Trace the entire database for just that SQL_ID using

alter system set events 'sql_trace[sql: gg5tyt1pvnr47] level=8';



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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;
Connor McDonald
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.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.