Re: Asynchronous Commit Feature of 10.2 - Basic Qs

  • From: "Anjo Kolk" <anjo.kolk@xxxxxxxxxxx>
  • To: VIVEK_SHARMA@xxxxxxxxxxx
  • Date: Fri, 22 Sep 2006 11:26:51 +0200

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

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

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

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

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

Other related posts: