Everything related to Locks

select

l.indx,

l.partnum,

p.dbsname,

p.tabname,

l.rowidr,

l.keynum,

l.grtime,

DECODE(l.type,

0, 'NONE',

1, 'BYTE',

2, 'IS',

3, 'S',

4, 'SR',

5, 'U',

6, 'UR',

7, 'IX',

8, 'SIX',

9, 'X',

10, 'XR') type,

r.sid,

s.pid,

s.hostname

from

syslcktab l, -- raw lock table

systabnames p, -- partnum -> dbs/tab map

systxptab x, -- transactions

sysrstcb r, -- rsam thread control blocks

sysscblst s -- session control blocks

where

l.partnum <> 1048578 and -- skip 'sysmaster.sysdatabases'...

l.partnum = p.partnum and

l.owner = x.address and

x.owner = r.address and

r.sid = s.sid

order by

l.grtime desc

BTR Buffer turnover ratio

BTR = (((bufwrits + pagreads) / BUFFERS) / elapsed)

elapse in hours (since you zero-ed the stats)

This might find the sql causing a locked mutex....

#!/bin/ksh

my_FILE="/tmp/glmx.out"

onstat -g lmx | awk '{printf $4 "\n"}' | egrep -v "Server|holder|on" > $my_FILE

if [ -s $my_FILE ]

then

while read line

do

if [ ! -z "$line" ] # string is not empty

then

ath_val=`onstat -g ath | grep "$line" | awk '{printf $3}'`

sid=`onstat -u | grep $ath_val | awk '{printf $3}'`

echo "SQL output for session: $sid"

onstat -g sql $sid > /tmp/$sid-lmx.out

else

echo "No session found for locked mutex."

fi

done < $my_FILE

else

echo "No locked mutexes found"

fi

bring ONLINE , chunks marked as down

select "onspaces -s "||trim(a.name)||" -p "|| trim(b.fname)|| " -o " || b.offset * BLOODY_PAGE_SIZE_IN_K || " -O -y "
from sysdbspaces a,syschunks b
where a.dbsnum=b.dbsnum and is_offline = 1 and is_temp=0
union
select "onspaces -s "||trim(a.name)||" -p "|| trim(b.mfname)|| " -o " || b.moffset * BLOODY_PAGE_SIZE_IN_K || " -O -y "
from sysdbspaces a,syschunks b
where a.dbsnum=b.dbsnum and mis_offline = 1 and is_temp=0

worst 15 queries

select first 15 sqx_estcost cost,
syssessions.username[1,10] user,
syssessions.tty[1,10],
sqx_iscurrent crnt,
sqx_sessionid sid,
trim(sqx_sqlstatement[1,180]) sql
from syssqexplain, syssessions
where sqx_iscurrent = 'Y'
and sqx_sessionid = syssessions.sid
and sqx_sessionid <> DBINFO('sessionid')
order by sqx_iscurrent desc, sqx_estcost desc ;

Query to show number of extents table has left rather than using oncheck

SELECT --+ORDERED,INDEX(a,systabs_pnix),INDEX(b,sysptnhdridx),INDEX(c,syspaghd)

a.dbsname AS database_name,

a.tabname,

d.name AS dbspace_name,

b.nextns AS extents_act,

TRUNC(c.pg_frcnt / 8) + b.nextns AS max_extents

FROM sysmaster:sysdbstab d,

sysmaster:syspaghdr c,

sysmaster:systabnames a,

sysmaster:sysptnhdr b

WHERE c.pg_partnum = sysmaster:partaddr(d.dbsnum, 1)

AND sysmaster:bitval(c.pg_flags, 2) = 1

AND c.pg_nslots = 5

AND a.partnum = sysmaster:partaddr(d.dbsnum, c.pg_pagenum)

AND a.partnum = b.partnum

How to trace a session holding a mutex lock

Example to trace a session holding a mutex lock and preventing another one to run:

Tracing this mutex lock through...

onstat -g lmx -r 1

Locked mutexes:

mid addr name holder lkcnt waiter waittime

181210 5d7cf3e30 rrlist 127856 1

onstat -g ath | grep 127856

127856 57e2381c0 5ca641b68 1 running 9cpu sqlexec

onstat -u | grep 5ca641b68

5ca641b68 ---P--- 113927 openbet - 0 0 1 0 0

onstat -g sql 113927

Sess SQL Current Iso Lock SQL ISAM F.E.

Id Stmt type Database Lvl Mode ERR ERR Vers Explain

113927 SELECT perfmon NL Wait 10 0 0 9.29 Off

Current SQL statement :

select distinct t.qry_id, t.text from tQryText t,

tQrySum s where t.qry_id = s.qry_id and s.app_id in

(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and s.date > ? order by 2;

onstat –g ath (perfom sql and update stats to trjnl)

110507 5df5e75a8 59ee052d0 1 cond wait netnorm 9cpu sqlexec

110508 6133c3028 596a5aef8 1 sleeping forever 9cpu sqlexec ß Update stats

127856 57e2381c0 5ca641b68 1 running 9cpu sqlexec ß SQL to perfmon