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;
- Building a Scalable and Secure AI Infrastructure in the Cloud - May 12, 2024
- Harnessing the Power of AI in the Cloud for Business Transformation - March 2, 2024
- Building a Resilient Cybersecurity Framework in the Oracle Cloud - January 16, 2024