Re: parallel read and buffer cache

Hi Jonathan ,

Thanks for your mail.

First of all in order prevent any confusion , by saying buffer cache , i mean oracle buffer cache (db_Cache_size) not os buffer cache.

select count(*) from x$bh where dbarfil <> 0

select count(*) from x$bh

Both queries return 31648. What does this mean?

> Potentially correct - and arguably you should swing your
> memory towards buffering at the file system level, and
> maximising the benefit of in-memory sorting, hashing,
> messaging etc.
>

    I use ocfs on linux , and ocfs uses direct io , so does not use any os buffering . Just because of this i was thinking of decrasing the os buffer cache in a way since linux allocates whatever free memory it finds as os buffer and results paging at last , but your suggestions changed the situation. What do you think about this?

> Bear in mind, though, that not all parallel execution is
> going to be direct read tablescans and index full scans,
> especially if you have partitoned tables.

  I have partitoned tables , the example query uses a hash partitoned table.
As i understand you correctly , if a table is partitoned than parallel read does not use direct read but uses oracle buffer cache. (i am confused if it works like this)

Kind Regards.
tolga

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ch5e0m$s9b$1_at_hercules.btinternet.com>...
> Note in-lines
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated July 27th
>
>
>
>
> "utkanbir" <hopehope_123_at_yahoo.com> wrote in message
> news:f6c90ebe.0409010622.3ed5697d_at_posting.google.com...
> > Hi Gurus ,
> >
> > a select statement runs parallel bypasses the buffer cache and uses
> > pga instead .
> >
>
> Yes - though if you want to split hairs I think it is
> in the UGA which is, of course, inside the SGA
> of a PX slave.
>
>
> > if the above statement is correct , than a datawarehouse (like me)
> > which uses mostly parallel queries does not take any advantage of
> > having a large buffer cache , does it?
>
> Potentially correct - and arguably you should swing your
> memory towards buffering at the file system level, and
> maximising the benefit of in-memory sorting, hashing,
> messaging etc.
>
> Bear in mind, though, that not all parallel execution is
> going to be direct read tablescans and index full scans,
> especially if you have partitoned tables.
>
> There are other uses of the buffer cache that may also
> come into play with temporary segments.
>
> As a quick (and not entirely complete) test of how
> much of your buffer may be redundant, you can always
> check x$bh where state = 0. These are FREE buffers,
> which may be free because the blocks they have held are
> from dropped or truncated objects - but may be free
> because they have never been used.
>
> Add in the predicate dbarfil != 0 to identify the buffers
> that have never been used.
>
>
> >
> > is this how it works?
> >
> > Kind Regards,
> > tolga
Received on Thu Sep 02 2004 - 01:06:50 CDT