RE: Tracking down excessive DBSPACETEMP i/o.
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
> -----Original Message-----
> From: owner-informix-list@iiug.org [SMTP:owner-informix-list@iiug.org]
> On Behalf Of Jeff
> Sent: Thursday, August 18, 2005 3:36 PM
> To: informix-list@iiug.org
> Subject: 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
sending to informix-list