Oracle DBA – A lifelong learning experience

Memory advisors – how reliable?

Posted by John Hallas on December 24, 2015

I like the memory advisors that are available from the OEM GUI or the direct database tables themselves.  If I want a quick overview of whether a memory area (shared_pool, cache or the overall memory target itself) needs reviewing look there first. Note that I did not include PGA here specifically, I am well aware that PGA is included with the MAX_MEMORY_TARGET value   – I am not considering that view in my discussion here because it is so subjective to the application and what the users are doing and the code being run.

Here is an example of the OEM advisor for MAX_MEMORY_TARGET for a specific database (Performance/Advisors Home/ Memory Advisors and select advice)

mm1

The graph tell us that we have a MAX_MEMORY_TARGET of 4Gb and increasing it will not improve database time (the blue line continues on the same horizontal tangent right through to 8Gb).

We can also verify that from the database view


 select * from v$memory_target_advice order by memory_size
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
       1024                .25      4320481               1.271          0
       2048                 .5      3402336              1.0009          0
       2560               .625      3399617              1.0001          0
       3072                .75      3399277                   1          0
       3584               .875      3399277                   1          0
       4096                  1      3399277                   1          0
       4608              1.125      3398937               .9999          0
       5120               1.25      3398937               .9999          0
       5632              1.375      3398937               .9999          0
       6144                1.5      3398937               .9999          0
       6656              1.625      3398937               .9999          0
       7168               1.75      3398937               .9999          0
       7680              1.875      3398937               .9999          0
       8192                  2      3398937               .9999          0


The value for ESTD_DB_TIME does not change even if we increase MMT to 8Gb

This database runs our production Help Desk application and is busy, with a lot of searches for incident, problems and change management records so it would seem logical to me that more memory might improve performance. Not guaranteed but worthy of investigation.

Given what we have seen so far it must be a no-brainer that any overall increase above a combined total of 4Gb for SGA and cache will not make any difference.

If we review the relevant views we can see if that is true – starting with SGA

select * from v$sga_target_advice order by sga_size
  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
 ---------- --------------- ------------ ------------------- -------------------
        668             .25     10226001              3.0071          1202053738
       1336              .5      7175306                2.11           798713642
       2004             .75      4632323              1.3622           461313894
       2672               1      3400619                   1           298372611
       3340            1.25      2848018               .8375           226345463
       4008             1.5      2513738               .7392           180933151
       4676            1.75      2477011               .7284           166342731
       5344               2      2476671               .7283           137340913

Interesting  – if we increase SGA to 5Gb then we would see 54% less disk reads – in theory at least. It is reasonable to assume that the shared pool does not need increasing but we had better check that first.

select shared_pool_size_for_estimate as estimate_size,
shared_pool_size_factor as factor,
estd_lc_time_saved as time_saved, estd_lc_load_time as load_time
from v$shared_pool_advice;
ESTIMATE_SIZE     FACTOR TIME_SAVED  LOAD_TIME
------------- ---------- ---------- ----------
          304      .6786   62180461     262760
          352      .7857   62181180     262041
          400      .8929   62185136     258085
          448          1   62189074     254147
          496     1.1071   62192977     250244
          544     1.2143   62196840     246381
          592     1.3214   62200668     242553
          640     1.4286   62204464     238757
          688     1.5357   62208233     234988
          736     1.6429   62211977     231244
          784       1.75   62215699     227522
          832     1.8571   62219402     223819
          880     1.9643   62223090     220131
          928     2.0714   62226765     216456

Agreed – the shared pool at 448Mb is just fine so the next step is to look at the db_cache advisor view


select
 size_for_estimate ,estd_physical_read_factor,estd_physical_reads
from v$db_cache_advice where name = 'DEFAULT'
and block_size = (SELECT value FROM V$PARAMETER
 WHERE name = 'db_block_size')
 and advice_status = 'ON';
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- ------------------------- -------------------
              208                    3.5902          1072105085
              416                    3.1367           936678072
              624                    2.7341           816453041
              832                    2.3848           712148855
             1040                    2.0815           621562635
             1248                    1.8164           542414732
             1456                    1.5832           472762103
             1664                    1.3774           411328633
             1872                    1.1953           356926470
             2080                    1.0327           308385782
             2128                         1           298618524
             2288                      .891           266073474
             2496                      .773           230817259
             2704                     .7228           215846583
             2912                     .6758           201793473
             3120                     .6297           188048439
             3328                     .5845           174536967
             3536                     .5401           161281775
             3744                     .4965           148263914
             3952                     .4532           135338996
             4160                     .4099           122404260

Finally a quick review of resize operations indicates that there is a constant pressure on the buffer cache

select
        to_char(start_time,'hh24:mi:ss') timed_at,
        oper_type,
        component,
        parameter,
--      oper_mode,
        initial_size/1024/1024 "St(Mb)"     ,
        final_size/1024/1024 "Fin(Mb)"
from
        v$sga_resize_ops
where
        start_time >= trunc(sysdate)
and component  = 'DEFAULT buffer cache'
order by
        start_time, component;
TIMED_AT OPER_TYPE      COMPONENT                PARAMETER                 St(Mb)    Fin(Mb)
 -------- -------------- ------------------------ --------------------- ---------- ----------
00:00:29 SHRINK         DEFAULT buffer cache     db_cache_size               2176       2160
00:03:31 SHRINK         DEFAULT buffer cache     db_cache_size               2160       2144
00:07:04 GROW           DEFAULT buffer cache     db_cache_size               2144       2160
00:11:39 GROW           DEFAULT buffer cache     db_cache_size               2160       2176
00:18:14 GROW           DEFAULT buffer cache     db_cache_size               2176       2192
00:22:47 SHRINK         DEFAULT buffer cache     db_cache_size               2192       2176
00:25:52 GROW           DEFAULT buffer cache     db_cache_size               2176       2192
00:26:52 SHRINK         DEFAULT buffer cache     db_cache_size               2192       2176
00:29:54 GROW           DEFAULT buffer cache     db_cache_size               2176       2192
00:30:55 SHRINK         DEFAULT buffer cache     db_cache_size               2192       2176
00:44:04 SHRINK         DEFAULT buffer cache     db_cache_size               2176       2160
01:32:13 GROW           DEFAULT buffer cache     db_cache_size               2160       2176
01:44:16 SHRINK         DEFAULT buffer cache     db_cache_size               2176       2160
03:02:38 SHRINK         DEFAULT buffer cache     db_cache_size               2160       2144
04:16:23 SHRINK         DEFAULT buffer cache     db_cache_size               2144       2128
06:00:47 GROW           DEFAULT buffer cache     db_cache_size               2128       2144
06:01:47 GROW           DEFAULT buffer cache     db_cache_size               2144       2160
06:02:47 GROW           DEFAULT buffer cache     db_cache_size               2160       2176
06:03:48 GROW           DEFAULT buffer cache     db_cache_size               2176       2192
06:06:18 SHRINK         DEFAULT buffer cache     db_cache_size               2192       2176
06:09:49 SHRINK         DEFAULT buffer cache     db_cache_size               2176       2160
06:15:20 SHRINK         DEFAULT buffer cache     db_cache_size               2160       2144
06:22:21 GROW           DEFAULT buffer cache     db_cache_size               2144       2160
06:33:57 GROW           DEFAULT buffer cache     db_cache_size               2160       2176
07:01:02 SHRINK         DEFAULT buffer cache     db_cache_size               2176       2160
07:04:33 SHRINK         DEFAULT buffer cache     db_cache_size               2160       2144
08:00:44 SHRINK         DEFAULT buffer cache     db_cache_size               2144       2128
08:09:19 SHRINK         DEFAULT buffer cache     db_cache_size               2128       2112
08:20:51 SHRINK         DEFAULT buffer cache     db_cache_size               2112       2096

So now we have clear evidence that increasing memory and in particular the size of the cache would reduce I/O by a very significant level, yet on first viewing of the memory target advisor that was not the case.

I cannot move forward with this just at the moment because I need to see if we can get more memory added to the server but it is certainly something to work on in the next couple of weeks. Obviously I would need to do more research before changing production values but I have sufficient evidence to think it might be a worthwhile exercise.

 

Advertisements

5 Responses to “Memory advisors – how reliable?”

  1. […] How reliable are the memory advisors? […]

  2. jkstill said

    Nice analysis John. What I think would be cool is a PL/SQL script to analyze these and make recommendations with justification;
    both for the v$ views and the DBA_HIST views.

    Something I’ve had in the back of my mind for some time now.

    What do you think, does that sound useful?

    • John Hallas said

      Thanks for the feedback Jared.
      I think it sounds very useful and I was thinking along those lines myself – it is really a matter of determining what the optimum point is – a colleague has something which I think he will post here soon which is a starting point. The script must offer advice only and options and not be too prescriptive.

    • jsevanslds said

      So I might have missed the point here… 🙂 but the below is a quick attempt at trying to produce a list of recommendations where there are gains to be had while also taking into account the law of diminishing returns:

      select sga.SGA_SIZE
              ,sga.ESTD_PHYSICAL_READS
              ,round( 1-( sga.ESTD_PHYSICAL_READS / A.ESTD_PHYSICAL_READS ) ,4 ) * 100 as "% Improv"
              ,sga.SGA_SIZE_FACTOR as "Size Factor"
              ,round( ( ratio_to_report(sga.ESTD_PHYSICAL_READS ) over () ),4 ) * 100 as "Cost Eff Ratio"
          from v$sga_target_advice sga
              ,( select ESTD_PHYSICAL_READS
                  from v$sga_target_advice
                  where SGA_SIZE_FACTOR = 1 ) A
              ,( select avg(ESTD_PHYSICAL_READS ) as ESTD_PHYSICAL_READS_AVG
                   from v$sga_target_advice
                  where SGA_SIZE_FACTOR >= 1) B
         where sga.SGA_SIZE_FACTOR > 1
           and B.ESTD_PHYSICAL_READS_AVG < (A.ESTD_PHYSICAL_READS * 0.9)
      order by sga.SGA_SIZE
      /

      SGA_SIZE ESTD_PHYSICAL_READS % Improv Size Factor Cost Eff Ratio
      ———- ——————- ———- ———– ————–
      3340 254653189 25.73 1.25 33.24
      4008 186832534 45.51 1.5 24.39
      4676 170580263 50.25 1.75 22.27
      5344 154019406 55.08 2 20.1

  3. […] reliable are the memory advisors […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: