Friday, March 4, 2016

ExaData/Exalogic resource usage(CPU/MEM/Storage) from OEM

ExaLogic/ExaData  CPU/Mem/Storage Usage (By Node) data:

Here is the sql I used to get the Exa Node CPU/Mem/Storage usage data from our OEM repo. We have several ExaData/ExaLogic machines in house and I was asked to get these data automatically, took me a while to get here.

select decode(Instr(Entity_Name,'exad'),0,'ExaLogic','ExaData') Catelog,substr(Entity_Name,1,9) Machine,Entity_Name node_name,
case Metric_Column_Label
       When 'CPU Utilization (%)' Then 'CPU'
       When 'Physical Memory Utilization (%)' Then 'Memory'
       When 'Memory Utilization (%)' then 'Memory'
       --when 'Total Disk Utilized (%) (across all local filesystems)' then 'Local FS'
        else 'Storage'
end case,
COLLECTION_TIME,round(Avg_Value,2) Avg_Usage
From sysman.GC$metric_Values_Daily
Where Entity_Type In( 'oracle_vm_server','host')
         And ((Substr(Entity_Name,4,4) ='exal'
         And Instr(Entity_Name,'-elc')=0) or Substr(Entity_Name,4,4) ='exad')
         And Metric_Column_Label in('CPU Utilization (%)',
                'Physical Memory Utilization (%)','Memory Utilization (%)',
                 'Used Space (%)')
--,'Total Disk Utilized (%) (across all local filesystems)')
and collection_time>=sysdate-15  and round(Avg_Value,2)>0;

Several notes:
  1. The above sql doesn't return Exadata ASM usages, I couldn't find those data anywhere in OEM and I now believe OEM doesn't have them. I ended up go to our Database instance directly to get them
  2. The ExaLogic Node Storage data is a little difference with the data showing in our Ops Center,  The difference is minor, don't know why but someone from Oracle told me that OEM data is just fine.

No comments:

Post a Comment