Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issues with GTT.??
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 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" <learning_still_at_hotmail.com> wrote in message news:6093c29e.0406050214.699388b_at_posting.google.com...Received on Sun Jun 06 2004 - 03:37:58 CDT
> Hi,
>
> 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