RE: Slow insert in GTT
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <toon.koppelaars@xxxxxxxxxxx>, "'Amir Gheibi'" <gheibia@xxxxxxxxx>
- Date: Wed, 25 Feb 2009 08:56:29 -0500
What Toon wrote, but I'm just curious what "a SP" is.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Toon Koppelaars
Sent: Wednesday, February 25, 2009 8:19 AM
To: Amir Gheibi
Cc: hrishys@xxxxxxxxxxx; 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@xxxxxxxxx> 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@xxxxxxxxxxx> 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@xxxxxxxxxxx> wrote:
> From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
> Subject: Re: Slow insert in GTT
> To: gheibia@xxxxxxxxx
> Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxx> 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@xxxxxxxxxxx
> www.RuleGen.com <http://www.rulegen.com/>
>
> (co)Author: "Applied Mathematics for Database
> Professionals"
--
Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
(co)Author: "Applied Mathematics for Database Professionals"
Other related posts:
