Resource intensive queries

-- ################################################
-- # Creator: Vincent Fenoll, Oracle DBA in Montreal (Quebec)
-- # Created: 2011/12/23
-- # Name: Resource intensive queries
-- ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # List SQL statements, duration > xx seconds
-- #                               (here 30s)
-- #
-- ################################################

SELECT *
  FROM (  SELECT sql_id,
                 MIN (elapsed_time_delta / executions_delta / 1000000) sec_moy
            FROM dba_hist_sqlstat
           WHERE executions_delta > 0
        GROUP BY sql_id
        ORDER BY sec_moy DESC)
 WHERE ROWNUM < 40 AND sec_moy > 30;
 


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.