Re: Better locks info

Danny Wright <dw420@airmail.net> wrote: >>Does someone have a better lock monitoring tool besides the one at >>iiug.org? (onstat -k interpreter?) >> >>There was a script there, but frankly, at least in the environment I >>currently find myself in, it is useless.....It reads syslocks, which >>I've never happened to have done before, but it does not come back in >>a reasonable amount of time (over 5 minutes.....by that time, I could >>have isolated the lock that I was having problems with manually!) >> >>I know better tools exist as I have used them before (a place I used >>to work had a decent shell script for this, which I actually enhanced, >>but of course that is the intellectual property of that former >>employer, so I don't have it now - though I could probably recreate it >>if noone has anything better). >> >>Thanks, >>Danny In article <38F36C1C.3CFEAEC7@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> replied:> >You could try the following if "who's locked out by whom?" is what you >are looking for? The script was designed specifically for sites that >could have a large number of locks active at any given time. > >#!/bin/ksh ># rudy Thu Mar 4 11:28:22 EST 1999 ># determines locking interactions - i.e which session is locking out >another > >TMP_FILE=$0.tmp.$$ >GET_ONSTATK=1 >FOUND=0 >ABNORMAL="Abnormal termination. Cleaning up and exiting..." >trap "echo $ABNORMAL;rm -f $TMP_FILE ; exit 1" 1 2 13 15 > >for SESS in `onstat -u | grep "L-" | cut -b 18-26` >do > FOUND=1 > echo "Session $SESS locked by session ... \\c" > for LOCK in `onstat -u | grep " $SESS " | cut -b 45-52` > do > if [ $GET_ONSTATK = 1 ] > then > onstat -k | cut -b 1-26 >$TMP_FILE > GET_ONSTATK=0 > fi > LOCKER=`grep "^${LOCK} " $TMP_FILE | awk '{ print $3 }'` > LOCKER_SESS=`onstat -u | grep "${LOCKER} " | cut -b 18-26` > echo $LOCKER_SESS > done>done >if [ $FOUND = 0 ] >then > echo 'No process waiting on locks' >fi >rm -f $TMP_FILE > >############ End ############ Jake picks up here: Danny, I presume (at the risk of being presumptuous) that you are referring to my utilities, who-lock.sh and who-access.sh, which I submitted a long time ago. I agree, in a busy system with many locks, it does take too long to get back values. However, its purpose was to determine who the ^%$#! is stopping me from getting to the specific tables/database I want to access. Rudy's script answers a slightly different question: Who the ^%$#! is getting in my way, in general. I like Rudy's script - I have already scarfed it and started to modify some of its code to the style I like. But I do have a proposal for Rudy: The wait column in "onstat -u" is not always a lock; it may be a latch (like waiting for the chance to muck-up a buffer or a checkpoint), which would not show up in onstat -k but would show up in onstat -s. I also don't know what other kind of events would show up (as a hex address) in the wait column of onstat -u. Yeah, I know a long-lived latch is unlikely but I also know most DBA's have had the pleasure of 10-minute checkpoints so a latch-aware script would be very helpful here. Rudy, wanna fill in some gaps? -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

Appreciate your comments, Jake. In fact, the "Locking" script actually supports another script (that I use far more frequently) that helps determine the first signs of trouble within an instance. All it does is reduce the rows returned by "onstat -u" to those sessions that are "active" - i.e either doing something or waiting to do something. (An understanding of the sessions flags is desirable :-)) #!/bin/ksh # rudy Wed Feb 11 08:22:09 EDT 1998 # without arguments. indicates "active" sessions # with 1 argument (session_id) indicates what that session is doing # with 1 argument ("all"). indicates what that all "active" sessions are doing if [ $# = 0 ] then onstat -u | \\ # Remove sessions waiting on netnorm or smread # - not always accurate grep -v '^.........Y' | \\ grep '^..........-' | \\ # Remove sessions with no locks (usually Informix admn. sessions) # User sessions have at least the database shared lock # Position of Locks column may be version specific grep -v "^..........................................................0" exit 0 fi if [ $1 = "all" ] then for SESS in `onstat -u | \\ grep -v '^.........Y' | \\ grep '^..........-' | \\ grep -v "^..........................................................0" | \\ awk '{ print $3 }' | sort -u` do onstat -g ses $SESS | more done exit 0fi onstat -g ses $1 | more ####### End ########## Rudy Jacob Salomon wrote: > ... > The wait column in "onstat -u" is not always a lock; it may be a latch > (like waiting for the chance to muck-up a buffer or a checkpoint), which > would not show up in onstat -k but would show up in onstat -s. I also > don't know what other kind of events would show up (as a hex address) in > the wait column of onstat -u. Yeah, I know a long-lived latch is > unlikely but I also know most DBA's have had the pleasure of 10-minute > checkpoints so a latch-aware script would be very helpful here. > > Rudy, wanna fill in some gaps? > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Before you buy.