Re: Daniel Morgan and Syband Bakker

In article <aff1pp$2t5$1_at_cronkite.cc.uga.edu>, jeff_at_work.com says...
>
>In article <afd6is02u4r_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com> wrote:
>
>>I didn't read it that way -- I read it as "Contrary to popular mythology -- the
>>sort area is NOT in the SGA when running MTS". You could read it other ways,
>>but as written, it is not inaccurate.
>
>Interpretation is half the battle, eh?
>
>As for the "popular mythology," it is mythology that Oracle has created
>and taught then.
>
>

fine, tell me then, if

sort_area_size = 1m
sort_area_retained_size = 64k

and you have MTS (shared server)

1. how much UGA memory will be allocated at most for the sort
2. how much PGA memory will be allocated at most for the sort
3. will the contents of the PGA memory be MOVED to the UGA (since PGA is in the process and UGA is in the SGA)

Answers

1. 64k -- for the stuff that is retained after the sort. at most, could be 0 if this was an internal sort and didn't need to be fetched by the client.
2. 1m -- at most, for the sort.
3. yes, since the sort actually takes place in the PROCESS (shared server) but the results need to be in the SGA (for the client to fetch from)

That is my point, nothing more nothing less. Yes, if differs from your reading, your interpretation but is valid none the less.

>
>>>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."
>>>
>>
>>that is NOT what I am disagreeing with. The part I pointed out:
>>
>>>Sybrand, if you are NOT "running dedicated server" (you're running MTS?) and
>>>using shared server processes, then the sort area is in the UGA, which IS in
>> >the SGA.
>
>Uh, that's EXACTLY what you were disagreeing with.
>
>
>
>>That is wrong. The sort area is always PGA memory, as the links you refer us to
>>below state, it is the retained portion that is in the UGA (session) memory.
>
>First off, reading the links below COMPLETELY, it does not state that!
>Re-read the "sort areas" section. It explicitly states that part of the sort
>area comes from the UGA. Re-read the explanations of SORT_AREA_SIZE and
>SORT_AREA_RETAINED_SIZE. It says:
>
>"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."
>
>"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."
>
>It does NOT say that memory is created in PGA and then copied over to the UGA.
>Again, it explicitly cites the UGA. Moreover, unless specifically set,
>SORT_AREA_RETAINED_SIZE *defaults* to SORT_AREA_SIZE. That means, according
>to the above, that the WHOLE sort area is retained in the UGA if you don't set
>the SORT_AREA_RETAINED_SIZE.
>
>Last, but not least, Oracle's own training material, as cited below,
>EXPLICITLY STATES--not infers, no shades of grey--STATES that the "sort space"
>is in the UGA on a MTS-shared server connection.
>
>
>>I believe the material below points that out. It says "sort area size" -- pga,
>>"sort area retained" -- uga.
>
>Bull. It says what it says, and it DOES NOT say that.
>
>"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."
>
>Memory is allocated. Memory is released. Not allocated here and then moved
>here... or allocated here and also over here.
>
>
>>It is that the sort is done in the PGA and upon completion -- the amount
>>specified by the sort_area_retained size may -- if need be -- be allocated in
>>the UGA. sort_area_size -- never affects the SGA. sort_area_retained_size
>>does.
>>
>>that is what I tried to point out. Sorry you feel I'm nit picking or
>>whatnot. I'll just go away now.
>
>Please don't. The point I'm making here is that you're arguing against
>Oracle's documentation. If it's wrong, then say so. If it's mythology, then
>Oracle's responsible for it, and if you know better, then by all means correct
>THEM. Just don't chalk this up as some newbie's mis-interpretations of
>well-documented facts. If I'm wrong in my statements, I believe it's because
>Oracle's misled me... and it's very important to me to know what is correct
>when it comes to this stuff.
>
>
>
>>>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.
>>>
>>
>>It is that the sort is done in the PGA and upon completion -- the amount
>>specified by the sort_area_retained size may -- if need be -- be allocated in
>>the UGA. sort_area_size -- never affects the SGA. sort_area_retained_size --
>>does.
>>
>>that is what I tried to point out. Sorry you feel I'm nit picking or whatnot.
>>I'll just go away now.
>>
>>
>>
>>
>>>
>>>> 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.
>>>
>>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp