Posted by: akolk | February 19, 2009

Oracle on 32-bit Windows: ORA-4030 errors

Over the last year I have seen a number of customers that have problems with Oracle on windows. They are getting frequently an ORA-4030 error.  Now Oracle on windows is implemented differently than Oracle on UNIX. The big difference is that Oracle on Windows shares one process with all clients (threads in this case). This process has a limited process space, which is by default 2GB and can be enlarged to 3.5GB. So this process space has to accommodate all the Oracle sessions (threads). So if you have a quick look at the Process space it basically consists of 4 parts (there are probably more, but I am not an Windows geek/expert): 

  1. The Oracle Executable
  2. The Oracle SGA
  3. The Heap (for all threads/sessions)
  4. The Stack

This has a number of implications. You can’t really tune the SGA to a very large size; you can only support a certain number of users; the heap can easily get fragmented and that means that large allocations will fail and there are other related problems.

So what can you really do to make this work? Reduce the SGA_MAX_SIZE or even remove the parameter and rely on your db_block_buffers and shared_pool_size settings. Or you can reduce the number of sessions/threads that connect to Oracle. But the your best bet is to reduce the amount of memory needed for the SGA. Another option is to switch to 64 bit windows and 64 bit windows, that will also allow for a larger process space.

I will update later with some more info.

About these ads

Responses

  1. I guess with windows 32bit the Memory can’t exceed more then 3Giga, though Intel with M$ has introduced PAE: http://msdn.microsoft.com/en-us/library/ms791485.aspx

    This could be a reason of 3.5 Giga limit on oracle process in Win 32bit.

    correct if i am wrong :)

    cheers

    • The limit of the 3.5GB instead of the 4GB seems to be from the fact that some memory is being used for Windows to map OS related libraries and areas into the process space.

      PAE seems and sounds like a good idea . There is one problem with it however if you use it with Oracle. You can only use it to store the Oracle Buffer Cache (if it grows beyond a certain size). You can’t use it for the shared pool for example.

  2. You can also reduce the memory footprint by changing the (per thread!) stack sizes or process heap size. Oracle used to pack utilities like orastack and oraheap.exe with the distribution..I dunno if they are still there however. We used orastack a lot during the good old Baan days, remember? :-) That we we could assure we could create enough sessions..

  3. Another issue with this (what I call) “workaround” of implementing /3GB and /PAE boot.ini parameters is that you will have to switch/use a lot of old Oracle 8 parameters like db_buffer_size etc.

    …or maybe this isn’t it an disadvantage….
    ;-)

  4. The 64bit version on Windows seems to have its own problems with large-page support… I read this on Howard Dizwell’s blog. Not sure if his blog is still viewable, he stopped blogging.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: