DATABASE Tuning Problem - Any help appreciated

Hi, I have a tuning problem and I am not sure if any of you had a similar problem.

I have an insert statement which runs in 5 minutes most of the time, but occasionally takes about a couple of hours. Could any one explain this. This process is run stand alone on a HP machine with ORACLE V7.

Volumes:

     call_events	= 4 Million records of size 200 bytes each
     network_points     = 6000 records of      size 80 bytes each

   The WHERE clause,
     WHERE ce.study_id = :Hstudy_id -  will Fetch 200,000 records

 INSERT INTO call_summaries (
              study_id,
              coll_spec_name,
              origin_np_code,
              nps_type,
              nps_code,
              session_code,
              call_count,
              usage)
       SELECT :Hstudy_id,
              :Hspec_name,
              ce.origin_np_code,
              ce.nps_type,
              NVL(np.parent_code,ce.nps_code),
              'ALL',
              COUNT(*),
              NVL(SUM(ce.call_duration),0)
       FROM   call_events ce,
              network_points np
       WHERE ce.study_id = :Hstudy_id
       AND   ce.status != 'T'
       AND   ce.nps_code = np.code(+)
       AND   to_date(to_char(ce.time_of_call_attempt)) in (
                 SELECT measured_date
                 FROM   study_dates
                 WHERE  study_id = :Hstudy_id
                 AND    select_flag = 'Y')
       GROUP BY ce.origin_np_code,
                ce.nps_type,
                NVL(np.parent_code,ce.nps_code);
 

Thanks

Sai Received on Thu Apr 13 1995 - 00:00:00 CEST