Peter Ramm's Oracle performance analysis stuff

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(*) &g

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