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?

On 8/4/05, Lex de Haan <> 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
> ------------------------------------------------------------------
> -----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)


Other related posts: