While monitoring the performance of a slow running program on a
Solaris 9 Ingres 2.6 installation, iimonitor sampling showed
SortThread at 100% computable. The active dbms session on this dbms
was using only 1% of cpu (from prstat -L).

Can anyone tell me what "SortThread at 100% computable" indicates
about the program behaviour? Are there any Ingres tuning
considerations?

thanks,
James Keane

Thread State Analysis: ----------- EventWait ------------
Thread Name Computable BIO DIO LIO LOG LOCK Other
MutexWait Other
AdminThread 0.0 100.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0
UserThread 4.6 55.3 39.4 0.4 0.0 0.0
0.2 0.0 0.0
FastCommitThread 0.0 0.0 4.2 0.0 0.0 0.0
95.8 0.0 0.0
WriteBehindThread 0.0 0.0 1.1 0.0 0.0 0.0
98.9 0.0 0.0
EventThread 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
LogWriter 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
CheckDeadThread 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
GroupCommitThread 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
ForceAbortThread 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
CheckTermThread 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
ReplicatorQueueMgr 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
SortThread 100.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0
LicenseThread 0.0 0.0 0.0 0.0 0.0 0.0
100.0 0.0 0.0
--------------------------------------------------------------------------------------
Computing Analysis:
Facility User System
CLF 0.1 0.0
DMF 0.0 0.0
OPF 1.2 0.0
PSF 1.2 0.0
QEF 1.8 0.0
SCF 0.2 0.0
--------------------------
Mutex Waits are less than 1%
----------------------------
Lock Waits are less than 1%
---------------------------
Event Wait Analysis:
DIOR DIOW LIOR LIOW BIOR BIOW Log Lock LGEvnt LKEvnt
Time(ms) 6 7 0 2 5 0 3 10 0 368
System 0.0 0.1 0.0 0.0 1.6 0.0 0.0 0.0 1.5 9.4
User 39.1 0.0 0.0 0.3 55.4 0.3 0.0 0.0 0.0 0.2
----------------------------------------------------------------------
I/O and Transaction Rates/Sec:
DIOR....kb DIOW....kb LIOR....kb LIOW....kb BIOR BIOW
Txn

--------------------------------------------------------------------------------------
Computing Analysis:
Facility User System
CLF 0.1 0.0
DMF 0.0 0.0
OPF 1.2 0.0
PSF 1.2 0.0
QEF 1.8 0.0
SCF 0.2 0.0
--------------------------
Mutex Waits are less than 1%
----------------------------
Lock Waits are less than 1%
---------------------------
Event Wait Analysis:
DIOR DIOW LIOR LIOW BIOR BIOW Log Lock LGEvnt LKEvnt
Time(ms) 6 7 0 2 5 0 3 10 0 368
System 0.0 0.1 0.0 0.0 1.6 0.0 0.0 0.0 1.5 9.4
User 39.1 0.0 0.0 0.3 55.4 0.3 0.0 0.0 0.0 0.2
----------------------------------------------------------------------
I/O and Transaction Rates/Sec:
DIOR....kb DIOW....kb LIOR....kb LIOW....kb BIOR BIOW
Txn
Current 25 68 0 0 0 0 0 0 43
41 0
Peak 341 4292 643 5304 0 0 16 128 241
584 0
--------------------------------------------------------------------------
getrusage() measurements of this process since sampling started:
UTIME STIME MAXRSS IDRSS MINFLT MAJFLT NSWAP INBLOCK
OUBLOCK
325.821 123.820 0 0 0 205103 0 344854
95820
MSGSND MSGRCV NSIGS NVCSW NIVCSW
0 0 0 749721 4878
--------------------------------------------------------------------------
IIMONITOR> IIMONITOR> Session 0170C100:64 (itpprod )
BIOR 368805 BIOW 771522 DIOR 391912 DIOW 259 LIOR 1 LIOW 5191 Lock 1
LKEvnt 42 Log 7

Session 01FFD880:1506 (ingres ) BIOR 69 BIOW 190 DIOR
5 LIOW 1 Log 1
Session 020091A0:2842 (ingres ) BIOR 3 BIOW 2

On Feb 27, 2009, at 12:02 PM, jkeane (AT) revenue (DOT) ie wrote:

Well, it means that every time the sampler thread woke up (which
happens at short regular interavls, although I don't remember
the sampling frequency), it queried the state in the Ingres
session control block for all the threads. Every time it sampled
a Sort thread, the state was Computable -- which actually means
that it wasn't anything else, like BIO, DIO, etc.

This might mean:
- that the sampler saw a Sort thread only very infrequently, and
when it did, the thread just happened to be computable;
- that there is some sort of "resonance" between the sampler
and the Sort threads, such that the sort threads aren't in wait
states when the sampler wakes up (Very unlikely!)
- that the Sort threads aren't actually running, but are in some
short-term wait state that doesn't change the state code in the
session control block (also unlikely, implies the CL has overlooked
some sort of wait state);
- or that there really are Sort threads that are taking up a lot of
CPU time with very little waiting of any sort.

The first and last are the most likely. Given the low numbers under
the Computing Analysis section, I'm inclined to think it's the first;
meaning that very little time is spent sorting and the sampler just
happened to catch a fleeting glimpse of a sort thread in computable
state.

If the sort threads really are running a lot, at 100% cpu, it should be
easy to see from "show system sessions", and cpu usage from vmstat
or equivalent should be at least one cpu's worth at least over the
short term, a few seconds worth.

Karl