Re: Archive Log affects performance?

Is it me?

or does 660 bytes of redo for a < 5 byte change seem OTT?

No wonder redo logs can be the performance bottleneck on high transaction rate systems!

Howard J. Rogers <dba_at_hjrdba.com> wrote in message news:3c4a5820$0$6772$afc38c87_at_news.optusnet.com.au...
> Well, if you're going to update every column in the table, you can of
course
> expect my statement not to hold -but that would (I believe) be a pretty
> unusual update! I suppose if I'd stuck the word 'typical' in front of
> 'update' and 'insert', I'd have been nearer the mark:
>
> SQL> insert into emp (empno, ename,job,mgr,sal,comm,deptno) values
> (123,'SMITH','CLERK',7934,467.78,0,10);
>
> 1 row created.
>
> Statistics
> ----------------------------------------------------------
> 151 recursive calls
> 15 db block gets
> 18 consistent gets
> 0 physical reads
> 1172 redo size
> 879 bytes sent via SQL*Net to client
> 723 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 2 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL> update emp set sal=987.56 where empno=123;
>
> 1 row updated.
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 9 db block gets
> 349 consistent gets
> 0 physical reads
> 660 redo size
> 880 bytes sent via SQL*Net to client
> 661 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> 1172 versus 660... around half the redo for the update compared with the
> insert.
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
> news:a2d0e401dnv_at_drn.newsguy.com...
> > 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
> >
>
>
Received on Sun Jan 20 2002 - 07:12:16 CST