Shulou(Shulou.com)05/31 Report--

This article mainly introduces "analyzing Oracle Nologging". In daily operation, I believe many people have doubts in analyzing Oracle Nologging problems. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "analyzing Oracle Nologging"! Next, please follow the editor to study!

Nologging:

The nologging option is often used when inserting data into large tables. It is not that Nologging does not produce redo.

Nologging+append just doesn't generate redo for the data (but there are other redo, such as redo caused by changes in the data dictionary).

Similarly, the undo under logging+append is also greatly reduced, reducing the undo of the data, the undo of the data itself, just like the reduction of redo, is the redo of the data itself.

This is a different concept from whether the database produces redo and undo, such as redo and undo for space allocation, which is not a change in the data itself.

The main effects of Nologging are:

Sql loader direct path loading

Insert of the direct path (append hint)

Create table as select

Alter table move

Create and rebuild indexes

In non-archived mode, using append for both nologging and logging modes does not generate redo for the data.

In archive mode, only if the table is placed in nologging mode and append is used, the data is not generated by redo.

Use the v$mystat view to show the redo generated by the current session for display:

Select a.name from v$statname b.value from v$statname a b.statistic# and a.name='redo size' vault mystat b where a.statistic# =

Test:

Be sure to start the force_logging feature before testing:

If the force_logging function is enabled, then nologging is not valid.

SYS@prod > select force_logging from v$database

FOR

-

NO

If the result is YES, then close it

Alter database no force logging

Tests in archive mode:

SYS@prod > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / u01/app/oracle/arch/pridb

Oldest online log sequence 230

Next log sequence to archive 232

Current log sequence 232

Create table Test:

View the redo value generated by the current session:

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

Nologging Test:

SYS@prod > create table test nologging as select * from dba_objects

Table created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 54928

The resulting redo size is 54928.

SYS@prod > drop table test

Table dropped.

Logging Test:

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > create table test logging as select * from dba_objects

Table created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 10262796

The number of redo produced was 10262796.

Conclusion: creating a table with nologging will not generate redo for the data, but only redo for the data dictionary.

DML Test:

Insert update delete

Delete:

Table logging:

SYS@prod > delete from test

86978 rows deleted.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 32996412

Table nologging:

SYS@prod > delete from test

86978 rows deleted.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 32991352

Insert and update tests are omitted and have the same effect as delete.

Conclusion:

For the DML operation of INSERT/UPDATE/DELETE, there is no difference between nologging and logging mode.

Test the insert using the direct path append:

Table logging inserts using append:

SYS@prod > create table test logging as select * from dba_objects

Table created.

In order to test the results, restart a session.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > insert / * + APPEND*/ into test select * from test

86980 rows created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 10239296

Table nologging inserts using append:

SYS@prod > create table test nologging as select * from dba_objects

Table created.

In order to test the results, restart a session.

SYS@prod > insert / * + APPEND*/ into test select * from test

86980 rows created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 13884

Conclusion:

For logging mode, there is no change in generating redo using append hint

For the nologging schema, no redo is generated for the data using append hint, only for the data dictionary redo.

Alter table move Test:

Move in table Logging mode:

SYS@prod > create table test logging as select * from dba_objects

Table created.

Restart a session

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > alter table test move tablespace example

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 10330784

Move in table nologging mode:

SYS@prod > create table test nologging as select * from dba_objects

Table created.

Restart a session

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > alter table test move tablespace example

Table altered.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 71712

Conclusion: when the table is moved in nologging mode, it will not record the data redo, but will only record the redo of the data dictionary changes.

Tests in non-archived mode:

SYS@prod > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination / u01/app/oracle/arch/pridb

Oldest online log sequence 250

Current log sequence 252

1.create table test

Logging to build tables:

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > create table test logging as select * from dba_objects

Table created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 54476

Nologging to build tables:

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > create table test nologging as select * from dba_objects

Table created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 53700

Conclusion: under the non-archiving mode, there is little difference between nologging and logging in table building.

2.DML test: (test insert only)

Insert:

Table Nologging

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > insert into test select * from test

86980 rows created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 10153240

Table Logging:

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > insert into test select * from test

86980 rows created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 10162700

Conclusion:

For the DML operation of INSERT/UPDATE/DELETE, there is no difference between nologging and logging mode

3.Insert+Append Test:

Logging:

SYS@prod > create table test logging as select * from dba_objects

Table created.

Restart a session

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > insert / * + APPEND*/ into test select * from test

86980 rows created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 13752

Nologging:

SYS@prod > create table test nologging as select * from dba_objects

Table created.

Restart a session:

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 0

SYS@prod > insert / * + APPEND*/ into test select * from test

86980 rows created.

SYS@prod > select a.name _ b.value from v$statname a _ camera _ mystat b where a.statistic# = b.statistic# and a.name='redo size'

NAME VALUE

-

Redo size 13884

Summary: for non-archived modes, for nologging and logging schemas, using append will not generate redo for the data.

For archive mode, only nologging+Append does not generate redo for data, but only redo for data dictionary

Test conclusions in two modes:

In archive mode:

If you create a table with nologging, you will not generate redo for the data, but only redo.

DML operation of insert/update/delete, there is no difference between logging and nologging

For logging mode, there is no change in generating redo using append hint

For the nologging schema, using append hint generates no redo for the data, only redo for the data dictionary.

Non-archive mode:

In non-archived mode, create table has little difference between nologging and logging modes.

For the DML operation of INSERT/UPDATE/DELETE, there is no difference between nologging and logging.

For both nologging and logging schemas, using append does not generate redo for the data.

At this point, the study of "analyzing Oracle Nologging" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.