Re: question on SGA/PGA/UGA/SEP relationship

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 31 Jan 2001 06:01:41 GMT
Message-ID: <3a77a774.1998898273@nsw.nnrp.telstra.net>

Hi Wayne,

The sort extent pool (SEP) is an instance wide SGA data structure used to manage the extents of sort segments within temporary contents tablespaces. Space is allocated to sessions by SMON in response to 'sort segment request' messages. Foregrounds wait on an equivalently named wait event. There is a 'sort extent pool' latch to protect the data structure so that queries against V$SORT_SEGMENT and V$TEMP_EXTENT_POOL do not see influx data.

Sessions keep track of the extents that they have been allocated in a sort segment via 'sort segment handle' state objects. These and all other state objects reside in the SGA so that they can be read by PMON when cleaning up after dead processes.

During a disk sort, sessions track the use of space within their sort segment extents for particular sort runs using a sort space map. The sort space map is a small part of the sort area. The major parts of the sort area are the sort/read buffers and the write buffers.

Sort areas are part of the 'cursor work heap' for a cursor. The cursor work heap is part of the 'runtime memory' of the 'private SQL area' for a cursor. For DML statements that complete in a single call, the private SQL area is allocated in the CGA which is always a subheap of the PGA, even for calls from multi-threaded sessions. For SELECT statements, the private SQL area must be located in the UGA because it has to remain accessible to subsequent fetch calls.

For dedicated connections the UGA resides in the PGA. For multi-threaded connections the fixed UGA resides in the shared pool, and the variable part of the UGA (including private SQL areas) resides in the large pool if it exists, and otherwise in the shared pool.

I hope that clears up the confusion. There is so much that is wrong in those quotes that I'm not surprised that you were confused. You were no more confused than the author - only more aware of it!

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: Cantonese Boy <cantoneseboy_at_netscape.net>

Hi

        I got a little bit confuse about the relationship between SGA/PGA/UGA/SEP.         Form my book, it said:

        "The SGA also contains the sort extent pool (SEP) in non-MTS instances;in MTS instances, the user global area(UGA) contains the SEP"

	So, is that SEP inside non-MTS instances=dedicated server ?
	Where is it reside?

	But in another paragraph , it said:

	"In dedicated server environments, the shared pool can 

be smaller than the equivalent MTS environment. This happens because, in a dedicated server system, the sort areas used by each process are kept in the user's program global area(PGA), whereas in an MTS environment , the UGA extents used for sorting are tracked using the SEP, which is a part of the shared pool. Any time a process needs a temporary segment for sorting , it checks the SEP first before allocating a new segment."

	Is the "sort areas" here refer the SEP?
	If the first paragraph I quoted is correct, which means

the "SGA also contains the sort extent pool (SEP) in non-MTS instances", then it got some contradiction with the second paragraph.          

        My question is "IS UGA inside SGA?"

        Here is my unstanding about difference between dedicated server and MTS server doing sorting.

	Dedicated server:
		the PGA contain the sort area , so user process will use it's
		own PGA to do the sorting first, if not enough use temporary
segement.
	MTS server:
		all the prcoess share the UGA which contain the SEP(I don't know
if it
		is the only existing sort area for all process) and use it to do

the
sorting.

Please correct me if I'm wrong.

Thanks

W Received on Wed Jan 31 2001 - 00:01:41 CST