[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1875
  • Last Modified:

Oracle 9i 9.2.0.7

We are running Windows Server 2003 Enterprise Ed spk 1. I understand the issues with 32bit platforms but I also know there is a way for me to allow Oracle to take more than 4G of RAM ...I need it for streams...can someone tell me if there are some hidden parameters that I need to set in oracle or in the registry...I saw a VERY old metlink write up on some but they say they are from Oracle 8...Can anyone let me know..
0
Tereza
Asked:
Tereza
  • 10
  • 8
  • 2
  • +1
4 Solutions
 
virtual_voidCommented:
I have pasted the relevent portions of the doc here:
"
Addressable Memory Limitations

There is an inherent maximum of 4 GB of addressable memory for the 32 bit architecture. This is a maximum per process. That is, each process may allocate up to 4 GB of memory. This may not seem like a big limitation, because one would assume that a large number of processes may run on a server at any given time. However, Microsoft Windows uses a multi-threaded model, where Oracle runs as multiple threads under a single process. For Windows 2000 Server and Windows Server 2003, one 4 GB memory segment is shared by all user threads. By default, if you run multiple Oracle instances on the same server, or run other applications on the same server, they will share the same 4 GB of memory.
Available memory is even less, due to OS overhead. By default, on Windows 2000 and Server 2003, 2 GB of the available 4 GB of memory is reserved for the 32-bit OS and 2 GB is shared for User Threads (i.e. the Oracle SGA). One implication of this limitation is that the Oracle SGA must be smaller than 2 GB in size in order to start the database successfully. Even if the SGA is smaller than 2 GB, there may still be insufficient memory for Oracle threads. This is because all memory utilized for Oracle user sessions must also fit inside the 2 GB limit, along with the SGA.
Even without expanding available memory above 4 GB, it is possible to allow Oracle to use more memory. To expand the total memory used by Oracle above 2 GB, the /3GB flag may be set in the boot.ini file. With the /3GB flag set, only 1 GB is used for the OS, and 3 GB is available for all user threads, including the Oracle SGA. While 3 GB isn’t enough for most production databases, at least it is a step in the right direction.
Workarounds for Enabling Large Memory on 32-bit Microsoft Windows Server
Workarounds are available for using memory above the 4 GB limit. Intel 32-bit processors such as the Xeon processor support Paging Address Extensions for large memory support. PAE allocates additional memory in a separate memory segment that is also assigned to the process. MS Windows 2000 and 2003 support PAE through Address Windowing Extensions (AWE). PAE/AWE may be enabled by setting the /PAE flag in the boot.ini file. The “USE_INDIRECT_BUFFERS=TRUE” parameter must also be set in the Oracle initialization file. In addition, the DB_BLOCK_BUFFERS parameter must be used instead of the DB_CACHE parameter in the Oracle initialization file. With this method, Windows 2000 Server and Windows Server 2003 versions can support up to 8 GB of total memory.
Windows Advanced Server and Data Center versions support up to 64 GB of addressable memory with PAE/AWE.
One limitation of AWE is that only the Data Buffer component of the SGA may be placed in extended memory. Threads for other SGA components such as the Shared Pool and the Large Pool, as well as the PGA and all Oracle user sessions must still fit inside a relatively small memory area. There is an AWE_WINDOW_SIZE registry key parameter that is used to set the size of a kind of “swap” area in the SGA. This “swap” area is used for mapping data blocks in upper memory to a lower memory location. By default, this takes an additional 1 GB of low memory. This leaves only 2 GB of memory for everything other than the Buffer cache, assuming the /3GB flag is set. If the /3GB flag is not set, only 1 GB of memory is available for the non-Buffer Cache components.

Workarounds for Enabling Large Memory on 32-bit Microsoft Windows Server

Workarounds are available for using memory above the 4 GB limit. Intel 32-bit processors such as the Xeon processor support Paging Address Extensions for large memory support. PAE allocates additional memory in a separate memory segment that is also assigned to the process. MS Windows 2000 and 2003 support PAE through Address Windowing Extensions (AWE). PAE/AWE may be enabled by setting the /PAE flag in the boot.ini file. The “USE_INDIRECT_BUFFERS=TRUE” parameter must also be set in the Oracle initialization file. In addition, the DB_BLOCK_BUFFERS parameter must be used instead of the DB_CACHE parameter in the Oracle initialization file. With this method, Windows 2000 Server and Windows Server 2003 versions can support up to 8 GB of total memory.
Windows Advanced Server and Data Center versions support up to 64 GB of addressable memory with PAE/AWE.
One limitation of AWE is that only the Data Buffer component of the SGA may be placed in extended memory. Threads for other SGA components such as the Shared Pool and the Large Pool, as well as the PGA and all Oracle user sessions must still fit inside a relatively small memory area. There is an AWE_WINDOW_SIZE registry key parameter that is used to set the size of a kind of “swap” area in the SGA. This “swap” area is used for mapping data blocks in upper memory to a lower memory location. By default, this takes an additional 1 GB of low memory. This leaves only 2 GB of memory for everything other than the Buffer cache, assuming the /3GB flag is set. If the /3GB flag is not set, only 1 GB of memory is available for the non-Buffer Cache components.

Oracle Database Performance Implications

For sufficiently large Oracle databases, performance often increases as the SGA size is increased to the 4 GB limit (or lower, with OS overhead). This is not always true, but with appropriate performance tuning, most medium size to large databases will benefit from an increased SGA size. However, the PAE workarounds mentioned above have
a fairly significant performance overhead. In fact, as the SGA size is increased to just above 4 GB (by using PAE), performance may actually decrease. As the SGA size is increased, performance may not improve above the level achieved at the 4 GB level until an 8 to 12 GB SGA size is enabled. This means that it is probably a good idea to build servers with 12 GB or more of RAM, if you wish to host an Oracle instance with a large SGA size on a 32-bit system. Even though the Advanced Server and Data Center versions of Microsoft Windows Server may access up to 64 GB of memory, there is still a performance “dip” between 4 and approximately 12 GB SGA sizes. This means that the Windows 2000 Server or Windows Server 2003 version should probably not be used for SGAs larger than 4 GB, since only 8 GB of memory is accessible with Windows Server.
When AWE is used, it causes the composition of the SGA to be highly skewed towards large Data Buffer caches, since other SGA components and user threads may not be placed above 4 GB. Increasing the Data Buffer cache size may be beneficial for systems that support long running queries that access large amounts of data, such as Data Warehouses. OLTP systems may benefit less, particularly when there are a large number of users simultaneously accessing the database. Supporting large user populations places additional burdens on the Large Pool and the Shared Pool. With only 1-2 GB to work with, this can significantly limit the scalability of Oracle in terms of number of user sessions supported. For many companies, this is the most severe limitation of Oracle on 32-bit Microsoft Windows.

Some relief may be obtained by setting the /3GB flag as well as the /PAE flag in Oracle. This at least assures that up to 2 GB of memory is available for the Large Pool, the Shared Pool, the PGA, and all user threads, after the AWE_WINDOW_SIZE parameter is taken into account. However, Microsoft recommends that the /3GB flag not be set if the /AWE flag is set. This is due to the fact that the total amount of RAM accessible for ALL purposes is limited to 16 GB if the /3GB flag is set. RAM above 16 GB simply “disappears” from the view of the OS. For PowerEdge 6850 servers that can support up to 64 GB of RAM, a limitation to only 16 GB of RAM is unacceptable.
"

The full doc can be read at:
http://www.siebelonmicrosoft.com/assets/pdf/oracle_performance_em64t_6850.pdf
0
 
Mark GeerlingsDatabase AdministratorCommented:
Here is the quick summary:

Basically on 32-bit Windows, by default, each application is limited to using 2GB of RAM, regardless of how much RAM the server has.

There is an easy way to increase that to 3GB, that is by adding the /3GB switch to the boot.ini file and rebooting the server.

Having Oracle use more than that is not impossible, but is much more complex, as virtual_void indicated.  Also, this extra memory is only used for the database buffer cache, not all parts of the SGA, so these options may or may not help you.
0
 
TerezaAuthor Commented:
I was concerned about this ..Its from the Oracle9i release 2 documentation...

Extended Buffer Cache Mechanism

The USE_INDIRECT_DATA_BUFFERS parameter enables the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory.

However, the dynamic buffer cache feature requires every buffer to have a valid virtual address. This is because the underlying unit of allocation, a granule, is identified by its virtual address. For this reason, the extended cache feature is not available in the current version.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark GeerlingsDatabase AdministratorCommented:
Just FYI, I ran Oracle9.2 on a WindowsServer2003 machine with 8GB of RAM for a couple years.  (We recently moved to Linux for stability, not performance or manageability reasons.)  I just used the /3GB switch and that allowed us to have an SGA of close to 2GB.  I also used a third-party utility to set up a 1GB RAM disk where I put the temporary tablespace - that was a big performance boost for us.  That product was called RamDisk Plus Enterprise and is available from: www.SuperSpeed.com.  (By the way, their technical help is about the best I've come across in recent years.)
0
 
actonwangCommented:
mark,

       1G+3G = 4G. how do you use the rest 4G?

a
0
 
Mark GeerlingsDatabase AdministratorCommented:
The O/S uses some, and we may not have used all of the RAM as effectivle as we could have.
0
 
actonwangCommented:
thanks. make sense. wish you give me that 4G :)
0
 
TerezaAuthor Commented:
thats good to know...so I actually want more than 3G...I was using Standard addition and now they moved it to EE and gave me more memory...so I would like to take advantage of that..there is 8G and I can have as much as I like...when I read that line out of the Oracle Documentation and then on metalink info is old...I wasnt sure if my version can handle the Large memory use parameter....has anyone done this?
0
 
TerezaAuthor Commented:
We have the switches in the boot.ini

I implemented the use_indirect_data_buffer, db_block_buffers BUT when I try to startup it wont allow the instance to open...in the alert log it says disable cache advisory with old cache parameters and has windows memory 728343( number slightly off ashould be roguhtly 8G) so I add dv_cache_advice OFF then it hates that and wont do ANYTHING saying cant use old and new cache parameters...HELP
0
 
TerezaAuthor Commented:
So I wonder why that document says use a deprecated parameter ....I will try it without if anyone has any other ideas
0
 
Mark GeerlingsDatabase AdministratorCommented:
How much RAM does your server have?  Are you sure that the large memory features of Oracle are worth the trouble (and limitations)?  You cannot have multiple block sizes in a 9i database and use indirect_data_buffers.

Remember that the Windows O/S will like to use 1-2GB of RAM for itself.  Would a RAM disk and your temp tablespace in the RAM disk give you as big (or maybe more) of a performance boost for less trouble?
0
 
TerezaAuthor Commented:
The system has 8G RAM...on Dell PowerEdge 6850..I dont know anything about RAM disks...I need the RAm for Streams..that is the only issues I am having with memory
0
 
Mark GeerlingsDatabase AdministratorCommented:
A RAM disk is a way to use a portion of RAM as a virtual (and *VERY* fast) disk.  It is exactly the opposite as Windows virtual memory, which uses a (slow) disk file to simulate RAM.  This was a very useful concept when PCs had only small amounts of RAM, but these days "virtual memory" is usually not a good idea on servers with gigs of RAM.  Unfortunately, Windows is still designed to use at least some virtual memory, but you want to keep that to a minimum.

Since the temporary tablespace in many Oracle databases gets as much I/O (or more I/O) than most other tablespaces, putting the temporary tablespace in a RAM disk can be a huge performance improvement, but this does depend on the application and on your SGA settings.

How do you know that you need the RAM for streams, and not for other parts of the system?  Do you have just one Oracle instance on this server?  What are you using streams for, to synchronize with another database?  Does the streams process have to run as part of your main instance, or can it run on another server, or as part of a second instance on this server?  (I have heard a bit about streams but haven't explored it much, so maybe these are dumb questions.)

Do you use the Windows Performance Monitor to check your: CPU, memory, swap file, and disk utilization?  Have you confirmed that memory is your main bottleneck?
0
 
TerezaAuthor Commented:
Yes this server has one Oracle instance and the error that has been occuring is on this server Stream Queue demanding more memory..I have the parameters set to use 50% of shared memory...I actually hadnt seen this problem till we moved to the Server 2003 Enterprise Ed but I dont see how that has an effect on things..according to the sysadim it shouldnt..I have the capture and propagate side on another instance on a Dell poweredge the side Im referring to is the apply side of streams and that is where I am lagging behind then running out of memory.....
I was out of town when they changed OS's and then only saw the errors...another DB person was there at the time but couldnt figure it out...
I found another document that has calculations for the Buffers and another hidden parameter _db_latch_XX I forget the rest...also AWE_WINDOWS_MEMORY reg hack that may come to play here although I did calculate that should be 512M and default is 1G...
Monday will be more tests on the server if I can at least get the memory to be recognized then watch it run under normal conditions till I see the memory errors then I can figure out when and why it occurs...

I am really not sure that the memory caused this...like I said when I was running standard with just 1500M of Shared so that means streams had access to 750M I didnt see any issues...then while I was out of town they called me and said the new OS is on and we have memory errors..

Thanks for the help..

0
 
Mark GeerlingsDatabase AdministratorCommented:
If the problem is clearly with Oracle Streams, have you asked Oracle Technical Assistance for help?
0
 
TerezaAuthor Commented:
Yes,,they say switch to 10G OR give it as much shared memory as you can...
thats why I am trying to get more of the RAM....so I finally got the parameters right so that I can access more than 3G RAM however...

I want to be able  set my Shared pool to at least 2G ( max)
and then set large pool to 400 max 700
What happens to the buffer cache that isnt being used? I kinda recall that shared pool can access this but is that true..
I havent been able to get about 1G for shared pool and 384M for large pool with these settings
0
 
Mark GeerlingsDatabase AdministratorCommented:
I'm not sure that the shared_pool_size can be set as high as 2GB on a 32-bit platform.  Which "large memory" parameters are you using to get beyond 3GB?  Are you sure that portions of the SGA other than block_buffers can use this RAM?

Is a second instance a possiblity, or must streams run in your primary instance?  (As I said, I'm not an expert on Oracle Streams, so I don't know.)  The advantage of a second instance is the fact that it would be a separate process with its own 2GB of base RAM available.
0
 
TerezaAuthor Commented:
Sorry I didnt mean 2G for Shared Memory...I was thinking about what I had for my max SGA before this..which was close to 3G...now I have over 6G BUT
Both servers /PAE /3GB

Windows Server 2003 EE
I have Large Pool set to 500M, Shared Pool 1000M and the Buffer cache =4000M --> I want more to be in the shared pool than 1G what Ive done is try to get more for the large then alter command and up the shared..
_db_XXX_lru_latches=128
Use_indirect_data_buffer=TRUE
Db_block_buffers=555234 ( close)

In my windows server 2003 Standard addition...I have Shared Pool up to 1500M I havent tried to use 2G but it doesnt appear like it would stop me as long as I dont bust the max SGA....
I want to be able to set my shared memory as HIGH as possible ...
0
 
TerezaAuthor Commented:
Oh sorry....I forgot to address the streams part the apply will start on this machine because that is where it needs to be..in the machine that needs to have the replicated data there must be an APPLY side so there realy is no "separateing"..
0
 
Mark GeerlingsDatabase AdministratorCommented:
I'm not aware of any "magic" ways to increase the shared_pool_size greatly.  It looks to me like you have found the documented options.  Unless you can go to a 64-bit server and O/S, you may have to live with what 32-bit hardware and software can support.
0
 
TerezaAuthor Commented:
Well what I mean is how come I can not acquire the same amount of SharedPool and Large Pool I had before and balance it out with the DB_Block_buffers...when I try to lower this value and raise the others back to what they were I receive the same errors I had before I utilitzed the _Db_XX_lru_latches...I even try to balnce that parameter out...but Im not sure Im doing it right...according to the calculations that would be 128 ...the AWE_WINDOWS_MEMORY hack didnt seem to do anything...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 10
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now