Re: Archive Log affects performance?

In article <3c49eddf$0$6772$afc38c87_at_news.optusnet.com.au>, "Howard says...
>
>That's what happens when you write posts at 4.00am. You miss juicy little
>bits like that.... but, of course, if it's an update, the redo involved will
>be trivially small in comparison to what an insert would generate, anyway.
>
>Regards
>HJR
really?

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace on stat ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects;

17161 rows created.

Statistics

       2429  recursive calls
       1644  db block gets
      82010  consistent gets
          0  physical reads
    1951956  redo size
       1149  bytes sent via SQL*Net to client
        864  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      17161  rows processed

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update t set   2 owner = owner, object_name = object_name, SUBOBJECT_NAME = SUBOBJECT_NAME, 3 object_id = object_id, data_object_id = data_object_id, object_type = object_type,
  4 created = created, last_ddl_time = last_ddl_time, timestamp = timestamp, 5 status = status, temporary = temporary, generated = generated, secondary = secondary;

17161 rows updated.

Statistics

          0  recursive calls
      18184  db block gets
        235  consistent gets
          0  physical reads
    9182504  redo size
       1009  bytes sent via SQL*Net to client
       1116  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      17161  rows processed

    1951956 redo size inserting...
    9182504 redo size updating...

even in the "less degenerative" case:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update t set object_name = object_name;

17161 rows updated.

Statistics

          0  recursive calls
      17658  db block gets
        469  consistent gets
          0  physical reads
    4948288  redo size
       1010  bytes sent via SQL*Net to client
        819  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      17161  rows processed

>
>--
>----------------------------------------------
>Resources for Oracle: http://www.hjrdba.com
>===============================
>
>
>"Igor Ushkalo" <igorus_at_protek.ru> wrote in message
>news:a2bubn$l65$1_at_news.sovam.com...
>> Hello, Howard!
>> You wrote on Sun, 20 Jan 2002 00:29:58 +1100:
>>
>> HJR> You can switch that off without having to disable archivelog mode
>> HJR> itself merely by altering the relavant table to be 'NOLOGGING' -and
>> HJR> then making sure that this bulk insert is being done by SQL Loader.
>> HJR> If it's just a conventional 'insert' statement you're doing then
>> HJR> NOLOGGING won't do anything for you at all. Otherwise, it will
>> HJR> switch off the generation of all redo -and since nothing is getting
>> HJR> into the online logs, you worries about its impact on ARCH will be
>> HJR> sorted at the same time.
>>
>> But it seems he wants UPDATE table... i dunno any way
>> to do update in nologging mode.
>>
>> Maybe you can get some perfomance gain to switch to noarchivelog
>> if all of your redo-logs are on the same device (mirror); but if logs are
>> situated on
>> two mirrors (even/odd logs on different mirrors) - archiver won't be a
>> problem...
>>
>> -- Best regards, Igor Ushkalo (igorus!) - igorus(at)mail.ru, ICQ #19972198
>>
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp