Re: Why is this cursor running so long?

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Tue, 2 Nov 1999 21:00:31 +0100
Message-ID: <941572877.8180.0.pluto.d4ee154e@news.demon.nl>

I'm not sure whether you noticed it, but the two statements don't return the same result.
To resolve your performance problem, we need more background

- which table is supposed to be the driving table
- where are keys and indexes
- the results of explain plan and/or tkprof sessions

I would start with making sure
 where
(a.hour_ending > TO_DATE('31-AUG-1999','DD-MON-YYYY')   and a.hour_ending <= TO_DATE('01-SEP-1999','DD-MON-YYYY') )
There is a chance that this construct is not handled as one expression and so the optimizer doesn't choose in favor of a range scan. Also which optimizer are you using? CBO? RBO?

Hth,

--
Sybrand Bakker, Oracle DBA
<no.spam_at_columbus.rr.com> wrote in message news:2QGT3.3258$iS.117496_at_viper...
> Here are two cursors I've written. If I hard-code the date range in the
> where clause, the PL/SQL program takes about 4 seconds to run. However,
> if I replace the date range in the WHERE clause with variables, then
> it takes forever to run! Why is this? I've used hints and I just cannot
> get the cursor with variables to run as fast (or even close) to the
> one with hard-coded values.
>
> Any help would be greatly appreciated!
>
> Here are the two PL/SQL blocks with their run times:
> ***********************************************************************
> 1 declare
> 2 p_start_date VARCHAR2(20);
> 3 p_stop_date VARCHAR2(20);
> 4 cursor c_getit is
> 5 select sum(a.net_energy) mlr_load,
> 6 trunc(a.hour_ending - (1/24), 'DDD') day_ending,
> 7 b.id report_comp_id,
> 8 a.comp_name_abbrv comp_name_abbrv
> 9 FROM mlr_load_view a,
> 10 customer_hist_view b
> 11 where a.hour_ending > TO_DATE('31-AUG-1999','DD-MON-YYYY')
> 12 and a.hour_ending <= TO_DATE('01-SEP-1999','DD-MON-YYYY')
> 13 and a.hour_ending > b.start_date
> 14 and a.hour_ending <= b.stop_date
> 15 and a.comp_name_abbrv = b.ems_name
> 16 and b.aep_comp_flag = 'Y'
> 17 GROUP BY TRUNC(hour_ending - (1/24), 'DDD'), b.id, a.comp_name_abbrv;
> 18 begin
> 19 p_start_date := '31-AUG-1999';
> 20 p_stop_date := '01-SEP-1999';
> 21 for c_rec in c_getit
> 22 loop
> 23 dbms_output.put_line('mlr load = '||c_rec.mlr_load);
> 24 end loop;
> 25* end;
> 14:05:29 CEA2D>/
>
> PL/SQL procedure successfully completed.
>
> ELAPSED: 0 00:00:04.24 CPU: 0:00:00.00 BUFIO: 9 DIRIO: 2 FAULTS: 0
> 14:05:34 CEA2D>
>
> *************************************************************************
>
> Here's the cursor with variables. The same problem exists if I use
> parameters with the cursor. This takes 4 minutes as opposed to 4
> seconds
>
>
> 1 declare
> 2 p_start_date VARCHAR2(20);
> 3 p_stop_date VARCHAR2(20);
> 4 cursor c_getit is
> 5 select sum(a.net_energy) mlr_load,
> 6 trunc(a.hour_ending - (1/24), 'DDD') day_ending,
> 7 b.id report_comp_id,
> 8 a.comp_name_abbrv comp_name_abbrv
> 9 FROM mlr_load_view a,
> 10 customer_hist_view b
> 11 where a.hour_ending > TO_DATE(p_start_date,'DD-MON-YYYY')
> 12 and a.hour_ending <= TO_DATE(p_stop_date,'DD-MON-YYYY')
> 13 and a.hour_ending > b.start_date
> 14 and a.hour_ending <= b.stop_date
> 15 and a.comp_name_abbrv = b.ems_name
> 16 and b.aep_comp_flag = 'Y'
> 17 GROUP BY TRUNC(hour_ending - (1/24), 'DDD'), b.id, a.comp_name_abbrv;
> 18 begin
> 19 p_start_date := '31-AUG-1999';
> 20 p_stop_date := '01-SEP-1999';
> 21 for c_rec in c_getit
> 22 loop
> 23 dbms_output.put_line('mlr load = '||c_rec.mlr_load);
> 24 end loop;
> 25* end;
> 14:06:44 CEA2D>/
> mlr load = 48263
> mlr load = 95817
> mlr load = 85156
> mlr load = 18102
> mlr load = 65541
> mlr load = 0
> mlr load = 48263
> mlr load = 95817
> mlr load = 85156
> mlr load = 18102
> mlr load = 65541
> mlr load = 0
>
> PL/SQL procedure successfully completed.
>
> ELAPSED: 0 00:04:01.53 CPU: 0:00:00.00 BUFIO: 25 DIRIO: 0 FAULTS:
0
> 14:10:47 CEA2D>
>
>
>
> --
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
-=-
> Ivan Samuelson *
> Staff Support Coordinator and * isamuels_at_columbus.rr.com
> Information Systems Consultant *
> Metro Information Services * http://home.columbus.rr.com/isamuels
> http://www.MetroIS.com *
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
-=- Received on Tue Nov 02 1999 - 14:00:31 CST