When is NOLOGGING to take effect?
LINK:
http://www.itpub.net/242761.html
http://www.itpub.net/239905.html
?
We know, NOLOGGING is only effective in rarely, and DML operations always generate redo.
I don't have much to say this.
There have been many misunderstandings about Nologging and Append. After a series of studies, the truth of NOLOGGING was finally discovered.
Let's take a look at the test:
1. Nologging settings are related to the running mode of the database
a. Database operation in non-archive mode:
SQL> Archive log list; database log mode ????????????? no archive modeautomatic archival ????????????? enabledarchive destination ?????????? ? / OPT / ORACLE / ORADATA / HSJF / ARCHIVEOLDEST online log sequence ???? 155current log sequence ?????????? 157
SQL> @redosql> Create Table Test As SELECT * FROM DBA_Objects where 1 = 0;
Table created.
SQL> SELECT * from redo_size;
???? value ---------- ?????? 63392
SQL> SQL> INSERT INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? Value ---------- ?? 1150988
SQL> SQL> INSERT / * APPEND * / INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? Value ---------- ?? 1152368
SQL> SELECT (1152368 -1150988) Redo_Append, (1150988 -63392) redo from dual
REDO_APPEND ?????? redo -------------------- ???????????????? 1087596
SQL> Drop Table Test;
Table Dropped.
We see in NOARCHIVELOG mode, INSERT APPEND for regular tables generates a small amount of REDO
b. In archiving mode
SQL> Shutdown ImmediateDatabase Closed.Database Dismounted.Orcle Instance Shut Down.SQL> Startup MountainOracle Instance Started.
TOTAL SYSTEM GLOBAL AREA? 235999908 BYTESFIXED SIZE ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???? 33554432 BYTESREDO BUFFERS ????????????????? 667648 BytesDatabase mounted.
SQL> ALTER DATABASE ArchiveLog;
Database altered.
SQL> ALTABASE OPEN;
Database altered.
SQL> @redosql> Create Table Test As SELECT * FROM DBA_Objects where 1 = 0; Table CREATED.
SQL> SELECT * from redo_size;
???? value ---------- ???? 56288
SQL> SQL> INSERT INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? value ---------- ?? 1143948
SQL> SQL> INSERT / * APPEND * / INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? value ---------- ?? 2227712
SQL> SELECT (2227712 -1143948) Redo_Append, (1143948 -56288) Redo from Dual
REDO_APPEND ?????? redo -------------------- ??? 1083764 ??? 1087660
SQL> Drop Table Test;
Table Dropped.
We see in archiving mode, for the INSERT APPEND generated and inserts of the regular table, INSERT APPEND does not actually improve performance. But at this time Append is effective.
Get the following results by the LogMnr analysis log:
SQL> SELECT OPERATION, COUNT (*)? 2? From v $ logmnr_contents? 3? Group by Operation;
Operation ????????????????????????? counT (*) -------------------- ------------ ---------- COMMIT ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??????? 17direct INSERT ?????????????????????????????????? ??????????????????????Start ????????????????????? ????? 17 ?????????????????????????????????? 1 ?? ????????????????????????????????????????? We noticed that it is Direct Insert, and 10470 Records, that is, each record has recorded Redo.
2. Process for NOLOGGING TABLE
a. In archiving mode: SQL> CREATE TABLE TEST NOLGGING AS SELECT * FROM DBA_OBJECTS Where 1 = 0;
Table created.
SQL> SELECT * from redo_size;
???? value ---------- ?? 2270284
SQL> SQL> INSERT INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? Value ---------- ?? 3357644
SQL> SQL> INSERT / * APPEND * / INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? Value ---------- ?? 3359024sql> SELECT (3359024 -3357644) Redo_Append, (3357644 - 2270284) Redo from Dual
REDO_APPEND ?????? redo -------------------- ??????? 1380 ??? 1087360
SQL> Drop Table Test;
Table Dropped.
We noticed that only Append can reduce Redo
b. In the non-archive mode:
SQL> Shutdown ImmediateDatabase Closed.Database Dismounted.Orcle Instance Shut Down.SQL> Startup MountainOracle Instance Started.
TOTAL SYSTEM GLOBAL AREA? 235999908 BYTESFIXED SIZE ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???? 33554432 BYTESREDO BUFFERS ????????????????? 667648 BytesDatabase mounted.SQL> ALTER DATABASE NOARCHIVELOG;
Database altered.
SQL> ALTABASE OPEN;
Database altered.
SQL> @redosql> Create Table Test Nologging As SELECT * FROM DBA_OBJECTS Where 1 = 0;
Table created.
SQL> SELECT * from redo_size;
???? Value ---------- ????? 56580
SQL> SQL> INSERT INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? Value ---------- ?? 1144148
SQL> SQL> INSERT / * APPEND * / INTO TEST SELECT * FROM DBA_OBJECTS;
10470 rows created.
SQL> SELECT * from redo_size;
???? value ---------- ?? 1145528
SQL> SELECT (1145528 -1144148) Redo_Append, (1144148 -56580) Redo from Dual
REDO_APPEND ?????? redo -------------------- ??????? 1380 ??? 1087568
SQL> ???????????????????????????????????
Only Append can reduce the generation of REDO.
This is what you usually know. ?????????????????????????????????
