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

Thanks for all the feedback from everyone, it's looks like GT's should be avoided at certain times. So the real question is this. How do I avoid using them in PL/SQL? It seems like I could use a memory-only structure like Nested Tables or Varrays but what happens when 15 thousand rows (or as high as 500k) are inserted into the memory-only structure? Also, I'm confused how I should implement them. Would someone give write a bit of psuedo code on how you would handle this? Any other suggestions? Here is a short description of my current app.

I have a package that issues a query like this in a loop every second for one minute.

mysql := 'insert into global_temp_tab (col1, col2) select * from remote_tab@'||db_link;
execute immediate mysql;

At the end of the minute, it does something like this.

for myrow in (select * from global_temp_tab g, lookup_tab l, lookup_tab2, etc. where g.col1=l.col1, etc.)
 process each row and insert into another permanent table loop

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b3pojm$975$1$8300dec7_at_news.demon.co.uk...
>
> 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.
> >
>
>
>
Received on Sun Mar 02 2003 - 09:30:22 CST