Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Daniel Morgan and Syband Bakker

Re: Daniel Morgan and Syband Bakker

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 26 Jun 2002 13:01:32 -0700
Message-ID: <afd6is02u4r@drn.newsguy.com>


In article <afd1nl$bto$1_at_cronkite.cc.uga.edu>, jeff_at_work.com says...
>

[snip]

>
>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?
>

granted, yes. more correctly "the memory allocated for sorting is PGA memory"

>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 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.

>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."

jumped? I sorry you believe that:

....
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.
.......

is "jumping", that was not my intention. The problem I have is that many people do believe the sort_area_size stuff comes from the SGA (never does) and are afraid to increase it -- not realizing how it works.

>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.

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.

I believe the material below points that out. It says "sort area size" -- pga, "sort area retained" -- uga.

>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 
Received on Wed Jun 26 2002 - 15:01:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US