What happened when Oracle commit

1. Understand When logws write redo logs into the redo log file in the redo log buffer to understand commit;

• Write when redo log buffer 1/3 is full
• Write at commit
• When a log switch occurs, write
• Write when the generated redo is 1 MB
• When DBWN is to be written, write redo first.
• Write every 3 seconds
  

That is to say, redo writes log files in a relatively continuous manner. Therefore, no matter how many redo records a transaction generates, the work done in commit is similar, because the redo logs of large departments have been written into log files.

2. What happened during commit?

• Write the SCN number to the log file
• LGWN writes the remaining redo to the log file
• All locks are released
• To clear blocks. Block Clearing refers to clearing the transaction information stored in this block.

Therefore, in fact, there is very little work done each time a commit is made. The biggest task is to write the redo into the log file, but the redo has been written to the log file by many departments. But this does not mean that, each time you modify a block, you must go to commit to increase competition for log files (log files are a shared structure ), it also adds competition for latches (protecting access to the shared structure ). Therefore, we should decide when to commit based on the size of the logical transaction.

3. In the following example, a redo of different sizes is generated to indicate that during the submission, the work time is almost the same.

S1: create a large table

1. Scott @ WISON>Create TableTAs Select*FromAll_objects;
3. TableCreated.
5. Scott @ WISON>

S2: Create a table t_10 that needs to insert 10 rows of data and set automatic tracing.

1. Scott @ WISON>Create TableT_10As Select*FromTWhere1 = 0;
3. TableCreated.
5. Scott @ WISON>SetAutotraceOn Statistics;
6. Scott @ WISON>SetTimingOn
7. Scott @ WISON>Insert IntoT_10Select*FromTWhereRownum <= 10;
9. 10RowsCreated.
11. <Strong> Elapsed: 00:00:00. 06 insert time
12. </Strong>
13. Statistics
14. ----------------------------------------------------------
15. 451 recursive cballs
16. 56 db block gets
17. 337 consistent gets
18. 1 physical reads
19. & Lt; strong & gt; 7284 redoSize</Strong>
20. 919 bytes sent via SQL * NetToClient
21. 1018 bytes encoded ed via SQL * NetFromClient
22. 4 SQL * Net roundtripsTo/FromClient
23. 2 sorts (memory)
24. 0 sorts (disk)
25. 10RowsProcessed
27. Scott @ WISON>Commit;
29. CommitComplete.
31. Elapsed: 00:00:00. 00

S3: insert row 1000

1. Scott @ WISON>Insert IntoT_10Select*FromTWhereRownum <= 1000;
3. 1000RowsCreated.
5. Elapsed: 00:00:00. 02
7. Statistics
8. ----------------------------------------------------------
9. 39 recursive cballs
10. 143 db block gets
11. 180 consistent gets
12. 7 physical reads
13. & Lt; strong & gt; 104880 redoSize
14. </Strong> 919 bytes sent via SQL * NetToClient
15. 1020 bytes encoded ed via SQL * NetFromClient
16. 4 SQL * Net roundtripsTo/FromClient
17. 1 sorts (memory)
18. 0 sorts (disk)
19. 1000RowsProcessed
21. Scott @ WISON>Commit;
23. CommitComplete.
25. <Strong> Elapsed: 00:00:00. 12 </strong>
26. Scott @ WISON>

Insert row 10000

1. Scott @ WISON>Insert IntoT_10Select*FromTWhereRownum <= 10000;
3. 10000RowsCreated.
5. Elapsed: 00:00:00. 10
7. Statistics
8. ----------------------------------------------------------
9. 487 recursive cballs
10. 1808 db block gets
11. 596 consistent gets
12. 121 physical reads
13. & Lt; strong & gt; 1102708 redoSize
14. </Strong> 919 bytes sent via SQL * NetToClient
15. 1021 bytes encoded ed via SQL * NetFromClient
16. 4 SQL * Net roundtripsTo/FromClient
17. 1 sorts (memory)
18. 0 sorts (disk)
19. 10000RowsProcessed
21. Scott @ WISON>Commit;
23. CommitComplete.
25. <Strong> Elapsed: 00:00:00. 01 </strong>
26. Scott @ WISON>

We found that the time for inserting 10 rows, 1000 rows, 10000 rows, and commit is very short.