FAQ
Folks

Qs 1 When using COMMIT_WRITE=BATCH,NOWAIT, After a database crash, if
the respective Application has the capability to check within the
database for

volume of transactions lost after complete recovery & re-play the same,
Can the COMMIT_WRITE=BATCH,NOWAIT feature be used in such a case?

This basically refers to periodic Batch nature of Transactions which run
with NO intermediate User intervention.

Qs 2 For a Process/SESSION consider that COMMIT_WRITE is set to
BATCH,NOWAIT.

If 2 Commits are issued sequentially (i.e. one after another), is there
a possibility that the Commit which was issued later may be written 1st
to the

hard Disk ? Personally I think the answer is "NO"

Qs 3 If multiple processes are running parallely & committing data with
a related logical inter-dependency between them while doing updates, can
the

same be maintained when using the Asynchronous commit feature? To
Explain this in simpler words:-

Assume for 1 process COMMIT_WRITE is set to BATCH,NOWAIT.

Assume at 10:00 this process issues a Commit after updating a field
value to

say "A".

Assume that Oracle decides to write it to redo logs on Disk at 10:02.

Question - If at 10:01 ANOTHER process reads(SELECTs) the same field
value,

will it get the UPDATED field Value of "A"

Qs 4 Is there any difference in the crash recovery mechanism in general
with the use of this feature when compared to not using the same, both
for Single instance & RAC setup?

Qs 5 Is there any difference in the rollback mechanism in general with
the use of this feature when compared to not using the same both for
single instance & RAC?

Qs 6 For COMMIT_WRITE=BATCH what are the general guidelines for data to
be batched & written to disk? Is there an outer-time line involved? Do
writes to disk necessarily happen at some periodicity?

Qs 7 If archivelog mode is enabled, is there any change in the WRITEs
mechanism to the archived redo log files?

Qs 8 Performance benefits seen when using this feature?

Qs 9 Any Good Links / Docs on this?

Thanks indeed

CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Search Discussions

  • Anjo Kolk at Sep 22, 2006 at 9:26 am
    I will try to answer (answers inline)

    Qs 1 When using COMMIT_WRITE=BATCH,NOWAIT, After a database crash, if the
    respective Application has the capability to check within the database for

    volume of transactions lost after complete recovery & re-play the same,
    Can the COMMIT_WRITE=BATCH,NOWAIT feature be used in such a case?

    This basically refers to periodic Batch nature of Transactions which run
    with NO intermediate User intervention.
    If the application can recover it self, yes why not.
    Qs 2 For a Process/SESSION consider that COMMIT_WRITE is set to
    BATCH,NOWAIT.
    If 2 Commits are issued sequentially (i.e. one after another), is there a
    possibility that the Commit which was issued later may be written 1st to the


    hard Disk ? Personally I think the answer is "NO"
    I assume that you mean redo here. The redo is ordered in the in the log
    buffer, and the LGWR keeps track of where it is in the log buffer. So it
    writes the log buffer sequentially (in cycles).
    Qs 3 If multiple processes are running parallely & committing data with a
    related logical inter-dependency between them while doing updates, can the

    same be maintained when using the Asynchronous commit feature? To Explain
    this in simpler words:-



    Assume for 1 process COMMIT_WRITE is set to BATCH,NOWAIT.

    Assume at 10:00 this process issues a Commit after updating a field value
    to

    say "A".

    Assume that Oracle decides to write it to redo logs on Disk at 10:02.



    Question - If at 10:01 ANOTHER process reads(SELECTs) the same field
    value,

    will it get the UPDATED field Value of "A"

    The redo is only used for recovery. You are confusing the SCN number. The
    current SCN and the snapshot scn (start of statement) dictate what you will
    see as commited or not.

    Qs 4 Is there any difference in the crash recovery mechanism in general
    with the use of this feature when compared to not using the same, both for
    Single instance & RAC setup?

    There is a (small) window of exposure. This goes back to your first
    question. Oracle can only recover what it has in the redo log. If that redo
    is missing, you can't re execute the transaction. Doesn't matter RAC or not.

    Qs 5 Is there any difference in the rollback mechanism in general with the
    use of this feature when compared to not using the same both for single
    instance & RAC?
    For normal rollback no, for rollback as part of instance recovery: you could
    be missing transactions.

    >

    Qs 6 For COMMIT_WRITE=BATCH what are the general guidelines for data to be
    batched & written to disk? Is there an outer-time line involved? Do writes
    to disk necessarily happen at some periodicity?
    Yes, there is a timeout feature for the LGWR. rougly once a second (haven't
    checked if that has changed lately)

    >

    Qs 7 If archivelog mode is enabled, is there any change in the WRITEs
    mechanism to the archived redo log files? No, why?
    Qs 8 Performance benefits seen when using this feature?
    Yes! Tremendous escpecially for these great 'batch' programs that do 'TX'
    followed by a COMMIT. All the log file sync latency will dissapear for that
    program/process.
    Qs 9 Any Good Links / Docs on this?
    >

    I read the 10.2 doc and that was pretty clear. I think that you are making
    it more complicated than it is. The issue is that redo is not always on disk
    for that session. That could mean that the session is logically corrupting
    the data, however there is only small chance. Backup the data before you
    run the program :)
    Thanks indeed


    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
    solely for the use of the addressee(s). If you are not the intended
    recipient, please notify the sender by e-mail and delete the original
    message. Further, you are not to copy, disclose, or distribute this e-mail
    or its contents to any other person and any such actions are unlawful. This
    e-mail may contain viruses. Infosys has taken every reasonable precaution to
    minimize this risk, but is not liable for any damage you may sustain as a
    result of any virus in this e-mail. You should carry out your own virus
    checks before opening the e-mail or attachment. Infosys reserves the right
    to monitor and review the content of all messages sent to or from this
    e-mail address. Messages sent to or from this e-mail address may be stored
    on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • VIVEK_SHARMA at Sep 22, 2006 at 9:42 am
    Anjo, Folks

    If you could possibly Clarify Anjo's reply below?

    Also I infer that Anjo reply means "YES" to my Question below (since an
    SCN would have been generated by the COMMIT at 10:00)

    Qs 3 If multiple processes are running parallely & committing data with
    a related logical inter-dependency between them while doing updates, can
    the

    same be maintained when using the Asynchronous commit feature? To
    Explain this in simpler words:-

    Assume for 1 process COMMIT_WRITE is set to BATCH,NOWAIT.

    Assume at 10:00 this process issues a Commit after updating a field
    value to say "A".

    Assume that Oracle decides to write it to redo logs on Disk at 10:02.

    Question - If at 10:01 ANOTHER process reads(SELECTs) the same field
    value, will it get the UPDATED field Value of "A"

    Anjo's Reply - The redo is only used for recovery. You are confusing the
    SCN number. The current SCN and the snapshot scn (start of statement)
    dictate what you will see as commited or not.

    Thanks indeed

    CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
  • Anjo Kolk at Sep 22, 2006 at 10:07 am
    The answer is YES. Nothing really to clarify. SCNs determine what you see in
    a transaction/select. Remember currently Oracle never goes back to the redo
    log file to read an SCn as part of the scenario that you describe. It
    doesn't even look in the redo log buffer. The only place that is inspected
    is the data block, the corresponding undo and the corresponding undo header
    (TX table).

    Anjo.
    On 9/22/06, VIVEK_SHARMA wrote:

    Anjo, Folks



    If you could possibly Clarify Anjo's reply below?



    Also I infer that Anjo reply means "YES" to my Question below (since an
    SCN would have been generated by the COMMIT at 10:00)



    Qs 3 If multiple processes are running parallely & committing data with a
    related logical inter-dependency between them while doing updates, can the

    same be maintained when using the Asynchronous commit feature? To Explain
    this in simpler words:-



    Assume for 1 process COMMIT_WRITE is set to BATCH,NOWAIT.

    Assume at 10:00 this process issues a Commit after updating a field value
    to say "A".

    Assume that Oracle decides to write it to redo logs on Disk at 10:02.



    Question - If at 10:01 ANOTHER process reads(SELECTs) the same field
    value, will it get the UPDATED field Value of "A"



    Anjo's Reply - The redo is only used for recovery. You are confusing the
    SCN number. The current SCN and the snapshot scn (start of statement)
    dictate what you will see as commited or not.



    Thanks indeed


    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
    solely for the use of the addressee(s). If you are not the intended
    recipient, please notify the sender by e-mail and delete the original
    message. Further, you are not to copy, disclose, or distribute this e-mail
    or its contents to any other person and any such actions are unlawful. This
    e-mail may contain viruses. Infosys has taken every reasonable precaution to
    minimize this risk, but is not liable for any damage you may sustain as a
    result of any virus in this e-mail. You should carry out your own virus
    checks before opening the e-mail or attachment. Infosys reserves the right
    to monitor and review the content of all messages sent to or from this
    e-mail address. Messages sent to or from this e-mail address may be stored
    on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • VIVEK_SHARMA at Sep 22, 2006 at 9:57 am
    Anjo, Folks

    Qs 1 When using COMMIT_WRITE=BATCH,NOWAIT, After a database crash, if
    the respective Application has the capability to check within the
    database for

    volume of transactions lost after complete recovery & re-play the same,
    Can the COMMIT_WRITE=BATCH,NOWAIT feature be used in such a case?

    This basically refers to periodic Batch nature of Transactions which run
    with NO intermediate User intervention.

    Anjo's reply - If the application can recover it self, yes why not.

    Seek the following Clarification by citing a particular example:-

    Our Banking Application runs the Interest Calculation batch Job for a
    SET of Accounts. If for any reason the Job gets aborted while underway,
    on re-starting the Application has the capability to check from within
    the Database & run the interest calculation for only those accounts of
    the set, whose calculation was still pending during the previous
    calculation run. Those accounts of the SET whose Interest Calculation
    was complete during the previous run are NOT subjected to re-run.

    In such a case can COMMIT_WRITE=BATCH, NOWAIT be deployed in PRODUCTION
    environment for the respective batch job?

    Thanks indeed

    CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
  • Anjo Kolk at Sep 22, 2006 at 10:44 am
    Does this batch job commit after every account or after a number of
    accounts? If it is a number of accounts, can you specify the number of
    accounts (commit frequency). If you, do that.

    If you want to play around with commit_write be prepared to take a backup :)

    Anjo.
    On 9/22/06, VIVEK_SHARMA wrote:

    Anjo, Folks



    Qs 1 When using COMMIT_WRITE=BATCH,NOWAIT, After a database crash, if the
    respective Application has the capability to check within the database for

    volume of transactions lost after complete recovery & re-play the same,
    Can the COMMIT_WRITE=BATCH,NOWAIT feature be used in such a case?

    This basically refers to periodic Batch nature of Transactions which run
    with NO intermediate User intervention.



    Anjo's reply - If the application can recover it self, yes why not.



    Seek the following Clarification by citing a particular example:-



    Our Banking Application runs the Interest Calculation batch Job for a SET
    of Accounts. If for any reason the Job gets aborted while underway, on
    re-starting the Application has the capability to check from within the
    Database & run the interest calculation for only those accounts of the set,
    whose calculation was still pending during the previous calculation run.
    Those accounts of the SET whose Interest Calculation was complete during the
    previous run are NOT subjected to re-run.



    In such a case can COMMIT_WRITE=BATCH, NOWAIT be deployed in PRODUCTION
    environment for the respective batch job?



    Thanks indeed


    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
    solely for the use of the addressee(s). If you are not the intended
    recipient, please notify the sender by e-mail and delete the original
    message. Further, you are not to copy, disclose, or distribute this e-mail
    or its contents to any other person and any such actions are unlawful. This
    e-mail may contain viruses. Infosys has taken every reasonable precaution to
    minimize this risk, but is not liable for any damage you may sustain as a
    result of any virus in this e-mail. You should carry out your own virus
    checks before opening the e-mail or attachment. Infosys reserves the right
    to monitor and review the content of all messages sent to or from this
    e-mail address. Messages sent to or from this e-mail address may be stored
    on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
    --
    Anjo Kolk
    Owner and Founder OraPerf Projects
    tel: +31-577-712000
    mob: +31-6-55340888

    --
    http://www.freelists.org/webpage/oracle-l
  • VIVEK_SHARMA at Sep 22, 2006 at 11:48 am
    YES Anjo - Commit is being issued after a number of Accounts i.e. 300
    A/cs by default & NOT after every A/c.

    This Number of Accounts is parameterizable within the application & may
    be changed by the User thereby changing the Commit frequency.

    Thanks so much

    From: akolk_at_oraperf.com On Behalf Of Anjo
    Kolk
    Sent: Friday, September 22, 2006 4:15 PM
    To: VIVEK_SHARMA
    Cc: oracle-l_at_freelists.org
    Subject: Re: Asynchronous Commit Feature of 10.2 - Basic Qs

    Does this batch job commit after every account or after a number of
    accounts? If it is a number of accounts, can you specify the number of
    accounts (commit frequency). If you, do that.

    If you want to play around with commit_write be prepared to take a
    backup :)

    Anjo.

    On 9/22/06, VIVEK_SHARMA wrote:

    Anjo, Folks

    Qs 1 When using COMMIT_WRITE=BATCH,NOWAIT, After a database crash, if
    the respective Application has the capability to check within the
    database for

    volume of transactions lost after complete recovery & re-play the same,
    Can the COMMIT_WRITE=BATCH,NOWAIT feature be used in such a case?

    This basically refers to periodic Batch nature of Transactions which run
    with NO intermediate User intervention.

    Anjo's reply - If the application can recover it self, yes why not.

    Seek the following Clarification by citing a particular example:-

    Our Banking Application runs the Interest Calculation batch Job for a
    SET of Accounts. If for any reason the Job gets aborted while underway,
    on re-starting the Application has the capability to check from within
    the Database & run the interest calculation for only those accounts of
    the set, whose calculation was still pending during the previous
    calculation run. Those accounts of the SET whose Interest Calculation
    was complete during the previous run are NOT subjected to re-run.

    In such a case can COMMIT_WRITE=BATCH, NOWAIT be deployed in PRODUCTION
    environment for the respective batch job?

    Thanks indeed

    CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 22, '06 at 8:10a
activeSep 22, '06 at 11:48a
posts7
users2
websiteoracle.com

2 users in discussion

VIVEK_SHARMA: 4 posts Anjo Kolk: 3 posts

People

Translate

site design / logo © 2022 Grokbase