Home » RDBMS Server » Server Administration » Increasing memory for Oracle on Windows
| Increasing memory for Oracle on Windows [message #141020] |
Thu, 06 October 2005 12:21  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Bellow is the snippet from metalink where they explain how to set bigger memory for Oracle. I have /3G on the boot file, but It from the text bellow, it looks that I cannot use the Automatic Memory Management on Windows - is this true, and is there a way to overcome this problem?
I have 8G RAM, what sample value you could give me for setting up SGA for heavy OLTP.
Thanks a lot for the help.mj
Dynamic Memory Management/Automatic Memory Management with AWE Enabled
=============================================================
Oracle10g introduces the concept of Automatic Memory Management,
whereby the Oracle RDBMS will dynamically adjust SGA parameters
such as SHARED_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, etc.
This is enabled by setting the parameter SGA_TARGET to a non-zero value.
However, in order for this to work properly, you must use DB_CACHE_SIZE
for the buffer cache. When setting USE_INDIRECT_DATA_BUFFERS, you cannot
set DB_CACHE_SIZE, as noted above. Therefore, SGA_TARGET should not be set
when using AWE - these two features are mutally exclusive.
When setting USE_INDIRECT_DATA_BUFFERS=TRUE on Oracle10g, you should also
set SGA_TARGET to 0.
|
|
|
|
| Re: Increasing memory for Oracle on Windows [message #141156 is a reply to message #141020] |
Fri, 07 October 2005 04:35  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
Please check out below link ....for sizing SGA.
http://www.orafaq.com/forum/t/51956/67993/
You can look at the size of your SGA by looking at the initialization parameters that control its size. Here is a simple query and its output.
select name, value
from v$parameter
where name in ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
'log_buffer', 'db_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',
'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size',
'db_keep_cache_size', 'db_recycle_cache_size', 'large_pool_size');
Stepping through Letting Oracle Take Control
There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter.
1. Take a look and see if you are already in automated sizing of SGA
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- --------
sga_target big integer 0
2. Alter system to begin automated sizing of SGA
SQL> alter system set sga_target=216m;
System altered.
3. Done
What happens when you switch to Automatic Shared Memory Tuning is a bit interesting. After you alter SGA_TARGET parameter, your SPFILE will undergo a change and now have the following parameters defined. Note that k101 is my instance name and will take on whatever the instance name is.
k101.__db_cache_size=25165824
k101.__java_pool_size=50331648
k101.__large_pool_size=8388608
k101.__shared_pool_size=83886080
In addition, when you issue the previously given SQL to show the parameter settings for the individual components of the SGA you will notice that they now have a value of zero.
select name, value
from v$parameter
where name in ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
'log_buffer', 'db_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',
'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size',
'db_keep_cache_size', 'db_recycle_cache_size', 'large_pool_size');
NAME VALUE
------------------------- ---------
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
db_cache_size 0
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
log_buffer 262144
13 rows selected.
If you truly want to see the parameters after setting the SGA_TARGET you will need to modify the query to include the newly created underscore variables. This can be of some concern if you are relying on the "normal" parameters for any database monitoring scripts.
select name, value
from v$parameter where name in ('__shared_pool_size', '__java_pool_size',
'streams_pool_size', 'log_buffer', '__db_cache_size', 'db_2k_cache_size',
'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size',
'db_32k_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size',
'__large_pool_size');
NAME VALUE
------------------------- ---------
__shared_pool_size 67108864
__large_pool_size 4194304
__java_pool_size 8388608
streams_pool_size 0
__db_cache_size 142606336
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
log_buffer 262144
13 rows selected.
Switching over to the Automatic Shared Memory Tuning is as easy as setting an initialization parameter. How this will behave under load is yet to be determined but since these numbers are driven by the various advisories and I am mostly happy with them as individual components, I see no reason not to venture down the path of having Oracle automatically size my SGA. Of course, as always, in a test environment first. I would suggest you take a snapshot of your initialization parameters before letting Oracle take control and then compare the end settings that Oracle has implemented. It is always easy to switch back, just reset the SGA_TARGET parameter and set the individual components back to their original values.
I hope above mention things will be useful to you.
Frankly saying ....Thanks to James Koopmann, to show how to Automate the Sizing of SGA in Oracle 10g ....  
Regards
Always friend Sunilkumar
|
|
|
|
| Re: Increasing memory for Oracle on Windows [message #141197 is a reply to message #141156] |
Fri, 07 October 2005 08:18 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Is your instance running on Windows?
I do not have problem with the "non_windows" set ups of SGA.
All you listed bellow is not possible to run on this machine although the /3G is added in the boot file. My SGA_target canot be changed from 590M to anything bigger...- even 591!!!
My question is how to use the rest of the 8G memory available to the server - if there are 8G to Windows box, I'll need 4 to 5G to be allocated to the Oracle instance - PGA and SGA - other way is a waist of RAM. Do you have idea what I need to change for that?
Thanks,mj
|
|
|
|
| Re: Increasing memory for Oracle on Windows [message #141205 is a reply to message #141020] |
Fri, 07 October 2005 08:37 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I can't really help you, but isn't there a specific OS on windows you need to be able to address the extra RAM? Like 2003 Advanced Server or something? Do you have that? Or a specific processor, 64 bit instead of 32 bit?
Where did you buy your server from? They should have some resources on their website for exactly this situation, and should have discussed it with you during the purchasing decision.
|
|
|
|
| Re: Increasing memory for Oracle on Windows [message #175505 is a reply to message #141020] |
Fri, 02 June 2006 09:31 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm in a windows environment now, and they had not configured their server to use larger memory. So it is 32 bit windows 2003 on intel xeon, which by default runs as 2bg max per process, any process, including oracle, of user addressable space. Another 2gb is server / kernal overhead for each process. This 4gb is the max on a 32 bit environment.
The /3GB parameter is not AWE. It is 4GT. It lets you increase the user addressable space so that you have 3gb for user and 1gb for kernal overhead. This seems to be the preferred oracle / Dell approach, as far as performance goes. It gets you extra RAM at no performance hit.
You can even make finer tuning adjustments, to either (I forget which), use slightly less than 3gb of user addressable, or slightly more, in fairly fine increments of your choosing.
However, AWE is different. It allows you to use far more than 3gb, up to I wanna say 32 or 64 gb, but don't quote me on that. However, the same Oracle Dell paper (which I found on www.oracle.com/dell or /windows) indicates that their performance tests indicated that due to the performance overhead hit of AWE, which is a set of special APIs and libraries to make use of additional RAM in an environment (32 bit) which would not normally support it), you were better off either:
a) not using it at all, and sticking with 3 gb total for your oracle (remember this is total addressable space by the process, and on windows oracle runs in a single process, so this is SGA plus PGA for all users). The thought being if you need more memory than this then you really should be on 64 bit environment (both OS and processor), as that is one of the major points of 64 bit vs 32 bit.
b) use it, but have at your disposal well over 8 gb of RAM, to make up for the overhead. The 4-8 range is a gray area / losing battle, where the overhead doesn't balance out the increased performance of the extra ram. Remember this is just typically, and according to their tests, so your results on your system may vary. The idea is that once you have over 8 gb, you start to see a payback return on your AWE overhead investment.
I don't know anything about the use_indirect_data_buffers, but that may be part of the extra work and overhead involved with configuring and using AWE, but that is just speculation on my part. If I recall, turning on AWE is not as simple as turning on 4GT with a simple boot.ini switch. It would naturally follow in my mind that further administration would then also be not as simple, which led me to choose option a over b here.
Anyway I'm doing a search for something unrelated, ran accross this thread, and thought I'd comment, as I recently had to become familiar myself with this situation.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 12:43:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
