Home » RDBMS Server » Performance Tuning » SGA_MAX_SIZE to more than 2G? (10.2.0.1.0, OS=Windows 2003 server 64 bit with 16G RAM)
SGA_MAX_SIZE to more than 2G? [message #519294] Wed, 10 August 2011 21:14 Go to next message
pt12340
Messages: 8
Registered: August 2011
Junior Member
I am running Oracle 10.2.0.1.0 on MS Windows 2003 server 64-bit with 16G RAM.

Here is the findings for my Oracle database.

SQL> select * * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1293560 No
Redo Buffers 7094272 No
Buffer Cache Size 830472192 Yes
Shared Pool Size 1275068416 Yes
Large Pool Size 8388608 Yes
Java Pool Size 8388608 Yes
Streams Pool Size 16777216 Yes
Granule Size 8388608 No
Maximum SGA Size 2147483648 No
Startup overhead in Shared Pool 75497472 No
Free SGA Memory Available 0
SQL> show parameters sg_ a_max_size;
sga_max_size big integer 2G
SQL> show parameter sga_target;
sga_target big integer 2G

I find that the SGA component "Buffer Cache" is decreasing from the start "1.8G" and down to now 0.8G. On the other hand, the component "Shared Pool" is increasing from the start 0.3G to now 1.2G. I noticed that there are 100 operations of shrinking of "Buffer cache" and growth of "Shared Pool" in Oracle every day.
Is it a indicator that I should raise up the SGA_MAX_SIZE?

I tried to increase the SGA_MAX_SIZE to 4G. But I cannot start the Oracle afterward.
Is it a limitation of MS Windows(OS) or Oracle?

I set the SGA_MAX_SIZE to 3G. This time, I can startup Oracle.
What is the optimum/maximum I can set to SGA_MAX_SIZE?

Is there any adverse effect/concern when setting the SGA_MAX_SIZE more than 2G?

Thanks.
Re: SGA_MAX_SIZE to more than 2G? [message #519295 is a reply to message #519294] Wed, 10 August 2011 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post FORMATTED results from SQL below
SELECT * FROM V_$SGA_TARGET_ADVICE;

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;
Re: SGA_MAX_SIZE to more than 2G? [message #519296 is a reply to message #519295] Wed, 10 August 2011 21:48 Go to previous messageGo to next message
pt12340
Messages: 8
Registered: August 2011
Junior Member
Thanks your quick reply. Here is the requested detail.

SQL> select * from V$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      2048          1       298645      1            53293464
       512           .25    580685      1.9444     4559580935
      1024           .5     351147      1.1758     1651532473
      4096          2       298645      1            53293464
      2560          1.25    298645      1            53293464
      3072          1.5     298645      1            53293464
      3584          1.75    298645      1            53293464
      1536           .75    298914      1.0009      275543197

8 rows selected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE10.2.0.2.0Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Re: SGA_MAX_SIZE to more than 2G? [message #519298 is a reply to message #519296] Wed, 10 August 2011 21:55 Go to previous messageGo to next message
pt12340
Messages: 8
Registered: August 2011
Junior Member
For better viewing, I repost the formatted SGA info here.

SQL> select * * from v$sgainfo;
NAME                               BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1293560 No
Redo Buffers                        7094272 No
Buffer Cache Size                 830472192 Yes
Shared Pool Size                 1275068416 Yes
Large Pool Size                     8388608 Yes
Java Pool Size                      8388608 Yes
Streams Pool Size                  16777216 Yes
Granule Size                        8388608 No
Maximum SGA Size                 2147483648 No
Startup overhead in Shared Pool    75497472 No
Free SGA Memory Available                 0
SQL> show parameters  sg_ a_max_size;
sga_max_size     big integer 2G
SQL> show parameter sga_target;
sga_target     big integer 2G

Re: SGA_MAX_SIZE to more than 2G? [message #519299 is a reply to message #519296] Wed, 10 August 2011 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I want to greatly THANK YOU for actually doing as requested.

>select * from V$SGA_TARGET_ADVICE;
results do not indicate that DB would benefit from any change.
about how long has this instance been up & online?

>TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
Indicates Oracle is 32-bit; not 64-bit.
64-bit Oracle supports SGA larger than 3GB; without modification
Consider installing 64-bit Oracle
Re: SGA_MAX_SIZE to more than 2G? [message #519301 is a reply to message #519299] Wed, 10 August 2011 22:23 Go to previous messageGo to next message
pt12340
Messages: 8
Registered: August 2011
Junior Member
Thanks again your prompt advice. Actually, I have a multi-user program running on this Oracle database. (developed by third party). The program becomes to run slow gradually after startup the Oracle (A normal 4 minutes work can be up to and keep 10 minutes). The restart of Oracle sometimes fix the problem, sometimes not. As far as I know, "Buffer cache" is related to copies of data blocks read from datafiles. If "Buffer cache" is small (decreasing), is it possible that the Oracle spending more time to read data?

First, is it helpful to increase the SGA_MAX_SIZE for the above case?

Second, is it ok to increase the SGA_MAX_SIZE to 3G under my 32-bit Oracle? Is there any adverse effect to the Oracle? If it is just the memory issue to the OS, I have 16G RAM for that. And that server only serves this Oracle database.

Third, the maximum value of SGA_MAX_SIZE is 3G for 32-bit Oracle, right?

Forth, what else I can look at the Oracle to find the root cause?

Thanks again your valuable advice.

BlackSwan wrote on Thu, 11 August 2011 10:58
I want to greatly THANK YOU for actually doing as requested.

>select * from V$SGA_TARGET_ADVICE;
results do not indicate that DB would benefit from any change.
about how long has this instance been up & online?
Due to the performance issue, the instance needs to stop/start around every 2-3 weeks.>

TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
Indicates Oracle is 32-bit; not 64-bit.
64-bit Oracle supports SGA larger than 3GB; without modification
Consider installing 64-bit Oracle

[Updated on: Wed, 10 August 2011 22:26]

Report message to a moderator

Re: SGA_MAX_SIZE to more than 2G? [message #519302 is a reply to message #519301] Wed, 10 August 2011 22:32 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Consider installing 64-bit Oracle software (as soon as possible)

I am not clear why you are fixated on SGA without having any measurements (SQL & results) to substantiate this concern.
Many, many, many different factors can elongate elapsed time.
At OS level is CPU, RAM, I/O, or network a resource bottleneck?
change AWR snapshot period down from 1 hour to 15 minutes?
when application slowdown occurs, what does AWR report to be possible problem areas?
Previous Topic: Simple SQL with High Concurrency Wait Time (cursor: pin S wait on X)
Next Topic: nvl problem i think
Goto Forum:
  


Current Time: Thu Mar 28 11:02:09 CDT 2024