1 Reply Latest reply: Apr 15, 2014 12:50 PM by B Hall RSS

    Slow spatial query in oracle 11.2.0.3 [WAS 10.2.0.4 to 11.2.0.1 upgrade = SLOW sdo_geometry return]

    user12176722

      Hi,

      I've found a discussion ended last year.

      I've Oracle 11.2.0.3 with the same problem: some query are very slow (in the Excecution plan I've found "PX COORDINATOR")

      Others installation of Oracle 11.2.0.3 haven't same problem: in the excecution plan found "DOMAIN INDEX".

      I 've solved using statement "ALTER TABLE table_name NOPARALLEL;" which disable parallel feature in the table

       

      Can anyone tell me if it's a good (bad) solution ? thank you

       

      Bye

      Gabriele

        • 1. Re: Slow spatial query in oracle 11.2.0.3 [WAS 10.2.0.4 to 11.2.0.1 upgrade = SLOW sdo_geometry return]
          B Hall

          Gabriele,

           

          As indicated in the original post - the following work-around might work for you for bug 9743250. What this does is essentially force the optimizer to ALWAYS use the spatial index. That is usually good - unless the query is actually more efficient not using the index, such as when it is much cheaper to just to a FTS. Anyhow this is what you can do from 11.2.0.1 to 11.2.0.3 - fixed in 11.2.0.4 and 12.1.0.1:

           

          connect /as sysdba

          alter session set current_schema=MDSYS;

          DISASSOCIATE STATISTICS FROM INDEXTYPES spatial_index FORCE

          DISASSOCIATE STATISTICS FROM PACKAGES sdo_3gl FORCE;

          DISASSOCIATE STATISTICS FROM PACKAGES prvt_idx FORCE;


          Bryan