 |
|
|
Oracle Library Cache scripts
A script similar to the preceding library cache scripts, applied to the library cache, can reveal deficiencies within the shared pool. The utility takes time-based Oracle tuning information, such as the library cache miss ratio, and stores it within Oracle tables.
Once the DBA is familiar with the structure of the tables and columns within these tables, simple Oracle queries, like rpt_lib_cache_hr.sql below that will display trend-based information can be devised. The trend-based data can then be applied to predictive models that inform the DBA of the appropriate times to change the internal structure of the SGA.
rpt_lib_cache_hr.sql
set lines 80; set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16 column c1 heading "execs" format 9,999,999 column c2 heading "Cache Misses|While Executing" format 9,999,999 column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select to_char(snap_time,'yyyy-mm-dd HH24') mydate, sum(new.pins-old.pins) c1, sum(new.reloads-old.reloads) c2, sum(new.reloads-old.reloads)/ sum(new.pins-old.pins) library_cache_miss_ratio from stats$librarycache old, stats$librarycache new, stats$snapshot sn where new.snap_id = sn.snap_id and old.snap_id = new.snap_id-1 and old.namespace = new.namespace group by to_char(snap_time,'yyyy-mm-dd HH24');
The output below indicates a RAM shortage in the shared pool between 9:00 and 10:00 a.m.
Cache Misses Library Cache Yr. Mo Dy Hr. execs While Executing Miss Ratio --------------- ------- --------------- ------------- 2001-12-11 10 10,338 6,443 .64 2001-12-12 10 182,477 88,136 .43 2001-12-14 10 190,707 101,832 .56 2001-12-16 10 72,803 45,932 .62
The DBA merely needs to schedule additional RAM for the shared_pool_size during the deficient period.
From the example above, it is apparent that a high number of library cache misses indicate that the shared pool is too small. To further summarize, a DBHR of less than 90 percent for any of the seven Oracle data buffer pools indicates that memory should be moved from other database regions and reallocated to the data buffer area.
Also, whenever the percentage of optimal executions within the Program Global Area (PGA) is less than 95, the value of the PGA aggregate target parameter should be increased. The next step is to evaluate those times at which the DBA should trigger a dynamic reconfiguration of Oracle.
Once the DBA understands the basics of buffer block size allocation, time can be spent taking a closer look at the internal mechanisms of the data buffers.
SEE CODE DEPOT FOR FULL SCRIPTS
|
|
|
