gajanan, November 13, 2008 - 6:04 am UTC
I did go through the Jonathan Lewis document, on page 32 it shows huge sort(i think this will be multi-pass) it shows small sorts are done and then they are merged and then these bigger units are merged, is it possible( in some cases atleast), that these small units after being merged, needs another sort, before being merged?
Also in cases where the data needs to be in pga, which part of it does it reside, like (private sql area, sort area etc).
Also I, read the oracle documents for pga, but it does not give details of each component e.g. ( it will say "The run-time area, which is freed when the execution is terminated." but for what purpose it is used, when it is allocated etc is not given)
So can you please provide a link or document which has some extra information.
November 14, 2008 - 4:34 pm UTC
a multipass sort is just that, yes, can be multiple passes.
It resides in the sort workarea of the PGA.
The PGA is process memory that is managed by the OS as a heap (malloc()'ed for C programmers) or dynamically attached and detached using memmap() for some workareas (sort workareas using automatic memory management can do that if the OS permits). That is all - the PGA is just process memory, you don't and we don't have to assign a "name" to everything, it isn't managed like the SGA at all (which is a fixed size shared data structure, PGA memory is just allocated as needed on the fly)
You have access to everything you need already.
There are no "each component" of the PGA to study in such detail, it is just process memory - allocated as needed, freed when done...
(truth be told, if you want to understand it better - program in C for a couple of years.... Or assembler, if you know how to program in C, you know all about the PGA)
A reader, November 14, 2008 - 8:38 am UTC
Hi Tom,
In your example of a one-pass sort, after the first pass has created several chunks of sorted data, why (ie under what conditions) would Oracle not be able to "read a bit of each chunk", therby requiring another pass? Can you give a simple numeric example such as,
Chunk1: 2,4,5,8,13
Chunk2: 1,3,7,9,10
Chunk3: 6,11,12,14
Would Oracle now have to sort the 3 chunks in a 2nd pass before it can return 1,2,3,...,13,14? But if it can do that then why the need to break it up into chunks in the first place?
November 18, 2008 - 5:53 pm UTC
the condition would be:
a bit of each chunk - of EACH chunk - doesn't fit in the sort area size. We cannot merge unless and until we read each and every chunk.