1748111 Members
3896 Online
108758 Solutions
New Discussion юеВ

oracls SGA

 
Mike Boswell_1
Frequent Advisor

oracls SGA

What would explain that w/ ipcs -ma I see a SEGSZ of 655331328 but in the spfile.ora it shows *.sga_max_size=600M#old-450m ? 11.11 and 9.2.0

Thanks,
Mike
10 REPLIES 10
TwoProc
Honored Contributor

Re: oracls SGA

Mike,

My guess is that it's because that number is used to set the sga_max_size to something *larger* than the default. Since you're not there, it would have no impact.

That's just my guess as to how this could happen. But - you can standup a 600M sized database without indicating the sga_max_size at all.

I don't know what the default is, but I know that I if I don't have mine set large enough, I can't bring up the database. However, my SGA sizes are much larger than what we are talking here.
We are the people our parents warned us about --Jimmy Buffett
Mike Boswell_1
Frequent Advisor

Re: oracls SGA

If I understand you correctly you are 'guessing' that 600M is less than the Oracle default and that it is being ignored during startup?

Mike
TwoProc
Honored Contributor

Re: oracls SGA

I can only "guess" becuase the last time I had an SGA that small, that parameter didn't exist.
We are the people our parents warned us about --Jimmy Buffett
skt_skt
Honored Contributor

Re: oracls SGA

are u sure the shmid corresponding to the 655331328 is the one that DB related to?
klb
Valued Contributor

Re: oracls SGA


Find out if the default is higher by looking it up in the running instance:

show parameter sga

select name,value from v$parameter where
name like '%sga%'
Mike Boswell_1
Frequent Advisor

Re: oracls SGA

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 487551400


1* select name,value from v$parameter where name like '%sga%'

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
sga_max_size
487551400

pre_page_sga
FALSE

lock_sga
FALSE

Yogeeraj_1
Honored Contributor

Re: oracls SGA

hi mike,

what does the query below show?

show parameter sga_target

revert
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: oracls SGA

Hmmm, I thought you said 600MB? Now it shows sga_max_size = 487551400 ?

This is 64 bit oracle right, or still using 32 bit?

Anyway, here are some thoughts...


Check out metalink 171634.1:
"Spfile Parameter SGA_MAX_SIZE can be Smaller than SGA Size"
...
"The SGA_MAX_SIZE parameter is used to limit the dynamic sizing of SGA
parameters. If the SGA_MAX_SIZE parameter value in the spfile is smaller than
the addition of all SGA components, then the dynamic value of SGA_MAX_SIZE
instance parameter is automatically set by Oracle to the SGA size."

You may also want to check out # 148495.1 "Dynamic SGA" it explains how there will be 16MB granules.

600MB is not divisible by 16, so it may round up to 38*16*1024*1024 = 637534208, but that's still not 655331328

Good luck,
Hein.
Mike Boswell_1
Frequent Advisor

Re: oracls SGA

Yogeeraj
I am returned a blank like w/ that query.

Hein van den Heuvel,
We had memory pressure do to the increase to 600mb and we have reverted to 450. 29*16*1024*1024 = 486539264 (464mb) seems to make sense now but still can't explain what was going on at 650mb. I'll let this go for now. Thanks for everyone's involvement.

Mike