Oracle nologging Usage

One, the Oracle log mode is divided into (Logging,force logging,nologging)

The default is logging, which is recorded in the Redo log, force logging is forced to log logs, nologging is to minimize the log. Force logging can be set at the database level, at the table space level, and logging and nologging can be set at the table level.

Note: Force logging is not more than the average logging log, the nologging option will not be valid if the database is in force logging state, because nologging will destroy the recoverability of Dataguard. Force Logging forces the database to log in any state.

Logging,force logging and nologging are only recorded in the redo log, archiving is not an archive is another setting, but if the use of nologging, then obviously even if the archive, the archive log is less, but may not be used for media reply, Because some are not recorded at all.

Second, the use of the situation

1.logging mode

This is the default mode for logging, which does not change the default logging mode at the table space and object level, regardless of whether the database is in archive mode. for temporary tablespace, the log is not logged to the online redo log file.

2.nologing mode

This mode does not log logs, but minimizes the number of logs produced, and is typically used in the following situations nologging

The nologing mode is commonly used in conjunction with append.

Append Introduction:

/*+append*/

(1) Append belongs to direct Insert, the archive mode append+table nologging will reduce the number of logs, non-archival mode append will be a lot of reduction of the log, append way to insert will only produce very little undo

Using Append, one is to reduce the search for space, and the other is the possibility to reduce the generation of redolog. So the append way will be much faster, generally for the processing of large data volume. It is recommended not to use append often, so that the table space will always be on the high water level, unless you do not delete this table append explanation: Ask, Oracle append is what to do.
Insert/*+append*/to table1 select * from table2
After using the APPEND option, the Insert data is added directly to the last face of the table without inserting data into the table's free block. Using append will increase the speed of data insertion. /*+append*/'s role is to allocate space on the table's High water level and no longer use the free space in the table's extent
Append belongs to direct Insert, the archive mode append+table nologging will reduce the number of logs, non-archival mode append will be a lot of reduction of the log, append mode inserts will only produce very little undo not to find Freelist The free block in the table HWM to add data directly above it. Check Redo size statement: Select Name,value,class from V$sysstat where name= ' redo size '; Display Good method: Select (1745704-582728) Redo1, ( 1839872-1745704) Redo2 from dual;//Note that the parentheses are actually two number subtraction, which is then written in to show it.(2) nologging relationship with table mode, insert mode, database run mode (archived/unarchived):The following is only a description of what happens when you insert data using append, such as inserting it in the following way (this is whether the table is set to nologging beforehand)

Insert/*+append+*/into Tb_name Select colnam1,colname2 from table_name;

Database in archive mode

When the table mode is in the logging state, redo is generated regardless of whether append mode is used or not. When the table mode is nologging state, only append mode will not generate redo.

Database is in non-archive mode

Either in logging or nologing mode, the Append mode does not generate redo, and append is generated in no Redo mode.

ALTER TABLE AA nologging

ALTER TABLE AA logging

Insert/*+append*/into ... nologing
SELECT * From ...

Insert/*+ Append, parallel */into ods_list_t nologging
SELECT * from Ods_list;

But this is not possible: the entire table can be inserted, but to a certain field cannot join nologging
Insert/*+ Append, parallel */into ods_list_t (A, b) nologging
Select a b from Ods_list;

But you can do this:
Insert/*+ Append, parallel */into ods_list_t nologging (A, B)
Select a b from Ods_list;

CREATE TABLE ods_list_t nologging as SELECT * from Ods_list;

Insert/*+ Append, parallel */into ods_list_t nologging
SELECT * from Ods_list;

Insert/*+ Append Parallel (tablename,number) */into ods_list_t nologging
SELECT * from Ods_list;

TableName: Table name
Number: Degree of parallelism

Using the bulk copy method
Set ArraySize 20
Set Copycommit 5000
Copy from username/[email protected]_name Append table_name1
Using SELECT * from Table_name2;

Processing large amounts of data with Exp/imp

(1) Renaming the current two tables separately
ALTER TABLE TAB1 Rename to Tab11;
ALTER TABLE TAB2 Rename to Tab1;
(2) Export the tab2 before renaming
Exp User/[email protected] file= ... log= ... tables= (TAB1)
(3) Change the name back
ALTER TABLE TAB1 Rename to tab2;
ALTER TABLE TAB11 Rename to Tab1;
(4) Import data
Imp user/[email protected] file= ... log= ... fromuser=user touser=user tables= (TAB1)

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

(3) Real Summary (useful):

As you can see (this is the insert/*+ append */into ddddd nologging as SELECT ... ）

1. No matter which mode append to use with the nologging mode in order to achieve good results.

2. Non-archiving and archiving methods, only using nologging is not effective.

3. Non-archive Append has achieved good results, but not with the nologging of the use of the way.

4. Filed under the single append does not have the effect.

In addition, if the library is in forcelogging mode, this time the nologging mode is not valid, this I also tested
5. Non-archive mode:
No optimizations before (1281372 redo size)
(1), the single use of nologging parameters, the production of redo has no impact. (1214836 redo size)
(2), single use append hint, redo reduction is significant (43872 redo size)
(3), Nologging+append, more significant (1108 redo size)

6. Archive Mode:
No optimizations before: 
(1), using the nologging parameter alone, (1231904 redo size)
(2), use append hint alone, (1245804 redo size)
(3), nologging + Append, (3748 redo size)
A, using the nologging parameter does not mean that Oracle does not produce redo in DML operations, but that the updated data for the specified table does not produce redo, but Oracle still records these operations, so no matter how optimizations are done, the DML operation must produce redo, However, the effect of using these parameters on redo size is considerable.
b, the use of nologging parameters alone, redo size does not have much impact, only with the append with the effect can be produced.
C, use alone append hint, to the redo of the impact is very big, this is I now do not understand the truth, supposedly append is to bypass freelists, directly to find new block, can reduce the contention for freelists, why will less so much redo it?
D, archive mode and non-archive mode, the parameters have different effects, especially when using the append parameter alone, it seems that Oracle has more security considerations in the archive mode.

Iii. Summary of Inquiries

1. View different levels of log mode

View archive mode, logging mode for database-level logs

Sql>select log_mode,force_logging from V$database;

Log_mode force_logging

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

ARCHIVELOG NO

To view the logging mode at the table space level

Sql>select tablespace_name,logging,force_logging from Dba_tablespaces;

Tablespace_name LOGGING force_logging

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

SYSTEM LOGGING NO

UNDOTBS1 LOGGING NO

Sysaux LOGGING NO

TEMP nologging NO

USERS LOGGING NO

To view the logging mode at the object level

[Email protected]>select table_name,logging from User_tables;

TABLE_NAME LOG

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

Tb_a YES

Tb_b YES

2. Conversion of logging modes

A. Database switching from non-mandatory day mode to forced log mode

[Email protected]>alterdatabase force logging;

B. Database switching from forced log mode to non-forced log mode

[Email Protected]>alterdatabaseno force logging;

C. tablespace level switch from forced log mode to non-forced log mode

[Email Protected]>alterdatabaseno force logging;

D. tablespace level switch from non-forced log mode to forced log mode

[Email Protected]>alterdatabaseno force logging;

E. Object-level logging mode

[Email protected]>altertable tb_a nologging; --Do not log mode

[Email protected]>altertable tb_a logging; --Using logging mode

Attention:

(1) Whether the table is nologging mode, also need to check the User_tables.logging field.
I just tested in Oracle 10g, create TABLE XXX nologging as SELECT * .... The resulting table is not a nologging way, but it has to be changed manually:
ALTER TABLE XXXX nologging.
After the change, in archive mode, insert/*+append*/does log very rarely.

(2) After the use of Append fast loading data completion, you need a commit or roll to proceed to the table process operation otherwise error: Ora-12838:cannot read/modify an object after modifying it in Parallel error. Insert and append insert are completely different in principle, and insert append can implement direct path loading, which is much faster than normal loading. But one thing to note: When you insert append, you add a "6" type of exclusive lock on the table, which is an exclusive lock and a table-level lock that blocks all DML statements on the table. It is therefore prudent to use it in a business-run upload environment.