Note 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.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 Wed Sep 01 2004 - 16:12:54 CDT
