Re: _wait_for_sync , dirty buffer flushing and direct reads in parallel

Hi!

It seems that my post of performance gain by setting the parameter has got lost somewhere.
Anyway, a step in Oracle Apps upgrade process, which involved running about 30000 scripts (probably more than 100000 DDLs and commits in it), ran about 3-4 hours, while without this optimization, it ran for 8 hours.

This helped to "speed up" commits greatly. But with small number of large transactions it wouldn't help - for that you'd have to check out the _disable_logging parameter...

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 20, 2003 5:30 PM in parallel

> Hi!
>
> Yup, I was bold enough to use this parameter during production upgrade
only because it worked well in several tests and simulations.
>
> Cheers,
> Tanel.
>
>
> > Well,
> >
> > some disk writes need to wait for the LGWR to flush the corresponding
> > redo
> > to disk. So now you can have a situation that the blocks that are
> > dirty are
> > on disk (without a commited transaction) but the redo is not yet. So
> > if you
> > crash in that period, you can't recover.
> >
> > Anjo.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, November 20, 2003 2:59 PM
> > parallel
> >
> >
> > > Anjo,
> > >
> > > I also thought it affects only lgwr sync, but Jonathan Lewis once
> > told
> > that it affects any disk writes...
> > >
> > > If it affects only lgwr, then great, I can make Apps upgrades,
> > which do
> > really lots of DDLs and small transactions, quite much faster that
> > way...
> > >
> > > Thank you,
> > > Tanel.
> > >
> > >
> > > > _wait_for_sync basically meant that a session is waiting for the
> > sync
> > > > of the
> > > > redo by the lgwr. Normally the redo log writer writes to disk and
> > then
> > > > notifies the session that the transaction is completed. By setting
> > > > this to
> > > > false, you no longer wait for the redo to go to disk.
> > > >
> > > > That has no impact on your situation.
> > > >
> > > > Anjo.
> > > >
> > > > ----- Original Message -----
> > > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > > Sent: Wednesday, November 19, 2003 11:20 PM
> > > > query
> > > >
> > > >
> > > > > Hi!
> > > > >
> > > > > I've sometimes used setting _wait_for_syncúlse during Apps
> > upgrade
> > > > > projects, to upgrade performance. (As long as your database
> > doesn't
> > > > crash
> > > > > during the parameter is set to false, no problems should occur).
> > > > >
> > > > > I just started wondering, what would be the case if a parallel
> > query
> > > > starts
> > > > > during someone is modifying data...
> > > > >
> > > > > As I understand, when doing parallel query:
> > > > > 1) the dirty blocks which are supposed to be read by PQ in
> > direct
> > > > mode,
> > > > are
> > > > > flushed to disk
> > > > > 2) PQ reads the blocks in direct mode
> > > > >
> > > > > But when _wait_for_sync is set, the writes get acknowledged
> > > > immediately
> > > > (or
> > > > > acknowledgement is not waited for). Could this result in the
> > > > unlikely
> > > > > situation, that PQ issues the flush command to dirty buffers and
> > > > starts to
> > > > > read them, but actually reads the old images of the blocks,
> > since it
> > > > thinks
> > > > > the write has already occurred?
> > > > >
> > > > > (actually, this doesn't touch only PQ, it's possible to have
> > direct
> > > > reads
> > > > to
> > > > > PGA in serial mode too...)
> > > > >
> > > > > Tanel.
> > > > >
> > > > >
> > > > > --
> > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > > --
> > > > > Author: Tanel Poder
> > > > > INET: tanel.poder.003_at_mail.ee
> > > > >
> > > > > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > > > > San Diego, California -- Mailing list and web hosting
> > > > services
> > > > >
> > > >
> > ---------------------------------------------------------------------
> > > > > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > > (or the name of mailing list you want to be removed from). You
> > may
> > > > > also send the HELP command for other information (like
> > subscribing).
> > > > >
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: Anjo Kolk
> > > > INET: anjo_at_oraperf.com
> > > >
> > > > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting
> > services
> > > >
> > ---------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> > in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed from). You
> > may
> > > > also send the HELP command for other information (like
> > subscribing).
> > > >
> > > >
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Anjo Kolk
> > INET: anjo_at_oraperf.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).