Re: SQL Select taking forever

Tom Slattery wrote in message <6lh2js$kfc_at_gosset.maths.tcd.ie>...
>I'm joining on two tables, each about 800,000 rows and inserting the
>resul into a table.
 

>INSERT INTO

 <snipped>
> SELECT
> /*+ USE_NL(d) */

<snipped>
> TO_DATE(TO_CHAR(d.clearing_date),'YYYYMMDD'),
> TO_DATE(TO_CHAR(d.date_comp_w_o), 'YYYYMMDD'),
> FROM
> devdata.w_o_transaction t,
> devdata.w_o_detail d
> WHERE
> t.sms_db_source (+) = d.sms_db_source
> AND
> t.work_order# (+) = d.work_order#

A couple of comments.

The TO_DATE and TO_CHAR functions cause additional overheads, which can be eliminated. Rather use ALTER SESSION SET NLS_DATE_FORMAT to set the date format for that session, instead of applying the same date format 3 times per every row processed.

You've got a NESTED LOOP JOIN hardcoded as a hint. Have you tried other join methods such as a HASH JOIN for example? Though I think that NLJ is the best for joining to largish tables via an index.

Outer join. Know that you can't avoid using it, but unfortunately this is slower than a normal join.

Analysing the indexes and tables. Not sure if this will help though as you're hardcoding the execution plan (good idea IMHO) instead of relying on CBO. When running the above insert, what does the v$session_wait and v$session_event say about the session? This should give you a fair idea of what is taking up most of the processing time for this insert.

Last option is to tune db parameters - but this should be the final meassure IMO. Rather work from the top down when troubleshooting performance problems, i.e. query -> execution plans -> events ->database -> operating system.

regards,
Billy Received on Fri Jun 12 1998 - 00:00:00 CDT