Peter Ramm's Oracle performance analysis stuff

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