????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;