FW: Slow insert in GTT

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Wednesday, February 25, 2009 2:02 PM To: 'gheibia_at_gmail.com'; 'hrishys_at_yahoo.co.uk' Cc: 'toon.koppelaars_at_rulegen.com'; '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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amir Gheibi
Sent: Wednesday, February 25, 2009 12:26 PM To: hrishys_at_yahoo.co.uk
Cc: toon.koppelaars_at_rulegen.com; 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>  

--
http://www.freelists.org/webpage/oracle-l