Re: Buffer Sort explanation

Hi Lex, 

   If Oracle determines that if a block will be accessed multiple
times by the _same_ SQL, then it moves it to PGA. If the same can be
accessed multiple times by _different_ SQL statements it ends up in
SGA?  Is there a cut off number for accessing the data block above
which Oracle places it to PGA?
   
Thanks.



On 8/4/05, Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx> wrote:
> a BUFFER SORT typically means that Oracle reads data blocks into private 
> memory,
> because the block will be accessed multiple times in the context of the SQL
> statement execution. in other words, Oracle sacrifies some extra memory to
> reduce the overhead of accessing blocks multiple times in shared memory. this
> has nothing to do with sorting ...
> 
> additions/corrections welcome,
> kind regards,
> 
> Lex.
> 
> ------------------------------------------------------------------
> Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
> ------------------------------------------------------------------
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Deepak Sharma
> Sent: Wednesday, August 03, 2005 23:31
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Buffer Sort explanation
> 
> What does the 'BUFFER SORT' step mean and how does it get calculated, say in
> below example? It is out of a tkprof output of a star transformation query.
> 
>    105      BITMAP MERGE
>  351549        BITMAP KEY ITERATION
> 127009880       BUFFER SORT
> 1607720         TABLE ACCESS FULL SYS_TEMP_4254956840
>  351549       BITMAP INDEX RANGE SCAN OBJ#(3441108)
> PARTITION: 1 177 (object id 3441108)
> 

-- 
Thanks,
Ram.
--
http://www.freelists.org/webpage/oracle-l

Other related posts: