AnsweredAssumed Answered

ArcSDE 10.4.1 st_geometry performance issue

Question asked by birdface on Sep 20, 2016

Hi everyone,

we’ve upgraded  from 10.2.2 to 10.4.1 and the performance went down dramatically.

It only happens when using st_intersects ( as much as we could test it).

We have the two DB up and running so we can compare them in real time.

Our local distributor, Mor, worked on the data and couldn’t find anything wrong in the data structure, tables or indexes.

The only difference he could see between the two machine was the value of the ”consistent gets” .

 

I’m enclosing the results from the trace of selects we made on both machines.

Could someone please  take a look and maybe tell us what is wrong?

Thank you ,

Iris Hadar

Israel antiquities authority

 

  1. New DB – rashutnew

    Oracle 11.2.0.4

    ArcSDE 10.4.1

    Solaris 11

 

SQL> select a.ata_id, a.ata_tat_id, p.parcel from gis_atar a, gis_parcel p where sde.st_intersects(p.shape, a.shape)=1 and rownum < 2000;

 

1999 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2177751191

 

------------------------------------------------------------------------------

| Id  | Operation                       | Name       | Rows  | Bytes | Cost  |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |            |   212 |   148K| 12079 |

|*  1 |  COUNT STOPKEY                  |            |       |       |       |

|   2 |   NESTED LOOPS                  |            |   212 |   148K| 12079 |

|   3 |    TABLE ACCESS FULL            | GIS_ATAR   | 27405 |  6342K|    17 |

|   4 |    TABLE ACCESS BY INDEX ROWID  | GIS_PARCEL |     1 |   479 | 12079 |

|*  5 |     DOMAIN INDEX (Sel: .0000008)| A476_IX1   |       |       |     1 |

------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter(ROWNUM<2000)

   5 - access("SDE"."ST_INTERSECTS"("P"."SHAPE","A"."SHAPE")=1)

 

 

 

Statistics

----------------------------------------------------------

       4682  recursive calls

          0  db block gets

     415136  consistent gets

          1  physical reads

          0  redo size

      41637  bytes sent via SQL*Net to client

       1987  bytes received via SQL*Net from client

        135  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1999  rows processed

 

 

 

2 .Old DB – newarcheo

    Oracle 10.2.0.3

   ArcSDE 10.2.2

    Solaris 10

 

Oracle 10.2.0.3

Solaris 10

 

select a.ata_id, a.ata_tat_id, p.parcel from gis_atar a, gis_parcel p where sde.st_intersects(p.shape, a.shape)=1 and rownum < 2000;

 

1999 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2177751191

 

----------------------------------------------------------------------------------------------

 

| Id  | Operation                       | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

 

----------------------------------------------------------------------------------------------

 

|   0 | SELECT STATEMENT                |            |   204 |   100K|   107K  (1)| 00:21:26 |

 

|*  1 |  COUNT STOPKEY                  |            |       |       |  |          |

 

|   2 |   NESTED LOOPS                  |            |   204 |   100K|   107K  (1)| 00:21:26 |

 

|   3 |    TABLE ACCESS FULL            | GIS_ATAR   | 26760 |  6141K|    90   (5)| 00:00:02 |

 

|   4 |    TABLE ACCESS BY INDEX ROWID  | GIS_PARCEL |     1 |   269 |   107K  (1)| 00:21:26 |

 

|*  5 |     DOMAIN INDEX (Sel: .0000008)| A476_IX1   |       |       |     4   (0)| 00:00:01 |

 

----------------------------------------------------------------------------------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter(ROWNUM<2000)

   5 - access("SDE"."ST_INTERSECTS"("P"."SHAPE","A"."SHAPE")=1)

 

 

Statistics

----------------------------------------------------------

       5100  recursive calls

          0  db block gets

       6330  consistent gets

          0  physical reads

          0  redo size

      41594  bytes sent via SQL*Net to client

       1951  bytes received via SQL*Net from client

        135  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1999  rows processed




Outcomes