Re: Insert append nologging

On Feb 28, 2:36 pm, "Steve Howard" <stevedhow..._at_gmail.com> wrote:
> 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
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
> 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?

We are talking about append + combinations of logging/nologging.

A normal insert will produce the SAME amount of redo in archivelog or noarchivelog mode.

You seem to be testing the wrong thing. Maybe my comment was not clear?

You'll see:

append + logging + archivelog = more redo
append + nologging + archivelog = less redo
append + logging + noarchivelog = less redo
append + nologging + noarchivelog = less redo

SO archivelog/noarchivelog makes a difference when append + logging is there.

Anurag Received on Wed Feb 28 2007 - 13:53:46 CST