Re: problem with extermely slow process..

From: Jim Kennedy <kennedy-family_at_home.com>
Date: Mon, 26 Nov 2001 04:18:37 GMT
Message-ID: <xojM7.79033$XJ4.42515000@news1.sttln1.wa.home.com>

Can you tell us what the explain plan is? In Toad it is under one of the options to specify what the plan table is. Jim
  "Ravi A" <ravica_at_quixnet.net> wrote in message news:9tsgd6$fdv$1_at_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:18:37 CST