Re: Slow insert in GTT

From: Amir Gheibi <gheibia_at_gmail.com>
Date: Wed, 25 Feb 2009 19:03:09 +0800
Message-ID: <c906cd460902250303q7122334evdab862d7704b8b34_at_mail.gmail.com>

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




text/plain attachment: result.txt

Received on Wed Feb 25 2009 - 05:03:09 CST