Re: Slow insert in GTT
- From: Amir Gheibi <gheibia@xxxxxxxxx>
- To: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- Date: Thu, 26 Feb 2009 01:21:52 +0800
Thanks for all the replies.. appreciate it..
Yes, SP stands for Stored Procedure.. Sorry for the confusion..
Regarding the PARALLEL hint, I can't agree more that it didn't make it
better.. it was obvious as when I used it, Oracle couldn't even finish the
whole Stored Procedure in a given time...
As for the Where clause, here it is:
WHERE T1.some_numeric_field = variable1
AND TT.startDate <= variable2
AND TT.endDate >= variable3
AND TT.IsDeleted = 0
AND T1.IsDeleted = 0
AND T2.IsDeleted = 0 );
variable1, variable2, variable3 are Stored Procedure's input variables.
I'm sorry that I'm not able to share everything about the Stored Procedure
and the data structure. But as it can be seen from the trace file, the temp
table (from which we are selecting) contains 400,000 record and the other
two permanent tables that are joined with the temp table in the select
statement are very small.
On Wed, Feb 25, 2009 at 10:03 PM, Stefan Knecht <knecht.stefan@xxxxxxxxx>wrote:
> a "Stored Procedure" I'd say ? :)
>
>
> =========================
>
> Stefan P Knecht
> CEO & Founder
> s@xxxxxxxx
>
> 10046 Consulting GmbH
> Schwarzackerstrasse 29
> CH-8304 Wallisellen
> Switzerland
>
> Phone +41-(0)8400-10046
> Cell +41 (0) 79 571 36 27
> info@xxxxxxxx
> http://www.10046.ch
>
> =========================
>
>
>
> On Wed, Feb 25, 2009 at 2:56 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
>
>> 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:
