Printable View
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
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.Quote:
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.
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?Quote:
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
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!!
:D
MH
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
Can u teach me how COMMIT is related to SORTING thats' taking place in TEMP TS? :confused:Quote:
Originally posted by Mr.Hanky
Tech the damn developers to use a commit in their SQL, or just shoot them!!
MH
That's easy to explain: it won't solve the problem, therefore Mr H. will have a pretext for shooting them :)Quote:
Originally posted by abhaysk
Can u teach me how COMMIT is related to SORTING thats' taking place in TEMP TS? :confused:
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
Yeah thas right, then whom shud we shoot now? :DQuote:
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 :)
My bad, I always mix up TEMP with redo (undo). Shoot em anyway.
And abhaysk, thanks for your "contribution" :rolleyes:
MH
I used to have a 200 GB sort space but then it was DW and I had to rollup some really big fact tables. Considered doing it with some tool before loading into Oracle but continued with Oracle sorts which I found no way to control its sort algorithm.
I would set that sessions ram sort area size to 1GB before and set it back aftwards, sort_area_retained_size is pretty important.
For smaller ones where you are not the one using it, you need to write a little monitor program that uses the guys script above or something appropriate and log who is using how much when. Find out exactly what is going on in there all day.
hmm I dont understand why people set 1gb for sort_area_size when over 32mb there is no extra benefit at all
if using 9i, use PGA_AGREGATE_SIZE. Takes care of lot of issues.Quote:
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
and why is that? Allowing the job to use as much ram as possible seems to make sense to me, perhaps it cannot use both ram and disk and since it was forced to disk but I would think it would at least help the process somewhere or Oracle needs to improve their sort process.Quote:
Originally posted by pando
hmm I dont understand why people set 1gb for sort_area_size when over 32mb there is no extra benefit at all
hi,
somewhere i read this formula for temp extents.
somebody can validate this.
the extent size = (x * sort_area_size) + ( 1 data_block)
x * sort_area_size - multiple of sort_area_size will not
cause fragmentation.
+1 data_block is meant for header.
if temp is locally managed it is better.
because over 32mb does no extra benefit, becnhmarked, tested and put in productionQuote:
Originally posted by BJE_DBA
and why is that? Allowing the job to use as much ram as possible seems to make sense to me, perhaps it cannot use both ram and disk and since it was forced to disk but I would think it would at least help the process somewhere or Oracle needs to improve their sort process.
Well my ETL automatation was rolling up all kinds of fact tables and dimensions, building indexes etc and it definately was faster or I would not have put it into production..Quote:
Originally posted by pando
because over 32mb does no extra benefit, becnhmarked, tested and put in production
how much faster, that´s the question
if using 32mb takes 30 minutes to build and index and 1gb takes a couple of minutes less I am not sure that´s worthy
Dont recall the details, just that it was important and I put it into the automation, there were hundreds of indexes. This was 'like' materialized views in that we had a fact table and dim tables down to the billing telephone number ( massive ) then repeated as they were rolled up 14 levels, at some point everything was sorting in memory, but the most of the those levels and all the lower levels were forced to disk do to the size. The app would choose the highest possible level that would answer the question in order to work on the smallest amount of data.Quote:
Originally posted by pando
how much faster, that´s the question
if using 32mb takes 30 minutes to build and index and 1gb takes a couple of minutes less I am not sure that´s worthy
It was always the 'work thru the night get it loaded as fast as you can'. Could have been frustration or wishful thinking but I seem to recall it shaved hours off of a 36 hour process.