Re: Slow insert in GTT
- From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- To: Amir Gheibi <gheibia@xxxxxxxxx>
- Date: Wed, 25 Feb 2009 14:19:09 +0100
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:
