Performance issues with GTT.??
Newbie
Has anyone faced any performance issues with use of Global Temporary
Tables?
We have an application where a piece of code within a loop inserts
38000 records into an Oracle Temp table. Using pl/sql developer's
profiler feature, we found that INSERTs into the temp table is taking
almost 70% of the total execution time. I am not sure how to measure
other performance parameters and bottlenecks. But are there issues
with using TEMP tables having to do with so much INSERTs.?? TEMP
tables reside in temporary tablespace. So does that mean INSERT of
38000 records will actually be 38000 hits to disk (I/O) ??
O9i - 9.2.0.2
OS - HP Unix
I am lost here. Could anyone please help?
TIA
Howard J. Rogers
"Newbie" <learnin...@hotmail.com> wrote in message
news:6093c29e.0406...@posting.google.com...
> Hi,
>
> Has anyone faced any performance issues with use of Global Temporary
> Tables?
Lots of people. There are some nasty bugs...
>
> We have an application where a piece of code within a loop inserts
> 38000 records into an Oracle Temp table. Using pl/sql developer's
> profiler feature, we found that INSERTs into the temp table is taking
> almost 70% of the total execution time. I am not sure how to measure
> other performance parameters and bottlenecks. But are there issues
> with using TEMP tables having to do with so much INSERTs.?? TEMP
> tables reside in temporary tablespace. So does that mean INSERT of
> 38000 records will actually be 38000 hits to disk (I/O) ??
>
> O9i - 9.2.0.2
> OS - HP Unix
> I am lost here. Could anyone please help?
>
> TIA
All I can say is: why not consider (ie, assess and evaluate) upgrades to the
latest patch level (.0.5 if I'm not much mistaken).
And then what I'd say is: why are you using temporary tables in the first
place? Are you *sure* their use is required, because Oracle's
read-consistency model means it very seldom is.
Check Google for reference to bugs with temporary tables in this newsgroup
in the past. It has come up many times.
Sorry not to be much more help than that.
Regards
HJR
Jim Kennedy
Are you using bind variables? row by row is slow by slow. Do things in a
set basis if possible.
Jim
Connor McDonald
One bug with gtt's if I remember correctly was excessive redo
generation. If you've got a poor redo subsystem, pummmelling gtt's
might make it worse.
hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_...@yahoo.com
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
Jonathan Lewis
I wouldn't expect the temporary tablespace to
get hit 38,000 times.
How much time does the loop take, anyway ?
Something has to be the most expensive line,
and if the pl/sql is trivial it should be surprising
that the SQL should appear relatively expensive.
How many indexes on the GTT ?
How long are the rows ?
Is the insert doing an insert of values, or is it
doing: insert ... select aggregate ?
There is a documented bug about excess redo
generated by GTTs compared to standard tables;
but that does apply to single row inserts, just to
array inserts (typically insert select {lots of rows})
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
"Newbie" <learnin...@hotmail.com> wrote in message
news:6093c29e.0406...@posting.google.com...