Why is this cursor running so long?

From: <no.spam_at_columbus.rr.com>
Date: Tue, 02 Nov 1999 19:20:30 GMT
Message-ID: <2QGT3.3258$iS.117496@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 - 13:20:30 CST