Re: Slow insert in GTT
Date: Wed, 25 Feb 2009 18:46:26 +0100
Message-ID: <ecf3dae70902250946j1ff1e783s115759c707e6cf4e_at_mail.gmail.com>
Why create/destroy the index everytime?
You can have a permanent index on the temporary table. No problem. Might be benificial.
On Wed, Feb 25, 2009 at 6:26 PM, Amir Gheibi <gheibia_at_gmail.com> wrote:
> This isn't a batch program.. this temp table ( ANOTHER_TEMP_TBL TT) is
> filled inside a stored procedure with about 400,000 records and then used in
> the same SP in multiple places.. this is one of them.. 400,000 record. Isn't
> that too big to create an Index everytime and destroy it?
>
>
> On Wed, Feb 25, 2009 at 7:22 PM, hrishy <hrishys_at_yahoo.co.uk> wrote:
>
>> Hi
>>
>> If this is a batch program.
>> You can also create a index on that table ANOTHER_TEMP_TBL TT on TT.FKafter the dataload and drop it onse the insert into the main temporary table
>> is done.
>>
>> regards
>> Hrishy
>>
>>
>> --- On Wed, 25/2/09, Amir Gheibi <gheibia_at_gmail.com> wrote:
>>
>> > From: Amir Gheibi <gheibia_at_gmail.com>
>> > Subject: Re: Slow insert in GTT
>> > To: hrishys_at_yahoo.co.uk
>> > Cc: toon.koppelaars_at_rulegen.com, "ORACLE-L" <oracle-l_at_freelists.org>
>> > Date: Wednesday, 25 February, 2009, 11:03 AM
>> > The problem is that the Select statement itself is using
>> > another temp table
>> > which will not have any value until runtime. So in order to
>> > know whether my
>> > changes (either in the select command or the table
>> > structure) can resolve
>> > the issue or not, I have to make the change and then run
>> > the application and
>> > trace the SQL commands. Then analyze the outcome. Tedious!
>> > So far I only
>> > tried a couple of hints. I don't want to make any index
>> > on the Temp Table
>> > (the one that is used in the select statement) cause the
>> > table is gonna have
>> > 400,000 records in runtime.
>> > I put together in one file (attached) a couple of snippets
>> > from the Sql
>> > Trace files ... first one is the original slow Insert and
>> > the second one is
>> > the one in which I used a hint.. (both trace files were
>> > translated using
>> > TKPROF)..
>> > The Second one actually caused Oracle to break with the
>> > "ORA-01013" error
>> > (user requested cancel of current operation) which means
>> > the connection
>> > reached its timeout and Oracle stopped processing.. So the
>> > hint was a bad
>> > idea..
>> >
>> > Any idea and help is appreciated...
>> >
>> > ~ Amir
>> >
>> >
>> >
>> > On Wed, Feb 25, 2009 at 4:35 PM, hrishy
>> > <hrishys_at_yahoo.co.uk> wrote:
>> >
>> > > I am willing to bet that your select statment that
>> > feeds into tho GTT is
>> > > the culprit and you might try running the sql select
>> > statement alone and see
>> > > how slow or fast it is
>> > >
>> > > regards
>> > > Hrishy
>> > >
>> > >
>> > > --- On Wed, 25/2/09, Toon Koppelaars
>> > <toon.koppelaars_at_rulegen.com> wrote:
>> > >
>> > > > From: Toon Koppelaars
>> > <toon.koppelaars_at_rulegen.com>
>> > > > Subject: Re: Slow insert in GTT
>> > > > To: gheibia_at_gmail.com
>> > > > Cc: "ORACLE-L"
>> > <oracle-l_at_freelists.org>
>> > > > Date: Wednesday, 25 February, 2009, 8:10 AM
>> > > > 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"
>> > >
>> > >
>> > >
>> > >
>>
>>
>>
>>
>
-- 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 - 11:46:26 CST