Shulou(Shulou.com)06/01 Report--

Effect of ORACLE 10g append and nologging insertion on REDO

/ * + append*/

1. Append belongs to direct insert. Append+table nologging will greatly reduce logs in archive mode.

Non-archived mode append will greatly reduce logs, while append insertion will only produce very little undo.

two。

To sum up: first, reduce the search for space; second, it is possible to reduce the generation of redolog. So the append method will be much faster, generally used for large amounts of data processing 3. It is recommended that you do not use append frequently, so that the tablespace will always be at a high water level, unless you insert only 4. 5% of the table.

- -

What is the purpose of oracle append?

Please tell me what append is used in oracle.

Insert / * + append*/ into table1 select * from table2

After using the append option, insert data is added directly to the end of the table without inserting data in the free block of the table.

Using append increases the speed of data insertion. The function of / * + APPEND*/ is to allocate space on the high water level of the table, instead of using the free space in the extent of the table.

Append belongs to direct insert. Append+table nologging in archive mode will greatly reduce logs, while append in non-archive mode will greatly reduce logs. Inserting in append mode will only generate very little undo instead of looking for free block in freelist and directly adding data to table HWM.

- -

SQL > 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 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

1. Comparison of REDO generated by data in three cases of non-archiving mode append,nologging,append+nologging

PHP:

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 13

Current log sequence 15

SQL > create table t as select * from dba_objects where 1: 2

Table created.

SQL > select name,value,class from v$sysstat where name='redo size'

NAME VALUE CLASS

Redo size 582728 2

SQL > insert into t select * from dba_objects

10947 rows created.

SQL > select name,value,class from v$sysstat where name='redo size'

NAME VALUE CLASS

Redo size 1745704 2

SQL > insert / * + append*/ into t select * from dba_objects

10947 rows created.

SQL > select name,value,class from v$sysstat where name='redo size'

NAME VALUE CLASS

Redo size 1839872 2

SQL > select (1745704-582728) redo1, (1839872-1745704) redo2 from dual

REDO1 REDO2

--

1162976 94168

SQL > drop table t

Table dropped.

SQL > create table t nologging as select * from dba_objects where 1: 2

Table created.

SQL > select name,value,class from v$sysstat where name='redo size'

NAME VALUE CLASS

Redo size 3441836 2

SQL > insert into t select * from dba_objects

10947 rows created.

SQL > select name,value,class from v$sysstat where name='redo size'

NAME VALUE CLASS

Redo size 4660204 2

SQL > insert / * + append*/ into t select * from dba_objects

10947 rows created.

SQL > select name,value,class from v$sysstat where name='redo size'

NAME VALUE CLASS

Redo size 4667180 2

SQL > select (4660204-3441836) redo1, (4667180-4660204) redo2 from dual

REDO1 REDO2

--

1218368 6976

two。 Archiving: comparison of REDO generated by data in three cases of append,nologging,append+nologging

PHP language: SQL > select flashback_on from v$database

FLASHBACK_ON

-

NO

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 13

Next log sequence to archive 15

Current log sequence 15

SQL > select log_mode from v$database

LOG_MODE

-

ARCHIVELOG

SQL > create table t as select * from dba_objects where 1: 2

Table created.

SQL > select name,value from v$sysstat where name='redo size'

NAME VALUE

--

Redo size 155624

SQL > insert into t select * from dba_objects

10947 rows created.

SQL > select name,value from v$sysstat where name='redo size'

NAME VALUE

--

Redo size 1316420

SQL > insert / * + append*/ into t select * from dba_objects

10947 rows created.

SQL > select name,value from v$sysstat where name='redo size'

NAME VALUE

--

Redo size 2461876

SQL > select (1316420-155624) redo1, (2461876-1316420) redo2 from dual

REDO1 REDO2

--

1160796 1145456

SQL > truncate table t

Table truncated.

SQL > alter table t nologging

Table altered.

SQL > select name,value from v$sysstat where name='redo size'

NAME VALUE

--

Redo size 2505320

SQL > insert into t select * from dba_objects

10947 rows created.

SQL > select name,value from v$sysstat where name='redo size'

NAME VALUE

--

Redo size 3667856

SQL > insert / * + append*/ into t select * from dba_objects

10947 rows created.

SQL > select name,value from v$sysstat where name='redo size'

NAME VALUE

--

Redo size 3670424

SQL > select (3667856-2505320) redo1, (3670424-3667856) redo2 from dual

REDO1 REDO2

--

1162536 2568

Summary:

Normal

Append

Nologging

Append+nologging

Noarchive

1162976

94168

1218368

6976

Archive

1160796

1145456

1162536

2568

You can see

1. No matter which mode, append has to be combined with nologging in order to achieve good results.

two。 Without archiving and archiving, it is ineffective to use NOLOGGING alone.

3. Append has achieved good results without archiving, but it is not as good as the combination with nologging.

4. Filing and issuing an order append has no effect.

You'd better make a backup after the NOLOGGING is plugged in.

In addition, if the library is in FORCELOGGING mode, the nologging mode is invalid at this time, which I have also tested.

There are also discussions on ITPUB about when NOLOGGING will take effect.

Http://www.itpub.net/showthread.php?threadid=239905

Eygle also did this experiment.

Http://www.eygle.com/faq/Nologging&append.htm

Originally posted by jwzl at 2004-12-6 09:02:

I want to confirm whether the / * + append*/ system can log or not.

Many people say that they do not write logs, but why is there another parameter, nologing?

If there is no diary, it will be very troublesome.

Write a different amount of log ^ _ ^.

Summarize the information about redo log in the following example.

Pure insert 587624

Insert with nologging 585496

Insert with append 2240

Insert with append & nologging 400

[quote]

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0-Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0-Production

SQL > drop table t

Table dropped.

SQL > create table t nologging as select * from dba_objects where null = null

Table created.

SQL > set autot traceonly stat

SQL > insert into t select * from dba_objects

5888 rows created.

Statistics

271 recursive calls

773 db block gets

12653 consistent gets

0 physical reads

587624 redo size

617 bytes sent via SQL*Net to client

539 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

5888 rows processed

SQL > insert into t nologging select * from dba_objects

5888 rows created.

Statistics

28 recursive calls

721 db block gets

12654 consistent gets

0 physical reads

585496 redo size

618 bytes sent via SQL*Net to client

549 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

5888 rows processed

SQL > insert / * + append*/ into t select * from dba_objects

5888 rows created.

Statistics

29 recursive calls

31 db block gets

12526 consistent gets

0 physical reads

2240 redo size

603 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

5888 rows processed

SQL > commit

Commit complete.

SQL > insert / * + append*/ into t nologging select * from dba_objects

5888 rows created.

Statistics

7 recursive calls

8 db block gets

12517 consistent gets

0 physical reads

400 redo size

603 bytes sent via SQL*Net to client

561 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

5888 rows processed

SQL > commit

Commit complete.

SQL > exit

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.