Oracle memory gets trimmed every 6 hours

[leonhard_bernhart]

We have a very strange behaviour in our SAP R/3 Enterprise 4.7 production system (SAP_BASIS 620).

SAP runs on Windows Server 2003 Enterprise Edition.

10 GB RAM, PAE enabled (Physical Address Extension).

The affected server is the database server, which also runs some working processes (DIA, BTC and UPD).

There are also 6 Windows application servers (x32, x64 and Itanium).

After a normal SAP start, all Windows processes bit by bit allocate their memory.

oracle.exe starts with a Mem Usage 236 MB (VM Size 1.900 MB).

You can see this in Windows Task Manager.

After about 30 minutes oracle.exe reaches its average value of about 2 GB.

The value ranges from 1,9 GB up to 2,5 GB.

Then, about every 6 hours the following happens:

oracle.exe deallocates its memory completely !

No answer in SAPGUI, no reaction on the console for about 5 Minutes.

Then when i get the first look at the Task manager, i see that oracle.exe allocated about 80 MB.

In the next 20 minutes Mem Usage raises up to the average value of about 2 GB.

During this time, the performance comes up again step by step.

Not only Oracle is affected, at least every disp+work process also frees all allocated mamory.

But it seems as if Oracle would be the first to free up its memory and then drags down the SAP Kernel processes.

We have no changes made to the SAP Kernel, we did not apply any Windows updates.

SAP operated error-free for the last 2 years in this configuration.

The only thing we did, was to apply several SAP Support Packages (Basis + Application).

This behaviour occured the next day after we imported those packages.

So we have to suspect these packages, although the symptoms point to a problem with the SAP kernel, Oracle or the Windows memory mamagement.

SAP Support adviced us to reduce the load on the server, so we suspended some work processes.

Result: no improvement.

Next we reduced the Oracle cache size by 250 MB.

Result: the situation became even worse, the error occured every hour.

So we icreased the cache size up to 1,36 GB.

Result: could be an improvement, not sure yet.

I am wondering what must happen, that all processes on a Windows Server deallocate their memory.

Can a ABAP-Report provoke this error ?

Has anybody else ever seen such a behaviour ?

Any ideas ?

[Former Member]

Hello Leonhard,

have you set the Oracle parameters SGA_MAX_SIZE and / or SGA_TARGET? If yes, to which values?

Regards

Martin

[leonhard_bernhart]

The problem has been fixed with Service Pack 2 for Windows 2003.

We applied the SP one week ago, and since then this strange behaviour did nor occur again.

Regards.

[Former Member]

Hi Leoahard,

As you mentioned "We have no changes made to the SAP Kernel, we did not apply any Windows updates.

SAP operated error-free for the last 2 years in this configuration.

The only thing we did, was to apply several SAP Support Packages (Basis + Application).

This behaviour occured the next day after we imported those packages.

So we have to suspect these packages, although the symptoms point to a problem with the SAP kernel, Oracle or the Windows memory mamagement"

Can you provide the kernel version?

You check the Oracle & windows memory management. Have you check the SAP kernel?

I think you need to upgrade the kernel.

Regards,

Rajesh

[Former Member]

Hello Leonhard,

A strange behaviour, is there any kind of antivirus / spyware running on the server? Any unwanted services active on the server? A NT Administrator can perform these checks.

Were there any updates to the OS? is the virtual memory sufficient?

Cheers,

Nish

[Former Member]

Hello Leonhard,

I would expect this kind of scenario if there is an external process that allocates a lot of memory. As a consequence a lot of memory pages of already running processes like Oracle or SAP are paged out. Can you see any strange process with a high memory allocation during the critical time frames?

Regards

Martin

[former_member433984]

Hello Leonhard,

suggestion from Martin sounds very reasonable - take any action that will allocate some large amount of memory and the OS will start trimming the working sets of other processes. But the question is why "db_block_buffers" has direct influence on this behavior?

Br,

Yaroslav

[leonhard_bernhart]

@Martin:

I'm looking for such a process too.

I could identify such a process in Task Manager: Peak Mem Usage should be very high.

The highest Peak Mem Usage of course has Oracle (~2,4 GB), followed the disp+work processes (200-600 MB).

The rest is less than 100 MB.

If there was such a process, it died before i can see it.

@Rajesh Kale:

When i applied the support packages, i also changed the kernel to the newest version available: 210.

Then the error occured, and the first thing i did, was go back to the old kernel: 175.

The error occurs with both kernel versions.

@Nisch:

There is no anti-virus software running.

In the meantime, i am sure that increasing the Oracle-cache has improved the situation:

900 MB cache: memory gets trimmed every hour

1,36 GB cache: memory gets trimmed every 24 hours

This is significant.

Regards

[markus_doehr2]

I would also suggest your database is crashing and then automatically restarting - during the automatic recovery the database is "halted" and that's why your workprocesses are going to reconnect mode and waiting until the database came up.

I think it's really something with your memory configuration. You said you changed nothing but adding application servers. Each Oracle shadow process/thread consumes memory - you may now have reached the point, where, in case all application servers are working, the available memory for the database is not enough and it's crashing.

What version of Oracle are you using? Check Note 441663 - USING > 3 GB of memory for Oracle on WINDOWS platforms

I would highly recommend using one of your 64bit application servers and switch the database over to a 64bit platform.

Markus

[leonhard_bernhart]

Thx for your interest in this issue.

For clarification:

- Database version is 9.2.0.7.0

- We will upgrade to 64 Bit in the next months, but we still need a solution for our 32 Bit system.

- We did not add new application servers. These servers were up and running before and after the problem occured.

- I don't think that Oracle restarts. There are no ORA-entries in the Oracle Log and there is no Oracle-Usertrace.

The system slows down, because every byte, that is backed up in the paging file (as far as i know in MS terms this is called "standby list"), has become invalid and must be read from disk.

Not only Oracle is affected, every process trimmed its working set.

For example Terminal Services is unresponsive for about 4 minutes.

In the end all processes continue their work, but it takes some time until their working set has been restored from the paging file.

No errors occur, no Dumps, no EventLog or SystemLog entries.

There are just some TIMEOUTs, caused by the unresponsiveness of the server in the first minutes of the memory crash.

@Markus:

Yes, i also think that we reached some kind of Oracle memory limit.

Since we increased the Oracle cache size, the frequency of the error has been significantly reduced.

But still i am wondering what funny things can happen.

I would expect Oracle to crash, Windows to bluescreen, SAP to dump.

But freeing the memory of all processes is something completely new to me.

Edited by: Leonhard Bernhart on Jan 8, 2008 5:10 PM

Edited by: Leonhard Bernhart on Jan 8, 2008 5:11 PM

[markus_doehr2]

Did you configure the server for "optimize background processing"?

Markus

[leonhard_bernhart]

Yes i did.

[markus_doehr2]

Are you maybe facing the issues described in 1009297 - Windows Server 2003 Family: High Paging Rates

Markus

[leonhard_bernhart]

Yes we applied this hotfix, but it did not help.

[markus_doehr2]

I must admit, I don't have any more idea

Markus

[former_member204746]

check for traces in alert_SID.log file. My guess is that Oracle restarts after some kind of error.

you did not specify which Oracle version you are using... have you installed latest patch level on your Oracle system?