SAP Mix

RE: Database locks

Posted By:
Date:

The book 'SAP R/3 for the Informix DBA' indicates that the ratio
(lokwaits/lockreqs)*100 < 1
from the 'onstat -p' is used to judge whether adequate locks have been
defined. The number of locks should be increased when the calculation is
greater than 1%






>From: "NormaJean.S...." <NormaJean.Sebastian@tellabs.com>
>To: sapmix@iiug.org
>Subject: RE: Database locks [51] Date: Thu, 27 Feb 2003 20:33:46 -0500
>(EST)
>
>Hi,
>We run with 1 million locks on our SAP PRD database of 850 GB (10 GB
>memory, with 6GB memory ear-marked for Informix(64bit)).
>We run with 500,000 locks in our SAP QA databases in the landscape.
>
>We run 24x7. Any outage in PRD is not a happy thing. User outage
>includes the database being unavailable due to the rollback of a
>transaction because of lock table overflow. We've had 30+ minutes of
>outage on occasion because of this. It is not acceptable in our
>environment.
>
>We monitor like Marco does and page the on-call DBA if we surpass
>475,000 locks (we change this page-out number as we see fit). If the
>DBA is paged, we have full authority to kill the offending transaction
>as Marco described.
>
>Just because SAP/OSS may encourage 'loose' ABAP code, doesn't mean you
>have to permit it. There is an OSS note (don't know which one) that
>discusses begin/commit/transaction processing in ABAP. It's better to
>make the programmers aware of this and accountable for their code than
>to let them accidentally put 'loose' code into PRD. If that happens,
>and PRD locks up...as you know... fingers are pointed at the DBAs.
>
>We have lower locks on our QA servers than in PRD. We hope that will
>help the programmers code more efficiently and not get our PRD server
>into an outage situation for the good of the entire company.
>
>Norma Jean Sebastian
>
>
>
>-----Original Message-----
>From: Marco.Krijt@VendexKBB-ITS.nl [mailto:Marco.Krijt@VendexKBB-ITS.nl]
>Sent: Wednesday, February 26, 2003 2:04 PM
>To: sapmix@iiug.org; forum.subscriber@iiug.org
>Subject: RE: Database locks [46]
>
>
>We have also 2000000 locks on a database of 1,1TB.
>If it is not enough, I say always to look to the ABAP-programm,
>or the variant.
>
>Sometimes programms have build in a commit, if a job fails or make to
>many
>locks the commit interval has to be reduced in the ABAP-programm (or
>sometimes variant).
>
>We have already 2000000 locks for many years, and it works fine.
>
>With onstat -u, you can see which proces has many locks.
>We also are called by phone automatically if more then 1000000 locks are
>in
>use
>(for 24 hours a day.)
>
>Then we look with:
>onstat -u to the locks and the SID,
>then we look to onstat -g ses|grep <SID> and get the PID
>You can see the PID back in SM66/SM50.
>We tell it to the user (at office hours) and kill the SID with onmode -z
>or
>use SM66/SM50.
>If with onstat -u the third caracter is a R the transaction is rolling
>back
>and will not take any locks any more, After the rollback (sometimes
>hours)
>the problem is solved.
>
>If you wait to long to kill the SAP-proces al other programms will be
>failing with,
>"no more locks available" and that's a pitty, so be on time, that's
>nicer.
>
> > Marco Krijt
> > Medewerker Operationeel Beheer SAP
> > Vendex KBB IT Services b.v.
> > Frankemaheerd 6
> > 1102 AN Amsterdam Z.O.
> > Postbus 12644
> > 1100 AP Amsterdam Z.O.
> > Tel: +31 (0)20-3129254
> > E-mail: marco.krijt@vendexkbb-its.nl
> >
>
>
>-----Oorspronkelijk bericht-----
>Van: Chang, Nicole [mailto:changn@tycoelectronics.com]
>Verzonden: woensdag 26 februari 2003 20:29
>Aan: sapmix@iiug.org
>Onderwerp: Database locks [45]
>
>
>Hello,
>
>I was just wondering how many database locks you have available for a
>Production instance. We have a 344 gig database, with 2 million locks
>available, and an average of 500 users that are logged on.
>
>I ask only because OSS is urging us to increase to 4 million locks,
>which is
>double the locks I have.
>
>Thank you in advance.
>
>Nicole Chang
>Database Analyst
>M/A-COM, A Tyco International Company
>978-442-4389
>
>
>
>
>--openmail-part-39c80cdf-00000002
>Content-Type: application/rtf
>Content-Disposition: attachment; filename="BDY.RTF"
> ;Creation-Date="Thu, 27 Feb 2003 19:30:50 -0600"
>Content-Transfer-Encoding: base64
>
>e1xydGYxXGFuc2lcYW5zaWNwZzEyNTJcZnJvbXRleHQgXGRlZmYwe1xmb250dGJsDQp7XGYw
>XGZzd2lzcyBBcmlhbDt9DQp7XGYxXGZtb2Rlcm4gQ291cmllciBOZXc7fQ0Ke1xmMlxmbmls
>XGZjaGFyc2V0MiBTeW1ib2w7fQ0Ke1xmM1xmbW9kZXJuXGZjaGFyc2V0MCBDb3VyaWVyIE5l
>dzt9fQ0Ke1xjb2xvcnRibFxyZWQwXGdyZWVuMFxibHVlMDtccmVkMFxncmVlbjBcYmx1ZTI1
>NTt9DQpcdWMxXHBhcmRccGxhaW5cZGVmdGFiMzYwIFxmMFxmczIwIEhpLFxwYXINCldlIHJ1
>biB3aXRoIDEgbWlsbGlvbiBsb2NrcyBvbiBvdXIgU0FQIFBSRCBkYXRhYmFzZSBvZiA4NTAg
>R0IgKDEwIEdCIG1lbW9yeSwgd2l0aCA2R0IgbWVtb3J5IGVhci1tYXJrZWQgZm9yIEluZm9y
>bWl4KDY0Yml0KSkuXHBhcg0KV2UgcnVuIHdpdGggNTAwLDAwMCBsb2NrcyBpbiBvdXIgU0FQ
>IFFBIGRhdGFiYXNlcyBpbiB0aGUgbGFuZHNjYXBlLlxwYXINClxwYXINCldlIHJ1biAyNHg3
>LiAgQW55IG91dGFnZSBpbiBQUkQgaXMgbm90IGEgaGFwcHkgdGhpbmcuICBVc2VyIG91dGFn
>ZSBpbmNsdWRlcyB0aGUgZGF0YWJhc2UgYmVpbmcgdW5hdmFpbGFibGUgZHVlIHRvIHRoZSBy
>b2xsYmFjayBvZiBhIHRyYW5zYWN0aW9uIGJlY2F1c2Ugb2YgbG9jayB0YWJsZSBvdmVyZmxv
>dy4gIFdlJ3ZlIGhhZCAzMCsgbWludXRlcyBvZiBvdXRhZ2Ugb24gb2NjYXNpb24gYmVjYXVz
>ZSBvZiB0aGlzLiAgSXQgaXMgbm90IGFjY2VwdGFibGUgaW4gb3VyIGVudmlyb25tZW50Llxw
>YXINClxwYXINCldlIG1vbml0b3IgbGlrZSBNYXJjbyBkb2VzIGFuZCBwYWdlIHRoZSBvbi1j
>YWxsIERCQSBpZiB3ZSBzdXJwYXNzIDQ3NSwwMDAgbG9ja3MgKHdlIGNoYW5nZSB0aGlzIHBh
>Z2Utb3V0IG51bWJlciBhcyB3ZSBzZWUgZml0KS4gIElmIHRoZSBEQkEgaXMgcGFnZWQsIHdl
>IGhhdmUgZnVsbCBhdXRob3JpdHkgdG8ga2lsbCB0aGUgb2ZmZW5kaW5nIHRyYW5zYWN0aW9u
>IGFzIE1hcmNvIGRlc2NyaWJlZC5ccGFyDQpccGFyDQpKdXN0IGJlY2F1c2UgU0FQL09TUyBt
>YXkgZW5jb3VyYWdlICdsb29zZScgQUJBUCBjb2RlLCBkb2Vzbid0IG1lYW4geW91IGhhdmUg
>dG8gcGVybWl0IGl0LiAgVGhlcmUgaXMgYW4gT1NTIG5vdGUgKGRvbid0IGtub3cgd2hpY2gg
>b25lKSB0aGF0IGRpc2N1c3NlcyBiZWdpbi9jb21taXQvdHJhbnNhY3Rpb24gcHJvY2Vzc2lu
>ZyBpbiBBQkFQLiAgSXQncyBiZXR0ZXIgdG8gbWFrZSB0aGUgcHJvZ3JhbW1lcnMgYXdhcmUg
>b2YgdGhpcyBhbmQgYWNjb3VudGFibGUgZm9yIHRoZWlyIGNvZGUgdGhhbiB0byBsZXQgdGhl
>bSBhY2NpZGVudGFsbHkgcHV0ICdsb29zZScgY29kZSBpbnRvIFBSRC4gIElmIHRoYXQgaGFw
>cGVucywgYW5kIFBSRCBsb2NrcyB1cC4uLmFzIHlvdSBrbm93Li4uIGZpbmdlcnMgYXJlIHBv
>aW50ZWQgYXQgdGhlIERCQXMuXHBhcg0KXHBhcg0KV2UgaGF2ZSBsb3dlciBsb2NrcyBvbiBv
>dXIgUUEgc2VydmVycyB0aGFuIGluIFBSRC4gIFdlIGhvcGUgdGhhdCB3aWxsIGhlbHAgdGhl
>IHByb2dyYW1tZXJzIGNvZGUgbW9yZSBlZmZpY2llbnRseSBhbmQgbm90IGdldCBvdXIgUFJE
>IHNlcnZlciBpbnRvIGFuIG91dGFnZSBzaXR1YXRpb24gZm9yIHRoZSBnb29kIG9mIHRoZSBl
>bnRpcmUgY29tcGFueS5ccGFyDQpccGFyDQpOb3JtYSBKZWFuIFNlYmFzdGlhblxwYXINClxw
>YXINClxwYXINClxwYXINCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tXHBhcg0KRnJvbTog
>TWFyY28uS3JpanRAVmVuZGV4S0JCLUlUUy5ubCBbbWFpbHRvOk1hcmNvLktyaWp0QFZlbmRl
>eEtCQi1JVFMubmxdXHBhcg0KU2VudDogV2VkbmVzZGF5LCBGZWJydWFyeSAyNiwgMjAwMyAy
>OjA0IFBNXHBhcg0KVG86IHNhcG1peEBpaXVnLm9yZzsgZm9ydW0uc3Vic2NyaWJlckBpaXVn
>Lm9yZ1xwYXINClN1YmplY3Q6IFJFOiBEYXRhYmFzZSBsb2NrcyBbNDZdIFxwYXINClxwYXIN
>ClxwYXINCldlIGhhdmUgYWxzbyAyMDAwMDAwIGxvY2tzIG9uIGEgZGF0YWJhc2Ugb2YgMSwx
>VEIuXHBhcg0KSWYgaXQgaXMgbm90IGVub3VnaCwgSSBzYXkgYWx3YXlzIHRvIGxvb2sgdG8g
>dGhlIEFCQVAtcHJvZ3JhbW0sXHBhcg0Kb3IgdGhlIHZhcmlhbnQuXHBhcg0KXHBhcg0KU29t
>ZXRpbWVzIHByb2dyYW1tcyBoYXZlIGJ1aWxkIGluIGEgY29tbWl0LCBpZiBhIGpvYiBmYWls
>cyBvciBtYWtlIHRvIG1hbnlccGFyDQpsb2NrcyB0aGUgY29tbWl0IGludGVydmFsIGhhcyB0
>byBiZSByZWR1Y2VkIGluIHRoZSBBQkFQLXByb2dyYW1tIChvclxwYXINCnNvbWV0aW1lcyB2
>YXJpYW50KS5ccGFyDQpccGFyDQpXZSBoYXZlIGFscmVhZHkgMjAwMDAwMCBsb2NrcyBmb3Ig
>bWFueSB5ZWFycywgYW5kIGl0IHdvcmtzIGZpbmUuXHBhcg0KXHBhcg0KV2l0aCBvbnN0YXQg
>LXUsIHlvdSBjYW4gc2VlIHdoaWNoIHByb2NlcyBoYXMgbWFueSBsb2Nrcy5ccGFyDQpXZSBh
>bHNvIGFyZSBjYWxsZWQgYnkgcGhvbmUgYXV0b21hdGljYWxseSBpZiBtb3JlIHRoZW4gMTAw
>MDAwMCBsb2NrcyBhcmUgaW5ccGFyDQp1c2UgXHBhcg0KKGZvciAyNCBob3VycyBhIGRheS4p
>XHBhcg0KXHBhcg0KVGhlbiB3ZSBsb29rIHdpdGg6XHBhcg0Kb25zdGF0IC11IHRvIHRoZSBs
>b2NrcyBhbmQgdGhlIFNJRCxccGFyDQp0aGVuIHdlIGxvb2sgdG8gb25zdGF0IC1nIHNlc3xn
>cmVwIDxTSUQ+IGFuZCBnZXQgdGhlIFBJRFxwYXINCllvdSBjYW4gc2VlIHRoZSBQSUQgYmFj
>ayBpbiBTTTY2L1NNNTAuXHBhcg0KV2UgdGVsbCBpdCB0byB0aGUgdXNlciAoYXQgb2ZmaWNl
>IGhvdXJzKSBhbmQga2lsbCB0aGUgU0lEIHdpdGggb25tb2RlIC16IG9yXHBhcg0KdXNlIFNN
>NjYvU001MC5ccGFyDQpJZiB3aXRoIG9uc3RhdCAtdSB0aGUgdGhpcmQgY2FyYWN0ZXIgaXMg
>YSBSIHRoZSB0cmFuc2FjdGlvbiBpcyByb2xsaW5nIGJhY2tccGFyDQphbmQgd2lsbCBub3Qg
>dGFrZSBhbnkgbG9ja3MgYW55IG1vcmUsIEFmdGVyIHRoZSByb2xsYmFjayAoc29tZXRpbWVz
>IGhvdXJzKVxwYXINCnRoZSBwcm9ibGVtIGlzIHNvbHZlZC5ccGFyDQpccGFyDQpJZiB5b3Ug
>d2FpdCB0byBsb25nIHRvIGtpbGwgdGhlIFNBUC1wcm9jZXMgYWwgb3RoZXIgcHJvZ3JhbW1z
>IHdpbGwgYmVccGFyDQpmYWlsaW5nIHdpdGgsXHBhcg0KIm5vIG1vcmUgbG9ja3MgYXZhaWxh
>YmxlIiBhbmQgdGhhdCdzIGEgcGl0dHksIHNvIGJlIG9uIHRpbWUsIHRoYXQncyBuaWNlci5c
>cGFyDQpccGFyDQo+IFx0YWIgXHRhYiBcdGFiIE1hcmNvIEtyaWp0XHBhcg0KPiBcdGFiIFx0
>YWIgXHRhYiBNZWRld2Vya2VyIE9wZXJhdGlvbmVlbCBCZWhlZXIgU0FQXHBhcg0KPiBcdGFi
>IFx0YWIgXHRhYiBWZW5kZXggS0JCIElUIFNlcnZpY2VzIGIudi5ccGFyDQo+IFx0YWIgXHRh
>YiBcdGFiIEZyYW5rZW1haGVlcmQgNlxwYXINCj4gXHRhYiBcdGFiIFx0YWIgMTEwMiBBTiBB
>bXN0ZXJkYW0gWi5PLlxwYXINCj4gXHRhYiBcdGFiIFx0YWIgUG9zdGJ1cyAxMjY0NFxwYXIN
>Cj4gXHRhYiBcdGFiIFx0YWIgMTEwMCBBUCBBbXN0ZXJkYW0gWi5PLlxwYXINCj4gXHRhYiBc
>dGFiIFx0YWIgVGVsOiArMzEgKDApMjAtMzEyOTI1NFxwYXINCj4gXHRhYiBcdGFiIFx0YWIg
>RS1tYWlsOiBtYXJjby5rcmlqdEB2ZW5kZXhrYmItaXRzLm5sXHBhcg0KPiBccGFyDQpccGFy
>DQpccGFyDQotLS0tLU9vcnNwcm9ua2VsaWprIGJlcmljaHQtLS0tLVxwYXINClZhbjogQ2hh
>bmcsIE5pY29sZSBbbWFpbHRvOmNoYW5nbkB0eWNvZWxlY3Ryb25pY3MuY29tXVxwYXINClZl
>cnpvbmRlbjogd29lbnNkYWcgMjYgZmVicnVhcmkgMjAwMyAyMDoyOVxwYXINCkFhbjogc2Fw
>bWl4QGlpdWcub3JnXHBhcg0KT25kZXJ3ZXJwOiBEYXRhYmFzZSBsb2NrcyBbNDVdIFxwYXIN
>ClxwYXINClxwYXINCkhlbGxvLFxwYXINClxwYXINCkkgd2FzIGp1c3Qgd29uZGVyaW5nIGhv
>dyBtYW55IGRhdGFiYXNlIGxvY2tzIHlvdSBoYXZlIGF2YWlsYWJsZSBmb3IgYVxwYXINClBy
>b2R1Y3Rpb24gaW5zdGFuY2UuICBXZSBoYXZlIGEgMzQ0IGdpZyBkYXRhYmFzZSwgd2l0aCAy
>IG1pbGxpb24gbG9ja3NccGFyDQphdmFpbGFibGUsIGFuZCBhbiBhdmVyYWdlIG9mIDUwMCB1
>c2VycyB0aGF0IGFyZSBsb2dnZWQgb24uXHBhcg0KXHBhcg0KSSBhc2sgb25seSBiZWNhdXNl
>IE9TUyBpcyB1cmdpbmcgdXMgdG8gaW5jcmVhc2UgdG8gNCBtaWxsaW9uIGxvY2tzLCB3aGlj
>aCBpc1xwYXINCmRvdWJsZSB0aGUgbG9ja3MgSSBoYXZlLlxwYXINClxwYXINClRoYW5rIHlv
>dSBpbiBhZHZhbmNlLlxwYXINClxwYXINCk5pY29sZSBDaGFuZ1xwYXINCkRhdGFiYXNlIEFu
>YWx5c3RccGFyDQpNL0EtQ09NLCBBIFR5Y28gSW50ZXJuYXRpb25hbCBDb21wYW55XHBhcg0K
>OTc4LTQ0Mi00Mzg5XHBhcg0KXHBhcg0KXHBhcg0KfQ==
>
>--openmail-part-39c80cdf-00000002--
>
>


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail

Messages In This Thread

• RE: Database locks
  -- Tuesday, 4 March 2003, at 12:41 p.m.

SAP Mix is maintained by Administrator with WebBBS 5.12.