1c.17. Instance Costs estimations (OCI360_INSTANCES) 1c.17. Instance Costs estimations (OCI360_INSTANCES)
| # | Compute Name | Shape | Regi | AD | State | OS | Compartment | Boot Size | Volumes Size | Backups Size | Shape - US$ Cost per month | Windows - US$ Cost per month | Storage - US$ Cost per month | Backup - US$ Cost per month | Total - US$ Cost per month | Instance OCID |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | oci360comp | VM.Standard.E2.1.Micro | iad | CYtq:US-ASHBURN-AD-3 | RUNNING | Oracle Linux | compt_dev_app | 47 | 100 | 33 | 0 | 6.25 | .84 | ocid1.instance.oc1.iad.000000000000000000000000000000000000000000000000000000000575 |
#: click on a column heading to sort on it
* US$ costs are estimations for Month Flex, not considering machine state changes (24h x 30d) / volumes or any other changes. Values base date: 2018-08-28
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ID VARCHAR2(128)
SHAPE VARCHAR2(32)
REGION VARCHAR2(4)
IMAGE_ID VARCHAR2(128)
METADATA$SSH_AUTHORIZED_KEYS VARCHAR2(512)
IPXE_SCRIPT VARCHAR2(4)
LAUNCH_MODE VARCHAR2(16)
SYSTEM_TAGS$ORCL_CLOUD$FREE_TIER_RETAINED VARCHAR2(4)
AGENT_CONFIG$IS_MANAGEMENT_DISABLED VARCHAR2(8)
AGENT_CONFIG$IS_MONITORING_DISABLED VARCHAR2(8)
DEFINED_TAGS$ORACLE_TAGS$CREATEDBY VARCHAR2(64)
DEFINED_TAGS$ORACLE_TAGS$CREATEDON VARCHAR2(32)
DISPLAY_NAME VARCHAR2(16)
FAULT_DOMAIN VARCHAR2(16)
SHAPE_CONFIG$GPUS NUMBER
SHAPE_CONFIG$OCPUS NUMBER
SHAPE_CONFIG$LOCAL_DISKS NUMBER
SHAPE_CONFIG$MEMORY_IN_GBS NUMBER
SHAPE_CONFIG$GPU_DESCRIPTION VARCHAR2(4)
SHAPE_CONFIG$MAX_VNIC_ATTACHMENTS NUMBER
SHAPE_CONFIG$PROCESSOR_DESCRIPTION VARCHAR2(64)
SHAPE_CONFIG$LOCAL_DISK_DESCRIPTION VARCHAR2(4)
SHAPE_CONFIG$NETWORKING_BANDWIDTH_IN_GBPS NUMBER
SHAPE_CONFIG$LOCAL_DISKS_TOTAL_SIZE_IN_GBS VARCHAR2(4)
TIME_CREATED VARCHAR2(32)
COMPARTMENT_ID VARCHAR2(128)
LAUNCH_OPTIONS$FIRMWARE VARCHAR2(8)
LAUNCH_OPTIONS$NETWORK_TYPE VARCHAR2(16)
LAUNCH_OPTIONS$BOOT_VOLUME_TYPE VARCHAR2(16)
LAUNCH_OPTIONS$REMOTE_DATA_VOLUME_TYPE VARCHAR2(16)
LAUNCH_OPTIONS$IS_CONSISTENT_VOLUME_NAMING_ENABLED VARCHAR2(4)
LAUNCH_OPTIONS$IS_PV_ENCRYPTION_IN_TRANSIT_ENABLED VARCHAR2(8)
SOURCE_DETAILS$IMAGE_ID VARCHAR2(128)
SOURCE_DETAILS$KMS_KEY_ID VARCHAR2(4)
SOURCE_DETAILS$SOURCE_TYPE VARCHAR2(8)
SOURCE_DETAILS$BOOT_VOLUME_SIZE_IN_GBS VARCHAR2(4)
LIFECYCLE_STATE VARCHAR2(8)
AVAILABILITY_DOMAIN VARCHAR2(32)
DEDICATED_VM_HOST_ID VARCHAR2(4)
TIME_MAINTENANCE_REBOOT_DUE VARCHAR2(4)
WITH t_inst AS (SELECT /*+ materialize */ * FROM OCI360_INSTANCES),
t_comp AS (SELECT /*+ materialize */ * FROM oci360_compartments),
t_img AS (SELECT /*+ materialize */ * FROM oci360_images),
t_price_comp AS (SELECT /*+ materialize */ inst_type, mf FROM "OCI360_PRICING" WHERE subject = 'COMPUTE'),
t_price_win AS (SELECT /*+ materialize */ * FROM "OCI360_PRICING" WHERE subject = 'OS_WINDOWS'),
t_price_vol AS (SELECT /*+ materialize */ * FROM "OCI360_PRICING" WHERE subject = 'STORAGE' AND inst_type = 'Block Volumes'),
t_price_bkp AS (SELECT /*+ materialize */ * FROM "OCI360_PRICING" WHERE subject = 'STORAGE' AND inst_type = 'Object Storage'),
t_vols AS
(SELECT t1.id,
t5.size_in_gbs BOOTVOL_SIZE_GBS,
NVL(SUM(t3.size_in_gbs), 0) VOL_SIZE_GBS
FROM (SELECT /*+ materialize */ * FROM OCI360_INSTANCES) t1,
(SELECT /*+ materialize */ * FROM oci360_vol_attachs) t2,
(SELECT /*+ materialize */ * FROM oci360_volumes) t3,
(SELECT /*+ materialize */ * FROM oci360_bv_attachs) t4,
(SELECT /*+ materialize */ * FROM oci360_bvolumes) t5
WHERE t1.id = t2.instance_id (+)
AND t2.volume_id = t3.id (+)
AND t2.lifecycle_state (+) = 'ATTACHED'
AND t1.id = t4.instance_id (+)
AND t4.boot_volume_id = t5.id (+)
AND t4.lifecycle_state (+) = 'ATTACHED'
GROUP BY t1.id, t5.size_in_gbs),
t_bvols_bkp AS
(SELECT t1.id,
NVL(SUM(t4.unique_size_in_gbs), 0) BKP_BOOTVOL_SIZE_GBS
FROM (SELECT /*+ materialize */ * FROM OCI360_INSTANCES) t1,
(SELECT /*+ materialize */ * FROM oci360_bv_attachs) t2,
(SELECT /*+ materialize */ * FROM oci360_bvolumes) t3,
(SELECT /*+ materialize */ * FROM oci360_bv_backups) t4
WHERE t1.id = t2.instance_id (+)
AND t2.boot_volume_id = t3.id (+)
AND t2.lifecycle_state (+) = 'ATTACHED'
AND t3.id = t4.boot_volume_id (+)
AND t4.lifecycle_state (+) = 'AVAILABLE'
GROUP BY t1.id),
t_vols_bkp AS
(SELECT t1.id,
NVL(SUM(t4.unique_size_in_gbs), 0) BKP_VOL_SIZE_GBS
FROM (SELECT /*+ materialize */ * FROM OCI360_INSTANCES) t1,
(SELECT /*+ materialize */ * FROM oci360_vol_attachs) t2,
(SELECT /*+ materialize */ * FROM oci360_volumes) t3,
(SELECT /*+ materialize */ * FROM oci360_backups) t4
WHERE t1.id = t2.instance_id (+)
AND t2.volume_id = t3.id (+)
AND t2.lifecycle_state (+) = 'ATTACHED'
AND t3.id = t4.volume_id (+)
AND t4.lifecycle_state (+) = 'AVAILABLE'
GROUP BY t1.id)
SELECT display_name "Compute Name",
shape "Shape",
region "Region",
availability_domain "AD",
lifecycle_state "State",
operating_system "OS",
compartment_name "Compartment",
bootvol_size_gbs "Boot Size",
vol_size_gbs "Volumes Size",
bkp_size_gbs "Backups Size",
ROUND(price_comp_month, 2) "Shape - US$ Cost per month",
ROUND(price_win_month, 2) "Windows - US$ Cost per month",
ROUND(price_vol_month, 2) "Storage - US$ Cost per month",
ROUND(price_bkp_month, 2) "Backup - US$ Cost per month",
ROUND(price_comp_month + price_win_month
+ price_vol_month + price_bkp_month, 2) "Total - US$ Cost per month",
id "Instance OCID"
FROM (SELECT t_inst.display_name,
t_inst.shape,
t_inst.region,
t_inst.availability_domain,
t_inst.lifecycle_state,
t_img.operating_system,
t_comp.name COMPARTMENT_NAME,
t_vols.bootvol_size_gbs,
t_vols.vol_size_gbs,
t_bvols_bkp.bkp_bootvol_size_gbs + t_vols_bkp.bkp_vol_size_gbs BKP_SIZE_GBS,
t_price_comp.mf * to_number(substr(t_inst.shape,instr(t_inst.shape,'.',-1)+1)) * 24 * 30
price_comp_month,
DECODE(t_img.operating_system, 'Windows',t_price_win.mf * to_number(substr(t_inst.shape,instr(t_inst.shape,'.',-1)+1)) * 24 * 30, 0) price_win_month,
t_price_vol.mf * ( t_vols.bootvol_size_gbs + t_vols.vol_size_gbs ) price_vol_month,
t_price_bkp.mf * ( t_bvols_bkp.bkp_bootvol_size_gbs + t_vols_bkp.bkp_vol_size_gbs ) price_bkp_month,
t_inst.id
FROM t_inst,
t_comp,
t_img,
t_vols,
t_vols_bkp,
t_bvols_bkp,
t_price_comp,
t_price_win,
t_price_vol,
t_price_bkp
WHERE t_inst.compartment_id = t_comp.id (+)
AND t_inst.image_id = t_img.id (+)
AND substr(t_inst.shape,1,instr(t_inst.shape,'.',-1)-1) = t_price_comp.inst_type (+)
/* AND t_inst.shape = t_price_win.inst_type (+) */
AND t_inst.id = t_vols.id (+)
AND t_inst.id = t_vols_bkp.id (+)
AND t_inst.id = t_bvols_bkp.id (+))
ORDER BY "Total - US$ Cost per month" DESC;
1 rows selected.