Peter Ramm's Oracle performance analysis stuff

Short overview on peak I/O requests, transfer volume and CPU-usage for given time range. Based on average values within AWR-cycle. 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)

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 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