Re: limit temp usage before error or out of space condition?
From: Yong Huang <yong321_at_yahoo.com>
Date: Wed, 23 Oct 2013 07:46:51 -0700 (PDT)
Message-ID: <1382539611.70464.YahooMailNeo_at_web184803.mail.gq1.yahoo.com>
Josh,
We have this problem occasionally. Unfortunately I don't think Oracle has a way to limit one user or one session's usage of temp space so as to leave some space for others using the same temporary tablespace. You can have a script frequently checking v$tempseg_usage (or v$sort_usage) for blocks a session is using, and alert if it exceeds a threshold defined by you. Save the captured data in a table or file for later review, since you may have missed the critical moment because runaway SQLs came too fast. Once they're identified, assign the user(s) to a dedicated temporary tablespace. If the SQLs did run to completion (not failed), check v$sql_workarea for entries with high max_tempseg_size and last_tempseg_size and have the developers take a look.
Date: Wed, 23 Oct 2013 07:46:51 -0700 (PDT)
Message-ID: <1382539611.70464.YahooMailNeo_at_web184803.mail.gq1.yahoo.com>
Josh,
We have this problem occasionally. Unfortunately I don't think Oracle has a way to limit one user or one session's usage of temp space so as to leave some space for others using the same temporary tablespace. You can have a script frequently checking v$tempseg_usage (or v$sort_usage) for blocks a session is using, and alert if it exceeds a threshold defined by you. Save the captured data in a table or file for later review, since you may have missed the critical moment because runaway SQLs came too fast. Once they're identified, assign the user(s) to a dedicated temporary tablespace. If the SQLs did run to completion (not failed), check v$sql_workarea for entries with high max_tempseg_size and last_tempseg_size and have the developers take a look.
Yong Huang
> Does anyone have any ideas how to limit temp usage before the query at
hand
> fills up the temp tablespace and then suspends everyone else until
the condition
> is cleared? Is there a way to monitor queries that are
using too much temp and
> send an email? Maybe in OEM?
...
> I use resumable already. Just wanted to see if I could catch them before
they suspend.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 23 2013 - 16:46:51 CEST