Global Temp Tables - lots of redo in 9i, why? how to fix?

I found that tons of redo is generated when uses Global Temporary Tables in 9i. Here was my test. I tested in 8.1.7, 9i and 9i Release 2. 8i does not have the same problem.

1. Drop large redo logs and create small ones (64k in my case) so I can tell how much redo is being generated. I'll count the log switches.
2. Create two tables, one as global temp the other normal.
3. Insert into each table (in an idle db) and see how many logs are switched.

--Create the tables

create global temporary table tabGT as select * from dba_objects where rownum<1;
create table tabNORM as select * from dba_objects;
--Check the log sequence number

select * from (select * from v$log_history order by first_time desc) where rownum=1;
--Insert into GT

insert into tabGT select * from dba_objects;
--Check the log sequence number

select * from (select * from v$log_history order by first_time desc) where rownum=1;
--Insert into Normal Table

insert into tabGT select * from dba_objects;
--Check the log sequence number

select * from (select * from v$log_history order by first_time desc) where rownum=1;

In 8i, the global temp table insert would cause 5% of the redo compared to the normal table. But in 9i, the redo generated by the insert into the GT is equivalent to the redo generated by the insert into the normal table. What gives? How do I fix it? Received on Fri Feb 28 2003 - 17:37:17 CST