oracle sga etc. and memory on new small system...

[John Kittel]

We are starting up oracle 10.2 on a new rp2470 with 1GB memory, hp-ux 11.11. It is supposed to be a "small" development system / sandbox. I don't know oracle. Memory is already nearly 100% used with no user activity occurring, - just oracle started up, no one logged in doing anything. Currently dbc_max_pct is 10, for 100MB max buffer cache size.

Oracle dba, and dba consultant, are saying sga is a reasonable value of aprox 450 Meg with a pga_aggregate_target of 153 Meg. They recommend setting dbc_max_pct down from 10 to 5 and dbc_min_pct to a value of 2 to free up additional RAM on the server from the filesystem buffer cache. It will also ease the memory pressure. That's all they have to say about it.

I know it would be best to buy more memory. But short of doing that, can anyone advise if the sga / pga sizes might need further adjustment? Also, reducing max buffer cache from 10 to 5 will only free up additional 50MB of memory, - is this advisable?

[Patrick Wallek · Accepted solution]

The first thing I would do is go through ALL files in the /etc/rc.config.d directory and turn OFF anything that is not absolutely essential. This will help to free up some RAM.

I would really worry too much about DBC at this point. 50MB vs. 100MB for a max probably won't make much difference.

See how low you can get your OS RAM usage before you start worrying about Oracle.

Even for a test/sandbox system, especially with Oracle 10G, 1GB RAM is really small. I wouldn't do less than 4GB RAM myself.

[Steven E. Protter]

Shalom,

Have the oracle person reduce sga memory requirements.

You can free up buffer cache by lowering dbc_max_pct a few points. It is advisable to do both.

SEP

[spex]

Hi John,

> Memory is already nearly 100% used with no
> user activity occurring

This is good--you want Oracle to utilize nearly all available RAM for cache in the SGA.

> They recommend setting dbc_max_pct down
> from 10 to 5 and dbc_min_pct to a value of
> 2 to free up additional RAM on the server
> from the filesystem buffer cache.

'dbc_max_pct' doesn't really matter. 'dbc_min_pct' does, but even so, you're only talking a potential savings of 30MB or so.

> I know it would be best to buy more memory.
Absolutely. In fact, I'm not sure that configuration even meets the minimum requirements for 10g under HP-UX.

> But short of doing that, can anyone advise
> if the sga / pga sizes might need further
> adjustment?

Tune the shared pool, buffer cache, and redo log buffer sizes. Set 'java_pool_size=0' if you don't use Java. Disable unnecessary/excessive Oracle server processes (e.g. parallel servers, redo log archivers, etc.). Disable unnecessary OS daemons.

Also realize that 'pga_aggregate_target' is just that: a target. There's nothing to prevent the PGA from exceeding this setting as more sessions are established, in which case, with such little RAM, you will be in trouble.

PCS

[John Kittel]

Thanks everyone. I will follow all the suggestions.

- John