RE: Automatic Memory Management

From: Juan Miranda <jcmiranda_at_oesia.com>
Date: Mon, 14 Jan 2013 12:04:39 +0100
Message-ID: <017101cdf246$f2fb4b40$d8f1e1c0$_at_oesia.com>


I knew I can define minimums but this way AMM don´t help too much... I think Oracle must review this issue.

Thanks for your help.
Juan

-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Christopher.Taylor2_at_parallon.net Enviado el: jueves, 10 de enero de 2013 13:48 Para: Juan Cruz Miranda Vigo; remigiusz.sokolowski_at_nordea.com; martin.a.berger_at_gmail.com
CC: oracle-l_at_freelists.org; dlpl-sed-mad-db_at_nordea.com Asunto: RE: Automatic Memory Management

<quote>
My conclusion: If I have any sql not using bind variables, then I will have a big shared pool and little buffer cache... Is this conclusion correct ?
Could you test this also?
Are you using AMM?
</quote>

If you solely let Oracle manage your memory without any adjustments, then YES you are correct when you have a lot of non-shared SQL.

However, as has been pointed out, if you set a VALUE for db_cache_size, shared_pool_size (etc) then you are in effect setting a FLOOR for those sizes

So if you set, shared_pool_size=5000M for example, then you are telling the AMM to never reduce your shared_pool_size BELOW that value. Same for buffer cache sizing.

If you switch off AMM, you do run a small risk of the ORA-4031 errors if you don't have everything adjust correctly (at least in 10g).

Regards,

Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Miranda
Sent: Thursday, January 10, 2013 4:23 AM To: remigiusz.sokolowski_at_nordea.com; martin.a.berger_at_gmail.com Cc: 'Oracle-L oracle-l'; dlpl-sed-mad-db_at_nordea.com Subject: RE: Automatic Memory Management

More shared pool than buffers:
select name, round(bytes/1024/1024) from v$sgainfo;

Buffer Cache Size                                 2288
Shared Pool Size                                   3600
Maximum SGA Size                              5973
Free SGA Memory Available                      0


This database have near 2GB of SQL plans residing on memory... equal than buffer cache!:
select pool,name,round(bytes/1024/1024) from v$sgastat order by 3;

shared pool  free memory                      442
shared pool  KGLH0                                 794
shared pool  SQLA                                 1898


We have, "of course", lots of SQL duplicates: SELECT COUNT(HASH_VALUE) COPIES, SUBSTRB(SQL_TEXT,1,150) SQL_TEXT FROM V$SQLAREA
  GROUP BY SUBSTRB(SQL_TEXT,1,150) ORDER BY COPIES ASC; COPIES SQL_TEXT

	...........
       694 SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0)
no_sql_tune no_monitoring optimizer_features_enable(default) OPT_ESTIMATE(_at_"innerQuer

       796 select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_expand_table(t) index_rs(t) no_parallel(t) no_parallel_index(t) dbms_stats curso

      1555 select corequartz0_.ID as ID0_, corequartz0_.SISTEMA as SISTEMA0_, corequartz0_.ACTIVA as ACTIVA0_, corequartz0_.IDEPEDI4_0_, corequa

My conclusion: If I have any sql not using bind variables, then I will have a big shared pool and little buffer cache... Is this conclusion correct ?
Could you test this also?
Are you using AMM?

Thanks.

-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Remigiusz Sokolowski Enviado el: miércoles, 09 de enero de 2013 8:36
Para: martin.a.berger_at_gmail.com
CC: Juan Cruz Miranda Vigo; Oracle-L oracle-l; dlpl-sed-mad-db_at_nordea.com Asunto: Re: Automatic Memory Management

from time to time in certain situations even ASMM makes some troubles. So if You are more or less sure how much memory there should be in all the pools/caches then You may still set it manually - works very stable.

On 08.01.2013 22:24, Martin Berger wrote:

> Juan,
> You can set a value for DB_CACHE_SIZE which is used as a minimal setting.
> 
> By doing so you can allow AMM within some boundaries.
> 
> Hth,
> Martin
>  Am 08.01.2013 15:42 schrieb "Juan Miranda" <jcmiranda_at_oesia.com>:
> 

>>
>> I used to configure AMM assigning appropiate values to sga_target and
>> pga_aggregate_target
>>
>> but nearly all my dbs have more memory assigned to shared_pool than
>> buffer_cache.
>>
>>
>>
>> v$sgainfo;
>>
>>
>>
>> NAME BYTES RES
>>
>> -------------------------------- ---------- ---
>>
>> Buffer Cache Size 2751463424 Yes
>>
>> Shared Pool Size 3238002688 Yes
>>
>> Maximum SGA Size 6263357440 No
>>
>> Free SGA Memory Available 0
>>
>>
>>
>> I can´t solve the possible problems with bind variables, etc.
>>
>>
>>
>> What are you doing in this situation? manual MM?
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 


--
Pole na kazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
pos   : Senior DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A st, 81-537 Gdynia, Poland phone :
+48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem:
0000021828, dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc
w Gdansku, VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego, o kapitale
zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych,
NIP: 586-000-78-20, REGON: 190024711--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 14 2013 - 12:04:39 CET

Original text of this message