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
------------------------------------------------------------------------------
| 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
