5 Replies Latest reply: Oct 11, 2013 10:06 AM by Gokul Gopal RSS

    SDO_ANYINTERACT Performance

    Gokul Gopal

      Hello,

       

      Oracle 11gR2.

       

      I am using a query of the form..

       

      <code>

      select tariff_layer_id from R$TARIFF_LAYER_POLY

                where sdo_anyinteract(tarifflayer_long_lat,mdsys.sdo_geometry (2001, 8307,sdo_point_type (5.25195504, 52.29621,null),null, null)) = 'TRUE'

      <code>

       

      There is a spatial index created on tarifflayer_long_lat column. The performance with such queries is not very predictable.

       

      The column is contrained to use POLYGON data.

       

      From the sql trace output, I see three main steps executed.

       

      1. selection from the table using domain index - This step is fine

       

      2. selection from MDRT_1904E$ table using rowid many times - This steps is fine

       

      3. selection from table using rowid many times - HERE IS THE ISSUE

       

      For Step 3, I see about 19000 gets being done and when in worst case this is associated with about 1000 disk reads.

       

      My question is, can Step 3 be tuned further ?

       

      I also tried ordering data based on centroid, but still no luck.

       

      If anyone has come across this problem, pls let me know.

       

      Rgds,

      Gokul

        • 1. Re: SDO_ANYINTERACT Performance
          B Hall

          Gokul,

           

          Have you applied the work-around for bug 9743250?

           

          Bryan

          • 2. Re: SDO_ANYINTERACT Performance
            Ying Hu-Oracle

            Hi Gokul,

             

            When you say "ordering data based on centroid", what do you mean?

             

            From Pro Oracle Spatial, you may see:

             

            CREATE OR REPLACE FUNCTION linear_key

            (

            location SDO_GEOMETRY,

            diminfo SDO_DIM_ARRAY

            )

            RETURN RAW DETERMINISTIC

            IS

            ctr SDO_GEOMETRY;

            rval RAW(48);

            lvl INTEGER;

            BEGIN

            -- Compute the centroid of the geometry

            -- Alternately, you can use the 'faster' sdo_pointonsurface function

            ctr := SDO_GEOM.SDO_CENTROID(location, diminfo);

            lvl := 8; -- Specifies the encoding level for hhcode function

            rval :=

            MD.HHENCODE

            ( -- Specify value, lower and upper bounds, encoding level for each dimension

            location.sdo_point.x, diminfo(1).sdo_lb, diminfo(1).sdo_ub, lvl,

            location.sdo_point.y, diminfo(2).sdo_lb, diminfo(2).sdo_ub, lvl

            );

            RETURN rval;

            END;

            /

             

            And then you can create an ordered table:

            CREATE TABLE R$TARIFF_LAYER_POLY_ORD AS SELECT * FROM R$TARIFF_LAYER_POLY WHERE ROWNUM<=0;

            INSERT INTO R$TARIFF_LAYER_POLY_ORD

            SELECT * FROM R$TARIFF_LAYER_POLY_ORD st

            ORDER BY

            linear_key

            (

            st.tarifflayer_long_lat,

            (

            SELECT diminfo FROM USER_SDO_GEOM_METADATA

            WHERE table_name = 'R$TARIFF_LAYER_POLY_ORD' AND column_name='TARIFFLAYER_LONG_LAT'

            )

            );

            then create spatail index on R$TARIFF_LAYER_POLY_ORD and run the same query on R$TARIFF_LAYER_POLY_ORD.

            By this way table fetch from rowids can be clustered, so that the number of disk reads will decrease.


            Thanks,

            Ying

            • 3. Re: SDO_ANYINTERACT Performance
              Gokul Gopal

              Bryan,

               

              Thanks for your response. Tried this, still the same.

               

              Rgds,

              Gokul

              • 4. Re: SDO_ANYINTERACT Performance
                Gokul Gopal

                Hi Ying,

                 

                This is exactly how my data is setup.

                 

                Rgds,

                Gokul

                • 5. Re: SDO_ANYINTERACT Performance
                  Ying Hu-Oracle

                  Is your database 11.2.0.1, 11.2.0.2, or 11.2.0.3?

                  If it is not 11.2.0.3, can you try your queries on 11.2.0.3?