Re: Slow insert in GTT

From: Amir Gheibi <gheibia_at_gmail.com>
Date: Thu, 26 Feb 2009 01:26:27 +0800
Message-ID: <c906cd460902250926p6c91f492t2de77eaf1506e3e2_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 25 2009 - 11:26:27 CST