Forum Stats

  • 3,782,437 Users
  • 2,254,645 Discussions


Track historical long running queries

Is there any other way to track the long running queries from past 2 months? For example, that took more than 5 minutes? I am using the dba_hist_snapshotbut because of the default snapshot configuration, I am getting only past few days of data. I am posting the query below that might be useful for someone else. If there is anyother way please do let me know.

SELECT stat.sql_id, parsing_schema_name "SCHEMA", to_char(ss.begin_interval_time,'dd-mm-yy hh24:mi:ss') "BEGIN_TIME",  to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "END_TIME",  round(elapsed_time_total/1000000,0) "SECONDS",   stat.executions_total, dbms_lob.substr(sql_text,4000,1) "QUERY"

FROM dba_hist_sqlstat stat, dba_hist_sqltext txt, dba_hist_snapshot ss

WHERE stat.sql_id = txt.sql_id

AND stat.dbid = txt.dbid

AND ss.dbid = stat.dbid

AND ss.instance_number = stat.instance_number

AND stat.snap_id = ss.snap_id

-- AND parsing_schema_name IN ('USER_A','USER_B')

AND ss.begin_interval_time >= to_date('01-09-21', 'MM-DD-YY')

AND stat.elapsed_time_total/1000000 >= 3600 -- in microseconds (1 second = 1000000 microseconds)

ORDER BY end_time desc;

Best Answer