Tuesday, July 22, 2008

shared_pool_tuning (1)

Codes taken from : http://www.burleson.cc/AWR/tuning_awr.htm

If librry cache miss ratio [sum of library chache reloads:sum of pins]> 1 shared pool size needs to be increased.

Library cache hit ratio:

select
   to_char(sn.end_interval_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
   dba_hist_librarycache old,
   dba_hist_librarycache new,
   dba_hist_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(sn.end_interval_time,'yyyy-mm-dd HH24')
;

 

Shared pool advice query:

 

SQL> set lines  100
set pages  999

column  c1      heading 'Pool |Size(M)'
column  c2      heading 'Size|Factor'
column  c3      heading 'Est|LC(M)  '
column  c4      heading 'Est LC|Mem. Obj.'
column  c5      heading 'Est|Time|Saved|(sec)'
column  c6      heading 'Est|Parse|Saved|Factor'
column  c7      heading 'Est|Object Hits'   format 999,999,999

SELECT
   shared_pool_size_for_estimate        c1,
SQL> SQL> SQL> SQL> SQL> SQL>    shared_pool_size_factor                c2,
SQL> SQL> SQL> SQL> SQL>   2    3    4     estd_lc_size                 c3,
  5     estd_lc_memory_objects          c4,
  6     estd_lc_time_saved              c5,
   estd_lc_time_saved_factor    c6,
  7    8     estd_lc_memory_object_hits c7
FROM
  9   10     v$shared_pool_advice;

                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved          Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
       704      .5789         65       4921    1659961      .9131   17,712,847 -- 50%
       832      .6842        193      12086    1706560      .9387   34,778,165
       960      .7895        321      17421    1773737      .9756   34,842,602
      1088      .8947        449      24992    1804605      .9926   34,868,558
      1216          1        576      34731    1818029          1   34,876,428
      1344     1.1053        704      45360    1825211      1.004   34,878,070
      1472     1.2105        832      57229    1828603     1.0058   34,878,431
      1600     1.3158        960      69770    1829199     1.0061   34,878,576
      1728     1.4211       1088      82478    1829222     1.0062   34,878,642
      1856     1.5263       1160      90295    1829222     1.0062   34,878,659
      1984     1.6316       1169      91672    1829223     1.0062   34,878,661
      2112     1.7368       1169      91672    1829223     1.0062   34,878,661
      2240     1.8421       1169      91672    1829223     1.0062   34,878,661
      2368     1.9474       1169      91672    1829223     1.0062   34,878,661
      2496     2.0526       1169      91672    1829223     1.0062   34,878,661 -- 200%

15 rows selected.

 

Increase the SGA size by multiplying with the factor for appropriate size.

 

PGA:

 

SQL> SELECT ROUND(pga_target_for_estimate / 1024 / 1024) target_mb,
  estd_pga_cache_hit_percentage cache_hit_perc,
  estd_overalloc_count
FROM v$pga_target_advice;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
       336            100                    0
       672            100                    0
      1344            100                    0
      2016            100                    0
      2688            100                    0
      3226            100                    0
      3763            100                    0
      4301            100                    0
      4838            100                    0
      5376            100                    0
      8064            100                    0
     10752            100                    0
     16128            100                    0
     21504            100                    0

14 rows selected.

 

SQL> select distinct name from v$sysstat  where name like '%pga%'  or name like 'pga%' order by name;

NAME
----------------------------------------------------------------
session pga memory
session pga memory max

No comments:

Post a Comment