SAP Performance tuning with Oracle 11G in OS/ Linux

Summary:-

This document contains how to tune the parameters in SAP level as well in database level based on your hardware, SAP application and database configuration.

Author: Brindavan Mookaiah

Designation: SAP BASIS Consultant

Instruction

The information about the parameters which show here are after analysis from our system landscape. It might be different from your landscape. If you are facing any performance issue in your system then you should analysis first for past three months data like response time, CPU utilization , database load , new z*program ,expensive SQL statement ,snote , might be if user access backend system from portal..etc… . This document only shows how to analysis and do the tuning based on the hardware, SAP application and database. We have analyzed and found that nothing can be increased any hardware or update patches or any z*program created by ABAP developers (include any new variants on existing z*program) or any expansive SQL statement using like “join queries ,using selection screen more” are causing performance issue. Here we have the system performance is fine and something slowness for background process. But, system is not overloaded with any new SAP application changes, so we have to analysis and tune the parameters based on current RAM.

SAP Application: SOLAMAN 7.1

OS : Red hat Linux

Database : Oracle 11G

While checking from hardware side we have found that the system is configured with good memory running with 200 MB frees space and there is no way to improve the performance without adding the RAM. Before going to add the Ram we planned to use another method, that means we have checked and analysis where it causing the performance issue to tune. Gone through some T0-code like from SAP level ST02,DB02,ST03,EWA report expensive SQL statement..etc.. but we found in initial stage that in DB02 that the database buffer quality area is 94.6 , as per SAP recommendation this buffer quality should be more than 99.* The database buffer quality issue is due the memory configured very lees for SGA,PGA,DATABASE_BUFFER_CACHE, SAHRED_POOL_SIZE..etc.

Now we identified the issue and need some memory to tune, but we don’t have memory for the same. So, we followed another method and analysis what are the memory area which is not utilized from SAP level and shared to database as well to SAP another parameters to tune. We found some memory and tuned the same.

Find the below plan for your knowledge.

DATABASE

System	Instance	Month	Total Memory Available (GB)	Total Memory Used Max(GB)	DB Cache Memory Configured (MB)	DB Buffer Quality (%)	Shared Pool Memory Configured_MB	SGA (GB)	PGA(MB)
SOLMAN	SAP Database		35.33	35.16	800	94.6	472	1.48	332
New Value					1224		1107	2.48	358

SAP APPLICATION

Profile parameter	Standard 64-bit	Current Value	Unit:	New Value
em/initial_size_MB	[PM]	4092	MB
em/blocksize_KB		4096	KB
em/max_size_MB	1.5 * [PM]	4096	MB
em/address_space_MB	4096	4096	MB
ztta/roll_first	1024	1024	byte
ztta/roll_area	3000000	6500000	byte
ztta/roll_extension	2000000000	4000683008	byte
abap/heap_area_dia	2000000000	2000683008	byte
abap/heap_area_nondia	0	2000683008	byte
abap/heap_area_total	[PM]*1048576	2000683008	byte
abap/heaplimit		40894464	byte	40894464
rdisp/ROLL_MAXFS	[US] * 100	32768	8KB block	262144
rdisp/ROLL_SHM	[US] * 100	16384	8KB block	131072
rdisp/PG_MAXFS	32768	32768	8KB block	262144
rdisp/PG_SHM	[US] * 50	8192	8KB block	65536
abap/buffersize		900000	KB	1500000
zcsa/presentation_buffer_area		25000000	bytes	7400128
zcsa/table_buffer_area		150000896	bytes	90000128
zcsa/db_max_buftab		15000		16000
rsdb/obj/buffersize		40000	KB	30000
rsdb/obj/large_object_size		512000	bytes	522000
em/initial_size_MB		4092	MB	2248
rsdb/ntab/irbdsize		20150	KB	30000
ztta/roll_area		6291456	bytes	7410352
ztta/roll_extension MB		4000683008	bytes	2007600000

After tuning the parameter the system behaviour bit faster than before e and database buffer quality is 99.4. This some way managed to tune the parameter in worst case without adding the RAM. Mostly you have to check for latest patches, expansive SQL statement ,hardware side, memory side , CPU ..etc.. First need to identified the performance issue where its come from, then based on that you can tune for the same.

Reference OSS notes:-

941735 – SAP memory management system for 64-bit Linux systems

425207 – SAP memory management, current parameter ranges

146289 – Parameter Recommendations for 64-Bit SAP Kernel

789477 – Large extended memory on AIX (64-bit)

1171650 – Automated Oracle DB parameter check