Thanks for the example.
December 21, 2007 - 2pm Central time zone
Reviewer: Tom
Yes, there are definitely larger fish to fry, and we are frying them...
We need to create a process that affects approximately 10-50 million records (extreme and
unpredictable variance) bi weekly. We want to use the merge statement to affect a single SQL
statement to do the updates and inserts. Due to the variance in number of records we receive we
need to ensure we limit the overhead wherever possible to fit within our scheduled job time.
Your example gives me the response I need, I may have confused "the myth" with the oracle
recommendation of having trailing null columns at the end of a table definition to limit row size.
Thanks again for the great response.
Is 10gR2 different ?
December 27, 2007 - 4pm Central time zone
Reviewer: Tamil from Atlanta, USA
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 27 16:38:10 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: tamil
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table t10 purge;
Table dropped.
SQL> create table t10 (id1 number(10), big_vc varchar2(4000), id2 number(10));
Table created.
SQL> insert into t10 select 1, rpad('*',2000,'*') , 2 from all_source;
105246 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T10');
PL/SQL procedure successfully completed.
SQL> update t10 set id1 = 9999999999;
105246 rows updated.
SQL> select used_ublk from v$transaction ;
USED_UBLK
----------
2310 ------------>
SQL> update t10 set id2 = 9999999999;
105246 rows updated.
SQL> select used_ublk from v$transaction ;
USED_UBLK
----------
3695 --------------->
It seems to be that some thing changed in 10gR2 undo mechanism.
Tamil
Is 10gR2 different?
December 27, 2007 - 4pm Central time zone
Reviewer: Tamil from Atlanta, USA
My 2nd test includes "commit" after the first update:
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T10');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> update t10 set id1 = 9999999999;
105246 rows updated.
SQL>
SQL> select used_ublk from v$transaction ;
USED_UBLK
----------
2310 --->
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> update t10 set id2 = 9999999999;
105246 rows updated.
SQL>
SQL>
SQL> select used_ublk from v$transaction ;
USED_UBLK
----------
2417 --->
The number of undo blocks are not same.
Tamil
Followup December 28, 2007 - 4pm Central time zone:
well, in my example, I had one row per block (8k block, over 4000 bytes for a row, one row per block)
you have more than one row per block, you are probably MIGRATING rows after the second update (that would happen if you update a random column - doesn't matter the position)
Let me just flip flop your results and I can show that updating the last column generates less undo :) (but only when the update to the last column does not migrate the row!!)
ops$tkyte%ORA10GR2> create table t10 (id1 number(10), big_vc varchar2(4000), id2 number(10));
Table created.
ops$tkyte%ORA10GR2> insert into t10 select 1, rpad('*',2000,'*') , 2 from all_objects;
49834 rows created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T10');
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> update t10 set id2 = 9999999999;
49834 rows updated.
ops$tkyte%ORA10GR2> select used_ublk from v$transaction ;
USED_UBLK
----------
992
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t10 set id1 = 9999999999;
49834 rows updated.
ops$tkyte%ORA10GR2> select used_ublk from v$transaction ;
USED_UBLK
----------
1042
No Row Migration
December 29, 2007 - 9am Central time zone
Reviewer: Tamil from Atlanta GA USA
>>you have more than one row per block, you are probably MIGRATING rows after the second update
(that would happen if you update a random column - doesn't matter the position)
Yes, I have more than one row per block.
But after the 2nd update, row migration did not happen.
SQL> select count(*) from t10;
COUNT(*)
----------
105246
SQL> analyze table t10 list chained rows ;
Table analyzed.
SQL> select * from CHAINED_ROWS ;
no rows selected
Tamil
Followup January 1, 2008 - 5pm Central time zone:
if not migrated, then the block probably needed some reorganization on the second update, to avoid the row migrating...
did you do it in reverse order - to "prove" that updating the last column generates LESS undo than the first did?
that is the point here - it is the SECOND UPDATE that generated SLIGHTLY more undo - not that you updated the last column....
More or less undo
January 2, 2008 - 5pm Central time zone
Reviewer: Tamil from Atlanta GA USA
>>did you do it in reverse order - to "prove" that updating the last column generates LESS undo
than the first did?
>>that is the point here - it is the SECOND UPDATE that generated SLIGHTLY more undo - not that you
updated the last column....
OK, I got what you are saying..
I did the reverse test.
SQL> insert into t10 select 1, rpad('*',2000,'*') , 2 from all_source;
105246 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T10');
PL/SQL procedure successfully completed.
SQL> update t10 set id2 = 9999999999;
105246 rows updated.
SQL> select used_ublk from v$transaction ;
USED_UBLK
----------
2310 --->
SQL> commit;
Commit complete.
SQL> update t10 set id1 = 9999999999;
105246 rows updated.
SQL> select used_ublk from v$transaction ;
USED_UBLK
----------
2417 --->
SQL> spool off
Thanks for your help.
Tamil
|