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

There seems to be a bug in 9 with global temporary tables. When doing array inserts, Oracle 8 generates one redo record per
BLOCK affected in v8. Under v9, you get
one redo record per ROW inserted. Since a lot of the redo record is typically overhead rather than data in GTTs, the relative
change is dramatic.

NB A slightly clearer test, if you have the system to yourself.

    select * from v$sysstat where name like '%redo%'; before and after the test, and find the differences.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Dean Smith" <idontthinkso_at_nope.com> wrote in message
news:NoS7a.302335$Ec4.297105_at_rwcrnsc52.ops.asp.att.net...


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

>