This is just intended to demonstrate redo usage of various insert operations rather than answer the whole question. Results on my 10g instance are not 100% deterministic, but the broad picture remained the same each time I ran through.
For the heap tables, I do not know why the insert /*+ append */ generated more redo.
testbed:
create table heap_noappend(id integer, dummy char(500));
create table heap_append(id integer, dummy char(500));
create global temporary table gtt_noappend(id integer, dummy char(500));
create global temporary table gtt_append(id integer, dummy char(500));
create global temporary table gtt_results(stage integer, val integer);
test:
insert into gtt_results(stage, val)
select 0, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert into heap_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 1, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert /*+ append */ into heap_append(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 2, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert into gtt_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 3, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert /*+ append */ into gtt_append(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 4, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
result:
select *
from( select decode(stage,1,'heap noappend',
2,'heap append',
3,'gtt noappend',
4,'gtt append') as operation,
val-lag(val) over(order by stage) as redo
from gtt_results)
where redo is not null;
OPERATION REDO
------------- ----------------------
heap noappend 606932
heap append 690768
gtt noappend 41488
gtt append 256
It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently):
1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately updating them. Perhaps you need to look into insert using select statements to dynamically derive the full value, and then insert the rows in their final state.
2) By inserting 30 million rows, I suspect that you're altering your tables by a value > 10%. Oracle's automated jobs will reanalyze stats on any table with a change of 10% or greater; I make it a practice to do the same thing when I'm inserting/updating/deleting data. This is why you're seeing this behavior when you stop the 2nd procedure and analyze the table.
3) For the update statement, have you properly indexed the columns?
4) What is your expectation for the second procedure's run time? Does 1-2 hours to update 30 million rows sound too long? Only you can know that. For example, in the environment I work in, we can run our databases at 17,000 IOPS, but we force logging on everything because we run a data guard to a DR site, which adds some overhead on operations. So for me, 1-2 hours for a 30 million row update is right about where I normally am. Of course, this also depends on factors like row size (are you updating a single character, or a blob?), the where clause of the update (are you updating based on primary key, a foreign key, or based on an unindexed field?), and the overall load on your system at run time.
HTH.
Best Answer
This turned out to be an issue with OPENQUERY and the related "inconsistent metadata" issue requiring OPTION (RECOMPILE). Switching to EXEC AT for write queries (UPDATE, INSERT and DELETE) resolved the throttling effect.