Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
Alzhy
Honored Contributor

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

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?


Hakuna Matata.
9 REPLIES
Hein van den Heuvel
Honored Contributor

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

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

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

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.

Hakuna Matata.
Eric Antunes
Honored Contributor
Solution

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

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
Each and every day is a good day to learn.
Alzhy
Honored Contributor

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

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.
Hakuna Matata.
Dennis Handly
Acclaimed Contributor

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

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

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

Awesome Dennis Sir.
Indeed it still is.. just a handful that remained a tweakable.
Hakuna Matata.
Eric Antunes
Honored Contributor

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

"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
Each and every day is a good day to learn.
Don Morris_1
Honored Contributor

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

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

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

Thanks Gents.

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

# kmtune|grep -i shmmax
shmmax 25769803776 Y 25769803776

Hakuna Matata.