I've got an OLTP system that has developed poor performance. I eventually
tracked it down to excessive DBSPACETEMP I/O by monitoring onstat -D. The pages
read/written to the temp space far exceed the I/O on our data spaces.

It's been suggested in a previous post (see: Tracking down excessive DBSPACETEMP
I/O) that I use the PSORT_DBTEMP environment variable to bypass DBSPACETEMP and
use the file system instead. I'm a little worried about that however. It seems
to me that it would be slower. I'll have to do some testing

Using the query from the FAQ on locating temp tables, I am seeing a lot of
tables named th_probe_ffffffffffffffff and th_build_ffffffffffffffff in database
HASHTEMP that persist much longer than I would think is required for most queries.

Any ideas what these tables are and why they are killing my performance?

--
Jeff
jlar310 at yahoo

Jeff wrote:
Well, sounds like you have OPTCOMPIND set to 2 and some joins which used
to be done by nested loop are now doing hash joins and overflowing to
disk.

Have you tried :

1. Setting OPTCOMPIND to 0.

2. update statistics "appropriately".

Just out of interest how many temp dbspaces do you have - 3 is good

Jeff said:
Probably temporary "tables" for hash joins.

PSORT_DBTEMP in conjunction with PSORT_NPROCS and PDQPRIORITY can make a
jaw-dropping difference on large sort operations -- but only when your
source tables are fragmented (partitioned). I managed to improve
performance on an index build for a 60GB table from 1 index in over 24
hours to 3 indexes in an hour, using PSORT_* and PDQ.

I'd be interested to know what sort of queries you are running and what
sizes the tables are. You may have an indexing issue here, apart from
everything else.

--

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
- Coluche

A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list

Build and probe are internal temporary tables used in hash joins
between
tables. These are normally used in DSS systems. If you have an OLTP
system then indexes are either missing or not being used.

In databases sysmaster query

select sid,isreads+bufreads, iswrites+isrewrites+bufwrites
from syssesprof
order by 2 desc;

Take the first sid and run

onstat -g ses <sid>

and that should tell you what the sessions is doing.