OraFAQ Forum: Server Administration » Changing SGA_MAX_SIZE

Home » RDBMS Server » Server Administration » Changing SGA_MAX_SIZE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Changing SGA_MAX_SIZE [message #197558]

Wed, 11 October 2006 15:15

itzac
Messages: 4
Registered: October 2006

Junior Member

I'm using 9i and I'm having a heck of a time finding out how to change the value of sga_max_size. I've tried:

alter system set sga_max_size = 100M scope = SPFILE

It of course won't run if the database is up, but it also doesn't work after a shutdown command. Could someone please spell it out for me from sqlplus login on a running instance as a starting point? Or point me to the pertinent Oracle doc, even. I'd be your best friend forever.

It's very frustrating to know exactly what you want to do and not be able to figure out how to do it.

[Updated on: Wed, 11 October 2006 16:17]

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #197568 is a reply to message #197558]

Wed, 11 October 2006 18:41

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I see no problem.

Code: [Select all] [Show/ hide]

sys@9i > show user
USER is "SYS"
sys@9i > ALTER SYSTEM SET sga_max_size=100m scope=spfile;

System altered.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #197592 is a reply to message #197558]

Wed, 11 October 2006 23:08

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Can u plz paste exactly what r u did.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #197778 is a reply to message #197592]

Thu, 12 October 2006 12:10

itzac
Messages: 4
Registered: October 2006

Junior Member

Code: [Select all] [Show/ hide]

C:\>sqlplus /nolog

SQL> connect sys/* as sysdba
Connected.
SQL> alter system set sga_max_size = 700MB scope=SPFILE;
alter system set sga_max_size = 700MB scope=SPFILE
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 11 10:44:11 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect sys/* as sysdba
Connected to an idle instance.
SQL> alter system set sga_max_size = 700MB scope=SPFILE;
alter system set sga_max_size = 700MB scope=SPFILE
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> help startup
SP2-0171: HELP not accessible.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  839983304 bytes
Fixed Size                   454856 bytes
Variable Size             310378496 bytes
Database Buffers          528482304 bytes
Redo Buffers                 667648 bytes
SQL> alter system set sga_max_size = 700MB scope=SPFILE;
alter system set sga_max_size = 700MB scope=SPFILE
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #197780 is a reply to message #197778]

Thu, 12 October 2006 12:31

ebrian
Messages: 2794
Registered: April 2006

Senior Member

As Mahesh indicated,

Code: [Select all] [Show/ hide]

SQL> alter system set sga_max_size = 700M scope=SPFILE;

Notice it's 700M not 700MB.

[Updated on: Thu, 12 October 2006 12:31]

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #197781 is a reply to message #197780]

Thu, 12 October 2006 12:33

itzac
Messages: 4
Registered: October 2006

Junior Member

Thanks, guys. I figured it would something braindead simple.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #197783 is a reply to message #197781]

Thu, 12 October 2006 12:38

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Happens to all of us at some point or another!!

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324638 is a reply to message #197783]

Tue, 03 June 2008 07:48

shrinika
Messages: 306
Registered: April 2008

Senior Member

Hello,

I changed the sga_max_size. But i am not able to see when i run the query select value from v$parameter where name='sga_max_size';

Here is the screenshot..

Code: [Select all] [Show/ hide]

C:\>set oracle_sid=oraprod
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jun 3 08:26:25 2008
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> connect sys/sys as sysdba
Connected.
SQL> select value from v$parameter where name='sga_max_size';

VALUE
--------------------------------------------------------------------------------

135338868
SQL> alter system set sga_max_size=100M scope=SPFILE
  2  ;

System altered.
SQL> select value from v$parameter where name='sga_max_size';
VALUE
--------------------------------------------------------------------------------
135338868
SQL> startup force
ORACLE instance started.
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name='sga_max_size';
VALUE
--------------------------------------------------------------------------------
135338868

SQL>

Is there any other init paramter i should change for this??

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324642 is a reply to message #324638]

Tue, 03 June 2008 08:18

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is a STATIC parameter, you have to restart the instance.

Regards
Michel

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324643 is a reply to message #324638]

Tue, 03 June 2008 08:20

ebrian
Messages: 2794
Registered: April 2006

Senior Member

That's because the sum of all the SGA components add up to more than what you are specifying for SGA_MAX_SIZE.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324693 is a reply to message #324643]

Tue, 03 June 2008 13:04

shrinika
Messages: 306
Registered: April 2008

Senior Member

Hello ebrian, I agree with you. Thanks again.

Michel, Based on the reply, here is my understanding. We need to restart the instance for seeing the changed value in data dictionary. But once we altered the values by using the alter system command, it will be affective immediately. Please correct me if i miss anything...

Another question.

Code: [Select all] [Show/ hide]

SQL> select value from v$parameter
  2  where name='log_buffer';

VALUE
--------------------------------------------------------------------------------

524288

My understanding is, the above query should match the below Redo Buffers value. But it is not matching... Am i missing anything here... Please clarify me.

Code: [Select all] [Show/ hide]

SQL> show sga

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

SQL>

I would appreciate if you could answer.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324702 is a reply to message #324693]

Tue, 03 June 2008 13:37

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> But once we altered the values by using the alter system command, it will be affective immediately

http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_2013.htm#i2154767

>>My understanding is, the above query should match the below Redo Buffers value.
Always post your oracle version. From your early posting, it seems you are using 9i.
Show SGA will get its value from current SGA. It will not read the init parameters from the file and values may be a little off.
Did you also look into v$spparameter?
In 10g, you may get an accurate result.
http://www.orafaq.com/forum/m/218728/42800/?srch=show+sga#msg_218728

[Updated on: Tue, 03 June 2008 13:44]

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324708 is a reply to message #324693]

Tue, 03 June 2008 13:58

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

But once we altered the values by using the alter system command, it will be affective immediately.

Wrong, a static parameter sees its change only on the next instance life.
And this is the case for ANY parameter if you only record the change in spfile as you did.

Log buffer is flanked by guard pages that you can see in show sga.

Regards
Michel

[Updated on: Tue, 03 June 2008 14:05]

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324710 is a reply to message #324702]

Tue, 03 June 2008 14:02

shrinika
Messages: 306
Registered: April 2008

Senior Member

Mahesh & Michel, Thank you. It makes sense. Thanks again.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324732 is a reply to message #197558]

Tue, 03 June 2008 16:27

shrinika
Messages: 306
Registered: April 2008

Senior Member

Hello,

One more question. sorry to ask you too many question.

Here is my database version.

Code: [Select all] [Show/ hide]

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

I changed the shared_pool_size dynamically as below.

Code: [Select all] [Show/ hide]

SQL>
SQL> alter system set shared_pool_size=30m scope=both;

System altered.

The below query should match with 30M. But why it is not matching? Am i missing anything here..

Code: [Select all] [Show/ hide]

SQL> select
  2      sum(bytes)/1024/1024
  3   from
  4      v$sgastat
  5    where
  6  pool='shared pool';

SUM(BYTES)/1024/1024
--------------------
                  48

SQL>

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324733 is a reply to message #197558]

Tue, 03 June 2008 16:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what was the value BEFORE the ALTER SYSTEM command?

In my opinion, 30M is a tiny, tiny SGA.
What is minimum size for the SGA?

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324754 is a reply to message #324732]

Tue, 03 June 2008 23:02

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to wait a little while in order to let Oracle shrink the shared pool.
In addition, whatever you give, the final size is a multiple of granule size.

Regards
Michel

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324951 is a reply to message #324754]

Wed, 04 June 2008 10:42

shrinika
Messages: 306
Registered: April 2008

Senior Member

Anacedent, Yes. it is tiny database. It is my test database which is created in my home computer.

Michel,

Code: [Select all] [Show/ hide]

SQL> alter system set shared_pool_size=30m scope=both;

System altered.

SQL> 

SQL> select
  2  sum(bytes)/1024/1024
  3  from
  4  v$sgastat
  5  where
  6  pool='shared pool';

SUM(BYTES)/1024/1024
--------------------
                  48

SQL> select min_size/1024/1024 from v$sga_dynamic_components where
  2  component='shared pool';

MIN_SIZE/1024/1024
------------------
                32
SQL> 
SQL> select max_size/1024/1024 from v$sga_dynamic_components where
  2  component='shared pool';

MAX_SIZE/1024/1024
------------------
                32

SQL> 

The above query is showing 2 MB more, because of the granule size..

Now my question here is, i am not sure why v$sgastat and v$sga_dynamic_components are showing different figure.

In the previous thread, you said, we need to wait for some time to shrink.. but i waited for couple of days. But no difference.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324952 is a reply to message #197558]

Wed, 04 June 2008 10:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I am just curious as to what gets returned by
select * from v$sgainfo

All of the "V$" names are nothing more than automagical mappings into memory structures within the SGA.
These various views just may be looking at different parts of this elephant.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #324969 is a reply to message #324952]

Wed, 04 June 2008 11:55

shrinika
Messages: 306
Registered: April 2008

Senior Member

anacedent, This is oracle9i. I think, v$sgainfo is available in oracle10g

Again i checked v$parameter. It shows same as v$sga_dynamic_components. Only v$sgastat is not matching. May be it is bug in oracle9i.

Code: [Select all] [Show/ hide]

SQL> ;
  1* select value/1024/1024 from v$parameter where name ='shared_pool_size'
SQL> /

VALUE/1024/1024
---------------
             32

SQL>

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633368 is a reply to message #324638]

Tue, 17 February 2015 04:17

ricojoseyn
Messages: 1
Registered: February 2015
Location: Philippines

Junior Member

You want to see the size of SGA? Laughing

Laughing

sqlplus /nolog
conn /as sysdba
show parameter sga;

Now, you can see those SGA/PGA values.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633371 is a reply to message #633368]

Tue, 17 February 2015 04:31

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

This thread started almost nine years ago Smile

Smile

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633373 is a reply to message #633371]

Tue, 17 February 2015 04:42

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

... and "show sga" does not give any PGA information... and "show sga" has already been mentioned in the topic. Laughing

Laughing

[Updated on: Thu, 19 February 2015 08:04]

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633475 is a reply to message #633371]

Thu, 19 February 2015 07:18

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

gazzag wrote on Tue, 17 February 2015 04:31

This thread started almost nine years ago

I'm always curious about what a new forum member is doing to cause them to even FIND these archeological artifacts, much less feel compelled to respond to them.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633477 is a reply to message #633475]

Thu, 19 February 2015 07:59

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's not like google treats them as though they're archaeological artefacts.
Whenever I enter something oracle related into google it almost always displays some really old forum posts on the first page.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633478 is a reply to message #633477]

Thu, 19 February 2015 08:15

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

I must admit to having been guilty of failing to notice the date of a post occasionally. Like you say, when they bubble to the top of a Google search, I assume (wrongly) that they're more current than they actually are.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633483 is a reply to message #633478]

Thu, 19 February 2015 08:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Except anything specifically related to the version, I wouldn't mind to answer a newbie's question if it exactly related to the original question.

I would of course, expect it to be a new question if the post is related to a different version compared to the original question. The basic problem while a newbie posts a new question on a zombie thread is, there is no version specified.

In this thread, the original question was posted almost 7 years ago. The latest Oracle version then was obviously not what it is now. Therefore, it creates a big confusion. Google obviously doesn't take care of all this and might just show a zombie thread on top. It is only upto us to decide whether it should be a new question or not.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633514 is a reply to message #633483]

Fri, 20 February 2015 00:10

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you say so ... /forum/fa/1693/0/

/forum/fa/1693/0/

/forum/fa/12497/0/

Attachment: orafaq_7_years_ago.png
(Size: 111.95KB, Downloaded 8317 times)

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633516 is a reply to message #633514]

Fri, 20 February 2015 00:24

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Shocked

Eyes wide open now. Apologies.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633530 is a reply to message #633516]

Fri, 20 February 2015 13:36

itzac
Messages: 4
Registered: October 2006

Junior Member

Cut it out. You all are making feel old.

Report message to a moderator

Re: Changing SGA_MAX_SIZE [message #633540 is a reply to message #633477]

Fri, 20 February 2015 16:43

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

cookiemonster wrote on Thu, 19 February 2015 07:59

It's not like google treats them as though they're archaeological artefacts.
Whenever I enter something oracle related into google it almost always displays some really old forum posts on the first page.

Ah. I didn't consider the google factor.

Report message to a moderator

Previous Topic:	Bigfile Tablespace
Next Topic:	Partition Exchange Error On Table With Nested Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 05:05:13 CDT 2025