整理后sql:
SELECT *
FROM ( SELECT A.INSTANCE_NUMBER,
A.SNAP_ID,
B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
B.END_INTERVAL_TIME + 0 END_TIME,
ROUND(VALUE - LAG( VALUE, 1 , '0')
OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
FROM (SELECT B.SNAP_ID,
INSTANCE_NUMBER,
SUM(VALUE ) / 1000000 / 60 VALUE
FROM DBA_HIST_SYS_TIME_MODEL B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD')
ORDER BY BEGIN_TIME;
SQL 结果:
INSTANCE_NUMBER
SNAP_ID
BEGIN_TIME
END_TIME
DB TIME273072015/3/20 0:002015/3/20 1:00 13173082015/3/20 0:592015/3/20 1:59 220273082015/3/20 1:002015/3/20 2:00 2173092015/3/20 1:592015/3/20 2:59 62273092015/3/20 2:002015/3/20 3:00 0173102015/3/20 2:592015/3/20 3:59 61273102015/3/20 3:002015/3/20 4:00 0173112015/3/20 3:592015/3/20 4:59 62273112015/3/20 4:002015/3/20 5:00 0173122015/3/20 4:592015/3/20 5:59 62273122015/3/20 5:002015/3/20 6:00 0173132015/3/20 5:592015/3/20 6:59 86273132015/3/20 6:002015/3/20 7:00 0173142015/3/20 6:592015/3/20 7:59 128273142015/3/20 7:002015/3/20 8:00 4173152015/3/20 7:592015/3/20 8:59 212273152015/3/20 8:002015/3/20 9:00 52173162015/3/20 8:592015/3/20 9:59 238273162015/3/20 9:002015/3/20 10:00 220173172015/3/20 9:592015/3/20 10:59 328273172015/3/20 10:002015/3/20 11:00 441173182015/3/20 10:592015/3/20 11:59 455273182015/3/20 11:002015/3/20 12:00 492173192015/3/20 11:592015/3/20 12:59 371273192015/3/20 12:002015/3/20 13:00 392173202015/3/20 12:592015/3/20 13:59 466273202015/3/20 13:002015/3/20 14:00 4421 73212015/3/20 13:592015/3/20 14:59 492273212015/3/20 14:002015/3/20 15:00 424173222015/3/20 14:592015/3/20 15:59 457273222015/3/20 15:002015/3/20 16:00 362173232015/3/20 15:592015/3/20 16:59 536273232015/3/20 16:00 2015/3/20 17:00 418173242015/3/20 16:592015/3/20 17:59 545273242015/3/20 17:00 2015/3/20 18:00 399173252015/3/20 17:592015/3/20 18:59 332273252015/3/20 18:002015/3/20 19:00 271173262015/3/20 18:592015/3/20 19:59 169273262015/3/20 19:002015/3/20 20:00 112173272015/3/20 19:592015/3/20 20:59 1212
7327
2015/3/20 20:00
2015/3/20 21:00
60
图示: