在Oracle中，如何用SQL实现AWR报告中Load Profile部分？

WITH TMP_S AS
 (SELECT CURR_REDO - LAST_REDO REDOSIZE,
         CURR_LOGICALREADS - LAST_LOGICALREADS LOGICALREADS,
         CURR_PHYSICALREADS - LAST_PHYSICALREADS PHYSICALREADS,
         CURR_EXECUTES - LAST_EXECUTES EXECUTES,
         CURR_PARSE - LAST_PARSE PARSE,
         CURR_HARDPARSE - LAST_HARDPARSE HARDPARSE,
         DECODE((CURR_TRANSACTIONS - LAST_TRANSACTIONS),
                0,
                NULL,
                (CURR_TRANSACTIONS - LAST_TRANSACTIONS)) TRANSACTIONS,
         ROUND(((CURRTIME + 0) - (LASTTIME + 0)) * 3600 * 24, 0) SECONDS,
         TO_CHAR(CURRTIME, 'yyyy-mm-dd') SNAP_DATE,
         TO_CHAR(CURRTIME, 'hh24:mi') CURRTIME,
         TO_CHAR(LASTTIME, 'YYYY-MM-DD HH24:MI') || '~' ||
         TO_CHAR(CURRTIME, 'YYYY-MM-DD HH24:MI') SNAP_TIME_RANGE,
         CURRSNAP_ID ENDSNAP_ID,
         TO_CHAR(STARTUP_TIME, 'yyyy-mm-dd hh24:mi:ss') STARTUP_TIME,
         SESSIONS || '~' || CURRSESSIONS SESSIONS,
         CURSORS1 || '~' || CURRCURSORS CURSORS2,
         INSTANCE_NUMBER
    FROM (SELECT A.REDO LAST_REDO,
                 A.LOGICALREADS LAST_LOGICALREADS,
                 A.PHYSICALREADS LAST_PHYSICALREADS,
                 A.EXECUTES LAST_EXECUTES,
                 A.PARSE LAST_PARSE,
                 A.HARDPARSE LAST_HARDPARSE,
                 A.TRANSACTIONS LAST_TRANSACTIONS,
                 A.SESSIONS,
                 TRUNC(A.CURSORS  A.SESSIONS, 2) CURSORS1,
                 LEAD(A.REDO, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_REDO,
                 LEAD(A.LOGICALREADS, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_LOGICALREADS,
                 LEAD(A.PHYSICALREADS, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_PHYSICALREADS,
                 LEAD(A.EXECUTES, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_EXECUTES,
                 LEAD(A.PARSE, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_PARSE,
                 LEAD(A.HARDPARSE, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_HARDPARSE,
                 LEAD(A.TRANSACTIONS, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURR_TRANSACTIONS,
                 B.END_INTERVAL_TIME LASTTIME,
                 LEAD(B.END_INTERVAL_TIME, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURRTIME,
                 LEAD(B.SNAP_ID, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURRSNAP_ID,
                 LEAD(A.SESSIONS, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURRSESSIONS,
                 LEAD(TRUNC(A.CURSORS  A.SESSIONS, 2), 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) CURRCURSORS,
                 B.STARTUP_TIME,
                 B.INSTANCE_NUMBER
            FROM (SELECT SNAP_ID,
                         DBID,
                         INSTANCE_NUMBER,
                         SUM(DECODE(STAT_NAME, 'redo size', VALUE, 0)) REDO,
                         SUM(DECODE(STAT_NAME,
                                    'session logical reads',
                                    VALUE,
                                    0)) LOGICALREADS,
                         SUM(DECODE(STAT_NAME, 'physical reads', VALUE, 0)) PHYSICALREADS,
                         SUM(DECODE(STAT_NAME, 'execute count', VALUE, 0)) EXECUTES,
                         SUM(DECODE(STAT_NAME, 'parse count (total)', VALUE, 0)) PARSE,
                         SUM(DECODE(STAT_NAME, 'parse count (hard)', VALUE, 0)) HARDPARSE,
                         SUM(DECODE(STAT_NAME,
                                    'user rollbacks',
                                    VALUE,
                                    'user commits',
                                    VALUE,
                                    0)) TRANSACTIONS,
                         SUM(DECODE(STAT_NAME, 'logons current', VALUE, 0)) SESSIONS,
                         SUM(DECODE(STAT_NAME,
                                    'opened cursors current',
                                    VALUE,
                                    0)) CURSORS
                    FROM DBA_HIST_SYSSTAT
                   WHERE STAT_NAME IN ('redo size',
                                       'session logical reads',
                                       'physical reads',
                                       'execute count',
                                       'user rollbacks',
                                       'user commits',
                                       'parse count (hard)',
                                       'parse count (total)',
                                       'logons current',
                                       'opened cursors current')
                   GROUP BY SNAP_ID, DBID, INSTANCE_NUMBER) A,
                 DBA_HIST_SNAPSHOT B
           WHERE A.SNAP_ID = B.SNAP_ID
             AND A.DBID = B.DBID
             AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
             AND B.END_INTERVAL_TIME > SYSDATE - 7
           ORDER BY END_INTERVAL_TIME)),
TMP_T AS
 (SELECT LEAD(A.VALUE, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) - A.VALUE DB_TIME,
         LEAD(B.SNAP_ID, 1, NULL) OVER(PARTITION BY B.INSTANCE_NUMBER, B.STARTUP_TIME ORDER BY B.END_INTERVAL_TIME) ENDSNAP_ID,
         B.SNAP_ID,
         B.INSTANCE_NUMBER
    FROM DBA_HIST_SYS_TIME_MODEL A, DBA_HIST_SNAPSHOT B
   WHERE A.SNAP_ID = B.SNAP_ID
     AND A.DBID = B.DBID
     AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
     AND A.STAT_NAME = 'DB time'),
TMP_ASH AS
 (SELECT INST_ID, SNAP_ID, COUNT(1) COUNTS
    FROM (SELECT N.INSTANCE_NUMBER INST_ID,
                 N.SNAP_ID,
                 N.SESSION_ID,
                 N.SESSION_SERIAL#
            FROM DBA_HIST_ACTIVE_SESS_HISTORY N
           GROUP BY N.INSTANCE_NUMBER,
                    N.SNAP_ID,
                    N.SESSION_ID,
                    N.SESSION_SERIAL#) NT
   GROUP BY NT.INST_ID, NT.SNAP_ID)
SELECT S.SNAP_DATE,
       S.INSTANCE_NUMBER INST_ID,
       SNAP_TIME_RANGE,
       T.SNAP_ID || '~' || (T.SNAP_ID + 1) SNAP_ID_RANGE,
       DECODE(S.REDOSIZE, NULL, '--shutdown or end--', S.CURRTIME) "TIME",
       STARTUP_TIME,
       TO_CHAR(ROUND(S.SECONDS / 60, 2)) "Elapsed(min)",
       ROUND(T.DB_TIME / 1000000 / 60, 2) "DB_time(min)",
       S.SESSIONS,
       (SELECT COUNTS
          FROM TMP_ASH NNT
         WHERE S.INSTANCE_NUMBER = NNT.INST_ID
           AND NNT.SNAP_ID = T.SNAP_ID) || '~' ||
       (SELECT COUNTS
          FROM TMP_ASH NNT
         WHERE S.INSTANCE_NUMBER = NNT.INST_ID
           AND NNT.SNAP_ID = T.SNAP_ID + 1) ACTIVE_SESSION,
       S.CURSORS2 "Cursors/Session",
       S.REDOSIZE REDO,
       ROUND(S.REDOSIZE / S.SECONDS, 2) "redo/s",
       ROUND(S.REDOSIZE / S.TRANSACTIONS, 2) "redo/t",
       S.LOGICALREADS LOGICAL,
       ROUND(S.LOGICALREADS / S.SECONDS, 2) "logical/s",
       ROUND(S.LOGICALREADS / S.TRANSACTIONS, 2) "logical/t",
       PHYSICALREADS PHYSICAL,
       ROUND(S.PHYSICALREADS / S.SECONDS, 2) "phy/s",
       ROUND(S.PHYSICALREADS / S.TRANSACTIONS, 2) "phy/t",
       S.EXECUTES EXECS,
       ROUND(S.EXECUTES / S.SECONDS, 2) "execs/s",
       ROUND(S.EXECUTES / S.TRANSACTIONS, 2) "execs/t",
       S.PARSE,
       ROUND(S.PARSE / S.SECONDS, 2) "parse/s",
       ROUND(S.PARSE / S.TRANSACTIONS, 2) "parse/t",
       S.HARDPARSE,
       ROUND(S.HARDPARSE / S.SECONDS, 2) "hardparse/s",
       ROUND(S.HARDPARSE / S.TRANSACTIONS, 2) "hardparse/t",
       S.TRANSACTIONS TRANS,
       ROUND(S.TRANSACTIONS / S.SECONDS, 2) "trans/s"
  FROM TMP_S S, TMP_T T
 WHERE S.ENDSNAP_ID = T.ENDSNAP_ID
   AND T.INSTANCE_NUMBER = S.INSTANCE_NUMBER
 ORDER BY S.INSTANCE_NUMBER, S.SNAP_DATE DESC, SNAP_ID DESC, TIME ASC;

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：230161599

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步