5e.1.449. Top 15 Resources - Total per hour, between 2020-04-18 and 2020-06-28 (OCI360_USAGECOSTS)
Notes:
1) drag to zoom, and right click to reset
2) up to 31 days of awr history were considered
3)
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
COSTS$UNITPRICE NUMBER
COSTS$OVERAGESFLAG VARCHAR2(1)
COSTS$COMPUTEDAMOUNT NUMBER
COSTS$COMPUTEDQUANTITY NUMBER
CURRENCY VARCHAR2(4)
QUANTITY NUMBER
ENDTIMEUTC VARCHAR2(32)
SERVICENAME VARCHAR2(32)
DATACENTERID VARCHAR2(16)
GSIPRODUCTID VARCHAR2(8)
RESOURCENAME VARCHAR2(64)
STARTTIMEUTC VARCHAR2(32)
SUBSCRIPTIONID VARCHAR2(8)
SUBSCRIPTIONTYPE VARCHAR2(16)
LASTCOMPUTATIONDATE VARCHAR2(32)
SERVICEENTITLEMENTID VARCHAR2(16)
WITH t1 AS (
SELECT SUM(COSTS$COMPUTEDAMOUNT) COMPUTEDAMOUNT,
TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') ENDTIMEUTC,
RESOURCENAME
FROM OCI360_USAGECOSTS
GROUP BY TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM'), RESOURCENAME
),
trange as (
select trunc(min(TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')),'HH24') min,
trunc(max(TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')),'HH24') max
FROM OCI360_USAGECOSTS
),
allhours as ( /* Will generate all hours between Min and Max Start Time */
SELECT trange.min + (rownum - 1)/24 vdate,
rownum seq
FROM trange
WHERE trange.min + (rownum - 1)/24 <= trange.max - 1/24 /* Skip last entry as may be incomplete. */
CONNECT BY LEVEL <= (trange.max - trange.min)*24 + 1
)
select seq snap_id,
TO_CHAR(vdate, 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(vdate+1,'YYYY-MM-DD HH24:MI') end_time,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_DATABASE_CLOUD_ENTERPRISE_EDITION',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line1,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_BLOCK_STORAGE_STANDARD',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line2,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_COMPUTE_STANDARD_E2',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line3,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_DATABASE_CLOUD_ALL_EDITION_BYOL',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line4,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_STANDARD_STORAGE',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line5,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_OBJECT_STORAGE_REQUEST_TIERED',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line6,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_STANDARD_PERFORMANCE',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line7,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'ATP_PAAS_FREE_OCPU_HOUR',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line8,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'ATP_PAAS_XD_FREE_TB_MONTH',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line9,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_BLOCK_STORAGE_STANDARD_FREE',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line10,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_COMPUTE_OUTBOUND_DATA_TRANSFER',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line11,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_COMPUTE_VM_STANDARD_E2_MICRO_FREE',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line12,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_METRIC_CONSUMPTION',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line13,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_OBJECT_STORAGE_TIERED',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line14,
TO_CHAR(NVL(CEIL(SUM(DECODE(RESOURCENAME,'PIC_OCI_KMS_KEYVERSION',COMPUTEDAMOUNT,0))*100)/100,0),'99999990D00') line15
from t1, allhours
where ENDTIMEUTC(+) >= vdate and ENDTIMEUTC(+) < vdate+1/24
group by seq, vdate
order by seq;
1683 rows selected.
oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T16:30:10