| SGA_MAX_SIZE to more than 2G? [message #519294] |
Wed, 10 August 2011 21:14  |
 |
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 #519301 is a reply to message #519299] |
Wed, 10 August 2011 22:23  |
|
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:58I 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 |
 |
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
