Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issues with GTT.??

Re: Performance issues with GTT.??

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 6 Jun 2004 08:37:58 +0000 (UTC)
Message-ID: <c9ul56$sua$1@hercules.btinternet.com>

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

> 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
Received on Sun Jun 06 2004 - 03:37:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US