Steve Howard schreef:
> 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?
>
There's one combo you forgot about (and it finally became
clear to me):
(As Tom puts it:)
Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
In your table:
LOGGING ARCHIVELOGMODE APPEND REDO
Yes Yes Yes ???
And that combo is the one exception (force_logging on db level
not taken in account)
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Wed Feb 28 2007 - 14:16:35 CST
