Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Memory Utilisation

SOLVED
Go to solution
George Nikoloudis_1
Frequent Advisor

Oracle Memory Utilisation

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
9 REPLIES
Steven Sim Kok Leong
Honored Contributor

Re: Oracle Memory Utilisation

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
Email: steven@beepz.com. Homepage: https://www.beepz.com
Praveen Bezawada
Respected Contributor
Solution

Re: Oracle Memory Utilisation

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
Frequent Advisor

Re: Oracle Memory Utilisation

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
Respected Contributor

Re: Oracle Memory Utilisation

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
Honored Contributor

Re: Oracle Memory Utilisation

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
Frequent Advisor

Re: Oracle Memory Utilisation

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
Honored Contributor

Re: Oracle Memory Utilisation

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
Honored Contributor

Re: Oracle Memory Utilisation

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
Honored Contributor

Re: Oracle Memory Utilisation

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