How Many time was SQL run in previous times?

===============

General

===============

How Many time was SQL run in previous times?

Checking DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT

===============

SQLs

===============

per 1 hour - for a specific SQL

SELECT 'db_name' as source_db,  

       TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi'), 

       sql_id,  

       executions_delta, 

       loaded_versions, 

       invalidations_delta, 

       parse_calls_delta, 

       disk_reads_delta

  FROM DBA_HIST_SQLSTAT, 

       DBA_HIST_SNAPSHOT 

 WHERE sql_id = '71bmcyg2f5td0'   

   AND DBA_HIST_SQLSTAT.snap_id = DBA_HIST_SNAPSHOT.snap_id

ORDER BY 2 DESC;

for 24 hours

SET LINESIZE 120

SET PAGESIZE 1000

COL STARTUP_TIME FOR A16

COL SQL_TEXT FOR A100

COL executions FOR 9999999999999

SELECT startup_time, 

       executions_total as executions, 

       SUBSTR(sql_text,1,100) sql_text

FROM 

 (SELECT

    SNAP.snap_id, 

    TO_CHAR(SNAP.begin_interval_time,'YYYYMMDD hh24')||':00' as startup_time, 

    sql_id, 

    parsing_schema_name, 

    SUM(executions_total) executions_total

  FROM DBA_HIST_SQLSTAT STAT, 

       DBA_HIST_SNAPSHOT SNAP

 WHERE STAT.parsing_schema_name = 'LAB_QANFV_ALLQQ'

   AND STAT.snap_id = STAT.snap_id

   AND SNAP.begin_interval_time < TRUNC(sysdate-1) 

   AND SNAP.begin_interval_time > TRUNC(sysdate-2) 

  GROUP BY SNAP.snap_id, 

        TO_CHAR(SNAP.begin_interval_time,'YYYYMMDD hh24')||':00',

        sql_id, 

        parsing_schema_name

 HAVING SUM(executions_total) > 50000

 ORDER BY SNAP.snap_id, 

          TO_CHAR(SNAP.begin_interval_time,'YYYYMMDD hh24')||':00', 

          sql_id

 )SQLS_STATS,

 V$SQLAREA 

 SQLAREA

WHERE SQLS_STATS.sql_id = SQLAREA.sql_id

ORDER BY executions_total DESC;