OraFAQ Forum: Server Administration » Deallocation of extent in Temporary Segments??

Home » RDBMS Server » Server Administration » Deallocation of extent in Temporary Segments??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Deallocation of extent in Temporary Segments?? [message #219031]

Mon, 12 February 2007 11:07

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

Report message to a moderator

Re: Deallocation of extent in Temporary Segments?? [message #219039 is a reply to message #219031]

Mon, 12 February 2007 11:54

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: Deallocation of extent in Temporary Segments?? [message #219177 is a reply to message #219039]

Tue, 13 February 2007 09:02

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

Report message to a moderator

Re: Deallocation of extent in Temporary Segments?? [message #219182 is a reply to message #219177]

Tue, 13 February 2007 09:19

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: Deallocation of extent in Temporary Segments?? [message #219184 is a reply to message #219182]

Tue, 13 February 2007 09:22

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

Report message to a moderator

Previous Topic:	DB LINK Error Help!
Next Topic:	Unzip

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:35:35 CDT 2024