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

Dean Smith wrote:

> 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?

I don't think there is anything to fix. And I highly doubt the impact is significant enough to matter much to anyone.

What I'd like to know is what is anyone doing that requires GTTs anyway. I've been coding in Oracle for more than a decade and have yet to "need" one.

Daniel Morgan Received on Fri Feb 28 2003 - 19:05:33 CST