Average Usage Over The Past 12 Months

Get the average growth over the past 12 months. Ensure you change the TARGET_GUID.

SELECT
  (max(round(avg(a_size.size_gb),2)) - min(round(avg(a_size.size_gb),2)))/12
FROM
 (SELECT 
      m.rollup_timestamp AS month_timestamp,
      sum(m.average/1024) AS size_gb
    FROM
      mgmt$metric_daily m,
      mgmt$target_type t
    WHERE
      t.target_guid=HEXTORAW('A06F734DEB35E1CFE38C9AB4729508E2') AND
      (t.target_type='rac_database' OR
      (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND     
      m.target_guid=t.target_guid AND
      m.metric_guid=t.metric_guid AND
      t.metric_name='tbspAllocation' AND
      (t.metric_column='spaceAllocated') AND
      m.rollup_timestamp >= sysdate-365 AND
      m.rollup_timestamp <= sysdate
    GROUP BY m.metric_column, m.rollup_timestamp) a_size,
  (SELECT 
      m.rollup_timestamp AS month_timestamp,
      sum(m.average/1024) AS used_gb
    FROM
      mgmt$metric_daily m,
      mgmt$target_type t
    WHERE
      t.target_guid=HEXTORAW('A06F734DEB35E1CFE38C9AB4729508E2') AND
      (t.target_type='rac_database' OR
      (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND     
      m.target_guid=t.target_guid AND
      m.metric_guid=t.metric_guid AND
      t.metric_name='tbspAllocation' AND
      (t.metric_column='spaceUsed') AND
      m.rollup_timestamp >= sysdate-365 AND
      m.rollup_timestamp <= sysdate
    GROUP BY m.metric_column, m.rollup_timestamp) used
WHERE
  a_size.month_timestamp =used.month_timestamp
GROUP BY TO_CHAR(a_size.month_timestamp,'MON RR');

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.