Notes in-line.
--
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.0409012206.30d351_at_posting.google.com...
> 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.
>
Yes - that was my interpretation of your question
> select count(*) from x$bh where dbarfil <> 0
>
> select count(*) from x$bh
>
> Both queries return 31648. What does this mean?
>
Every buffer in the buffer cache has, at some time,
held a copy of a block from the database. (Though
you don't report state = 0, so some buffers may have
been invalidated at some point and now be free).
This means you are using all your buffer cache - although it is
possible that part of that use is simply to allow excessive copies
of populate blocks to accumulate. It's probably not an issue
if you are running lots of direct path parallel queries, but you
can check for excessive copies by
select dbarfil, dbablk, count(*)
from x$bh
group by dbarfil, dbablk
having count(*) > 6;
> > 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?
>
Sounds like a flaw in Linux if it results in excessive paging.
A system with a dynamic filesystem cache should acquire
memory for caching only if it is available, and should release
it if memory comes under pressure, NOT cause non-cache
activity into paging.
Be that as it may - if most of your reads are direct reads
dues to parallel tablescans and parallel fast full scans, you
MAY be able to do something better with your memory
than put it into the Oracle buffer cache.
> > 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)
>
Direct reads are used for parallel tablescans and parallel index fast
full scans. In the absence of partitioning, a parallel query will be
parallel because it has done at least one of these.
However -
a) a parallel query can drive off parallel tablescan, but for each
row that a slave acquires, it can index into a second table using
normal reads .. so not all reads in a parallel query need be direct.
b) a parallel query can START with something like an indexed
access into a partitioned table, doing normal reads, because each
slave handles one partition at a time, so will not collide with another
slave.
>
> 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 - 04:18:09 CDT
