| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Archive Log affects performance?
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...Received on Sat Jan 19 2002 - 23:40:55 CST
> 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
>
 |
 |