Re: Slow insert in GTT

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Wed, 25 Feb 2009 15:03:32 +0100
Message-ID: <486b2b610902250603u54cc77dcv87c4ec4d5fd3b2cb_at_mail.gmail.com>

a "Stored Procedure" I'd say ? :)

---

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Wed, Feb 25, 2009 at 2:56 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> What Toon wrote, but I’m just curious what “a SP” is.
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Toon Koppelaars
> *Sent:* Wednesday, February 25, 2009 8:19 AM
> *To:* Amir Gheibi
> *Cc:* hrishys_at_yahoo.co.uk; ORACLE-L
>
> *Subject:* Re: Slow insert in GTT
>
>
>
> I would suggest to stop using Parallel Query for this simple/small
> sql-statement.
>
> It is adding almost 6 seconds to your elapsed time...
>
>
>
> PX Deq Credit: send blkd 27 1.97 *
> 5.87*
>
>
>
> Can you also provide full WHERE-clause? Are there any pl/sql function-calls
> in it?
>
> On Wed, Feb 25, 2009 at 12:03 PM, Amir Gheibi <gheibia_at_gmail.com> wrote:
>
> 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 <http://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-l

Received on Wed Feb 25 2009 - 08:03:32 CST