on 01-20-2015 5:53 AM
Dear Experts
in our prd server our ST04 data buffer quality drop to 20% and no body able to take any reports and our physical reads are increasing rapidly
and in st02 our extended memory uses 18.8 where in qua and dev it user only 0.8 tell me what could be the reason and how to move ahead with this
ST04
Give some expert advice to solve this
Regards
The reason for low data buffer quality in ST04 may be due to restart of your DB server (Start up at 19.01.2015 21:51:23, the value checked at 20.01.2015 11:14:05). Try to check this value later. In any way you must no use data buffer quality value as performance indicator. Please read more about modern performance tuning methods.
The usage of the extended memory depends on workload performed on instance and more likely wouldn't be the same on different instances.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear
we have re start server every fort night for offline backup but data/buffer quality reaches to more then 90% with day but now it has already 3 days have gone but there is increase in data/buffer quality as you have seen
kindly share some document how to trace out expensive sql query and what needs to be done
some body suggested that our os is using more memory how to find that which process in OS consumes more cpu
i saw my cpu is ideal only 10% but at that time nobody logged in the server only 10 log on are there
what cloud be the reason
is there any process from oracle which we need to trace out
Kindly suggest some information
Regards
Hello Rajendra,
The values on transaction ST02 are fine, the Hit Ratio % of all the buffers are between the recommended values by SAP. On the Program Buffer there is no problem if the Hit Ratio % is slightly under 99%. Also, there is no problem if there is SWAP in each buffer if you keep the Hit Ratio % between the recommended values.
About the Data Buffer quality on transaction ST04, you have to keep in mind that the quality should be greater than 95%. If you want to improve this quality you have to analyze the SQL buffer in order to tune expensive SQL sentence or increase the buffer quality. To do this go to transaction ST04 --> Performance --> SQL Cache monitor and check columns such as Response Time, Disk Reads, Buffer Gets, etc.
Best regards.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rajendra ,
Dont see any issue with your SAP Configured memory ( except for program buffer , which could be increased slightly )
But this doesnt seems to be the problem , Your DB parameters needs to be reviewed
Is there anything running in the OS which is occupying memory ?
Thanks ,
Manu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rajendra ,
Dont see any Paging in your system . Dont see many oracle process either considering you have 28 GB of memory .
Is is ABAP instance running on the same server . How many work process have you configured ?
Can you share the Oracle memory parameters that you have configured .
Thanks ,
Manu
Hi Rajendra
If your are enterprise customer to SAP, you can avail the SAP Health Check service. they will advice very well.
Refer the sample health check http://wiki.scn.sap.com/wiki/download/attachments/333546070/Health+check+up+for+ABAP+System.pdf
BR
SS
Dear
following are the oracle parameters
--------------------------------------------
XXX.__oracle_base='/oracle'#ORACLE_BASE set from environment
*._fix_control='5099019:ON','5705630:ON','6055658:OFF','6120483:OFF','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6972291:ON','7168184:OFF','8937971:ON','9196440:ON','9495669:ON','13077335:ON','13627489:ON','14255600:ON','14595273:ON'
*._mutex_wait_scheme=1#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*._mutex_wait_time=10#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*._optim_peek_user_binds=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*._optimizer_adaptive_cursor_sharing=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*._optimizer_extended_cursor_sharing_rel='NONE'#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*._optimizer_use_feedback=FALSE#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*._securefiles_concurrency_estimate=50#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*.archive_lag_target=900
*.audit_file_dest='/oracle/XXX/saptrace/audit'
*.compatible='11.2.0'#Parameter setting for SAP on Oracle Release 11.2 (see SAP note 1431798)
*.control_file_record_keep_time=30
*.control_files='/oracle/XXX/origlogA/cntrl/cntrlXXX.dbf','/oracle/XXX/origlogB/cntrl/cntrlXXX.dbf','/oracle/XXX/sapdata1/cntrl/cntrlXXX.dbf'
*.db_block_size=8192
*.db_cache_size=8589934592
*.db_files=254
*.db_name='XXX'
*.diagnostic_dest='/oracle/XXX/saptrace'
*.dml_locks=4000
*.event='10027','10028','10142','10183','10191','10198','10995 level 2','31991','38068 level 100','38085','38087','44951 level 1024'#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*.fal_client='XXX'
*.fal_server='SXXX'
*.filesystemio_options='setall'
*.job_queue_processes=1
*.log_archive_config='DG_CONFIG=(XXX,SXXX)'
*.log_archive_dest_1='LOCATION=/oracle/XXX/oraarch/XXXarch mandatory'
*.log_archive_dest_2='SERVICE=SXXX LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SXXX optional reopen=30'
*.log_archive_dest=''
*.log_archive_dest_state_2='enable'
*.log_buffer=2097152#RECOMMENDED BY ORACLE/SAP FOR 11.2.0 - SAP note 1431798
*.log_checkpoint_interval=0
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20000'
*.open_cursors=800
*.optimizer_dynamic_sampling=6
*.optimizer_index_cost_adj=20
*.parallel_execution_message_size=16384
*.parallel_max_servers=40
*.parallel_threads_per_cpu=1
*.processes=800
*.query_rewrite_enabled='false'
*.recyclebin='off'
*.remote_login_passwordfile='EXCLUSIVE'
*.remote_os_authent=TRUE#SAP note 1431798
*.replication_dependency_tracking=FALSE
*.sessions=960
*.sga_max_size=12884901888
*.shared_pool_size=3472883712
*.standby_file_management='AUTO'
*.star_transformation_enabled='true'
*.undo_retention=43200
*.undo_tablespace='PSAPUNDO'
Dear
our is aix os 6.1
with 28 gb of ram
with 10 cpus
user 250 concurrent user
what are best oracle and sap parameter which suits our requirement
sga_max_size=12884901888
*.shared_pool_size=3472883712
*.standby_file_management='AUTO'
*.star_transformation_enabled='true'
*.undo_retention=43200
db_block_size=8192
*.db_cache_size=8589934592
sap parameters are
ES/TABLE = SHM_SEGS
EM/TOTAL_SIZE_MB = 16384
em/global_area_MB = 250
ES/SHM_SEG_SIZE = 2048
ipc/shm_psize_40 = 500000000
ipc/shm_psize_10 = 536000000
abap/heap_area_total = 8000000000
ztta/roll_first = 1
ztta/roll_extention = 4000000000
abap/heap_area_nondia = 3000000000
abap/heap_area_dia = 5000000000
ztta/roll_area = 6500000
Hi Raj ,
Can you increase the Oracle parameter
sga_max_size to 18 GB , ( Orac;le )
EM/TOTAL_SIZE_MB = 12 GB ( SAP )
restart the SAP and DB applications . [ If possible get a restart of the server ] .
Once the server is restarted , Before staring the Oracle .. check for the and memory utilization .
Once Oracle is started check once again the memory utilization and Cpu
Finally start the SAP applications . Check again the CPU and memory .
We will start tracing if we dont get any improvements
Also share how many Work process are you using for the ABAP instance
Thanks ,
Manu
Hi Raj ,
Believe 50 % - 50% is the rule of thumb .. Your system were it is configured as 12 for DB and 16 GB for SAP.
Dont see any worse swapping in your st02 screen , also you could check the workprocess logs as to their usage and restarts .
so believe you could give more memory to DB
Also check the limits from <sid>adm and ora<sid> check whether memory usage is unlimited
Thanks ,
Manu
Hi Rajendra ,
It is unfortunate that it is a PRD server and you cannot recreate the issue in another system . Ideally if you could have recreated the issue in Qulaity or Preprod it would have been better .
As mentioned earlier this is a trial and error method and ideally should not be done on production .
What is the situation of the production system now . Are any users complaining of serious performance degrade ? If yes then : can you take a ST12 trace for the user/job and attach
If the users are not complaining and Jobs are all completing then better to raise the issue with SAP and wait for their opinion before the restart. ( As it is production )
Thanks ,
Manu
Hi Rajendra
Have you define the memory & buffers values based on you OS / DB? Could you share your OS /DB & SAP Version details?
BR
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Raj
From the ST02 snapshot you have to increase the buffer values, and also refer the SAP Note for SAP on AIX recommandations
1972803 - SAP on AIX: Recommendations
BR
SS
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.