Re: Performance problems/System generated temp tables

Jeff said: > > 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? 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