Re: Slow insert in GTT
- From: Amir Gheibi <gheibia@xxxxxxxxx>
- To: hrishys@xxxxxxxxxxx
- Date: Wed, 25 Feb 2009 19:03:09 +0800
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
> >
> > (co)Author: "Applied Mathematics for Database
> > Professionals"
>
>
>
>
INSERT INTO TT_TBL (...)
SELECT
TT.*
FROM ANOTHER_TEMP_TBL TT
LEFT JOIN T1 ON TT.FK = T1.PK
LEFT JOIN T2 ON T1.FK = T2.PK
WHERE ...
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 695 1.57 1.63 0 65600 528 635
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 696 1.57 1.63 0 65600 528 635
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
635 HASH JOIN (cr=65597 pr=0 pw=0 time=1361047 us)
628 NESTED LOOPS (cr=17241 pr=0 pw=0 time=300294 us)
628 TABLE ACCESS FULL T1 (cr=15985 pr=0 pw=0 time=144925 us)
628 TABLE ACCESS BY INDEX ROWID T2 (cr=1256 pr=0 pw=0 time=86924 us)
628 INDEX UNIQUE SCAN PK_T2 (cr=628 pr=0 pw=0 time=35798 us)(object id
70300)
398780 TABLE ACCESS FULL TT (cr=48356 pr=0 pw=0 time=7659565 us)
*****************************************************************************************************
INSERT INTO TT_TBL (...)
SELECT /*+ PARALLEL(T1, 2) PARALLEL(T2, 2) */
TT.*
FROM ANOTHER_TEMP_TBL TT
LEFT JOIN T1 ON TT.FK = T1.PK
LEFT JOIN T2 ON T1.FK = T2.PK
WHERE ...
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.15 6.67 0 515 14 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.15 6.67 0 515 14 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Join ACK 18 0.00 0.01
PX qref latch 884 0.00 0.04
PX Deq Credit: send blkd 27 1.97 5.87
PX Deq: Parse Reply 20 0.01 0.02
PX Deq: Execute Reply 171 0.00 0.01
PX Deq: Table Q Normal 10 0.00 0.00
PX Deq: Signal ACK 22 0.10 0.49
enq: PS - contention 7 0.03 0.04
PX Deq Credit: need buffer 3 0.00 0.00
*****************************************************************************************************
Other related posts:
