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:

This should narrow it down to a specific user:

SELECT n.dbsname AS database,
n.owner AS owner,
n.tabname AS temp_tabname,
case
when BITVAL(i.ti_flags, "0x0020") = 1
then "System created temp table"
when BITVAL(i.ti_flags, "0x0040") = 1
then "User created temp table"
when BITVAL(i.ti_flags, "0x4000") = 1
then "Special function temp table"
end AS temp_type,
COUNT(*) AS num_fragments,
SUM(i.ti_nptotal) AS total_pages,
SUM(i.ti_nrows) AS total_rows
FROM systabnames n,
systabinfo i
WHERE (BITVAL(i.ti_flags, "0x0020") = 1
OR BITVAL(i.ti_flags, "0x0040") = 1)
AND i.ti_partnum = n.partnum
GROUP BY 1, 2, 3, 4;


Bill