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" >