Re: problem with extermely slow process..

From: Ravi A <ravica_at_quixnet.net>
Date: Sun, 25 Nov 2001 21:04:43 -0700
Message-ID: <9tsgd6$fdv$1@newstest.laserlink.net>

Yes Jim,
  You are right. Tolereance is a user input in the report itself. It is optional.  If it is not specified it defaults to 0 or else to whatever value the user enters.    Any ideas on how to improve the performance   "Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:61XL7.71572$XJ4.39826977_at_news1.sttln1.wa.home.com...   In the last query have you tried using between? It looks like you are trying to find points or places that are near a specified point. (that's how I am reading tolerance).   Do you really want to consider points that are near (based upon a specified tolerance or 0 if no tolerance is specified) instead of exactly equal?

  e.g.
( 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) ))

  becomes
  (
( r_latitude> = lv_latitude + nvl(p_tolerance,0)
    and
    r_longitude> = lv_longitude + nvl(p_tolerance,0) )    and
( r_latitude <= lv_latitude - nvl(p_tolerance,0)
    and
    r_longitude <= lv_longitude - nvl(p_tolerance,0) ))   )

  A function based index helps when you are transforming the data in a column or columns via some function (the formal definition of a function - in Math - is a process that maps values that are one to one and on to other values. so f(x)=sqrt(x) is not a function because it is not one to one.). So if you were using sine(r_longitude) then you could create a function based index e.g. create index my_index on myTable(sine(r_longitude))...; You have to have query_rewrite=trusted for this to work.   Jim

    "Ravi Alluru" <ravica_at_quixnet.net> wrote in message news:9tpa57$e9o$1_at_newstest.laserlink.net...     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 Sun Nov 25 2001 - 22:04:43 CST