Re: Temp tablespace monitoring

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 14 Jun 2005 22:10:14 +0200
Message-ID: <d8ndl3$ud8$02$1@news.t-online.com>

DA Morgan schrieb:
> jerry dev wrote:
>

>> Hi all,
>>    i am trying to monitor the temp tablespace for transactions.
>> db: oracle 9.2.0.3
>> temp : 1GB
>> platform: Win XP.
>>
>> thanks

>
>
> Sounds like a horrifying waste of time: Why?

Don't think so, as every kind of resource , temp space might be sometimes worth to monitor as well.
I've seen in some big DWH environments sometimes overfilling of temp just because users written bad queries resulting in a crossjoin ( not that stupid users, just huge aggregation in terms of lines of code plus human errors) , sometimes optimizer prefer to filter resultset of crossjoin as the cheapest plan ( mostly due to wrong statistics ) etc. Can get worse if you have tempfile autoxtend on... For my needs this query worked well enough...

select sum(a.mb) mb,a.segtype,b.username,b.osuser from
(select sum(blocks)*8/1024 mb,session_addr ,segtype from v$sort_usage
group by session_addr,segtype) a, v$session b where a.session_addr=b.saddr
group by b.username,b.osuser,a.segtype

>
> And why 9.2.0.3? You should be at 9.2.0.4 or 9.2.0.6.

Best regards

Maxim Received on Tue Jun 14 2005 - 15:10:14 CDT