ArcSDE 10.4.1 st_geometry performance issue

irishadar · ‎09-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

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

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

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

| 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

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

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

| 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