7、寻找基于指定时间范围内的历史SQL语句

--注该查询受到awr快照相关参数的影响
-- filename:top_sql_in_spec_time.sql
--Top SQLs Elaps time and CPU time in a given time range..
--X.ELAPSED_TIME/1000000 => From Micro second to second
--X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA => How many times the sql ran

SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET LINESIZE 180
COL sql_text FORMAT a80 WRAP

  SELECT sql_text，
         dhst.sql_id，
         ROUND (x.elapsed_time / 1000000 / x.executions_delta， 3) elapsed_time_sec，
         ROUND (x.cpu_time / 1000000 / x.executions_delta， 3) cpu_time_sec，
         x.elapsed_time，
         x.cpu_time，
         executions_delta AS exec_delta
    FROM dba_hist_sqltext dhst，
         (  SELECT dhss.sql_id sql_id，
                   SUM (dhss.cpu_time_delta) cpu_time，
                   SUM (dhss.elapsed_time_delta) elapsed_time，
                   CASE SUM (dhss.executions_delta) WHEN 0 THEN 1 ELSE SUM (dhss.executions_delta) END
                      AS executions_delta
              FROM dba_hist_sqlstat dhss
             WHERE dhss.snap_id IN
                      (SELECT snap_id
                         FROM dba_hist_snapshot
                        WHERE begin_interval_time >= TO_DATE ('&input_start_date'， 'YYYYMMDD HH24:MI')
                              AND end_interval_time <= TO_DATE ('&input_end_date'， 'YYYYMMDD HH24:MI'))
          GROUP BY dhss.sql_id) x
   WHERE x.sql_id = dhst.sql_id
ORDER BY elapsed_time_sec DESC;

　　8、寻找基于指定时间范围内及指定用户的历史SQL语句

--注该查询受到awr快照相关参数的影响
--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612

SELECT DBMS_LOB.SUBSTR (sql_text， 4000， 1) AS sql，
         ROUND (x.elapsed_time / 1000000， 2) elapsed_time_sec，
         ROUND (x.cpu_time / 1000000， 2) cpu_time_sec，
         x.executions_delta AS exec_num，
         ROUND ( (x.elapsed_time / 1000000) / x.executions_delta， 2) AS exec_time_per_query_sec
    FROM dba_hist_sqltext dhst，
         (  SELECT dhss.sql_id sql_id，
                   SUM (dhss.cpu_time_delta) cpu_time，
                   SUM (dhss.elapsed_time_delta) elapsed_time，
                   CASE SUM (dhss.executions_delta) WHEN 0 THEN 1 ELSE SUM (dhss.executions_delta) END
                      AS executions_delta
              --DHSS.EXECUTIONS_DELTA = No of queries execution (per hour)
              FROM dba_hist_sqlstat dhss
             WHERE dhss.snap_id IN
                      (SELECT snap_id
                         FROM dba_hist_snapshot
                        WHERE begin_interval_time >= TO_DATE ('&input_start_date'， 'YYYYMMDD HH24:MI')
                              AND end_interval_time <= TO_DATE ('&input_end_date'， 'YYYYMMDD HH24:MI'))
                   AND dhss.parsing_schema_name LIKE UPPER ('%&input_username%')
          GROUP BY dhss.sql_id) x
   WHERE x.sql_id = dhst.sql_id
ORDER BY elapsed_time_sec DESC;

　　9、SQL语句被执行的次数

--exe_delta表明在指定时间内增长的次数
-- filename: sql_exec_num.sql
-- How many Times a query executed?
SET LINESIZE 180
SET VERIFY OFF

  SELECT TO_CHAR (s.begin_interval_time， 'yyyymmdd hh24:mi:ss')，
         sql.sql_id AS sql_id，
         sql.executions_delta AS exe_delta，
         sql.executions_total
    FROM dba_hist_sqlstat sql， dba_hist_snapshot s
   WHERE     sql_id = '&input_sql_id'
         AND s.snap_id = sql.snap_id
         AND s.begin_interval_time > TO_DATE ('&input_start_date'， 'YYYYMMDD HH24:MI')
         AND s.begin_interval_time < TO_DATE ('&input_end_date'， 'YYYYMMDD HH24:MI')
ORDER BY s.begin_interval_time;