Temp dbspace usage
Amit Patel asked how to find which session or query uses the most temp dbspace (94% used of 32 GB). Mike Walker suggested summing nptotal in sysmaster:sysptnhdr grouped by sid in later 12.x versions. Kirit Rana supplied a sysmaster query on systabinfo and systabnames; Paul Watson and Chaz Hsieh fixed a typo and an error 9635. Carlton Doe hinted 14.10.xC6 may make this easier.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello everyone, Hope all are safe and healthy in this pandemic. How can I check which query/session or activity is using more Temp dbspace. We have total 32 GB temp dbspace (16 temp dbspaces with 2 gb chunk each). Currently gets warning message for Temporary dbspace is 94% used , So willing to dig what type of SQL/session occupying more TEMP DBSPACE. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------ #Informix
In the later versions of Informix 12, a "sid" column was added to sysmaster:sysptnhdr. If you have this, then this is the easiest way to tie temp tables to session IDs. You can use the sum the nptotal column of sysptnhdr, and group by sid to find the sessions using the most temp space. ------------------------------ Mike Walker xDB Systems, Inc ------------------------------
Amit,.
Run this against the
sysmaster
database...
SELECT q1.*,
(100 * size_kb / dbs_size) :: DECIMAL(5,2) AS percent
FROM
(
SELECT
t2.owner [1,8],
t2.dbsname [1,18] AS database,
t2.tabname [1,22] AS table,
t3,name [1,10] AS dbspace,
(CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time,
(ti_nptotal * ti_pagesize/1024) :: INT AS size_kb
FROM
systabinfo AS t1,
systabnames AS t2,
sysdbspaces AS t3
WHERE t2.partnum = ti_partnum
AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
AND TRUNC(MOD(ti_flags,256)/16) > 0
) AS q1,
(
SELECT name AS dbspace,SUM(chksize * d1.pagesize/1024) AS dbs_size
FROM
syschunks AS d1,
sysdbspaces AS d2
WHERE d1.dbsnum = d2.dbsnum
GROUP BY 1
) AS q2
WHERE q1.dbspace = q2.dbspace
ORDER BY 6 DESC, 5 DESC;
...it will tell you which objects are in your temporary dbspace(s) and the percentage of the temporary dspace(s) being consumed by each object contained therein. It might even be one of Art's or Lester's scripts.
------------------------------
Kirit Rana
------------------------------
If you get
9635: An attempt has failed to convert an opaque type into another type without a cast function.Just remove the order by
just replace Line 9 : t3 , name to t3 . name and runs fine. ------------------------------ Chaz Hsieh ------------------------------
Multiple ways to fix :-) Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Copy and paste error, but yes as Paul states; there are many ways to skin a cat. ------------------------------ Kirit Rana ------------------------------
Glad we could help :-) https://www.oninitgroup.com/faq-items/dbspaces-how-to-list-temp-dbspace-contents ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Or http://www.informixfaq.com/wiki/doku.php/wiki:sysmaster Very old site now
Just an FYI -- in Informix 14.10.xC6 there ** might be ** something that makes this very easy to accomplish. :) :) :) :) There are ways right now using brute force SQL operations to get temp table statistics but who knows what the very near future might bring . . . . . . . . . . . . . ------------------------------ Carlton Doe ------------------------------
Hey Doug So that's where I got the script from. I knew it came from somewhere reputable. ------------------------------ Kirit Rana ------------------------------