Re: Identify the sessions which using all tempspac
Question: is there a way to identify which sessions are consuming all of the temp dbspaces? Answer: no built-in onstat/query gives the full picture (user temp tables can be found, but sort/internal temp usage can't be tied to a session), and respondents urged voting on an IBM RFE (an earlier one with 17 votes had been declined). Workarounds offered: enable syssqltrace and query syssqltrace where sql_sortdisk > 0; a listing script at oninitgroup.com; and a posted sysmaster SQL (systabinfo/systabnames/sysdbspaces) that lists temp tables by size. No proper resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
No. Please vote on this request: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=77869 It's another attempt after this other one was declined: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=43877 This had 17 votes, and several points arguing how important this is, but it was not enough to convince IBM to put it in the roadmap. Regards. On Wed, Nov 4, 2015 at 11:32 AM, PRAVIN BANKAR <pravinebankar@gmail.com> wrote: > Hi, > > Is there any way (any query/command), to identify the sessions which are > trying to use all of the tempspaces? > > Thank you!!! > > Pravin Bankar > pravinebankar@gmail.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1140ec24baee790523be4f41
Done. Unfortunately one needs an IBM ID to vote on this stuff, and I believe many on this list lack (and do not want) one! Ugh ... -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, November 04, 2015 5:13 PM To: ids@iiug.org Subject: Re: Identify the sessions which using all temp.... [36003] No. Please vote on this request: http://www.ibm.com/developerworks/rfe/execute?use_case=viewR fe&CR_ID=77869 It's another attempt after this other one was declined: http://www.ibm.com/developerworks/rfe/execute?use_case=viewR fe&CR_ID=43877 This had 17 votes, and several points arguing how important this is, but it was not enough to convince IBM to put it in the roadmap. Regards. On Wed, Nov 4, 2015 at 11:32 AM, PRAVIN BANKAR <pravinebankar@gmail.com> wrote: > Hi, > > Is there any way (any query/command), to identify the sessions which are > trying to use all of the tempspaces? > > Thank you!!! > > Pravin Bankar > pravinebankar@gmail.com > > > > ************************************************************ ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1140ec24baee790523be4f41 ************************************************************ ******************* Forum Note: Use "Reply" to post a response in the discussion forum. -- *CONFIDENTIALITY NOTICE*: This email communication may contain private, confidential, or legally privileged information intended for the sole use of the designated and/or duly authorized recipient(s). If you are not the intended recipient or have received this email in error, please notify the sender immediately by email and permanently delete all copies of this email including all attachments without reading them. If you are the intended recipient, secure the contents in a manner that conforms to all applicable state and/or federal requirements related to privacy and confidentiality of such information.
Voted 'cause we need this feature urgently. Mit freundlichem Gruß Reinhard Habichtsberg Dienstleistungsverantwortlicher IT-Rechenzentrum Bereich Server-, Speicher- und Datenbanksysteme B-203 -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von Fernando Nunes Gesendet: Mittwoch, 4. November 2015 23:13 An: ids@iiug.org Betreff: Re: Identify the sessions which using all temp.... [36003] No. Please vote on this request: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=77869 It's another attempt after this other one was declined: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=43877 This had 17 votes, and several points arguing how important this is, but it was not enough to convince IBM to put it in the roadmap. Regards. On Wed, Nov 4, 2015 at 11:32 AM, PRAVIN BANKAR <pravinebankar@gmail.com> wrote: > Hi, > > Is there any way (any query/command), to identify the sessions which > are trying to use all of the tempspaces? > > Thank you!!! > > Pravin Bankar > pravinebankar@gmail.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1140ec24baee790523be4f41 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks all for comments. Fernando - voted... Thank you! Pravin Bankar pravinebankar@gmail.com
Thank you for pointing out this RFE. I have voted for it.
While I wouldn't say this is a top priority for our business, as a DBA I find
getting a complete picture of who is using temp space is impossible. User
created temp tables can be discovered easily but are only part of the picture.
Usually I look at this reactively when prompted by a dbspace is full message
from the alarmprogram and given the number of sessions running under the same
user id on our system, I can almost never diagnose it. With most other stuff I
find there is always something I can do or learn more to delve into what is
going on but in this area you can only get so far before you get stuck.
I blogged about this in March and made enquiries via IBM contacts:
https://informixdba.wordpress.com/2015/03/29/temporary-dbspaces/
Shortly afterwards, perhaps coincidentally, perhaps not, this appeared in the
May 2015 IIUG Insider:
A sort of answer
Once in a while, Support is asked to help determine: "What session is
generating all the temporary sort files?" There is no onstat command to answer
this, but it can be done in a couple steps:
Turn on syssqltrace. Make sure you capture enough trace lines. The value for
"Number of traces" in the output of 'onstat -g his' should be under the number
of traces you specified, while enabling trace. If 'onstat -g his' shows the
number is beyond the value you specified when enabling tracing, it could miss
capturing some of the queries that ran.
Identify queries that need disk sort space:
select sql_totaltime, sql_statementfrom syssqltrace where sql_sortdisk > 0
Done! Meanwhile: http://www.oninitgroup.com/listing-temp-dbspace-contents Regards, Doug Lawry
I've used this one for quite awhile. The core sql was borrowed long ago ...
there may be some differences in later engines. I get an alert when a
particular user is using a large amount of temp space and then start logging
the session, etc.
FILE=/tmp/tempusage.unl
rm $OFILE 2>/dev/null
dbaccess sysmaster <<EOSQL 2>/dev/null
set isolation dirty read;
unload to $OFILE
SELECT t2.owner [1,15],
t2.dbsname [1,15] AS database,
t2.tabname [1,30] AS table,
t3.name [1,15] 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
ORDER BY 6 DESC, 5 DESC
EOSQL
cat $OFILE | head -20| awk 'BEGIN {FS="|";format = "%-15s %-15s %-30s %-15s
%-15s %s\\
"
printf format, "Owner", "Database","Table","Dbspace","Life_Time","Size_KB\\
"}
{ printf format, $1, $2,$3,$4,$5,$6 }'