Compare the impact of a single COMMIT and multiple COMMIT on the waste of log space while changing the data volume.

Compare the impact of a single COMMIT and multiple COMMIT on the waste of log space while changing the data volume.

The LGWR process writes online logs in sequence and does not skip in the middle, and the LGWR process does not write twice in the same log. Even if the logs written at a time occupy only a few bytes, they will not be used again next time, this results in a waste of log space. When Oracle performs a Commit operation, the LGWR process is triggered to buffer logs into log files. Therefore, it can be said that when the same data volume is changed, if the submission is too frequent, the more logs are generated, even if the log block occupied by the first Commit can still store the log buffer to be written for the next time, the next Commit will occupy a new log block again.

Lab:

1. the system log block size is 512 bytes.

SQL> select max (lebsz) from sys. x $ kccle;

MAX (LEBSZ)

----------

512

2. Create two tables with the same data volume.

SQL> select count (*) from t1;

COUNT (*)

----------

11188

SQL> select count (*) from t2;

COUNT (*)

----------

11188

3. view the amount of log space wasted by the system before table t1 is deleted.

SQL> select name, value from v $ sysstat where name like '% wastage % ';

NAME VALUE

--------------------------------------------------------------------------

Redo wastage 208060

4. delete records of table t1 one by one.

SQL> begin

2 for I in 1 .. 11188 loop

3 delete from t1 where rownum <2;

4 commit;

5 end loop;

6 end;

7/

5. view the log space waste again.

SQL> select name, value from v $ sysstat where name like '% wastage % ';

NAME VALUE

--------------------------------------------------------------------------

Redo wastage 1118740

SQL> select 1118740-208060 from dual;

1118740-208060

--------------

910680

The waste of log space is 910680 bytes.

6. view the SID of the current process.

SQL> select distinct sid from v $ mystat;

SID

----------

215

The total number of redo logs consumed by the current process is determined.

SQL> select B. name, a. value from v $ sesstat a, v $ statname B

2 where a. statistic # = B. statistic #

3 and B. name like '% redo size %'

4 and a. sid = 215;

NAME VALUE

------------------------------

Redo size 9103304

The log space waste rate is 10%.

SQL> select 910680/9103304 from dual;

910680/9103304

--------------

. 100038404

7. Next, choose to delete the records in Table t2 at a time, and record the log space waste.

SQL> select name, value from v $ sysstat where name like '% wastage % ';

NAME VALUE

------------------------------

Redo wastage 1130636

SQL> delete from t2;

11188 rows deleted.

SQL> commit;

Commit complete.

8. Check whether the current log space is wasted.

SQL> select name, value from v $ sysstat where name like '% wastage % ';

NAME VALUE

------------------------------

Redo wastage 1132060

9. Calculate the ratio of log waste space.

SQL> select 1132060-1130636 from dual;

1132060-1130636

---------------

1424

SQL> select B. name, a. value from v $ sesstat a, v $ statname B

2 where a. statistic # = B. statistic #

3 and B. name like '% redo size %'

4 and a. sid = 215;

NAME VALUE

------------------------------

Redo size 13154544

SQL> select 1424/13154544 from dual;

1424/13154544

-------------

. 000108252

The result shows that the log space waste rate is only 0.01%.

Conclusion:

1. The LGWR process writes the log buffer to the log block in sequence and does not write the log block twice. This may result in a waste of space for the last log block to be written, however, you cannot use it again. You can only write a new log block again.

2. When the data volume is changed, multiple Commit operations waste more space than one Commit operation.

How does one modify the commit of a large amount of oracle Data by page to avoid occupying a large amount of memory?

You can write a block (one commit per 10 thousand entries)
Declare
Begin
Update * set *;
If rownum % 10000 = 0 then
Commit;
End if;

End;

I have made several changes to the oracle database a long time ago, but have not executed the commit command. How can I verify whether the sqlplus tool has executed the commit command?

It's easy. Look up the table. select * from table name;