Re: Insert append nologging

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 28 Feb 2007 11:36:09 -0800
Message-ID: <1172691368.950043.90250@8g2000cwh.googlegroups.com>

On Feb 28, 2:06 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> ok .. now you are confusing undo and redo.
>
> APPEND hint still generates REDO in archivelogmode.
> It generates MINIMAL UNDO .. and hence the redo
> for the undo is minimal.
>
> So APPEND + LOGGING + ARCHIVE LOG will generate MORE
> redo than APPEND + NOLOGGING + ARCHIVE LOG.
>
> Now When a database is set to NOARCHIVELOG, it does
> not matter if the object is set to LOGGING OR NOLOGGING.
> The redo is nearly zero.
>
> Here, you might want to read this:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8...
>
> Anurag

Interesting discussion, but I can't prove it...

O:\>sqlplus sys/******** as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 28 14:15:43 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

SQL> connect rep/rep
Connected.
SQL> create table t0228(c varchar2(4000));

Table created.

SQL> insert into t0228 select rpad('x',4000,'x') from dba_users;

24 rows created.

SQL> commit;

Commit complete.

SQL> select value from v$mystat where statistic# in(select statistic# from v$statname where name = 'redo size');

VALUE

124956

SQL> alter table t0228 nologging;

Table altered.

SQL> insert /*+ append */ into t0228 select rpad('x',4000,'x') from dba_users;

24 rows created.

SQL> commit;

Commit complete.

SQL> select value from v$mystat where statistic# in(select statistic# from v$statname where name = 'redo size');

VALUE

137812

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> startup force mount
ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size                  1247900 bytes
Variable Size              92276068 bytes
Database Buffers           71303168 bytes
Redo Buffers                2945024 bytes

Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> connect rep/rep
Connected.
SQL> insert /*+ append */ into t0228 select rpad('x',4000,'x') from dba_users;

24 rows created.

SQL> commit;

Commit complete.

SQL> select value from v$mystat where statistic# in(select statistic# from v$statname where name = 'redo size');

VALUE

10868

SQL> insert into t0228 select rpad('x',4000,'x') from dba_users;

24 rows created.

SQL> commit;

Commit complete.

SQL> select value from v$mystat where statistic# in(select statistic# from v$statname where name = 'redo size');

VALUE

129276

SQL> Sorry for the formatting...

LOGGING 	ARCHIVELOGMODE 		APPEND	REDO
Yes		Yes			No	~125K
No		Yes			Yes	~10K
No		No			No	~120K
No		No			Yes	~10K

So when we change from archivelog to noarchivelog mode, we generate the same amount of redo, given a "non-APPENDed" insert.

I'm not sure I even understand conceptually why NOARCHIVELOG mode would generate less redo. What about instance recovery? Received on Wed Feb 28 2007 - 13:36:09 CST