LOGGING and NOLOGGING on table and index segments

-- ============================================

-- LOGGING and NOLOGGING on table and index segments

-- ============================================

In some cases, you can use the log recording mode for the table segment and index segment, or you can use the mode of not logging. For example, you can

In NOLOGGING mode, logs must be recorded in scenarios with data guard or high availability, or even forcibly recorded logs. This topic describes how to use table segments and index segments.

The size of the redo generated during LOGGING and NOLOGGING and the usage of direct insert append.

NOLOGGING is related to the running mode of the database. The default installation mode of I and I is not archive, and automatic archiving is disabled by default. When installing g and g, you can choose whether

File. NOLOGGIING records a small amount of log information to the log file. This option is invalid if the force logging mode is used at the database or table space level.

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

Sys @ ORCL> select log_mode, force_logging from v $ database;

LOG_MODE

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

ARCHIVELOG NO

Sys @ ORCL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination/u01/bk/arch

Oldest online log sequence 50

Next log sequence to archive 51

Current log sequence 51

Sys @ ORCL> 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

Scott @ ORCL> select * from v $ version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

2. Create a redo_size view to view the redo size

Create or replace force view "SYS". "REDO_SIZE"

AS

SELECT a. name, B. value

FROM v $ statname

JOIN v $ mystat B

ON a. statistic # = B. statistic #

WHERE a. name = 'redo size ';

Sys @ ORCL> create public synonym redo_size for redo_size;

Sys @ ORCL> grant select on redo_size to scott;

3. Compare NOLOGGING and LOGGING on table segments in archive Mode

Scott @ ORCL> select * from redo_size;

NAME VALUE

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

Redo size 1644

Scott @ ORCL> create table tb_obj_nolog nologging as select * FROM dba_objects; -- CREATE a TABLE in nologging Mode

Scott @ ORCL> 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

Scott @ ORCL> create table tb_obj_log logging as select * FROM dba_objects; -- use the logging mode to CREATE a TABLE

Sscott @ ORCL> select * from redo_size; -- view the current redo size

NAME VALUE

--------------- ---------- -- Check that the redo size generated by the logging mode is-68420 = 1274048.

Redo size 1344112 -- formula more than 19 times

Scott @ ORCL> select table_name, logging from user_tables where table_name like 'tb _ OBJ % '; -- view the log record mode of the created table

TABLE_NAME LOG

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

TB_OBJ_LOG YES

TB_OBJ_NOLOG NO

4. Compare redo size Based on indexes (also in archive Mode)

Scott @ ORCL> select * from redo_size; -- view the current redo_size

NAME VALUE

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

Redo size 1140

Scott @ ORCL> create index idx_tb_obj_log on tb_obj_log (object_id); -- create an index based on the table tb_obj_log

Scott @ ORCL> select * from 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

Scott @ ORCL> alter index idx_tb_obj_log rebuild; -- re-create the index

Scott @ ORCL> select 221600 last, 448132 cur, 448132-221600 diff from dual;

LAST CUR DIFF

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

221600 448132 226532 -- the redo size generated after index reconstruction is 226532, which is more than the redo size generated during direct creation.

Scott @ ORCL> alter index idx_tb_obj_log rebuild nologging; -- use nologging to recreate the index.

-- You can also directly use the nologging keyword when creating an index.

-- Example: create index idx_tb_obj_nolog tb_obj_nolog (object_id) nologging

Scott @ ORCL> select * from 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

Scott @ ORCL> drop table tb_obj_log purge;

Scott @ ORCL> drop table tb_obj_nolog purge;

Scott @ ORCL> select log_mode, force_logging from v $ database; -- after switching logs to non-archive mode, the query result is as follows:

LOG_MODE

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

NOARCHIVELOG NO

Scott @ ORCL> select * from redo_size; -- view the current redo size

NAME VALUE

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

Redo size 1688

Scott @ ORCL> create table tb_obj_log as select * from dba_objects; -- create a table object in the logging Mode

Scott @ ORCL> select * from 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

Scott @ ORCL> create table tb_obj_nolog nologging as select * from dba_objects; -- create a table object and use the nologging logging Mode

Scott @ ORCL> select * from 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.

Ii. Use of direct insert append for LOGGING and NOLOGGING

Direct insert append is one of the ways in which Oracle inserts data into the database. The APPEND method is used for import. In fact, records are directly stored on the HWM,

Do not consider idle blocks under the high water level line.

1. The database runs in non-archive mode.

A. Create a table in logging Mode

Scott @ ORCL> select log_mode from v $ database;

LOG_MODE

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

NOARCHIVELOG

Scott @ ORCL> select * from redo_size;

NAME VALUE

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

Redo size 1764

Scott @ ORCL> create table tb_obj_log as select * from dba_objects where 1 = 0;

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo volume generated by table creation is 23908-1764 = 22144

Redo size 23908

Scott @ ORCL> insert into tb_obj_log select * from dba_objects;

11634 rows created.

Elapsed: 00:00:00. 36

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo volume generated when insert is directly used: 1281060-23908 = 1257152

Redo size 1281060

Scott @ ORCL> insert/* + append */into tb_obj_log select * from dba_objects;

11634 rows created.

Elapsed: 00:00:00. 26

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo volume generated when the append mode is used: 1284740-1281060 = 3680

Redo size 1284740 -- General insert generates 1257152/3680 = 341 times more redo than append insert.

B. Create a table in nologging Mode

Scott @ ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1 = 0;

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- Use nologging to create an empty table tb_obj_nolog. The log volume generated is 1305812-1284740 = 21072.

Redo size 1305812

Scott @ ORCL> insert into tb_obj_nolog select * from dba_objects;

11635 rows created.

Elapsed: 00:00:00. 21

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The log volume generated by inserting a record using normal insert is 2562664-1305812 = 1256852

Redo size 2562664

Scott @ ORCL> insert/* + append */into tb_obj_nolog select * from dba_objects;

11635 rows created.

Elapsed: 00:00:00. 18

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo volume generated when the append mode is used: 3766404-2562664 = 1203740

Redo size 3766404

C. redo comparison

In objects with the logging attribute, the redo volume generated when the append mode is used is 1284740-1281060 = 3680,

Normal insert is generated more than append insert/3680 = 341 times redo

In objects with the nologging attribute, the redo volume generated in the append insert mode is not much different from that in the normal insert mode,

Append insert mode, while normal insert mode

2. The database runs in archive mode.

A. Preliminary handling

Scott @ ORCL> drop table tb_obj_log purge;

Scott @ ORCL> drop table tb_obj_nolog purge;

Sys @ ORCL> select log_mode from v $ database;

LOG_MODE

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

ARCHIVELOG

B. Create and compare table objects

Scott @ ORCL> create table tb_obj_log as select * from dba_objects where 1 = 0; -- create table object in logging Mode

Scott @ ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1 = 0; -- create table object in nologging Mode

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- Check that the current redo size is 46844

Redo size 46844

Scott @ ORCL> insert into tb_obj_log select * from dba_objects; -- insert records using regular insert for table tb_obj_log

11598 rows created.

Elapsed: 00:00:00. 25

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo size generated by the table tb_obj_log using the regular insert record is 1299120-46844 = 1252276

Redo size 1299120

Scott @ ORCL> insert into tb_obj_nolog select * from dba_objects; -- insert records using regular insert for table tb_obj_nolog

11598 rows created.

Elapsed: 00:00:00. 28

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo size generated by the table tb_obj_nolog using the regular insert record is 2552880-1299120 = 1253760

Redo size 2552880

Scott @ ORCL> insert/* + append */into tb_obj_log select * from dba_objects; -- the table tb_obj_log uses the insert append method.

11598 rows created.

Elapsed: 00:00:00. 20

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The table tb_obj_log uses insert append to insert the redo size generated by the record to be 3750852-2552880 = 1197972

Redo size 3750852

Scott @ ORCL> insert/* + append */into tb_obj_nolog select * from dba_objects; -- the table tb_obj_nolog uses the insert append method.

11598 rows created.

Elapsed: 00:00:00. 18

Scott @ ORCL> select * from redo_size;

NAME VALUE

--------------- ---------- -- The redo size generated by the table tb_obj_nolog Using insert append to insert records is 4948764-3750852 = 1197912

Redo size 4948764

C. redo comparison

In archive mode, if the table tb_obj_nolog has the nologging feature, the insert append method is the fastest and the log volume is the smallest. While

The log volume of the table tb_obj_log with the logging feature when using the insert append method is, the difference is not great. For general insert inserts

The number of logs generated varies greatly.

3. Summary

When inserting a record for a table object, use the conventional insert method to compare with the direct insert append method.

In non-archive mode, when the table object uses the nologging mode, the log volume generated by the two is not much different. In the logging mode, the log volume of regular insert is far

Greater than the direct insert append method.

In archive mode, the table object uses the logging mode, and the log volume generated by the two is not much different. When the table object uses the nologging mode, insert append is used.

This will improve the performance.

Inesrt append operations in non-archive mode will have the highest performance.

4. Notes for using direct insert append

A. When the insert into... values statement is used, the append method cannot be used.

B. The append method is batch insert records. Therefore, newly inserted records are stored on hwm, and idle blocks under hwm are not used.

C. After a record is inserted in append mode, you must execute commit to query the table. Otherwise, an error occurs:

ORA-12838: cannot read/modify a object after modifying it in parallel

D. In archive mode, a table object has the nologging attribute and records are added in batch in append mode to significantly reduce the number of redo records.

E. In non-archive mode, table objects can reduce the number of redo operations even if they have the logging attribute.

F. For table objects with indexes, if the number of new records is a small part of the entire table, add records in batch using the append method. If there are few records in the original table,

The real-time requirement is not very high, but there are many new records. You can delete the index first, append the record in append mode, and then create the index.

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html