FW: Slow insert in GTT

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 25 Feb 2009 14:04:10 -0500
snipped to fit.

 

  _____  

From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Wednesday, February 25, 2009 2:02 PM
To: 'gheibia@xxxxxxxxx'; 'hrishys@xxxxxxxxxxx'
Cc: 'toon.koppelaars@xxxxxxxxxxx'; 'ORACLE-L'
Subject: RE: Slow insert in GTT

 

Previously you sent:

 

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)

 

So 7.66 is the full table scan. The rest altogether is about 1.5ish. I don't
know where the filter operation is on the startdate, endate, or the three
isdeleted columns.

 

You mention that the table is used multiple times with in the stored
procedure.

 

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.

 

Well creating the index will have to do this same full table scan once, plus
a bit of overhead to actually sort and create the index, so the question is
whether there is a useful way to build an index that will improve the
operation of not only this select feeding your insert, but also some of the
other steps without incurring excessive maintenance costs on the index. If
not, you might consider building a single table cluster on startdate or
enddate, depending on which one tends to be more selective. If you're not
sure and don't have a good way to predict which is more selective, use
startdate because most human minds function thinking of time rolling
forward. Of course if all the startdate values are routinely less than the
variable2, then you'll still be doing effectively a full table scan.

 

I suppose you're not using a pl/sql table due to concerns about pga space.
Since it's a gtt it can't be loss of data concerns.

 

When I see something like this I have to fight the presumption that the
whole processing cycle needs to be examined to build a structure that will
be highly concurrent and performant, and that tuning one query fragment is
more of an academic exercise or puzzle than really doing you a favor.

 

Hmm - I wonder if you can't gate do the insert in the first place based on
variable2 and variable3. Again we have no idea whether that would be
selective. But you might.

 

Good luck. Sorry this was long. It's just off my fingers, so there might be
mistakes.

 

mwf

 

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Amir Gheibi
Sent: Wednesday, February 25, 2009 12:26 PM
To: hrishys@xxxxxxxxxxx
Cc: toon.koppelaars@xxxxxxxxxxx; ORACLE-L
Subject: Re: Slow insert in GTT

 

This isn't a batch program.. this temp table ( ANOTHER_TEMP_TBL TT) is
filled inside a stored procedure with about 400,000 records and then used in
the same SP in multiple places.. this is one of <SNIP>
FW: Slow insert in GTT

Other related posts:
