| 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?
In article <3c4a5820$0$6772$afc38c87_at_news.optusnet.com.au>, "Howard says...
>
>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:
>
singleton inserts vs bulk inserts, singleton updates vs bulk updates.
Hopefully - they would be using bulk operations like this:
scott_at_ORA817DEV.US.ORACLE.COM> set autotrace on stat scott_at_ORA817DEV.US.ORACLE.COM> update emp set ename = 'barney';
14 rows updated.
Statistics
0 recursive calls
20 db block gets
1 consistent gets
0 physical reads
3748 redo size
847 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
scott_at_ORA817DEV.US.ORACLE.COM> insert into emp select * from emp;
14 rows created.
Statistics
0 recursive calls
5 db block gets
2 consistent gets
0 physical reads
876 redo size
846 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
scott_at_ORA817DEV.US.ORACLE.COM>
(no indexes or anything)
any many ETL tools and programs out there do tend to update every column in many cases, even if only to set it back to itself (easier sometimes to update all columns then to figure out statement by statement what columns exactly to update).
Even doing singleton updates/inserts tho -- when you do more then one:
scott_at_ORA817DEV.US.ORACLE.COM> column value new_val V scott_at_ORA817DEV.US.ORACLE.COM> scott_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b
NAME VALUE ------------------------------ ---------- redo size 18028
scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> declare
2 l_cnt number := 0;
3 begin
4 for x in ( select rowid rid from emp ) 5 loop 6 update emp set ename = 'barney' where rowid = x.rid; 7 l_cnt := l_cnt + sql%rowcount; 8 end loop; 9 dbms_output.put_line( l_cnt );10 end;
PL/SQL procedure successfully completed.
scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and (a.name) = 'redo size'
5 /
old 1: select a.name, b.value, b.value-&V diff
new 1: select a.name, b.value, b.value- 18028 diff
NAME VALUE DIFF ------------------------------ ---------- ---------- redo size 21828 3800 scott_at_ORA817DEV.US.ORACLE.COM>
4 for x in ( select rowid rid from emp ) 5 loop 6 insert into emp select * from emp where rowid = x.rid; 7 l_cnt := l_cnt + sql%rowcount; 8 end loop; 9 dbms_output.put_line( l_cnt );10 end;
PL/SQL procedure successfully completed.
scott_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and (a.name) = 'redo size'
5 /
old 1: select a.name, b.value, b.value-&V diff
new 1: select a.name, b.value, b.value- 21828 diff
NAME VALUE DIFF ------------------------------ ---------- ---------- redo size 25400 3572
scott_at_ORA817DEV.US.ORACLE.COM>
scott_at_ORA817DEV.US.ORACLE.COM> rollback;
Rollback complete.
14 updates of a single column, 14 inserts of an entire row -- 3800 vs 3572... If I do it on a sizable table (create table t as select * from all_objects)
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> column value new_val V ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value2 from v$statname a, v$mystat b
NAME VALUE ------------------------------ ---------- redo size 75324
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare
2 l_cnt number := 0;
3 begin
4 for x in ( select rowid rid from T ) 5 loop 6 update T set object_name = 'barney' where rowid = x.rid; 7 l_cnt := l_cnt + sql%rowcount; 8 end loop; 9 dbms_output.put_line( l_cnt );10 end;
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and (a.name) = 'redo size'
5 /
old 1: select a.name, b.value, b.value-&V diff
new 1: select a.name, b.value, b.value- 75324 diff
NAME VALUE DIFF ------------------------------ ---------- ---------- redo size 4719476 4644152 ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
4 for x in ( select rowid rid from T ) 5 loop 6 insert into T select * from T where rowid = x.rid; 7 l_cnt := l_cnt + sql%rowcount; 8 end loop; 9 dbms_output.put_line( l_cnt );10 end;
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and (a.name) = 'redo size'
5 /
old 1: select a.name, b.value, b.value-&V diff
new 1: select a.name, b.value, b.value- 4719476 diff
NAME VALUE DIFF ------------------------------ ---------- ---------- redo size 9782104 5062628
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;
Rollback complete.
So there the insert did beat the update in amount of redo generated but I would not classify the redo generated by the UPDATE as "trivial" as compared to the insert...
>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
>>
>
>
-- 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 CorpReceived on Sun Jan 20 2002 - 09:41:52 CST
 |
 |