Re: parallel read and buffer cache

Hi Jonathan ,

Thanks for your mail .
Here is the out of gv$session_event :

INST_ID	EVENT	TOTAL_WAITS	TOTAL_TIMEOUTS	TIME_WAITED	AVERAGE_WAIT	TIME_WAITED_MICRO
1	db file parallel read	42195	0	167742	4	1677421151
1	db file scattered read	467432	0	766390	2	7663897841
1	db file sequential read	5877612	0	3274342	1	32743422283
1	db file single write	10726	0	1445	0	14454614
2	db file parallel read	23979	0	96507	4	965068448
2	db file parallel write	81540	0	69	0	692582
2	db file scattered read	723868	0	3398790	5	33987903548
2	db file sequential read	39227136	0	18177962	0	181779624427
2	db file single write	29	0	3	0	27671

(Which column is important total_Waits or time_waited?)

It seems to me that db file parallel reads are not as high as i expect comparing to other reads , so may be i need to increase by buffer pool .

select state,count(*) from x$bh where dbarfil <> 0 group by state  

and

select state,count(*) from x$bh group by state

returns same output:

this is node 1:

STATE	COUNT(*)
1	6412
2	25193
3	43

and this is node 2:

STATE	COUNT(*)
1	3895
2	27652
3	95
8	6

and same queries but this time i use gv$bh :

INST_ID	STATUS	COUNT(*)
1	cr	45
1	pi	1
1	scur	25189
1	xcur	6413
2	cr	97
2	pi	4
2	scur	27649
2	xcur	3898

I think gv$bh and x$bh are identical .

Based on these outputs is it possible to say that a larger buffer cache is better ?

Kind Regards ,
hope

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ch6ogh$1f5$1_at_hercules.btinternet.com>...
> 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 Fri Sep 03 2004 - 06:56:28 CDT