Re: Slow insert in GTT
Date: Wed, 25 Feb 2009 09:10:05 +0100
Message-ID: <ecf3dae70902250010l18446d4cs9217c044baadfcac_at_mail.gmail.com>
I would suggest creating a SQL trace file. And start your analysis from there.
On Wed, Feb 25, 2009 at 8:49 AM, Amir Gheibi <gheibia_at_gmail.com> wrote:
> Hi listers,
> What could cause an Insert in a Global Temporary Table takes a lot of time?
> This is how I do the insert:
>
> Insert into TT (...)
> Select .. From ...
>
> The insert is being done in a SP.
> The Temp table is created with the "on commit preserve rows" option and
> that's because the table is used later in the SP.
>
> The select statement might returns 10,000 records.
> I have used a couple of hints in the select statement and the "Append" hint
> in the Insert (even though I don't like to use append as it locks the table)
> but still have the issue.
> I also understand that the PARALLEL hint will be ignored for inserts on
> GTTs. So I can't use that either.
>
> Any Idea, please?
>
> One more thing, the select statement is built upon a join between another
> temp table and a couple of permanent tables.
>
> Thanks.
> Amir
>
-- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars_at_RuleGen.com www.RuleGen.com (co)Author: "Applied Mathematics for Database Professionals" -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 25 2009 - 02:10:05 CST