RE: Automatic Memory Management
Date: Thu, 10 Jan 2013 06:48:16 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8856A55462F_at_NADCWPMSGCMS10.hca.corpad.net>
<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-lReceived on Thu Jan 10 2013 - 13:48:16 CET