Performance problems/System generated temp tables

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: > 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 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 :)

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.