QUERY:
SELECT DT,
SUM(RB*8/3600000000*1.3) Mbps_REQ_A_DAY,
MIN(RB*8/3600000000*1.3) MIN_Mbps_REQ_AN_HOUR,
MAX(RB*8/3600000000*1.3) MAX_Mbps_REQ_AN_HOUR ,
AVG(RB*8/3600000000*1.3) AVG_Mbps_REQ_AN_HOUR
FROM(
SELECT TRUNC (COMPLETION_TIME) DT,
TO_CHAR (COMPLETION_TIME,’HH24′) HH,
SUM(BLOCKS*BLOCK_SIZE) RB
FROM
V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-5
AND DEST_ID=1
GROUP BY TRUNC(COMPLETION_TIME),
TO_CHAR (COMPLETION_TIME, ‘HH24′)
)
GROUP BY DT
order by DT;
SUM(RB*8/3600000000*1.3) Mbps_REQ_A_DAY,
MIN(RB*8/3600000000*1.3) MIN_Mbps_REQ_AN_HOUR,
MAX(RB*8/3600000000*1.3) MAX_Mbps_REQ_AN_HOUR ,
AVG(RB*8/3600000000*1.3) AVG_Mbps_REQ_AN_HOUR
FROM(
SELECT TRUNC (COMPLETION_TIME) DT,
TO_CHAR (COMPLETION_TIME,’HH24′) HH,
SUM(BLOCKS*BLOCK_SIZE) RB
FROM
V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-5
AND DEST_ID=1
GROUP BY TRUNC(COMPLETION_TIME),
TO_CHAR (COMPLETION_TIME, ‘HH24′)
)
GROUP BY DT
order by DT;
OUTPUT:
========================================================================
DT ||MBPS_REQ_A_DAY||MIN_MBPS_REQ_AN_HOUR||MAX_MBPS_REQ_AN_HOUR|| AVG_MBPS_REQ_AN_HOUR
————— ———————— ———————— ———————— ————————
20-JUL-12 1.40667756 .092599751 .757330034 .281335512
21-JUL-12 1.36889367 .092398592 .379794318 .136889367
22-JUL-12 .940478009 .072797412 .104935538 .094047801
23-JUL-12 3.23766921 .02369536 .855787065 .202354325
24-JUL-12 2.82066193 .067503673 .848231765 .176291371
25-JUL-12 .845672903 .045166137 .178660352 .08456729
