1 2 Previous Next 23 Replies Latest reply on Dec 3, 2013 12:34 AM by davidp 2 to upgrade = SLOW sdo_geometry return

      We are finally getting ready to move our applications from 10gR2 to 11gR2, and first off, I ran into a complete show stopper. This is with an in-place upgrade on Solaris x86-64.

      On a query using SDO_FILTER, we can:

      1. quickly count the number of records that would be returned
      2. quickly return all column data from the table EXCEPT sdo_geometry data

      Without the SDO_FILTER (using just ROWNUM for example), sdo_geometry data is returned quickly.

      I've dug around google and metalink without hitting on any suggestions. The table data we are querying is partitioned into 9 groups by range (PK). The spatial index is not partitioned.

      Here is a sample query:
               GEOMETRY_L GEOM,
                           SDO_GEOMETRY (2003,
                                         SDO_ELEM_INFO_ARRAY (1, 1003, 3),
                                         SDO_ORDINATE_ARRAY (-97.3618,
                                                             35.41))) =
            AND ROWNUM < 200;
      (There are 199 items - I just added the rownum to keep it from being a runaway query)

      Without the SDO_GEOMETRY column geometry_l returned, I get this plan - FAST:
      199 rows selected.
      Elapsed: 00:00:01.82
      Execution Plan
      Plan hash value: 1088531910
      | Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      |   0 | SELECT STATEMENT                    |                      |   199 | 29054 |     0   (0)| 00:00:01 |       |       |
      |*  1 |  COUNT STOPKEY                      |                      |       |       |            |          |       |       |
      |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| C_PATH_SEGMENT       |  3804 |   542K|     0   (0)| 00:00:01
      |*  3 |    DOMAIN INDEX                     | C_PATH_SEGMENT_SNDXL |       |       |     0   (0)| 00:00:01 |       |       |
      Predicate Information (identified by operation id):
         1 - filter(ROWNUM<200)
         3 - access("MDSYS"."SDO_FILTER"("A"."GEOMETRY_L","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_
               46  recursive calls
                2  db block gets
              254  consistent gets
                0  physical reads
                0  redo size
            22543  bytes sent via SQL*Net to client
              349  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                2  sorts (memory)
                0  sorts (disk)
              199  rows processed
      With the SDO_GEOMETRY column geometry_l returned, I get this plan (ouch!):
      199 rows selected.
      Elapsed: 00:00:51.44
      Execution Plan
      Plan hash value: 2419072205
      | Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT
      |   0 | SELECT STATEMENT       |                |   199 | 29054 |   356   (1)| 00:00:05 |       |       |        |      |            |
      |*  1 |  COUNT STOPKEY         |                |       |       |            |          |       |       |        |      |            |
      |   2 |   PX COORDINATOR       |                |       |       |            |          |       |       |        |      |            |
      |   3 |    PX SEND QC (RANDOM) | :TQ10000       |  3804 |   542K|   356   (1)| 00:00:05 |       |       |  Q1,00 | P->
      |*  4 |     COUNT STOPKEY      |                |       |       |            |          |       |       |  Q1,00 | PCWC |            |
      |   5 |      PX BLOCK ITERATOR |                |  3804 |   542K|   356   (1)| 00:00:05 |     1 |     9 |  Q1,00 |
      |*  6 |       TABLE ACCESS FULL| C_PATH_SEGMENT |  3804 |   542K|   356   (1)| 00:00:05 |     1 |   
      Predicate Information (identified by operation id):
         1 - filter(ROWNUM<200)
         4 - filter(ROWNUM<200)
         6 - filter("MDSYS"."SDO_FILTER"("A"."GEOMETRY_L","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO_ELEM_
           227579  recursive calls
                0  db block gets
          8764708  consistent gets
                0  physical reads
                0  redo size
            85130  bytes sent via SQL*Net to client
             2455  bytes received via SQL*Net from client
               29  SQL*Net roundtrips to/from client
                1  sorts (memory)
                0  sorts (disk)
              199  rows processed
        • 1. Re: to upgrade = SLOW sdo_geometry return
          I ran into the same issue recently when upgrading from to on Windows x64.
          I found that an sdo_filter query selecting columns other than the geometry column returned in expected time. Add the geometry to the SELECT and it took forever.

          I contacted Oracle Support and they said its a problem with the optimizer picking up incorrect stats on the spatial indexes. They pointed me towards a bug report which suggested disassociating statistics from the spatial index type, sdo_3gl & prvt_idx. Don't do this unless support direct you to though!

          Before I had done that I actually found (by fluke) that dropping system statistics on my box fixed it. When I put them back in after I had no trouble, so maybe try that first as its a pretty innocuous change.
          execute dbms_stats.delete_system_stats;
          1 person found this helpful
          • 2. Re: to upgrade = SLOW sdo_geometry return
            Thanks John for the suggestion.

            I tried deleting system stats, but that did not change anything. I then rebuilt the table and index stats, again no change.

            BTW - do you happen to know what bug report that was? I didn't stumble on it - but that would be helpful when I contact support.

            PS, I filed an SR, 3-2097288191 for this if anyone is interested.

            • 3. Re: to upgrade = SLOW sdo_geometry return
              Ying Hu-Oracle
              It may be bug 9743250. Anyway, try

              connect /as sysdba
              alter session set current_schema=MDSYS;

              or 2) add the index hint
              • 4. Re: to upgrade = SLOW sdo_geometry return
                I tried the NO_INDEX hint - that did nothing (as I would expect since I am not joining anything here).

                BUT... the disassociate stats did the trick.

                I can't believe that there is no patch for that - I would think everyone would have that problem.
                • 5. Re: to upgrade = SLOW sdo_geometry return
                  John O'Toole
                  I tried the NO_INDEX hint
                  Actually you need to use the INDEX hint rather than NO_INDEX - the idea is to force the optimizer to use the spatial index.

                  Yes bug 9743250 was the one I was referring to.

                  I found that issue upgrading to - strange that it wasn't resolved in the 11.2 release.
                  • 6. Re: to upgrade = SLOW sdo_geometry return
                    I got a response on the SR. One I cannot believe. I guess Spatial is now an un-important part of the database:

                    "What currently is happening has been considered an enhancement request under bug 9743250, and is planned to be implemented into Oracle in Oracle 12. Currently, this is not considered a bug and there will be no backports or bugfixes for this in any current release.

                    The effect of the DISASSOCIATE is not easy to reverse and will not be reversed by any form of statistics gathering. The one-time disassociate you've done should resolve this and you would not run into this same issue again."

                    My response:

                    "Although the workaround I found seems to correct the problem - the default behavior in 11g is clearly not. I can't believe that Oracle considers this an enhancement and not a severe bug. In 10g (not including data transfer time) this query takes around one second. With the workaround it takes around one second. Without the workaround (not published) - about 50 seconds - for an extensively used filter.

                    If this effected a WHERE clause comparing two numbers - it would be a high-priority bug. But because it is a Spatial function (and a heavily used one at that) it's not important? Totaly unacceptable on Oracle's part."
                    • 7. Re: to upgrade = SLOW sdo_geometry return
                      John O'Toole
                      Agreed. I found a few mentions of bug 9743250 on metalink, so there's clearly a few people coming across this.
                      • 8. Re: to upgrade = SLOW sdo_geometry return
                        So, has anyone successfully found a work-around for Bug 9743250? This issue is plaguing a major customer of mine and waiting for v12 is not really an option as their current production system is on 11g.

                        • 9. Re: to upgrade = SLOW sdo_geometry return

                          After this post (and rather negative feedback on the SR issue I submitted) I was contacted by our Air Force Account Manager, who elevated this up his chain. After that I was contacted by phone by Oracle and told that indeed this should be recognized as a bug and that they were working on a solution. Sorry, I'd have to go back through old notes to find a contact name since he only called me.

                          To the best of my knowledge, no patch currently exists to correct this issue. The workaround does not seem to effect anything else we utilize in the db, but I would not try that in an existing production system without extensive testing. Fortunately at this time we have extended EOL support through Feb 2013 for 10gR2, so for now we are staying on that edition until we have something more concrete on this issue.

                          If I learn anything else significant, I'll post it here.

                          • 10. Re: to upgrade = SLOW sdo_geometry return
                            Thanks Bryan! I'll keep working some other angles and will post anything I learn here as well.

                            • 11. Re: to upgrade = SLOW sdo_geometry return
                              François Lange

                              Met the same bug in

                              In, the dissociate statistics doesn't help! A new bug Bug 11797492: SPATIAL INDEX NOT USED AFTER UPGRADING TO has been rised but nothing has been made by Oracle sr 3-2648053951 ! They are able to reproduce the issue in house but it took them 10 DAYS to upload the 6Bgytes files, after sending two DVD that have been lost inside Oracle for an other 10 days they scratch the database without backup!

                              This SR HAS BEEN OPEN IN DECEMBER AND TILL THAT DATE Spatial is still not working!

                              Question are we ( those who complains ) the only one using that product ????

                              • 12. Re: to upgrade = SLOW sdo_geometry return

                                I would think there would be a significant number of licensed users for spatial. Of course there is little in the way to count all the locator users, who probably number 1000x more. Oracle's internal BI products use it for their map components for goodness sakes, and should be equally effected by this issue. I can't believe they would apply a hint for every single spatial select this effects.

                                Why in the world this issue was not made priority #1 by the product team and corrected quickly is beyond belief. Unfortunately, my thought is that since Oracle now has so many pies in the oven, the database and the specialty parts (like spatial) are on the back burner. And I would guess that the product managers get rewarded more for shiny new features, than keeping existing features working correctly.

                                While they claim it is corrected in 12, that does all of us in the real world using actual shipping products no good whatsoever. Oracle hint: This is a good way to lose customers.

                                After experiencing a few issues from back-ported code from 11g that changed the expected behavior of the results (with no documentation indicating such changes), we created a test suite to test all database patches for broken/changed spatial issues, and found several that we had to work around. I must say I am no longer comfortable about anything coming from Oracle working as advertised, sad to say. I'm now completely in the Show-Me camp for any feature, new or old.

                                My professional suggestion is to continue to use 10gR2 (which is based on mostly solid code written by people I knew) if you can, and ignore 11g altogether because of this long overdue issue. When 12 comes out, hopefully Oracle will gain back some respect from me, but I'm not real optimistic on that point.

                                Spatial Architect
                                • 13. Re: to upgrade = SLOW sdo_geometry return
                                  I re-opened my issue and posted this...

                                  "Forty-four weeks later, there is still no patch available for this bug. And according to bug 11797492, the DISASSOCIATE STATISTICS workaround no longer works with

                                  I was told that this was indeed a bug and there would be a patch for 11gR2 made available.

                                  When? I request an availability date."

                                  I will let you know what happens. Maybe it will shake a few trees in the Oracle forest.

                                  Spatial Architect
                                  • 14. Re: to upgrade = SLOW sdo_geometry return
                                    It's been a week - with no action on the SR. Apparently Oracle doesn't understand what customer support is. Total Failure.

                                    Given that we are not getting the support we have paid for, I see no need to renew support fees for spatial when they come due.

                                    1 2 Previous Next