REM $Id: dg_trend.sql,v 1.5 2013/06/21 14:06:42 ewan Exp $

column target_name format a40 new_value def_t
column target_type format a20
select target_name, target_type from mgmt$target
where
  target_type='osm_cluster'
  or (target_type='osm_instance' and type_qualifier2 != 'ASMINST')
order by lower(target_name)
/

set verify off

accept asm prompt "asm [&def_t]> " default '&def_t'

column target_guid new_value t_guid
select target_guid from mgmt_targets where target_name='&asm';

accept res prompt "aggregation (y, m or d) [d]> " default 'd'

column format new_value form
select
  case
    when '&res'='y' then 'yyyy'
    when '&res'='m' then 'yyyy-mm'
    else 'yyyy-mm-dd'
  end as format
from dual
/

alter session set nls_date_format='&form';

column dstart new_value def_d1
column dfinish new_value def_d2
select current_date - interval '12' month as dstart, current_date as dfinish
from dual
/

select distinct
  m.key_value as disk_group
from
  mgmt$metric_daily m
where
  m.target_guid=hextoraw('&t_guid')
  and m.metric_name='DiskGroup_Usage'
order by
  m.key_value
/

accept dg prompt "dg [%]> " default '%'
accept start prompt "start (&form) [&def_d1]> " default '&def_d1'
accept finish prompt "finish (&form) [&def_d2]> " default '&def_d2'

set linesize 80 pagesize 0 feedback off
set numformat 99999999
column disk_group format a32

define out=dg_trend_&res._&asm._&dg._&start._&finish
spool &out..txt

select
  to_date(to_char(alloc.timestamp,'&form'),'&form') as stamp,
  alloc.disk_group,
  round(avg(alloc.avg_size_mb),2)/1024 as avg_size_gb,
  round(avg(alloc.avg_size_mb - free.avg_free_mb),2)/1024 as avg_used_gb,
  round(avg(free.avg_free_mb),2)/1024 as avg_free_gb
from
  (
    select
      m.key_value as disk_group,
      m.rollup_timestamp as timestamp,
      avg(m.average) as avg_size_mb,
      min(m.minimum) as min_size_mb,
      max(m.maximum) as max_size_mb
    from
      mgmt$metric_daily m
      inner join mgmt$target_type t
        on m.target_guid=t.target_guid and m.metric_guid=t.metric_guid
    where
      t.target_guid=hextoraw('&t_guid')
      and (
        t.target_type='osm_cluster'
        or (t.target_type='osm_instance' and t.type_qualifier2 != 'ASMINST')
      )
      and m.key_value like '&dg'
      and t.metric_name='DiskGroup_Usage'
      and t.metric_column='usable_total_mb'
      and m.rollup_timestamp >= to_date('&start')
      and m.rollup_timestamp <= to_date('&finish')
    group by
      m.rollup_timestamp,
      m.key_value
  ) alloc
  inner join (
    select
      m.key_value as disk_group,
      m.rollup_timestamp as timestamp,
      avg(m.average) as avg_free_mb,
      min(m.minimum) as min_free_mb,
      max(m.maximum) as max_free_mb
    from
      mgmt$metric_daily m
      inner join mgmt$target_type t
        on m.target_guid=t.target_guid and m.metric_guid=t.metric_guid
    where
      t.target_guid=hextoraw('&t_guid')
      and (
        t.target_type='osm_cluster'
        or (t.target_type='osm_instance' and t.type_qualifier2 != 'ASMINST')
      )
      and m.key_value like '&dg'
      and t.metric_name='DiskGroup_Usage'
      and t.metric_column='usable_file_mb'
      and m.rollup_timestamp >= to_date('&start')
      and m.rollup_timestamp <= to_date('&finish')
    group by
      m.rollup_timestamp,
      m.key_value
  ) free
    on alloc.timestamp=free.timestamp and alloc.disk_group=free.disk_group
group by
  to_char(alloc.timestamp,'&form'),
  alloc.disk_group
order by
  alloc.disk_group,
  to_char(alloc.timestamp,'&form')
/

spool off

spool &out..gp

prompt set datafile separator whitespace
prompt set terminal pdfcairo
prompt set output "&out..pdf"
prompt set term pdfcairo enhanced font "Arial, 8"
prompt 
prompt set title "Disk group &dg history for &asm"
prompt 
prompt set xlabel "Date"
prompt set xdata time
prompt set timefmt "%Y-%m-%d"
prompt set xtics format "%m/%Y"
prompt 
prompt set ylabel "GiB"
prompt 
prompt set pointsize 0.4
prompt set style data linespoints
prompt 
prompt plot "&out..txt" using 1:3 title "Allocated", \
prompt      "&out..txt" using 1:4 title "Used", \
prompt      "&out..txt" using 1:5 title "Free"

spool off

set pagesize 14 verify on feedback on