Measure average I/O-load and CPU-usage on Oracle database instance

Short overview on peak I/O requests, transfer volume and CPU-usage for given time range.
Based on average values within AWR-cycle.

—–

Überblick über Spitzenwerte für I/O-Requests und transferierte Volumina sowie CPU-Auslastung im gegebenen Zeitraum.
Basiert auf Durchschnittswerten über einen kompletten AWR-Zyklus.

SELECT /* DB-Tools Ramm average system load */
       Instance_Number, MIN(Begin_Time) Start_Range, MAX(End_Time) End_Range,
       MAX(Phys_Write_Total_kB_per_Sec)                                            Max_Phys_Writes_kB_per_Sec,
       MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Phys_Write_Total_kB_per_Sec) Max_Write_per_Sec_BeginTime,
       MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Phys_Write_Total_kB_per_Sec) Cycle_Minutes_1,  
       MAX(Phys_Read_Total_kB_per_Sec)                                             Max_Phys_Read_kB_per_Sec,
       MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Phys_Read_Total_kB_per_Sec)  Max_Read_per_Sec_BeginTime, 
       MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Phys_Read_Total_kB_per_Sec) Cycle_Minutes_2,  
       MAX(Phys_Write_IO_per_Sec)                                                  Max_Phys_Write_IO_per_Sec,
       MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Phys_Write_IO_per_Sec)       Max_Write_IO_per_Sec_BeginTime,
       MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Phys_Write_IO_per_Sec) Cycle_Minutes_3,  
       MAX(Phys_Read_IO_per_Sec)                                                   Max_Phys_Read_IO_per_Sec,
       MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Phys_Read_IO_per_Sec)        Max_Read_IO_per_Sec_BeginTime, 
       MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Phys_Read_IO_per_Sec) Cycle_Minutes_4,  
       MAX(Host_CPU_Utilization_Pct)                                               Max_Host_CPU_Utilization_Pct,
       MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Host_CPU_Utilization_Pct)    Max_Host_CPU_Pct_BeginTime, 
       MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Host_CPU_Utilization_Pct) Cycle_Minutes_4,  
       MAX(DB_CPUs)                                                                Max_DB_CPUs,
       MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY DB_CPUs)                     DB_CPUs_BeginTime, 
       MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY DB_CPUs)     Cycle_Minutes_4  
FROM   ( 
        SELECT /*+ NO_MERGE LEADING(h.ash) PARALLEL(h,4) */ 
               Instance_Number, Begin_Time, End_Time,
               ROUND(SUM(CASE WHEN Metric_Name = 'Physical Write Total Bytes Per Sec' THEN Average ELSE 0 END)/1024) Phys_Write_Total_kB_per_Sec,
               ROUND(SUM(CASE WHEN Metric_Name = 'Physical Read Total Bytes Per Sec'  THEN Average ELSE 0 END)/1024)  Phys_Read_Total_kB_per_Sec,
               ROUND(SUM(CASE WHEN Metric_Name = 'Physical Write IO Requests Per Sec' THEN Average ELSE 0 END)) Phys_Write_IO_per_Sec,
               ROUND(SUM(CASE WHEN Metric_Name = 'Physical Read IO Requests Per Sec'  THEN Average ELSE 0 END))  Phys_Read_IO_per_Sec,
               ROUND(SUM(CASE WHEN Metric_Name = 'Host CPU Utilization (%)'           THEN Average ELSE 0 END))  Host_CPU_Utilization_Pct,
               ROUND(SUM(CASE WHEN Metric_Name = 'CPU Usage Per Sec'                  THEN Average ELSE 0 END)/100,2)  DB_CPUs
        FROM   DBA_Hist_SysMetric_Summary
        WHERE  Begin_Time > SYSTIMESTAMP - 7
        AND    Metric_Name IN ('Physical Write Total Bytes Per Sec', 'Physical Write IO Requests Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Read IO Requests Per Sec',
                               'Host CPU Utilization (%)', 'CPU Usage Per Sec' )
        GROUP BY Instance_Number, Begin_Time, End_Time
       ) h
GROUP BY Instance_Number       
;

Measure average system load on oracle database instance

Short overview over utilization of DB system.
Lists average and peak values for every instance (in case of RAC) for given time range:
– Total number of active DB sessions
– Number of active DB sessions on CPU (no other wait event)

Value and timestamp are shown for:
– maximum peak load
– average load over total time range
– maximum average load over one minute
– maximum average load over one hour

—–

Für einen schnellen Überblick über die Auslastung eines DB-Systems dient folgendes Statement.
Es listet für jeden Knoten (falls RAC) im vorgegebenen Zeitraum Durchschnitts- und Spitzenwerte für:
– die Anzahl aktiver DB-Sessions gesamt
– die Anzahl aktiver DB-Sessions mit CPU-Last (kein anderweitiger Wait-Event)

Ausgewiesen werden jeweils der Wert sowie der Zeitpunkt des Auftretens:
– Maximale Spitzenlast
– Durchschnittswert im gesamten Zeitraum
– Maximale Durchschnittswert über eine Minute
– Maximale Durchschnittswert über eine Stunde

SELECT /* DB-Tools Ramm average system load */
       Instance_Number, MIN(Sample_Time) Start_Range, MAX(Sample_Time) End_Range,
       MAX(Active)                                                          Max_Active,
       MAX(Sample_Time) KEEP (DENSE_RANK LAST ORDER BY Active)              Max_Active_Sample_Time, 
       MAX(CPU)                                                             Max_CPU,
       MAX(Sample_Time) KEEP (DENSE_RANK LAST ORDER BY CPU)                 Max_CPU_Sample_Time, 
       ROUND(AVG(Active),1)                                                 Avg_Active_Total,
       ROUND(AVG(CPU),1)                                                    Avg_CPU_Total,
       ROUND(MAX(Avg_Active_Minute),1)                                      Max_Avg_Active_Minute,
       MAX(Sample_Time) KEEP (DENSE_RANK LAST ORDER BY Avg_Active_Minute)   Max_Avg_Active_Minute_End, 
       ROUND(MAX(Avg_CPU_Minute),1)                                         Max_Avg_CPU_Minute,
       MAX(Sample_Time) KEEP (DENSE_RANK LAST ORDER BY Avg_CPU_Minute)      Max_Avg_CPU_Minute_End, 
       ROUND(MAX(Avg_Active_Hour),1)                                        Max_Avg_Active_Hour,
       MAX(Sample_Time) KEEP (DENSE_RANK LAST ORDER BY Avg_Active_Hour)     Max_Avg_Active_Hour_End, 
       ROUND(MAX(Avg_CPU_Hour),1)                                           Max_Avg_CPU_Hour,
       MAX(Sample_Time) KEEP (DENSE_RANK LAST ORDER BY Avg_CPU_Hour)        Max_Avg_CPU_Hour_End 
FROM   ( 
        SELECT h.*,
               AVG(Active) OVER (PARTITION BY Instance_Number, TRUNC(Sample_Time, 'MI'))    Avg_Active_Minute,      
               AVG(Active) OVER (PARTITION BY Instance_Number, TRUNC(Sample_Time, 'HH24'))  Avg_Active_Hour,      
               AVG(CPU) OVER (PARTITION BY Instance_Number, TRUNC(Sample_Time, 'MI'))       Avg_CPU_Minute,      
               AVG(CPU) OVER (PARTITION BY Instance_Number, TRUNC(Sample_Time, 'HH24'))     Avg_CPU_Hour      
        FROM   (
                SELECT /*+ NO_MERGE LEADING(h.ash) PARALLEL(h,4) */ 
                       Instance_Number, Sample_Time, 
                       COUNT(*) Active,
                       SUM(CASE WHEN Session_State = 'ON CPU' THEN 1 ELSE 0 END) CPU  
                FROM   DBA_Hist_Active_Sess_History h
                WHERE  Sample_Time > SYSTIMESTAMP - 4
                AND    NVL(Event, Session_State) NOT IN ('PX Deq Credit: send blkd') -- Idle-Events nicht zaehlen
                GROUP BY Instance_Number, Sample_Time
               ) h
       )
GROUP BY Instance_Number       
;