How to Reduce redo size in Oracle

To reduce the redo size of Oracle, use lab instructions www.2cto.com 1. In non-archive mode: [SQL] SQL> archive log list database log mode automatic archiving in non-archive mode disable archiving end point USE_DB_RECOVERY_FILE_DEST oldest online log sequence 2491 current log sequence 2493 www.2cto.com use sys user to create a view to query redo size (convenient query) [SQL] SQL> create or replace view redo_size 2 as 3 select value 4 from v $ mystat, v $ statname 5 where v $ mystat. statistic # = v $ statname. statistic #6 and v $ statname. name = 'redo size'; the view has been created. Use the sys user to create a synonym [SQL] SQL> create public synonym redo_size for redo_size; the synonym has been created. Run the scott operation to create a test table [SQL] SQL> create table test_redos as select * from dba_objects where 1 = 2; the table has been created. View the current redo volume [SQL] SQL> select * from redo_size; VALUE ---------- 736 insert data. See the result [SQL] SQL> insert into test_redos select * from dba_objects; 73104 rows have been created. SQL> select * from redo_size; VALUE ---------- 8473536 SQL> insert/* + append */into test_redos select * from dba_objects; 73100 rows have been created. SQL> select * from redo_size; VALUE ---------- 8504856 SQL> select (8473536-736) normal insert, (8504856-8473536) append insert from dual; general insert APPEND insert ---------- 8472800 31320 and above results indicate that in non-archive mode, append insert data produces much less redo. 2. In archive mode (when the database and tablespace level is set to force logging, the default is not force logging): [SQL] SQL> archive log list; database log mode Archive Mode Automatic archival Enabled Archive destination/archive1 Oldest online log sequence 114 Next log sequence to archive 116 Current log sequence 116 same as above (in non-Archive) Create test table ①: when the table is logging, [SQL] SQL> create Table test_redos as select * from dba_objects where 1 = 2; table created. SQL> select * fr Om redo_size; VALUE ---------- 26812 SQL> insert into test_redos select * from dba_objects; 71971 rows created. SQL> select * from redo_size; VALUE ---------- 8490044 SQL> insert/* + append */into test_redos select * from dba_objects; 71971 rows created. SQL> select * from redo_size; VALUE ---------- 17001396 SQL> select (8490044-26812) normal insert, (17001396-8490044) append insert from dual; normal insert APPEND insert --- ------- ---------- 8463232 8511352 it can be seen that the redo volume generated by append Inserts will not be reduced in the case of logging (default) in the archive mode table. ②: When the table is nologging, set the Table to nologging mode [SQL] SQL> alter table test_redos nologging; Table altered. continue to test [SQL] SQL> select * from redo_size; VALUE ---------- 8397184 SQL> insert into test_redos select * from dba_objects; 71971 rows created. SQL> select * from redo_size; VALUE ---------- 16801072 SQL> insert/* + append */into test_redos select * from dba_objects; 71971 rows created. SQL> select * from redo _ Size; VALUE ---------- 16836516 SQL> select (16801072-8397184) normal insert, (16836516-16801072) append insert from dual; normal APPEND insertion ---------- 8403888 35444 it can be seen that in the nologging mode of the table, append can greatly reduce the redo volume. 3. In the archive force logging mode: Change SCOTT's default tablespace to the force logging mode [SQL] SQL> select username, default_tablespace from dba_users where username = 'Scott '; USERNAME DEFAULT_TABLESPACE ------------------------------ ---------------------------- scott users -- set the statement to alter database force logging at the data level; SQL> alter tablespace users force logging; Tablespace altered. continue to test [SQL] SQL> select * from redo_size; VALUE ---------- 25488368 SQL> insert into test_redos select * from dba_objects; 72010 rows created. SQL> select * from redo_size; VALUE ---------- 33973556 SQL> insert/* + append */into test_redos select * from dba_objects; 72010 rows created. SQL> select * from redo_size; VALUE ---------- 42492396 SQL> select (33973556-25488368) normal insert, (42492396-33973556) append insert from dual; normal insert APPEND insert -------- -- ---------- 8485188 8518840 we can see that append in force logging mode cannot reduce the redo volume. Conclusion: in non-archive mode, append can greatly reduce the redo volume. Archive mode: In table space and database-level non-force logging mode, if the table is nologging, append can greatly reduce the redo volume.