Oracle Memory Utilisation

[George Nikoloudis_1]

Dear Forum

I have a problem with the Oracle memory utilisation.

This problem is the following:
As you can see from the document glance_snapshot.txt and in applicaiton number (4) the memory utilisation is 3.59GB physical and 190GB virtual.

All the other applications have less than 1GB.

In the init_db.txt file which is the init file of the same oracle database the SGA is about 1.5GB.

Each process of oracle reserve from 25MB up to 70MB and there are at least 100 such processes.

I have also atached the kernel file in order for you to check if any parameter is over estimated or wrongly adjusted.

Finnaly I am sending to you the swapinfo.txt file with the swap utilisation.

My goal is if it possible to get some system indications about the memory utilisation and the parameters affecting this from oracle side.

Also any changes or recommendations of the kernel in order the memory utilisation to be normalised are wellcome.

I would like to thank you for reading my message.

BR
George

[Steven Sim Kok Leong]

Hi,

First and foremost, you wanted an SGA of 1.5 GB, but your shmmax is only 0X40000000 or 1073741824 (1 GB).

You will need to increase this to at least 2 GB (OS also uses some shared memory).

Hope this helps. Regards.

Steven Sim Kok Leong

[Praveen Bezawada · Accepted solution]

Hi George
It also looks like IO for the application in question is very high compared with the other applications.
It may be that IO has become a bottleneck for this appl. Check the layout of the disk onto which this application processes do IO. Try to organise the disks in such a way that load is spread across different disks.
Also as Steve has suggested increase the SHMMAX, otherwise you will be needing to shared memory segments for the application SGA which is not optimal.

...BPK...

[George Nikoloudis_1]

Thanks for the help.
Although I am sending to you the memory utilisastion extract from:
UNIX95= ps -e -o ruser,vsz,pid,args | sort -rnk2 | grep bccs

What you will see is that the system utilises 6.5 GB of memory.

In the system there are 8 GB of Memory and 8 Processors.

Is it OK to use SHMMAX 8GB i.e. full memory or i have to use 7GB

Thanks a lot for the help

George

[Praveen Bezawada]

Hi George
SHMMAX is the system-wide maximum allowable shared memory segment size.
This means it is the maximum of a single shared memory segment. In your case as you need SGA of 1.5GB having SHMMAX value of 2GB would be sufficient.
This parameter in assocation with SHMMNI , which is the system wide maximum number of shared memory segment control your shared memory allocations.

...BPK...

[Brian Crabtree]

I would suggest increasing the kernel parameters mentioned previously to 4g each:

maxdsiz, maxdsiz_64bit, maxtsiz, maxtsiz_64bit, and shmmax.

Also, I would drop your log_buffer from 400m to 500k. Setting it this high is probably the cause of alot of your problems. Oracle doesn't recommend that this go over 1m. Also, what kind of application are you running. With 100 processes, a 10m sort_area_size will take 1g of memory alone.

The following are two notes in Metalink regarding sort_area_size. If you do not have a Metalink account, I will copy them as attachments:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=30918.1

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=109907.1

You should consider looking over all of the databases running on this machine for the same information.

Brian

[George Nikoloudis_1]

I would like to comment:

1) Can you provide more detail about the log buffer of oracle in order to modify accordingly.

2) Based on the parameter REDO_LOG_SPACE_REQUEST in the V$SYSSTAT, if this number is increased over an operational period the log_buffer should increased accordingly in order the value of the REDO_LOG_SPACE_REQUEST to get closer to zero

3) Assuming that the Oracle processes are sharing the data and text from the shared memory segment of SGA then can you explain why there are proceses that have size from 25MB up to 96MB? Assume that the SORT_AREA_SIZE is 10MB, please see the attached file.

[Brian Crabtree]

1. Personally, I look at "redo wastage" when looking for an oversized buffer. I dont recommend much over 1m except in situations where there are a large number of dataloads happening, and even then it should be kept low.

2. REDO_LOG_SPACE_REQUEST is an ok parameter to look at, as it should be 0 in most cases. However, there are a number of other parameters that you can set other than this. You can increase LOG_SIMULTANEOUS_COPIES to increase the number of redo latches available on the system. You can also increase or decrease the size of the LOG_SMALL_ENTRY_SIZE depending on how large each transation that you are running is.

An article on Metalink that talks about working out redo latch and log buffer contention is below.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=147471.1

3. My assumption in this case is that the processes in question are owning part of the shared memory. The "SZ" parameter from 'ps' will not give the correct information in these circumstances, because it does not differentiate between private and shared memory areas.

Thanks,

Brian

[Brian Crabtree]

1. Personally, I look at "redo wastage" when looking for an oversized buffer. I dont recommend much over 1m except in situations where there are a large number of dataloads happening, and even then it should be kept low.

2. REDO_LOG_SPACE_REQUEST is an ok parameter to look at, as it should be 0 in most cases. However, there are a number of other parameters that you can set other than this. You can increase LOG_SIMULTANEOUS_COPIES to increase the number of redo latches available on the system. You can also increase or decrease the size of the LOG_SMALL_ENTRY_SIZE depending on how large each transation that you are running is.

An article on Metalink that talks about working out redo latch and log buffer contention is below.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=147471.1

3. My assumption in this case is that the processes in question are owning part of the shared memory. The "SZ" parameter from 'ps' will not give the correct information in these circumstances, because it does not differentiate between private and shared memory areas.

Thanks,

Brian

[Brian Crabtree]

1. Personally, I look at "redo wastage" when looking for an oversized buffer. I dont recommend much over 1m except in situations where there are a large number of dataloads happening, and even then it should be kept low.

2. REDO_LOG_SPACE_REQUEST is an ok parameter to look at, as it should be 0 in most cases. However, there are a number of other parameters that you can set other than this. You can increase LOG_SIMULTANEOUS_COPIES to increase the number of redo latches available on the system. You can also increase or decrease the size of the LOG_SMALL_ENTRY_SIZE depending on how large each transation that you are running is.

An article on Metalink that talks about working out redo latch and log buffer contention is below.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=147471.1

3. My assumption in this case is that the processes in question are owning part of the shared memory. The "SZ" parameter from 'ps' will not give the correct information in these circumstances, because it does not differentiate between private and shared memory areas.

Thanks,

Brian