SAP is still slow after ram upgrade.

[Former Member]

Hi Experts,

     We recently upgraded our memory from 16Gb to 32Gb. Then we adjusted the SGA_MAX_SIZE to 27Gb and left our SGA_TARGET at 0 but our system still gets a little slow sometimes. Can anybody suggest what parameters to check in sap and in oracle. The version of our Oracle is 10g and our SAP is ecc 6.0. Any suggestion would be much appreciated.

Thank You

[yogesh-garg · Accepted solution]

Hi Rosendo,

Just Go to ST04 and check Data buffer Quality Buffer % , if it is less than 90 % the database tuning is required. For this increase SGA_MAX_SIZE and also SGA_TARGET parameters but SGA_TARGET < = SGA_MAX_SIZE. I have done this after increasing my RAM and my data Buffer % increases to 98.4 %.

For clearing concept of SGA_MAX_SIZE and also SGA_TARGET go to below link:

http://oracledbaschool.blogspot.in/2009/05/sgatarget-vs-sgamaxsize.html

Regards

Yogesh Garg

[Former Member · Accepted solution]

Hi Yogesh,

The value of Data buffer Quality Buffer % is 84.4. The current value of SGA_MAX_SIZE is 12Gb and the SGA_TARGET is 0. Can you give me an example of values that would increase the data buffer quality?

[yogesh-garg · Accepted solution]

Hi Rosendo,

Set SGA_TARGET size equal to or less than sag_max_size. This will auotmatically allocate size as per requirement to Data Buffers. Hope this increases your  Data Buffer Quality %.

SQL> alter system set SGA_TARGET=11.5G scope=spfile;

Regards

Yogesh Garg

[Former Member · Accepted solution]

Thank you Yogesh.

[former_member188883]

Hi Ariola,

Could you update the following statistics for your database

1) Missing statistics

2) Oracle dictionary statistics

3) System statistics

Additionally you may need to check ST02, you identify whether system is swaping or not. Incase it is swapping a lot you may need to adjust those buffer parameters using RZ10.

Post these updates, observe system performance.

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

     I'm sorry i am kind of new at this. What do you mean by missing statistics,Oracle dictionary statistics,System statistics? and i checked st02, it is swapping and the value is 20,000 is that value considered a lot?

[former_member188883]

Hi Rosendo,

Update of these statistics is required to enable database to identify fastest execution paths.

You may use brtools to execute these statistics. Refer link below on steps to perform this

http://help.sap.com/saphelp_nw73/helpdata/en/46/b9de2e5a731824e10000000a1553f6/content.htm?frameset=...

As you mentioned you have swaps value around 20,000 it means data is not being fetched from the Buffer but from database itself. You may need to identify paramters associated to the items for which swapping is more using transaction ST02 and try to increase ( double those ) values.

SAP system restart is required after modifying these parameters.

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

     Thank you for your help. But i would also like to ask if you are familiar with the parameters SGA_MAX_SIZE and SGA_TARGET? I read somewhere that these are the parameters i need to check for database tuning.

[former_member188883]

Hi Rosendo,

I am familiar with these parameters SGA_MAX_SIZE and SGA_TARGET.

The SGA_TARGET parameter sets the amount of memory available to the SGA. 
This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled
by the following parameters.

•DB_CACHE_SIZE (default block size)
•SHARED_POOL_SIZE
•LARGE_POOL_SIZE
•JAVA_POOL_SIZE

If these parameters are set to a non-zero value they represent the minimum size for the pool.
These minimum values may be necessary if you experience application errors when certain pool sizes drop below a specific threshold.

The following parameters must be set manually and take memory from the quota allocated by
the SGA_TARGET parameter.

•DB_KEEP_CACHE_SIZE
•DB_RECYCLE_CACHE_SIZE
•DB_nK_CACHE_SIZE (non-default block size)
•STREAMS_POOL_SIZE
•LOG_BUFFER

Ideally for an SAP system we refer following SAP notes to fine tune oracle database parameters.

The Oracle 10g tuning parameter note is #830576. You may run the script attached with SAP note #1171650. The ouput of this script is a recommendation on the tuning parameters and its required values.

In addition to database parameters you need to tune SAP buffers as mentioned before so that data fetch happens from SAP buffer most of the times. This increases system performance.

Regads,

Deepak Kori

[Former Member]

Hi Deepak,

Based on what you have posted my SGA_Target value should not be 0. Thank you very much 

appreciate all the help.

[former_member188883]

Hi Rosendo,

Request you to additionally run the scripts as mentioned in the SAP note number above to have further guidance on the Oracle database tuning parameters.

Regards,

Deepak Kori

[Former Member]

Thank you very much Deepak.

[Former Member]

Hi Deepak,

I have a follow up question regarding the swapping in st02. How can i know that the value that i will use is too high?

[former_member188883]

Hi Rosendo,

If I understood correctly, you are asking whether the buffer paramater value set is too high or not ( to avoid swapping)

-> Tuning SAP buffers is iterative. Hence you need to keep on updating these values based on system behaviour and requirement. Question here is whether configured values will be supported by SAP system or not. It depends upon available RAM for SAP application. Ideal practise for OLTP systems is 30% of total RAM to Database and 70% of total RAM to SAP applicaiton.

By mistake you put very large values to have your SAP buffers on higher end and take a restart, SAP system won't come up.

Regards,

Deepak Kori