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