Home » RDBMS Server » Performance Tuning » How to configure SGA_MAX_SIZE parameter (Oracle Enterprise 11.2.0.3, RH Enterprise Linux 5)
How to configure SGA_MAX_SIZE parameter [message #596544] Tue, 24 September 2013 11:46 Go to next message
rcct
Messages: 18
Registered: May 2013
Location: Ottawa, ON
Junior 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.

icon1.gif  Re: How to configure SGA_MAX_SIZE parameter [message #596546 is a reply to message #596544] Tue, 24 September 2013 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: How to configure SGA_MAX_SIZE parameter [message #596548 is a reply to message #596544] Tue, 24 September 2013 12:46 Go to previous messageGo to next message
rcct
Messages: 18
Registered: May 2013
Location: Ottawa, ON
Junior 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.
icon2.gif  Re: How to configure SGA_MAX_SIZE parameter [message #596549 is a reply to message #596548] Tue, 24 September 2013 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: How to configure SGA_MAX_SIZE parameter [message #596560 is a reply to message #596549] Tue, 24 September 2013 13:31 Go to previous messageGo to next message
rcct
Messages: 18
Registered: May 2013
Location: Ottawa, ON
Junior 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.
icon2.gif  Re: How to configure SGA_MAX_SIZE parameter [message #596564 is a reply to message #596560] Tue, 24 September 2013 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: How to configure SGA_MAX_SIZE parameter [message #597390 is a reply to message #596564] Fri, 04 October 2013 05:24 Go to previous messageGo to next message
8939513598$
Messages: 101
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 ?
icon2.gif  Re: How to configure SGA_MAX_SIZE parameter [message #597426 is a reply to message #597390] Fri, 04 October 2013 11:01 Go to previous message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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.

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


Current Time: Sun Apr 20 03:26:23 CDT 2014

Total time taken to generate the page: 0.07513 seconds