Temp tablespace constantly blowing out

[rgaskell]

We have a 2GB temp tablespace and I'm trying to find out what user is running the offending sort. Are there any scripts out there to find what this out.? We have added more space to the tablespace to 4GB and it blew out again. We have a 2mb sort area size and 2mb retained size.
Initial and next on the temp tablespace is 2mb.

any help would be grateful.

[Thomasps]

col sid format 999999
col tablespace format a10
col username format a25
col noexts format 9999 head EXTS
col proginfo format a30 trunc
col mbused format 999,999.90
set verify off
select s.sid,s.serial#,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
u.segtype,
s.module || ' - ' || s.program proginfo
from v$sort_usage u, v$session s, v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by sid,serial#,sql_hash_value,sqlhash,s.username,tablespace,
u.segtype,
s.module || ' - ' || s.program
order by 6;

[anandkl]

And also tell all oracle users to set their session sort_area_size=32MB beforing they executed sql's that do heavy sorting operation as this would reduce the temp tablespace usage and imporve the I/O

regards
anandkl

[sreddy]

Originally posted by rgaskell
We have a 2GB temp tablespace and I'm trying to find out what user is running the offending sort. Are there any scripts out there to find what this out.? We have added more space to the tablespace to 4GB and it blew out again. We have a 2mb sort area size and 2mb retained size.
Initial and next on the temp tablespace is 2mb.

any help would be grateful.

I'm also experienceing the same problem on 8i(8173)/AIX, and found that its bug. Not sure whats your platform and version of oracle.

[Mr.Hanky]

Originally posted by anandkl
And also tell all oracle users to set their session sort_area_size=32MB beforing they executed sql's that do heavy sorting operation as this would reduce the temp tablespace usage and imporve the I/O

regards
anandkl

Correct me if I am wrong but isn't this a really bad idea? I would think that if every user used 32MB for sorts it would wreak havok with you server RAM and/or SGA, no?


Also, does the transaction fail? Often TEMP will look to be 100% used but it is recycled. Your "used" percent will not free up until you re-boot.

Tech the damn developers to use a commit in their SQL, or just shoot them!!



MH

[tamilselvan]

rgaskell,

As long as your end users are NOT making any complaint, you are ok.
The "TEMP Tablespace" blow out problem is on the every oracle instance. Even if you add another 20 GB space, you will still find it again becuase of the SQL statement executed by the application that does cartesian product of 2 big tables.

You need to talk to the developers/end users to correct the SQL statement.

Tamil

[abhaysk]

Originally posted by Mr.Hanky
Tech the damn developers to use a commit in their SQL, or just shoot them!!

MH

Can u teach me how COMMIT is related to SORTING thats' taking place in TEMP TS?

[DaPi]

Originally posted by abhaysk
Can u teach me how COMMIT is related to SORTING thats' taking place in TEMP TS?

That's easy to explain: it won't solve the problem, therefore Mr H. will have a pretext for shooting them

[anandkl]

Mr.Hanky,
I dont think all the users would be using the full 32 MB all the time,So i most of the time recommend my users to use this only when they are runing heavy SQL with sorting operations.

regards
anandkl

[abhaysk]

Originally posted by DaPi
That's easy to explain: it won't solve the problem, therefore Mr H. will have a pretext for shooting them

Yeah thas right, then whom shud we shoot now?