Re: DATABASE Tuning Problem - Any help appreciated

In article <1995Apr13.021818.2816_at_scammell.ecos.tne.oz.au>, sp_at_scammell.ecos.tne.oz.au (Sai Prasad) writes:
> 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'

If you have an index that contains ce.status I hope you realize that it won't be used because of the != relation.

If you want to use the index then try

         AND (ce.status < 'T' or ce.status > 'T')

or if you know what valid ce.status values you want then

         AND ce.status IN ('A','B','Q','Y')

This will only help if 'T' records make up 95-100% of your table rows otherwise the index will actually slow you down.

> 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')

How many rows will this sub-query retrieve?

You could rearrange it to be:

 AND EXISTS (SELECT 'x'

               FROM study_dates a
              WHERE a.study_id = :Hstudy_id
                AND a.select_flag = 'Y'
                AND a.measured_date = to_date(to_char(ce.time_of_call_attempt))
            )
                    

Oracle recognizes that it needs only one match and willsatisfy the EXISTS on the first row and not retrieve the rest of that query.

If you have an index on the 3 fields in that subquery then it would run even faster.

Also, do you really need to do that to_date(to_char( stuff above?

> GROUP BY ce.origin_np_code,
> ce.nps_type,
> NVL(np.parent_code,ce.nps_code);
>
> Thanks
>
> Sai
>

Try it and let me know how it goes.

Hope that helps.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras