oracle sql awr,在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;