oci360 1b.8. Utilization Tree Map (OCI360_INSTANCES)



Notes:
1) Values are approximated
2) Left-click a node to move down the tree and right-click to move up.

3) Blue = Volumes and Boot Volumes. Red = Backups
 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_reg  AS (SELECT /*+ materialize */ region_name, region_key from OCI360_REGIONS_SUBS),
     t_inst AS
        (SELECT /*+ materialize */
                id,
                display_name || ' - ' || 'I' || rank() over (order by display_name, id) display_name,
                compartment_id,
                /*  For IAD and PHX, the key used in instance region column is actually the region key, while in other is the region name */
                decode(region,'iad','us-ashburn-1','phx','us-phoenix-1',region) region
                FROM oci360_instances),
     t_comp AS (SELECT /*+ materialize */ id, name || ' - ' || 'C' || rank() over (order by name, id) name FROM oci360_compartments),
     t_vols AS
        (SELECT t1.id,
                t3.display_name || ' - ' || 'V' || rank() over (order by t3.display_name, t3.id) display_name,
                t3.size_in_gbs
         FROM   (SELECT /*+ materialize */ * FROM oci360_instances) t1,
                (SELECT /*+ materialize */ * FROM oci360_vol_attachs) t2,
                (SELECT /*+ materialize */ * FROM oci360_volumes) t3
         WHERE  t1.id = t2.instance_id
         AND    t2.volume_id = t3.id
         AND    t2.lifecycle_state = 'ATTACHED'),
     t_bvols AS
        (SELECT t1.id,
                t3.display_name || ' - ' || 'B' || rank() over (order by t3.display_name, t3.id) display_name,
                t3.size_in_gbs
         FROM   (SELECT /*+ materialize */ * FROM oci360_instances) t1,
                (SELECT /*+ materialize */ * FROM oci360_bv_attachs) t2,
                (SELECT /*+ materialize */ * FROM oci360_bvolumes) t3
         WHERE  t1.id = t2.instance_id
         AND    t2.boot_volume_id = t3.id
         AND    t2.lifecycle_state = 'ATTACHED'),
     t_bvols_bkp AS
        (SELECT t1.id,
                t4.display_name || ' - ' || 'D' || rank() over (order by t4.display_name, t4.id) display_name,
                t4.unique_size_in_gbs size_in_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'),
     t_vols_bkp AS
        (SELECT t1.id,
                t4.display_name || ' - ' || 'E' || rank() over (order by t4.display_name, t4.id) display_name,
                t4.unique_size_in_gbs size_in_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')
SELECT 'All Regions' "Object", null "Parent", 0 "Value", 0 Color
FROM   dual
UNION
SELECT region_name, 'All Regions', 0, 0
FROM   t_reg
UNION
SELECT name || ' - ' || region_name, region_name, 0, 0
FROM   t_comp, t_reg
UNION
SELECT t2.display_name, t1.name || ' - ' || region, 0, 0
FROM   t_comp t1,
       t_inst t2
WHERE  t2.compartment_id = t1.id
UNION
SELECT t1.display_name, t2.display_name, t1.size_in_gbs, 0
FROM   t_vols t1,
       t_inst t2
WHERE  t2.id = t1.id
UNION
SELECT t1.display_name, t2.display_name, t1.size_in_gbs, 0
FROM   t_bvols t1,
       t_inst t2
WHERE  t2.id = t1.id
UNION
SELECT t1.display_name, t2.display_name, t1.size_in_gbs, 1
FROM   t_vols_bkp t1,
       t_inst t2
WHERE  t2.id = t1.id
UNION
SELECT t1.display_name, t2.display_name, t1.size_in_gbs, 1
FROM   t_bvols_bkp t1,
       t_inst t2
WHERE  t2.id = t1.id;


31 rows selected.

oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T15:50:19