Useful scripts related to number of executions of sql_id in Oracle Database

Here are a few scripts that can help you find the number of executions of a specific SQL_ID in an Oracle database:

Scripts based on V$SQL view which contains information about SQL statements that have been executed in the past and are still in the shared pool. The data in this view is reset each time the database is started and can be used for tracking SQL statements over a long period of time.

To find the total number of executions for a specific SQL_ID:

SELECT sql_id, count(*)
FROM v$sql
WHERE sql_id = '&sql_id' -- enter the specific SQL_ID
GROUP BY sql_id;

To find the total number of executions for a specific SQL_ID in the last hour:

SELECT sql_id, count(*)
FROM v$sql
WHERE sql_id = '&sql_id' -- enter the specific SQL_ID
AND last_active_time > SYSDATE - (1/24)
GROUP BY sql_id;

To find the total number of executions and average elapsed time for a specific SQL_ID:

SELECT sql_id, count(*), ROUND(AVG(elapsed_time/1000000),2) "Avg Elapsed Time (s)"
FROM v$sql
WHERE sql_id = '&sql_id' -- enter the specific SQL_ID
GROUP BY sql_id;

To find the total number of executions for a specific SQL_ID for a specific period of time:

SELECT sql_id, count(*)
FROM v$sql
WHERE sql_id = '&sql_id' -- enter the specific SQL_ID
AND last_active_time BETWEEN '&start_date' AND '&end_date' -- enter the start and end date of the period in the format 'yyyy-mm-dd hh24:mi:ss'
GROUP BY sql_id;

Queries based upon dba_hist_SQLSTAT AND dba_hist_snapshot to see historical data:

To find the total number of executions and average elapsed time for a specific SQL_ID over a period of time:

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, 
to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, 
abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour
from (end_interval_time-begin_interval_time))*60 + extract(day
from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) 
"avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='<PUT_SQL_ID>' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

SELECT SQL_ID,
COUNT(SQL_ID) as "Executions",
ROUND(AVG(ELAPSED_TIME_DELTA)/1000000,2) as "Avg Elapsed Time (s)",
ROUND(AVG(CPU_TIME_DELTA)/1000000,2) as "Avg CPU Time (s)",
ROUND(AVG(BUFFER_GETS_DELTA),2) as "Avg Buffer Gets",
ROUND(AVG(DISK_READS_DELTA),2) as "Avg Disk Reads",
ROUND(AVG(EXECUTIONS_DELTA),2) as "Avg Executions"
FROM dba_hist_SQLSTAT
WHERE SQL_ID = '&sql_id' -- enter the specific SQL_ID
AND SNAP_ID BETWEEN (SELECT MIN(snap_id) FROM dba_hist_snapshot WHERE BEGIN_INTERVAL_TIME > '&start_date') AND (SELECT MAX(snap_id) FROM dba_hist_snapshot WHERE END_INTERVAL_TIME < '&end_date')
GROUP BY SQL_ID;

select INSTANCE_NUMBER, module, action, program, session_id, to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss')
from dba_hist_active_sess_history
where sample_time between to_date('2022/07/20 00:00:00','YYYY/MM/DD HH24:MI:SS') and to_date('2022/07/20 23:59:00','YYYY/MM/DD HH24:MI:SS')
and sql_id = '<PUT_SQL_ID>'
order by sample_time;

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
s.ROWS_PROCESSED_TOTAL "Total Rows Processed"
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 order by 1, 2, 3;

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';

select sql_id,
starting_time,
end_time,
(EXTRACT(HOUR FROM run_time) * 3600
+ EXTRACT(MINUTE FROM run_time) * 60
+ EXTRACT(SECOND FROM run_time)) run_time_sec,
READ_IO_BYTES,
PGA_ALLOCATED PGA_ALLOCATED_BYTES,
TEMP_ALLOCATED TEMP_ALLOCATED_BYTES
from (
select
sql_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time,
sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,
sum(DELTA_PGA) PGA_ALLOCATED,
sum(DELTA_TEMP) TEMP_ALLOCATED
from
(
select sql_id,
sample_time,
sql_exec_start,
DELTA_READ_IO_BYTES,
sql_exec_id,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,
greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP
from
dba_hist_active_sess_history
where
sample_time >= to_date ('2017/02/22 00:00:00','YYYY/MM/DD HH24:MI:SS')
and sample_time < to_date ('2017/02/22 16:10:00','YYYY/MM/DD HH24:MI:SS')
and sql_exec_start is not null
and IS_SQLID_CURRENT='Y'
)
group by sql_id,SQL_EXEC_ID,sql_exec_start
order by sql_id
)
where sql_id = '<PUT_SQL_ID>'
order by end_time desc;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Useful scripts related to number of executions of sql_id in Oracle Database

Related posts: