SAP Mix

RE: Database locks

Posted By:
Date:

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--

Messages In This Thread

• RE: Database locks
  -- Friday, 28 February 2003, at 6:08 a.m.

SAP Mix is maintained by Administrator with WebBBS 5.12.