user13080027 wrote:As soon as you think that what you have done is correct. There is no set size of the rows which should come under a commit. Whenever you find that your work doens't need anymore thoughts to do, commit it.
If we have got a process that makes inserts to a table of many many rows, for instance millions of rows, what considerations one should take in order to make a decision at what portions to issue commit?
"The online redo log is a set of two or more online redo log files that record all changes made to the database, including *uncommitted* and committed changes" - this sentence tells us that my changes to the database (in this particular case these changes are inserts) are going to be recorded on the redo log files.If you mean to say that your doing the transactions and (assuming two log groups with one file each) both the log files are full, your database would go in a hang stage. There is no chance that Oracle would let you work if you don't have the space available in the redo log files to maintain your changes.
But what if the free space on these files "ends" and at the same time I still have not issued commit?
My question is totally theoretical. I am not going to do these inserts since there many different efficient methods of collecting large volumes of data.If the size of the files is going to be too small, you would end up filling them much more faster thus making LGWR switching back and forth over the groups much faster. This surely would lead to a point where all the log files would be filled up and there won't be a space available for the next trandaction entries to be maintained.
I just would like to know what would happen if the size of the files appear to be too "small" for such transaction?
And in what way one could calculate the upper boundary providing that one knows the size in KB of each row (at least approximately)?You mean the size of the log files or some thing else?
Or the whole process is totally different logically?HTH
Ogan Ozdogan wrote:Ogan,
Any uncommited data stays in the redolog buffer, waiting to be written in to the online redologs. If database crashes during that period, any uncommited data will be rollbacked and commited data will be written to the physical datafiles during the instance startup recovery, thanks to the SMON.
Your uncommited data will stay in the redolog buffer and when redolog is full, checkpoints is triggered by the Oracle database and data in the redologs will be written to the physical datafiles.
Do not over commit, do not wait for a commit. The application's logic is up to you. Please also read articles about Oracle Transactions and Data Concurrency & Consistency. You can go to the http://tahiti.oracle.com for further information. I hope i did understand your question in a right way.
Hope it helps,
Thank you for additional information for the OP.
* Due to a commit in any session. * Every 3 seconds * When the redo log buffer is 1/3 full * When the redo log buffer contains 1MB of data
This is a quote from Oracle Database Administrator's Guide 11g1:From the above quote, you must understand that the onyl writing that would be done in this event would be from the LGWR to the log file. I didn't understand what's your 2nd line means?
Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.
My question is whether the only thing that happens in such case is that LGWR flushes to a redo log file or in second place after the flushing the change records from the log file are also written to the datafiles?
Or the process of writing the change records to the datafiles will start when and only when the corresponding transaction is committed?Writing to the datafiles would be done by DBWR which doesn't work when LGWR works. The writing of the DBWR is limited to certain events which are different from LGWR's working. At commit, only the LGWR writes. Please read this following section of the docs to know about that when each process does what?
user13080027 wrote:Yes, one needs to read this concept several times. Its really among one of the most trickiest topics in the database.
Yes, I read it. As a matter of fact I had to read it several times to digest it. But if it was simple I wouldn't have asked about it :)
Thanks for the reply!
Q:Guess which do you think would be quicker to write to disk?A:Redo Log files