OraFAQ Forum: Performance Tuning » How to configure SGA_MAX

Home » RDBMS Server » Performance Tuning » How to configure SGA_MAX_SIZE parameter (Oracle Enterprise 11.2.0.3, RH Enterprise Linux 5)

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

How to configure SGA_MAX_SIZE parameter [message #596544]

Tue, 24 September 2013 11:46

rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON

Member

Hi all,

The Memory tuning advisor has recommended that I increase the size of the SGA_TARGET to 5G, from my current size of 3G.


SQL> select * from v$sga_target_advice order by sga_size;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      1536              .5      1555141               1.667             7251211
      2304             .75       949130              1.0174             4572127
      3072               1       932898                   1             2258733
      3840            1.25       930939               .9979             2168835
      4608             1.5       929819               .9967             2005529
      5376            1.75       929166                .996             2005529
      6144               2       928980               .9958             2005529

7 rows selected.

However, the SGA_MAX_SIZE parameter is set to 3G, so when I do:


SQL> ALTER SYSTEM SET sga_target=5376M scope=both;
ALTER SYSTEM SET sga_target=5376M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

My database used spfile. I know that the procedure to change this is the following:

Create pfile from spfile, shutdown immediate, change value in pfile, startup with pfile

However, the pfile does not have any parameter called max_sga_size. It only has the following:

<dbsid>.__sga_target=3221225472
*.sga_target=3221225472

Can someone recommend how to change sga_max_size? Thanks.

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #596546 is a reply to message #596544]

Tue, 24 September 2013 12:15

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

Senior Member
Account Moderator

1/ You have not to change the parameter; if you increase it from 3072MB to 5376MB you will increase the efficiency of the SGA by 0.4%, is this worth 2GB?

2/ To change the parameter you must use "scope=spfile" and restart.

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #596548 is a reply to message #596544]

Tue, 24 September 2013 12:46

rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON

Member

Hi Michel, the reason I wanted to increase that, was to decrease the physical reads from 2258733 to 2005529 (approx. 10% improvement). Would that be beneficial?

Also, just for my knowledge, would the steps to change sga_max_size be:

1) alter system set sga_max_size=5376M scope=spfile;
2) restart the database

Please advise. Thanks,

Roy.

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #596549 is a reply to message #596548]

Tue, 24 September 2013 12:49

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

Senior Member
Account Moderator

Quote:

the reason I wanted to increase that, was to decrease the physical reads from 2258733 to 2005529 (approx. 10% improvement)

Those are estimations.
Are you suffering of such io problems that you want to take these values into account?

Quote:

would the steps to change sga_max_size be

Yes.

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #596560 is a reply to message #596549]

Tue, 24 September 2013 13:31

rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON

Member

Actually there were some performance issues, but I have asked the developers to look into the code and tune their queries. Thanks for confirming the steps to change the parameter.

Roy.

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #596564 is a reply to message #596560]

Tue, 24 September 2013 13:53

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

Senior Member
Account Moderator

Quote:

Actually there were some performance issues,

You have to determine the origin of the problem.
It is good to ask the developers to review their code.
In the meantime, you can extract some AWR/StatsPack report which can help you to see the behaviour of the database.

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #597390 is a reply to message #596564]

Fri, 04 October 2013 05:24

8939513598$
Messages: 103
Registered: July 2013
Location: chennai

Senior Member

Hi Michel, whether the parameter "MEMORY_TARGET" obsolete in 11.2.0.1.0 ?,let me understand better, my output was,

SQL> alter system set MEMORY_TARGET=10000M scope=spfile;

System altered.

SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system
SQL>

what would be reason ?

Report message to a moderator

Re: How to configure SGA_MAX_SIZE parameter [message #597426 is a reply to message #597390]

Fri, 04 October 2013 11:01

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

Senior Member
Account Moderator

Quote:

Whether the parameter "MEMORY_TARGET" obsolete in 11.2.0.1.0 ?

SQL> select * from V$OBSOLETE_PARAMETER where upper(name)='MEMORY_TARGET';

no rows selected

No.
This can also be checked against the documentation.

ORA-00845: MEMORY_TARGET not supported on this system
 *Cause: The MEMORY_TARGET parameter was not supported on this operating system 
         or /dev/shm was not sized correctly on Linux.
 *Action: Refer to documentation for a list of supported operating systems. 
          Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance 
          running on the system.

This can also be checked against the documentation.

Report message to a moderator

Previous Topic:	Please advise on configuring memory
Next Topic:	MEMORY_TARGET error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 12:15:17 CST 2025