Re: Sort Area Size

Thomas Kyte wrote:

> 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)
>
> a) how much UGA memory will be allocated at most for the sort
> b) how much PGA memory will be allocated at most for the sort
> c) 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
>
> a) 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.
> b) 1m -- at most, for the sort.
>
> c) 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

Just posted this here because I am getting tired of seeing my name as the subject of a post.

Sorry for any inconvenience. You may now continue.

Daniel Morgan Received on Thu Jun 27 2002 - 12:24:56 CDT