Re: Automatic Memory Management

From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Thu, 10 Jan 2013 12:42:46 +0100
Message-ID: <50EEA936.3090309_at_nordea.com>

IMHO AMM has a tendency to exaggerate shared pool needs, thus assuming not too big activity on the database the memory migrates slowly from cache to shared pool to values, which are quite ridiculous (assuming no minimum set). On the other hand on databases with high activity and rapidly changing demand for memory in different pools it is unstable. So I use it mainly on test environments.

And Your conclusion is too far going for me - the only rule of thumb I use is to set db_cache_size to around 10% of the database assuming new quite small database with unknown load - the rest is quite hard to describe - usually first settings are based on "experienced" guess and afterwards goes observation and adjustment

On 10.01.2013 11:22, Juan Miranda wrote:
>
> 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.
>

-- 
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

Received on Thu Jan 10 2013 - 12:42:46 CET