Oracle 10G and HP-UX 11.11 - Is it still Best Practice to Fit SGA in 1 Shared Memory Segment?

[Alzhy]

I remember years ago being told it was recommended by Oracle that SGA should fit in 1 Shared Memory Segment. So our rule of thumb was to make SHMMAX large enough to fit the SGA.

Is this still the case with Oracle 10G and 11.11?

We've a large instance which is now at 41GB of SGA -- and we missed increasing SHMMAX. Will this be an issue?

[Hein van den Heuvel]

imho the deal is to avoid having dozens or hundreds of sections, and perhaps even cause an Oracle startup failure allocating too many segments.

I don't think you will be able to measure/feel the difference between one very large segment and a hand-ful good-sized ones.

If, as I suspect, your SMHMAX setting was already large, for example 4GB then I would not worry and only put it on a list of things to do before the next restart.
So set up the new larger value, and wait.

fwiw,
Hein.

[Alzhy]

Thanks Hein,

Our server currently only has 25GB as SHMMAX. UnBeknownst to us the SGA of the lone DB instance was increased to 41GB so right now the instance uses 2 SHM segments - a 25GB one and anothere 16GB.

Am not sure how to check if the segments are contigous though.

[Eric Antunes · Accepted solution]

Hi,

Yes, it is still the best practice to put the SGA in one shared memory segment.

But don't worry about that: shmamax is a dynamic parameter in HP-UX 11.11 so you can increase it without restarting the server and the Oracle database will allocate the SGA in one shared memory segment at the next startup.

Are you sure you need this kind of SGA? Can you post the following query output:

select to_number(value) shared_pool_size,
sum_obj_size,
sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool_size
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type <> 'CURSOR'),
(select sum(sharable_mem) sum_sql_size
from v$sqlarea),
(select sum(250 * users_opening) sum_user_size
from v$sqlarea), v$parameter
where name = 'shared_pool_size';


Best Regards,

Eric Antunes

[Alzhy]

Eric thanks....
Will relay that to my DBA.

I know the hard and fast rule in the past was based on cache hits.... lower pctg. means incerasing SGA some more until there's "balance".

We're using 10G --- are there wizards available to a DBA to "properly" tune (I know this is conttoversial) SGA?

I have to correct you though man -- SHMMAX is not a dynamic kern parm under HP-UX 11.11 aka HP-UX 11i v1.0. It is probably obsolte though under 11.31 -- aka HP-UX 11i v3.

[Dennis Handly]

>Z1ncwiskr: It is probably obsolete though under 11.31

No, it is still there as an enforcement mechanism:
http://docs.hp.com/en/B2355-60130/shmmax.5.html

[Alzhy]

Awesome Dennis Sir.
Indeed it still is.. just a handful that remained a tweakable.

[Eric Antunes]

"I know the hard and fast rule in the past was based on cache hits.... lower pctg. means incerasing SGA some more until there's "balance"

Let me be clear on this: this is not a past rule as Oracle 10g and 11g still uses the shared pool to SHARE sql. So, not sharable sql can and should be wiped out the shared pool as soon it is executed. Otherwise, you are just storing UNSHARABLE sql - trash - in your shared pool.

"I have to correct you though man -- SHMMAX is not a dynamic kern parm under HP-UX 11.11 aka HP-UX 11i v1.0. It is probably obsolte though under 11.31 -- aka HP-UX 11i v3."

It is dynamic in HP-UX 11.11.0509 (September 2005) or, I think, HP-UX 11i v1.5.

Best Regards,

Eric Antunes

[Don Morris_1]

No, shmmax was a dynamic tunable in 11i LR. One of the few in VM, so it sticks out in the memory.

(And 11i v1.5 was 11.20 -- Itanium only early adoption release. 1.6 was 11.22.)

[Alzhy]

Thanks Gents.

My coffee was not strong enough today. It is indeed dynamic:

# kmtune|grep -i shmmax
shmmax 25769803776 Y 25769803776