Re: Automatic Memory Management
- From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
- Date: Thu, 10 Jan 2013 12:42:46 +0100
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(@"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@xxxxxxxxxx>
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--
//www.freelists.org/webpage/oracle-l
Other related posts:
