Recently I posted about an ORA-04030 Oracle error we received at work. At the end of many hours of pain, the solution was to swap the apparently damaged physical memory for fresh sticks of memory. This post is a follow up to highlight that an Oracle ORA-04030 can be generated by an application error as well.

As soon as the application started generating the ORA-04030 error, we immediately started working through the first set of points from our last excursion with this error. Thankfully, when looking through the Oracle log files it was throwing an error on a particular PL/SQL package. After inspecting the PL/SQL package, there was a clear opportunity for the PL/SQL procedure to spiral out of control and consume all the memory on the server.

In this instance, the PL/SQL procedure was building up a tree of information using the Oracle CONNECT BY PRIOR clause. As you can imagine, if for some reason a node had itself as its parent or child or any recursive relationship; the SQL statement would loop infinitely. Soon enough, the statement has retrieved a million or more rows and the server suddenly has no more memory left and it throws an ORA-04030 error.

The solution in this case was just as simple as the cause, remove the recursive data. For safe measure, there will be checks added using a BEFORE ROW INSERT and BEFORE ROW UPDATE triggers to make sure that the same problem doesn’t resurface in the future.

Quite some time ago, we encountered a very strange Oracle problem at work:

1. ORA-04030: out of process memory when trying to allocate <number> bytes

Initially, the problem was intermittent then its frequency increased. Soon enough, you could nearly generate the ORA-0430 error on command by clicking through our primary site half a dozen times. The standard trouble shooting events took place:

• Confirm no database changes since the last known good state
• Check the load and performance statistics on the Oracle RAC nodes
• Check the Oracle log files to see if there was anything obvious going wrong
• Check the resources on the Oracle RAC nodes

After all of those options were worked through, we immediately moved onto the application:

• Confirm no application changes since the last known good state
• Check resources on the web servers
• Check web server log files for anything obvious

There were in fact some application changes, so they were rolled back immediately. Unfortunately, that didn’t restore the site to an error free state. The hunting continued to look for anything that might have changed and we continued to draw blanks. At this stage, a support request was logged through the Oracle Metalink to try and resolve the error.

Since the service we were providing was so fundamentally broken, the next thing on the list was to cycle the servers:

• The IIS services were restarted
• The web servers themselves were rebooted
• The Oracle RAC nodes were rebooted

The important thing we didn’t notice or think of immediately is that it could have been just one of the Oracle RAC nodes causing the ORA-04030 problem. When the nodes were rebooted, they were cycled too close together for us to notice if anything had changed. Shortly there after, the servers were shutdown one node at a time and with continued testing; revealed an individual node was causing the problem.

Now that services were restored (though slightly degraded), time was with us and not against us. It seemed quite reasonable that the problem was related to the physical memory in the server. Since the server uses ECC memory, when the boxes were rebooted if there were any defects in the RAM modules – the POST tests should have highlighted them. Unfortunately, after rebooting the server again; there were no POST error messages alerting us to that fact.

While waiting for the Oracle technical support to come back to us with a possible solution or cause, the physical memory was swapped out for an identical set from another server. To test the server, it was joined back into the cluster to see if the error could be regenerated. Of course, even though the server didn’t report any errors with the memory; replacing it seemed to solve the error. In this instance, nothing the Oracle technical support mentioned gave us any real help and after seemingly having the problem nipped, the ticket was closed.

To put it through its paces as we were convinced that it had to have been a physical memory error (given the apparent solution); the memory was run through a series of grueling memory test utilities for days on end. After days of testing, not a single error was reported – go figure.

The moral of this story is simple:

When troubleshooting a technical problem, confirm or double check that a possible problem really isn’t a problem just because something else suggests that it isn’t.

You might have noticed that at times, it appears that IIS6 is losing or dropping your ASP.net session information. After looking around under the hood of IIS, there are a couple settings in IIS6 which can affect the consistency of session data.

In case you’re not already aware, IIS has two different mechanisms to handle ASP.net session information:

• In ProcessWhen using the in process method for session storage, IIS places your session data in the same memory area as the ASP.net worker process which is servicing your site. Since the session information is being stored in the worker process, if your ASP.net application is reset for any reason – the session data is lost at the same time.
• Out Of ProcessConfigured using out of process means that the session information is stored in a different worker process, either on the same server or on a different server to that running the ASP.net application. As you’d expect, if the ASP.net application is restarted for whatever reason – the session data is not lost. Within this type of session management, there are two physical methods you can choose:
  • IIS State ServiceIIS6 comes with a Windows service which can be used for session storage. You can configure it to run on the same server as your application to support a web gardens (even when you only use a single worker process) or on a different server to support a web farm (though, you could do that with a single server as well if you wanted I believe).
  • SQL Server

  As with the IIS State service above, utilising SQL Server to store session data can be done locally or on a remote server.

  One point of interest about using out of process storage, is that if you’re storing rich objects and not simple primitives (int/string/float/..) then you’re objects must be serializable.

If your ASP.net site utilises out of process session management, then you really don’t have a lot to worry about. If on the other hand your site utilises the default configuration, which is in process management, then you have some important points to be aware of.

Application Pools

One of the new options within IIS6 is a feature called an application pool. An application pool is a way to isolate a web site or collection of, from other web sites. This isolation is created by each application pool having its own worker processes. Of course, if you’re using in process session management then each application pools session data is also isolated from another.

For convenience, a virtual host in IIS6 can contain as more than one application within it. If you are not aware of the impact of creating applications, then it is very simple for your ASP.net site to be serviced by more than one worker process. Of course, when that happens and a clients request is bounced between multiple worker processes; their session data is ‘lost’ for the first request in each process. From that point onwards, it exists however updating a session object in one process does not update it in the other, leading to inconsistent application execution.

Web Garden

A web garden is a small scale web farm (har har!) which allows multiple worker processes to service a single application pool on the same server. Since each worker process has its own memory for storing session information, you’ll run into issues using a web garden and in process session management at the same time.

Recycling Processes

IIS also provides a convenient way of recycling worker processes systematically. A lot of web hosts will use this feature to cycle processes daily in the early hours of the morning to keep the memory use of each worker process to a minimum. Of course, if you’re application is using in process management – this feature would destroy any active session data being stored. This might seem trivial, however it would be extremely frustrating to have a worker process automatically cycle while you were half way through filling up your online shopping cart.

Simple Solutions

If you happen to have multiple applications running within a single site, you can force each application to use the same application pool. By doing so, each application will be served by the same worker process (assuming you are not using a web garden) and thus all of your applications can share session information seamlessly.

If you’re using a web garden at the moment and you’re application doesn’t receive enough load to require the other worker processes, reducing it back to a single process will stop you ‘losing’ session information. If you require the other worker processes, then you don’t have a choice but to use an out of process storage mechanism. If you don’t want to use the state server or SQL Server, you could alternatively roll your own as well.

Unfortunately, if you’re using in process session storage you don’t have a leg to stand on when a worker process is recycled. If you are using any out of process mechanism, either the two listed above or your own custom version – you should find that your session information is perfectly safe whilst cycling processes.

Happy session management!