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_at_naturaljoin.nl> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
> Behalf Of Deepak Sharma
> Sent: Wednesday, August 03, 2005 23:31
> To: oracle-l_at_freelists.org
> 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