cancel
Showing results for 
Search instead for 
Did you mean: 

Our data buffer quality on PRD drop to 20% in st04

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

magexposito
Active Participant
0 Kudos

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.

manumohandas82
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Dear

i also had same thought and i restarted the server and i killed all os process and cleared all shared memory then i restart the server

then also same issue with st04 data buffer quality

Regards

manumohandas82
Active Contributor
0 Kudos

Hi Rajendra ,

Can you share the  topas screen shot from AIX

Also your Oracle Memory settings .

Thanks ,

Manu

Former Member
0 Kudos

Dear

Regards

manumohandas82
Active Contributor
0 Kudos

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

Sriram2009
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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'

Former Member
0 Kudos

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

Sriram2009
Active Contributor
0 Kudos

Hi

On SM21 you getting any error message? & Any dumps in ST22?

BR

manumohandas82
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Dear

how we can decide how much % we can give to sga_max_size depending on which factor

how we can decide EM/TOTAL_SIZE_MB on what basis is there is any thumb rule bcaz this is our prd server we have 28gb of memory on central instance

Kindly suggest

Regards

manumohandas82
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Dear

send me simple steps how to change parameter in oracle

i have following steps tell me is that ok

shutdown immedaite

satrtup nomount

create PFILE from SPFILE

alter system SET SGA_MAX_SIZE =18000M scope=SPFILE;

shutdown immediate;

create spfile form pfile

is that ok if not send me steps

Regards

Former Member
0 Kudos

Dear

as you said 50% 50% then it must be 14 only but you specify 18 can i go with that or first go with 14gb  i am asking all this this is an PRD server

Regards

manumohandas82
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Dear

Thank you very much for your kind suggestion i would rather go with SAP for the solution as you suggested

i also send you trace of st12 for user and job

Regards

manumohandas82
Active Contributor
0 Kudos

Hi Raj ,

Didnt receive the Trace yet .

Thanks ,

Manu

Former Member
0 Kudos

Dear

Our problem has been solved now our PRD server quality reaches to 80% as of now and it is continuously increasing yesterday we have re indexing all the table then we have taken restart of db and app then that will work for us

Thank for kind support

manumohandas82
Active Contributor
0 Kudos

Cheers Raj .

Kindly close the thread

Thanks ,

Manu

Former Member
0 Kudos

Dear

Thanks once again

Regards

Sriram2009
Active Contributor
0 Kudos

Hi Rajendra

Have you define the memory & buffers values based on you OS / DB?  Could you share your OS /DB & SAP Version details?

BR

Former Member
0 Kudos

Dear

OS AIX 6.0

DB Oracle 11.2.0.4.0

SAP    EHP6 for SAP ERP6

SAPKB73112

SAPKA73112

Regards

Sriram2009
Active Contributor
0 Kudos

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