Anurag Varma schreef:
> 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
>
As I replied to Jerome: I had to reread a couple of times,
but he is correct.
As you two seem to concur, so are you.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Wed Feb 28 2007 - 14:15:50 CST
