Re: problem with extermely slow process..

Hi Ravi,

re-write your query to

cursor c_dup is

   select count(*) kount from sdms_nav_points    where
   sdmsdsnav_id = p_id2
  and
  (
( r_latitude

     between least (lv_latitude - nvl (p_tolerance, 0), lv_latitude + nvl (p_tolerance, 0))

     and greatest (lv_latitude - nvl (p_tolerance, 0), lv_latitude + nvl (p_tolerance, 0))
  and
  r_longitude

     between least (lv_longitude - nvl (p_tolerance, 0), lv_longitude + nvl (p_tolerance, 0))

     and greatest (lv_longitude - nvl (p_tolerance, 0), lv_longitude + nvl (p_tolerance, 0))
));

What gives?

Martin

> Ravi Alluru wrote:
>
> Hi All,
> I have a problem with performance.I am trying to generate a report by
> calling some packages in oracle.
> I have ascertained where exactly the bottleneck is.
>
> I have a main program which calls a sub program.
>
> within this suprogram (procedure) I have a driving cursor which is
> cursor
> select *
> from sdms_nav_points
> where
> sdmsdsnav_id = p_id1;
>
> my second cursor is
>
> cursor c_dup is
> select count(*) kount from sdms_nav_points
> where
> sdmsdsnav_id = p_id2
> and
> (
> ( r_latitude = lv_latitude
> and
> r_longitude = lv_longitude));
> where lv_latitude and lv_longitude are values i get from the c_initi cursor.
>
> now my problem is for the second cursor if there query was exactly as shown
> above it would be fine ( there are indexes on r_latitude , r_longitude and
> sdmsnav_id).
>
> so my second cursor becomes
>
> cursor c_dup is
> select count(*) kount from sdms_nav_points
> where
> sdmsdsnav_id = p_id2
> and
> abs(r_latitude-lv_latitude) < nvl(p_tolerance,0)
> and
> abs(r_longitude-lv_longitude) < nvl(p_tolerance,0);
> now p_id1, p_id2 and p_tolerance are inputs into this procedure.
>
> As soon as my query changes my process becomes painfully slow ( understandably
> as the indexes on r_latitude and r_longitude are not being used).
> I did try doing a hack which is as follows :
>
> cursor c_dup is
> select count(*) kount from sdms_nav_points
> where
> sdmsdsnav_id = p_id2
> and
> (
> ( r_latitude = lv_latitude
> and
> r_longitude = lv_longitude));
> or
> ( r_latitude = lv_latitude + nvl(p_tolerance,0)
> and
> r_longitude = lv_longitude + nvl(p_tolerance,0) )
> or
> ( r_latitude = lv_latitude - nvl(p_tolerance,0)
> and
> r_longitude = lv_longitude - nvl(p_tolerance,0) ))
> ;
> This way I do end up using the indexes on r_latitude and r_longitude , but the
> ors are also almost half as slow as the very first query.
>
> I have over a million rows in my sdms_nav_points tables.
>
> What do u guys recommend. It is taking almost 3 hrs or more to process a very
> small data set . What are function based indexes.
>
> I have to maybe process arround more than 1000 pairs of p_id1 and p_id2 in one
> single report run.
>
> Also for running explain_plan does each schema have to have thier own
> plan_table.
>
> Also if someones using TOAD 6.3.3.1 how do you enable the plan_table within
> the tool.
>
> Thanks in anticipation.
>
>
>
>
>
> --
> Ravi Alluru
> Independent IS Consultant
> 796299 Alberta LTD
> PH (403) 298-4805
> Cell (403)607-8568
> mailto:ravi_alluru_at_yahoo.com
Received on Tue Dec 18 2001 - 06:33:31 CST