Home » RDBMS Server » Server Administration » Changing SGA_MAX_SIZE
Changing SGA_MAX_SIZE [message #197558] Wed, 11 October 2006 15:15 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I see no problem.
sys@9i > show user
USER is "SYS"
sys@9i > ALTER SYSTEM SET sga_max_size=100m scope=spfile;

System altered.
Re: Changing SGA_MAX_SIZE [message #197592 is a reply to message #197558] Wed, 11 October 2006 23:08 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Can u plz paste exactly what r u did.
Re: Changing SGA_MAX_SIZE [message #197778 is a reply to message #197592] Thu, 12 October 2006 12:10 Go to previous messageGo to next message
itzac
Messages: 4
Registered: October 2006
Junior Member
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
Re: Changing SGA_MAX_SIZE [message #197780 is a reply to message #197778] Thu, 12 October 2006 12:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As Mahesh indicated,

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 Go to previous messageGo to next message
itzac
Messages: 4
Registered: October 2006
Junior Member
Thanks, guys. I figured it would something braindead simple.
Re: Changing SGA_MAX_SIZE [message #197783 is a reply to message #197781] Thu, 12 October 2006 12:38 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Happens to all of us at some point or another!!
Re: Changing SGA_MAX_SIZE [message #324638 is a reply to message #197783] Tue, 03 June 2008 07:48 Go to previous messageGo to next message
shrinika
Messages: 266
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..

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??
Re: Changing SGA_MAX_SIZE [message #324642 is a reply to message #324638] Tue, 03 June 2008 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a STATIC parameter, you have to restart the instance.

Regards
Michel
Re: Changing SGA_MAX_SIZE [message #324643 is a reply to message #324638] Tue, 03 June 2008 08:20 Go to previous messageGo to next message
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.
Re: Changing SGA_MAX_SIZE [message #324693 is a reply to message #324643] Tue, 03 June 2008 13:04 Go to previous messageGo to next message
shrinika
Messages: 266
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.

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.


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.
Re: Changing SGA_MAX_SIZE [message #324702 is a reply to message #324693] Tue, 03 June 2008 13:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Mahesh & Michel, Thank you. It makes sense. Thanks again.
Re: Changing SGA_MAX_SIZE [message #324732 is a reply to message #197558] Tue, 03 June 2008 16:27 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Hello,

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

Here is my database version.

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.

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..

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

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

SQL>

Re: Changing SGA_MAX_SIZE [message #324733 is a reply to message #197558] Tue, 03 June 2008 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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?
Re: Changing SGA_MAX_SIZE [message #324754 is a reply to message #324732] Tue, 03 June 2008 23:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Changing SGA_MAX_SIZE [message #324951 is a reply to message #324754] Wed, 04 June 2008 10:42 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Anacedent, Yes. it is tiny database. It is my test database which is created in my home computer.

Michel,


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.
Re: Changing SGA_MAX_SIZE [message #324952 is a reply to message #197558] Wed, 04 June 2008 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: Changing SGA_MAX_SIZE [message #324969 is a reply to message #324952] Wed, 04 June 2008 11:55 Go to previous messageGo to next message
shrinika
Messages: 266
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.

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

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

SQL> 
Re: Changing SGA_MAX_SIZE [message #633368 is a reply to message #324638] Tue, 17 February 2015 04:17 Go to previous messageGo to next message
ricojoseyn
Messages: 1
Registered: February 2015
Location: Philippines
Junior Member
You want to see the size of SGA? Laughing

sqlplus /nolog
conn /as sysdba
show parameter sga;


Now, you can see those SGA/PGA values.
Re: Changing SGA_MAX_SIZE [message #633371 is a reply to message #633368] Tue, 17 February 2015 04:31 Go to previous messageGo to next message
gazzag
Messages: 824
Registered: November 2010
Location: Bristol, UK
Senior Member
This thread started almost nine years ago Smile
Re: Changing SGA_MAX_SIZE [message #633373 is a reply to message #633371] Tue, 17 February 2015 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and "show sga" does not give any PGA information... and "show sga" has already been mentioned in the topic. 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 Go to previous messageGo to next message
EdStevens
Messages: 707
Registered: September 2013
Senior Member
gazzag wrote on Tue, 17 February 2015 04:31
This thread started almost nine years ago Smile


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.
Re: Changing SGA_MAX_SIZE [message #633477 is a reply to message #633475] Thu, 19 February 2015 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: Changing SGA_MAX_SIZE [message #633478 is a reply to message #633477] Thu, 19 February 2015 08:15 Go to previous messageGo to next message
gazzag
Messages: 824
Registered: November 2010
Location: Bristol, 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.
Re: Changing SGA_MAX_SIZE [message #633483 is a reply to message #633478] Thu, 19 February 2015 08:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3122
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.
Re: Changing SGA_MAX_SIZE [message #633514 is a reply to message #633483] Fri, 20 February 2015 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you say so ... ./fa/1693/0/

./fa/12497/0/

Re: Changing SGA_MAX_SIZE [message #633516 is a reply to message #633514] Fri, 20 February 2015 00:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3122
Registered: May 2013
Location: World Wide on the Web
Senior Member
Shocked

Eyes wide open now. Apologies.
Re: Changing SGA_MAX_SIZE [message #633530 is a reply to message #633516] Fri, 20 February 2015 13:36 Go to previous messageGo to next message
itzac
Messages: 4
Registered: October 2006
Junior Member
Cut it out. You all are making feel old.
Re: Changing SGA_MAX_SIZE [message #633540 is a reply to message #633477] Fri, 20 February 2015 16:43 Go to previous message
EdStevens
Messages: 707
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.
Previous Topic: Bigfile Tablespace
Next Topic: Partition Exchange Error On Table With Nested Table
Goto Forum:
  


Current Time: Thu Dec 08 14:14:37 CST 2016

Total time taken to generate the page: 0.25970 seconds