-
Temp tablespace constantly blowing out
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.
-
Code:
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;
-
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
anandkl
-
Re: Temp tablespace constantly blowing out
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.
Reddy,Sam
-
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
I remember when this place was cool.
-
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
-
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?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
-
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
anandkl
-
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?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|