Home » RDBMS Server » Server Administration » Deallocation of extent in Temporary Segments??
Deallocation of extent in Temporary Segments?? [message #219031] Mon, 12 February 2007 11:07 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
I was going through Oracle 9i Concepts Chapter 2 Data Blocks, Extents and Segment. In this there was a section describing of extent deallocation in Temporary Segments.

It says

When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A single sort allocates its
own temporary segment in the temporary tablespace of the user issuing the statement and then returns the extents to the tablespace.
Multiple sorts, however, can use sort segments in a temporary tablespace designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort, but remain available for other
multiple sorts.


In this section I did not understand the "Single Sort and Multiple Sort".

Can anyone please describe what it means??

Thanks in advance
Anand
Re: Deallocation of extent in Temporary Segments?? [message #219039 is a reply to message #219031] Mon, 12 February 2007 11:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
In Oracle many operations will use sort (like index build/rebuild and order by).
Oracle will try to do these in memory (as determined by SORT_AREA_SIZE).
This default allocation (instance wide and shared by all users/transactions) may not be enough in case like
* a huge sort (call it single sort by one user)
* multiple sorts (by multiple users/transactions trying to do some explicit/implicit sort).
In these cases sorting is done in sort extents created in sort segments in a temporary tablespace
(which is on the disk). These sort segments are not always released back and could
be used for other future sorts.
Re: Deallocation of extent in Temporary Segments?? [message #219177 is a reply to message #219039] Tue, 13 February 2007 09:02 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks Mahesh for the update.
I am still not clear what is meant by Multiple Sorts.
Single Sort - (Creation of index, order by, group by etc.,)
Multiple Sort???? - What kind of transactions involve multiple sorts?

Thank you once again.
Anand
Re: Deallocation of extent in Temporary Segments?? [message #219182 is a reply to message #219177] Tue, 13 February 2007 09:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Multiple transactions ( or even different users) doing sorts concurrently
or
Even in case of a certain joins like sort merge join.
In sort merge join, based on the sort key all the rows in table1 are first sorted, then all rows in table2 is sorted and both are merged.
Re: Deallocation of extent in Temporary Segments?? [message #219184 is a reply to message #219182] Tue, 13 February 2007 09:22 Go to previous message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks Mahesh,
Now I got and understood the concept. Really appreciate your time spent for this.


Regards,
Anand
Previous Topic: DB LINK Error Help!
Next Topic: Unzip
Goto Forum:
  


Current Time: Fri Dec 09 09:21:36 CST 2016

Total time taken to generate the page: 0.11791 seconds