Test and comparison of Oracle redo log generation

Test and comparison of Oracle redo log generation

Oracle Database logs can be in several modes, such as forced logs, unforced log modes, and object levels! After the database mode is set to force, the remaining logs will be forced in any case! Now we will conduct a comparative test on the volume of logs generated in the oracle Database Log mode. logs generated in various modes!

Conversion of logging Mode
A. Switch the database from the non-forced day mode to the forced log Mode
SQL> alter database force logging;
B. Switch the database from the forced log mode to the unforced log mode.
SQL> alter database noforce logging;
C. Change the tablespace level from the forced log mode to the unforced log mode.
SQL> alter tablespace tablespacename noforce logging;
D. Change the tablespace level from unforced log mode to forced log mode.
SQL> alter tablespace tablespacename force logging;
E. Object-level logging Mode
SQL> alter tablet mytest nologging; -- the log mode is not logged.
SQL> alter tablet mytest logging; -- use the logging Mode
 
I. Table segments and index segments Use General DDL, DML, LOGGING and NOLOGGING
1. view the archiving mode of the database
For more information about how to set the log archiving mode, see:
Oracle ONLINE redo LOG FILE)
Oracle archiving logs
 
SQL> select log_mode, force_logging from v $ database;

LOG_MODE
---------------
ARCHIVELOG NO

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination/u01/mytest/arch
Oldest online log sequence 1024
Next log sequence to archive 1025
Current log sequence 1025

SQL> select tablespace_name, logging, force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING
------------------------------------------
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS LOGGING NO
PERFSTAT LOGGING NO

2. Use the following statement to query

SQL> SELECT a. name, B. value FROM v $ statname a JOIN v $ mystat B ON a. statistic # = B. statistic # WHERE a. name = 'redo size ';

 
3. Compare NOLOGGING and LOGGING on table segments in archive Mode
SQL> SELECT a. name, B. value FROM v $ statname a JOIN v $ mystat B ON a. statistic # = B. statistic # WHERE a. name = 'redo size ';

NAME VALUE
-------------------------
Redo size 1644

SQL> CREATE TABLE mytest_nolog NOLOGGING AS SELECT * FROM dba_objects; -- CREATE a TABLE in nologging Mode

SQL> SELECT a. name, B. value FROM v $ statname a JOIN v $ mystat B ON a. statistic # = B. statistic # WHERE a. name = 'redo size ';

NAME VALUE
-------------------------
Redo size 70064

SQL> select 1644 last, 70064 as cur, (70064-1644) diff from dual; -- the redo size generated by table creation in nologging mode is
 
LAST CUR DIFF
------------------------------
1644 70064 68420

SQL> CREATE TABLE mytest LOGGING AS SELECT * FROM dba_objects; -- use the logging mode to CREATE a TABLE
 
SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo size

NAME VALUE
-------------------------
Redo size 1344112

SQL> select 70064 last, 1344112 as cur, (1344112-70064) diff from dual;

LAST CUR DIFF
------------------------------
70064 1344112 1274048 ---- view the redo size generated by the logging mode is-68420 = 1274048, more than 19 times that of the nologging LOG model!


SQL> select table_name, logging from user_tables where table_name like 'mytest % '; -- view the log record mode of the created table
 
TABLE_NAME LOG
---------------------------------
MYTEST YES
MYTEST_NOLOG NO

4. Compare redo size Based on indexes (also in archive Mode)
SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo_size

NAME VALUE
-------------------------
Redo size 1140

SQL> create index idx_mytest on mytest (object_id); -- creates an index based on mytest.
 
SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo_size

NAME VALUE
--------------- ---------- -- Based on the loggiing mode, the redo size generated by creating an index is 221600-1140 = 220460
Redo size 221600

SQL> alter index idx_mytest rebuild; -- re-create an index

SQL> select 221600 last, 448132 cur, 448132-221600 diff from dual;

LAST CUR DIFF
------------------------------
221600 448132 226532 -- Re-indexing generates more logs than direct creation, mainly because the re-indexing process also deletes the old index.
 

SQL> alter index idx_mytest rebuild nologging; -- use nologging to recreate the index.
-- You can also directly use the nologging keyword when creating an index.
-- Example: create index idx_mytest_nolog mytest_nolog (object_id) nologging
SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo size

NAME VALUE
--------------- ---------- -- Redo size generated by re-Indexing Based on nologging log mode is 469160-448132 = 21028
Redo size 469160

5. LOGGING and NOLOGGING in non-archive Mode

SQL> drop table mytest purge;

SQL> drop table mytest_nolog purge;

SQL> select log_mode, force_logging from v $ database; -- after switching logs to non-archive mode, the query results are as follows:
 
LOG_MODE
---------------
NOARCHIVELOG NO

SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo size

NAME VALUE
-------------------------
Redo size 1688
SQL> create table mytest as select * from dba_objects; -- create a table object and use the logging mode of logging.
 
SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo size

NAME VALUE
--------------- ---------- -- In the logging mode, the redo size generated after the table object is created is-1688 = 68548.
Redo size 70236

SQL> create table mytest_nolog nologging as select * from dba_objects; -- create a table object and use the nologging log record mode
 
SQL> SELECT. name, B. value FROM v $ statname a JOIN v $ mystat B ON. statistic # = B. statistic # WHERE. name = 'redo size'; -- view the current redo size

NAME VALUE
--------------- ---------- -- The redo size generated after the table object is created in nologging logging mode is 135464-70236 = 65228

Redo size 135464

6. Summary:
When using logging and nologging to create objects or execute DML
In non-archive mode, the log information (redo size) generated by the archive mode is not significantly different.
In archive mode, logs generated in logging mode are much larger than the logs generated in nologging mode.

For more details, please continue to read the highlights on the next page:

1 2 Next Page