Posts

Caching of frequently used static master data (post 11g, using RESULT_CACHE)

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...
Post a Comment
Read more

Caching of frequently used static master data (pre 11g)

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 ----------------------------------------------------------...
Post a Comment
Read more

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. 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) ...
Post a Comment
Read more

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 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...
Post a Comment
Read more