Most Used SQL Queries

Buğra PARLAYAN

2 years ago

In today’s article we will cover how we can see the Most Used or Executed SQL Queries (Last 20).

SELECT *
  FROM (  SELECT ss.sql_text,
                 a.SQL_ID,
                 SUM (CPU_TIME_DELTA),
                 SUM (DISK_READS_DELTA),
                 COUNT (*)
            FROM DBA_HIST_SQLSTAT a, dba_hist_snapshot s, v$sql ss
           WHERE     s.snap_id = a.snap_id
                 AND a.sql_id = ss.sql_id
                 AND s.begin_interval_time > SYSDATE - 1
        GROUP BY ss.sql_text, a.SQL_ID
        ORDER BY SUM (CPU_TIME_DELTA) DESC)
 WHERE ROWNUM <= 20;

SELECT *

FROM ( SELECT ss.sql_text,

a.SQL_ID,

SUM (CPU_TIME_DELTA),

SUM (DISK_READS_DELTA),

COUNT (*)

FROM DBA_HIST_SQLSTAT a, dba_hist_snapshot s, v$sql ss

WHERE s.snap_id = a.snap_id

AND a.sql_id = ss.sql_id

AND s.begin_interval_time > SYSDATE - 1

GROUP BY ss.sql_text, a.SQL_ID

ORDER BY SUM (CPU_TIME_DELTA) DESC)

WHERE ROWNUM <= 20;