Peter Ramm's Oracle performance analysis stuff

Create SQL trace file for systems with connection pool If using connection pools the default SQL_TRACE=TRUE does not help because the DB-connection should only be traced if used for a special considered application. For identification of application you can use the context info set by DBMS_Application_Info.Set_Module. Trace the run of an single application For a special module info you can specify creation of trace file already before execution of this application. Setting trace config has to be done as SYSDBA. In addition you can set parameters for "waits" and "binds", default is FALSE. Examples Activate trace for application by module with recording of bind variables exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'SYS$USERS', module_name => 'ID_Application = 56', binds => TRUE); Deactivate trace for module exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'SYS$USERS', module_n...

With shown SQLs you can identify objects causing “library cache: mutex X”-waits in library cache. From oracle 11g this problem may be reduced by cloning objects in library cache via DBMS_SHARED_POOL.MARKHOT. Please keep in mind to set the exact namespace in call of DBMS_SHARED_POOL.MARKHOT, for PL/SQL packages it has to be called twice for package and body. View on Active Session History from current SGA: SELECT COUNT(*) Seconds_in_Wait, obj.NameSpace, h.Inst_ID, o.Owner, o.Type, o.Name, ROUND(AVG(Time_Waited)/1000,2) Avg_Time_waited_ms, MIN(Sample_Time) First_Sample, MAX(Sample_Time) Last_Sample FROM gv$Active_Session_History h LEFT OUTER JOIN gv$DB_Object_Cache o ON o.Hash_Value = h.P1 AND o.Inst_ID = h.Inst_ID LEFT OUTER JOIN DBA_Objects obj ON obj.Owner = o.Owner AND obj.Object_Name = o.Name AND obj.Object_Type = o.Type WHERE Event = 'library cache: mutex X' GROUP BY h.Inst_ID, o.Owner, o.Type, o.Name, obj.NameSpace HAVING COUNT(*) ...

frequent access on small tables includes risk of hot blocks in db-cache, especially if tables are mass-joined by nested loop operations Joining this master tables by hash joins reduces number of block access in db cache, but requires large data transfer for hash operations and possibly access to TEMP tablespace Often these master data may be cached in local DB session, for session pooling environment or long living sessions in addition with content aging for guaranteed currentness of data This example shows a PL/SQL package for caching of table data with 11g-feature “function result caching” For usage runtime measuring should be done to decide between these caching or usage of hash joins p.s.: More effective than this solution may be caching master data within application layer without access to PL/SQL CREATE OR REPLACE PACKAGE Cache_TableAlias AS -------------------------------------------------------------------------------- -- Author : %$LastChangedBy: arosenfe $derived...

frequent access on small tables includes risk of hot blocks in db-cache, especially if tables are mass-joined by nested loop operations Joining this master tables by hash joins reduces number of block access in db cache, but requires large data transfer for hash operations and possibly access to TEMP tablespace Often these master data may be cached in local DB session, for session pooling environment or long living sessions in addition with content aging for guaranteed currentness of data This example shows a PL/SQL package for caching of table data without 11g-feature “function result caching” For usage runtime measuring should be done to decide between these caching or usage of hash joins From 11g function result caching may be used instead of this solution p.s.: More effective than this solution is caching master data within application layer without access to PL/SQL CREATE OR REPLACE PACKAGE Cache_TableAlias AS ----------------------------------------------------------...

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