Re: Daniel Morgan and Syband Bakker

In article <afclel01ibp_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com> wrote:
>In article <afcikb$mqb$1_at_cronkite.cc.uga.edu>, jeff_at_work.com says...
>>
>>In article <afa8sq027mk_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com>
> wrote:
>>
>>>Nope, sort area size is always PGA memory -- sort area retained.... now
that
>>>might be in the UGA which is in the SGA when using shared server.....
>>>
>>>sort area = PGA always.
>>
>>That's semantics on Oracle's part then. They've certainly made the
>>distinction between PGA and UGA in their training classes.
>
>No semantics here. There is a huge difference between them -- what I said
> never
>would lead one to believe otherwise.

I fail to see the "huge"-ness of the difference... but, then again, I'm not sure to what exactly you're referring to as "them" either. ;-)

>It is that the UGA is part of the PGA in dedicated server.
>The UGA is part of the SGA in shared (MTS) server.

If I stated or inferred that the UGA wasn't a part of the PGA for a dedicated server connection, then my mistake. I didn't mean to, honest. :-)

My assertion is that:
<statement>
The sort area, at least in part, is stored in the UGA, MTS or no, and that the UGA is stored in the SGA with a shared server connection. </statement>

Of course, you're disagreeing that the sort area is stored in the UGA... at least, as it pertains to the sort area that's effected by the sort_area_size parameter? "sort area = PGA always."

>and most importantly for this question -- the sort_area_size is PGA memory.

If you want to pick apart statements, let's start here. Sort_area_size is an initialization parameter, not a memory structure. It limits the maximum size of the sort area, no?

Next if you'll go back to Sybrand's statement, he makes this statement: "... if you are not running dedicated server the sort_area_siz is NOT in the SGA." Now, apparently this didn't bother you, even though it infers that the SORT_AREA_SIZE could be "in the SGA" if you ARE "running dedicated server."

I said: "If you ARE using dedicated server processes, the sort area is in the PGA and not the SGA."

You jumped all over this statement, adamant that it is "factually inaccurate." I won't argue this since I'm not an Oracle employee who should know better; but if it is, so are Oracle's own documentation and training materials "factually inaccurate."

Check the links:

http://technet.oracle.com/doc/server.815/a67790/ch1.htm

"SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed.

Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time."

"SORT_AREA_RETAINED_SIZE specifies the maximum amount, in bytes, of User Global Area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer which is used to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space. Multiple sort spaces of this size may be allocated for each query. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort keeps its own memory area, as specified by SORT_AREA_RETAINED_SIZE. If the multi-threaded server is used, allocation is to the SGA until the value in SORT_AREA_RETAINED_SIZE is reached, the difference between SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE is allocated to the PGA."

--

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.
817/a76965/c06memor.htm#9124

--

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.
817/a76965/c06memor.htm#8815

"Sort Areas 
Sorting requires space in memory. Portions of memory in which Oracle sorts 
data are called sort areas. Sort areas use memory from the PGA of the Oracle 
server process that performs the sort on behalf of the user process. However, 
a part of the sort area (up to SORT_AREA_RETAINED_SIZE) exists in the runtime 
area of the process's private SQL area. For SELECT statements this memory in 
the private SQL area comes from different places depending on the connection 
configuration: 

From the PGA for connections through a dedicated server 

From the SGA for connections through the multi-threaded server. 

A sort area can grow to accommodate the amount of data to be sorted but is 
limited by the value of the initialization parameter SORT_AREA_SIZE. The 
default value, expressed in bytes, is operating system specific."

--

Enterprise DBA Part 2: Performance & Tuning, Vol 1, ch. 10, p. 7:

"Sort Area and Parameters
The sort space is in:
- The PGA for dedicated server connections
- The shared pool for MTS connections"


If "sort area = PGA always," then Oracle needs to change/delete their 
documentation and refund their students, because they've stated pretty 
clearly, IMHO anyway, that the sort area (or part of it, at least) is in the 
UGA.




> PGA stands for Process global area, it is always PROCESS memory, not SGA 

memory.

As in the above link, Oracle documents PGA as "Program Global Area,"  
"sometimes called a 'Process Global Area.'"  Though, for what it does, that 
certainly makes more sense.

Please note that I never said nor inferred (to my recollection, at least) that 
the PGA was ever in the SGA.




>If you have my book -- I actually use sort_area_size and

> sort_area_retained_size

>to demonstrate this behavior with lots of examples


I don't have your book, but if you know of any online resources on the 
subject, I'd like to see them.