Sessions doing sorts to disk

select username,

syssesprof.sid,

total_sorts,

dsksorts,

max_sortdiskspace

from syssesprof, syssessions

where syssesprof.sid = syssessions.sid

order by max_sortdiskspace desc

You can use a SQL statement to obtain information regarding each of the temporary tables that are currently created

SELECT t.tabname ,t.dbsname,t.owner,
DECODE(d.is_logging,1,"Y","N") AS db_with_log,
s.name AS dbspace,
DBINFO("UTC_TO_DATETIME",ti_created) AS created,
DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS
table_using_log,ti_npused AS num_usedpages,
ti_nptotal AS num_pages
FROM sysmaster:systabnames t, sysmaster:systabinfo i,
sysmaster:sysdbspaces s, sysmaster:sysdatabases d
WHERE t.partnum=ti_partnum AND
d.name=t.dbsname AND
s.dbsnum=TRUNC(t.partnum/1048576) AND
hex(mod(ti_flags,256)/16) IN ( 6,2 )

BTREE Cleaner, something to start with

onstat -C

onstat -C hot

onstat -C clean

onmode -C start X

onmode -C stop

onmode -C rangesize 100

If you are version 10.00.xC8 or higher I would suggest turning on ALICE=

scanning to value 6 or higher.

onmode -C alice 6

This will greatly increase the performance of the index cleaning.

Just to add that ALICE will only work if the indexes are detached which they

should if they were created under v10, but if you migrated in place, this

may not be the case...

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