Tracking down excessive DBSPACETEMP i/o.

We have four locations running the same apps against the same schema on the same hardware. It's an order-entry and dispatching OLTP system. Two of our locations sometimes report intermittent database 'sluggishness' which is also reflected in heavy CPU usage and disk i/o. The buffer turnover ratio has also been high during these periods. I have finally discovered with onstat -D that the two locations that complain about performance have a huge number of reads/writes on the temp dbspace (almost 10 times as many reads/writes as the actual data dbspaces), while the sites that are performing well do not. How can I identify the activity on the temp dbspace and trace it back to a particular user session? -- Jeff jlar310 at yahoo

This problem might be resolved by setting PSORT_DBTEMP to perform sorts in the filesystem. There are open bugs related to the slowness of performing sorts in temp dbspaces. Regards Guy Jeff wrote: > We have four locations running the same apps against the same schema on > the same hardware. It's an order-entry and dispatching OLTP system. Two > of our locations sometimes report intermittent database 'sluggishness' > which is also reflected in heavy CPU usage and disk i/o. The buffer > turnover ratio has also been high during these periods. > > I have finally discovered with onstat -D that the two locations that > complain about performance have a huge number of reads/writes on the > temp dbspace (almost 10 times as many reads/writes as the actual data > dbspaces), while the sites that are performing well do not. > > How can I identify the activity on the temp dbspace and trace it back to > a particular user session? > > -- > Jeff > jlar310 at yahoo