Re: Insert append nologging

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 28 Feb 2007 12:09:53 -0800
Message-ID: <1172693393.036487.211460@q2g2000cwa.googlegroups.com>

On Feb 28, 2:53 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> 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

Thank you! I learned something new today :)

SQL> truncate table t0228;

Table truncated.

SQL> alter table t0228 logging;

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

---

     12028

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> archive log list;

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7

SQL> truncate table rep.t0228;

Table truncated.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 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 archivelog;

Database altered.

SQL> alter database open;

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

---

    208192

SQL> Received on Wed Feb 28 2007 - 14:09:53 CST