Latest Contributions by EllenDean

Hi FJ, Thanks for getting back. We have awhile back worked with Esri Support on this issue (Esri Incident #953779), and they tracked it down to an issue with an Oracle memory leak that was happening with some ST_Geometry queries.  We get this error not only with the ST_INTERSECTS query with different SRID's, but in other queries that are in all aspects valid (SRIDs the same, all participating geometries = valid, etc.). Supposedly this leak was to be fixed in Oracle 11.2.0.2 (we are on 11.2.0.1 on our production Oracle), and we have not seen this error in our test and development environments which are at 11.2.0.2.  We've been trying to upgrade to 11.2.0.2 on our production systems, but have run into issues (problems upgrading the grid) and so have not yet been able to.  But now I'm wondering if it might still be an issue in later Oracle versions, given Stefano's getting the same error at 11.2.0.3.  But maybe Stefano's issue is different. We've been able to work around this problem whenever we run into it - in the cases of our external web applications, we insert a "dummy" SQL query before executing the query which has the memory glitch.  This has so far worked for us - albeit a little clugey. Hope this helps - we are very interested in hearing any update on this issue. Thanks, Ellen ... View more

We will get this same error if the feature classes have different SRID values.  (We are running ArcSDE 10.1 sp1, Oracle 11.2.0.1). For example on our system: The feature classes: stn_et_geom and bnd_024k_county - both are spatial reference of NAD_1983_HARN_UTM_Zone_17N, but have different SRID values: select sde.st_srid(shape) from stn_et_geom where rownum < 2; SDE.ST_SRID(SHAPE) ------------------                 11 select sde.st_srid(shape) from bnd_024k_county where rownum < 2; SDE.ST_SRID(SHAPE) ------------------                 24 Doing an st_intersects with these two will result in an error: select s.objectid,s.stn_id,c.objectid,c.county from    stn_et_geom s, bnd_024k_county c where    s.stn_id=22841 and    sde.st_intersects(c.shape,s.shape)=1 / select s.objectid,s.stn_id,c.objectid,c.county * ERROR at line 1: ORA-28579: network error during callback from external procedure agent ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100 ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340 However, doing a transform on one of them to the other SRID value the query will now work on our system: select s.objectid,s.stn_id,c.objectid,c.county from    stn_et_geom s, bnd_024k_county c where    s.stn_id=22841 and    sde.st_intersects(sde.st_transform(c.shape,11),s.shape)=1 /   OBJECTID     STN_ID   OBJECTID COUNTY ---------- ---------- ---------- --------------------   70332024      22841        105 Indian River ... View more

Thanks so much for your responses.  Yes, caching is an option and we'll keep that in mind.  So far, we've had very good performance with our current 9.3 image services without caching even with our external web applications. I think we were leaning toward the file gdb direction, so it's good to hear about the performance issues with SDE, even though I'm sure these will get better in time. Thanks again - ... View more

Thanks Vince, I tried swapping the a.shape and b.shape positions, which resulted in the spatial index not being selected even with only returning the first 10 rows.  Which in some ways made sense as the St_Geometry documentation says to use the second parameter as the one from the table with a filter. I also tried using the optimizer hint with the spatial index in the join clause - still didn't use the spatial index (when returning > 10 rows). But, another interesting thing - I tried this same query on a copy of these features on our test instance (which is at 11.2.0.2, also ArcSDE 10 sp5), and it worked correctly and the spatial index was selected in the original query with all rows returned (1114 records).  I'm not sure (I'm thinking not) that it is related to the Oracle version, but maybe more along the lines of some setup on our production instance (as you suggested).  I'll check with our DBA's on this.  At least this gives some path to explore. Thanks again for your thoughts and suggestions - if we find (hopefully) anything I'll post these on this thread. Ellen D. ... View more

Thanks Vince - I tried this, and it did use the offcl_prmt_id index (after I created it! - wasn't there originally), but still didn't get the spatial index. Interesting, though, I tried running the initial query (original query used by our developers for the web app that was getting records, not just a count) and ran it on the first 9 records.  This time it uses the spatial index.  Next, I tried running it on the first 99 records, and it did not use the spatial index, but did a full table scan on both.  Running the original query on all records returns 1114 and this doesn't use the spatial index.  I'm wondering - I remember Tom Brown mentioning awhile back at an Esri conference when I was talking to him something about Esri development was working on "opening up the pipe" (my own layman words) and allowing a larger number of records to be passed through at a time on a given st_geometry query.  I'm just wondering if maybe we're running into this "pipe" limitation and because of this it is not using the spatial index(?).  Anyway - thanks again for your thoughts and suggestions.  We may - on our end - need to revise how our apps are doing these queries, especially if they'll be returning a large number of features.  We can (although reluctantly) redo the code and use ArcObjects.  Ellen D. Here is the output from my queries using different number of returned records: 1.  Full query * returns 1114 rows * does not use spatial index on either table * uses index on offcl_prmt_id field select a.sec, a.twn, a.rng from    gislib.plss_024k_str a,    pdslib.cup_bnd_all b where    b.offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1 /        SEC        TWN        RNG ---------- ---------- ----------          8         -5         29         58         -4         29         66         -5         29          5         -5         29         63         -5         29 . . .         41         -4         29         57         -4         28         19         -4         29         13         -4         28         53         -4         28         13         -4         28         24         -4         28         25         -3         28         32         -3         29         41         -4         28         28         -3         28 1114 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1466874949 ------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                  |  2182 |  1866K|  2124   (2)| 00:00:26 | |   1 |  NESTED LOOPS                |                  |  2182 |  1866K|  2124   (2)| 00:00:26 | |   2 |   TABLE ACCESS BY INDEX ROWID| CUP_BND_ALL      |     4 |  2468 |     5   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | IDX_CUP_BND_ALL_ |     4 |       |     1   (0)| 00:00:01 | |*  4 |   TABLE ACCESS FULL          | PLSS_024K_STR    |   564 |   142K|   530   (2)| 00:00:07 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("OFFCL_PRMT_ID"=38)    4 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1) Statistics ----------------------------------------------------------     1973055  recursive calls           0  db block gets      355232  consistent gets           0  physical reads           0  redo size       19409  bytes sent via SQL*Net to client        1178  bytes received via SQL*Net from client          76  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        1114  rows processed SQL> 2.  Query run on first 9 rows * returns 9 rows * uses spatial index from "a" table * uses index on offcl_prmt_id field select a.sec, a.twn, a.rng from    gislib.plss_024k_str a,    pdslib.cup_bnd_all b where    b.offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1 where    rownum < 10 /        SEC        TWN        RNG ---------- ---------- ----------          8         -5         29         58         -4         29         66         -5         29          5         -5         29         63         -5         29         10         -5         29         35         -3         28         64         -4         29         24         -4         28 9 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 421526145 -------------------------------------------------------------------------------------------------- | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |                  |     9 |  7884 |    13   (8)| 00:00:01 | |*  1 |  COUNT STOPKEY                |                  |       |       |            |          | |   2 |   NESTED LOOPS                |                  |    15 | 13140 |    13   (8)| 00:00:01 | |   3 |    TABLE ACCESS BY INDEX ROWID| CUP_BND_ALL      |     4 |  2468 |     3   (0)| 00:00:01 | |*  4 |     INDEX RANGE SCAN          | IDX_CUP_BND_ALL_ |     4 |       |     1   (0)| 00:00:01 | |   5 |    TABLE ACCESS BY INDEX ROWID| PLSS_024K_STR    |    10 |  2590 |    13   (8)| 00:00:01 | |*  6 |     DOMAIN INDEX (Sel: 1)     | A10943_IX1       |       |       |    10  (10)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<10)    4 - access("OFFCL_PRMT_ID"=38)    6 - access("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1) Statistics ----------------------------------------------------------         699  recursive calls          40  db block gets         640  consistent gets         207  physical reads           0  redo size         584  bytes sent via SQL*Net to client         364  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           7  sorts (memory)           0  sorts (disk)           9  rows processed SQL> 3.  Query run on first 99 rows * returns 99 rows * does not use spatial index on either table * uses index on offcl_prmt_id field select a.sec, a.twn, a.rng from    gislib.plss_024k_str a,    pdslib.cup_bnd_all b where    b.offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1 where    rownum < 100 /        SEC        TWN        RNG ---------- ---------- ----------          8         -5         29         58         -4         29         66         -5         29          5         -5         29         63         -5         29         10         -5         29         35         -3         28         64         -4         29         24         -4         28 . . .         52         -4         28         19         -3         29          8         -4         29          5         -4         28          0         -5         29         28         -4         29          7         -5         29         25         -4         28         30         -3         29          5         -5         29          5         -5         29         68         -4         29         17         -4         28          4         -4         28 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4040828753 -------------------------------------------------------------------------------------------------- | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |                  |    99 | 86724 |    98   (2)| 00:00:02 | |*  1 |  COUNT STOPKEY                |                  |       |       |            |          | |   2 |   NESTED LOOPS                |                  |   146 |   124K|    98   (2)| 00:00:02 | |   3 |    TABLE ACCESS BY INDEX ROWID| CUP_BND_ALL      |     4 |  2468 |     3   (0)| 00:00:01 | |*  4 |     INDEX RANGE SCAN          | IDX_CUP_BND_ALL_ |     4 |       |     1   (0)| 00:00:01 | |*  5 |    TABLE ACCESS FULL          | PLSS_024K_STR    |   100 | 25900 |    95   (2)| 00:00:02 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<100)    4 - access("OFFCL_PRMT_ID"=38)    5 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1) Statistics ----------------------------------------------------------      143992  recursive calls           0  db block gets       30375  consistent gets           0  physical reads           0  redo size        2121  bytes sent via SQL*Net to client         430  bytes received via SQL*Net from client           8  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)          99  rows processed SQL> ... View more

Hi Vince, Thanks so much for getting back.  A little more info (in case it helps):  I'm using the "b" table as the second parameter in the st_intersects, as St_Geometry documentation said this should be the table that is being used as a filter.  The gislib.plss_024k_str feature class has 56373 records.  The pdslib.cup_bnd_all feature class has 21653 records. Hopefully I did this correctly - below is a revised query, but it still is doing a full table scan (note:  the pdslib.cup_bnd_all table's layer_id=13540): select /*+ INDEX(pdslib.cup_bnd_all, A13540_IX1) */ count(1) from    gislib.plss_024k_str a JOIN pdslib.cup_bnd_all b ON (b.offcl_prmt_id=38 and sde.st_intersects(a.shape, b.shape)=1) and    rownum < 2 /   COUNT(1) ----------          1 Execution Plan ---------------------------------------------------------- Plan hash value: 2651061692 -------------------------------------------------------------------------------------- | Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |               |     1 |   876 |  2620   (2)| 00:00:32 | |   1 |  SORT AGGREGATE      |               |     1 |   876 |            |          | |*  2 |   COUNT STOPKEY      |               |       |       |            |          | |   3 |    NESTED LOOPS      |               |  2182 |  1866K|  2620   (2)| 00:00:32 | |*  4 |     TABLE ACCESS FULL| CUP_BND_ALL   |     4 |  2468 |   501   (1)| 00:00:07 | |*  5 |     TABLE ACCESS FULL| PLSS_024K_STR |   564 |   142K|   530   (2)| 00:00:07 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter(ROWNUM<2)    4 - filter("B"."OFFCL_PRMT_ID"=38)    5 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1) SQL> The polygon (offcl_prmt_id=38) is fairly normal shape - I tried running the query using other polygons that are simple rectangles and would intersect with only a few polygons in the other layer.  All of these queries also did full table scans. - Ellen D. ... View more

Hi Vince, thanks for the suggestions.  I hadn't realized that we shouldn't be using the sdetable -o create_view command with ST_GEOMETRY, and have tried the sdelayer -o register to register an Oracle view as you suggested and it should work fine for us instead. Unfortunately, it seems to have the same behavior (i.e. doing a full table scan when querying from a remote account), even when I tried adding a hint, both in the original view creation as well as using it in the sql query.  But, running the same query on the original feature class from the remote account uses the domain index.  Below is output from this (for reference) - again, thanks for the ideas.  It was worth a shot - we can still use separate views in each of the remote accounts we need, which will perform well and use the domain index.  And we'll switch to using the sdelayer -o register command instead of the sdetable -o create view. Thanks - Ellen D. [sde@sdetest ~]$ sqlplus edean@sde SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 7 18:18:17 2012 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create or replace view v2_stn_et_geom as select /*+ INDEX (stn_et_geom a11232_ix1)*/ objectid, stn_id, stn_nm, stn_tp_cd, lat_no, long_no, lat_no_dd, long_no_dd, div_id, stn_stts_cd, sftwr_id, ctrl_dtm_cd, shape from stn_et_geom where stn_tp_cd=460 / View created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [sde@sdetest ~]$ --Next, registering this with the sdelayer -o command: [sde@sdetest ~]$ sdelayer -o register -l v2_stn_et_geom,shape -e p -t ST_GEOMETRY -C objectid,USER -i esri_sde -s earth -u edean -p **** ArcSDE 10.0  for Oracle11g Build 1937 Tue Aug 16 16:08:18  2011 Layer    Administration Utility ----------------------------------------------------- Successfully Created Layer. [sde@sdetest ~]$ -- Starting a SQL*Plus session connected as a different account (sdeuser) SQL> connect sdeuser@sde Enter password: Connected. SQL> SQL> select count(1) from edean.v2_stn_et_geom   2  where sde.st_intersects(shape,sde.st_buffer(sde.st_point(591071.612,3273176.608,11),2000))=1;   COUNT(1) ----------          2 Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------- | Id  | Operation           | Name           | Rows  | Bytes | Cost  | ---------------------------------------------------------------------- |   0 | SELECT STATEMENT    |                |     1 |    34 |   307 | |   1 |  SORT AGGREGATE     |                |     1 |    34 |       | |   2 |   VIEW              | V2_STN_ET_GEOM |  6155 |   204K|   307 | |   3 |    TABLE ACCESS FULL| STN_ET_GEOM    |  6155 |  1268K|   307 | ---------------------------------------------------------------------- SQL> -- Running the same query with the hint SQL> SQL> select /*+ INDEX (v a11232_ix1) */   2  count(1) from edean.v2_stn_et_geom v   3  where sde.st_intersects(v.shape,sde.st_buffer(sde.st_point(591071.612,3273176.608,11),2000))=1;   COUNT(1) ----------          2 Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------- | Id  | Operation           | Name           | Rows  | Bytes | Cost  | ---------------------------------------------------------------------- |   0 | SELECT STATEMENT    |                |     1 |    34 |   307 | |   1 |  SORT AGGREGATE     |                |     1 |    34 |       | |   2 |   VIEW              | V2_STN_ET_GEOM |  6155 |   204K|   307 | |   3 |    TABLE ACCESS FULL| STN_ET_GEOM    |  6155 |  1268K|   307 | ---------------------------------------------------------------------- SQL> -- Running the same query on the original feature class SQL> SQL> select count(1) from edean.stn_et_geom   2  where sde.st_intersects(shape,sde.st_buffer(sde.st_point(591071.612,3273176.608,11),2000))=1;   COUNT(1) ----------          2 Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------- | Id  | Operation                     | Name       | Rows  | Bytes | Cost  | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |            |     1 |   211 |     4 | |   1 |  SORT AGGREGATE               |            |     1 |   211 |       | |   2 |   DOMAIN INDEX (Sel: .0018053)| A11232_IX1 |       |       |     4 | ---------------------------------------------------------------------------- ... View more

Correction on the second sdetable command previously - it should have read: sdetable -o create_view -T PARCELS_ALL -t "gislib.parcels, gislib.v_parcels" -c "gislib.v_parcels.objectid, gislib.v_parcels.county, gislib.v_parcels.parcelid, gislib.v_parcels.oname, gislib.v_parcels.onaddr1, gislib.parcels.shape" -w "gislib.v_parcels.objectid=gislib.parcels.objectid" -i esri_sde -s earth -u gisapp -p **** ... View more

Yes - our feature class "stn_et_geom" is the one Master GIS table of all stations (wells, springs, water control structure points, water quality monitoring stations, hydrologic stations (e.g. rain gauges), and others) used and managed by our organization.  Different departments manage these stations and their data through specific applications.  We have created many SDE views off of the one stn_et_geom feature class, pertaining to these different station types.  It has been very helpful to us to use SDE views rather than creating (and maintaining) output feature class subsets. Thanks again - E. ... View more

Thanks so much Vince.  This worked. I repeated the steps, this time from a (Windows) client machine with Oracle client 10.2.0.3.  This time, the sdelayer -o register step did work.   Also, this time I used a direct connect to this layer in ArcMap and the query worked correctly. Thanks again - I really appreciate it! ... View more

Thanks for the suggestion - I did this, but then got an error when trying to register this through sdelayer -o register: [sde@earth ~]$ sdelayer -o register -l STN_ET_SPRINGSEEP_V,shape -e p -t ST_GEOMETRY -C objectid -i esri_sde -s earth -u gislib -p ****** ArcSDE 9.3.1  for Oracle10g Build 3055 Mon May 10 10:12:50  2010 Layer    Administration Utility ----------------------------------------------------- Error: Not supported on a view (-251). Error: Unable to alter registration for table STN_ET_SPRINGSEEP_V [sde@earth ~]$ ************************************************ Output from creating the original view: [sde@earth ~]$ sqlplus gislib@sde SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 8 12:40:04 2011 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> SQL> create view stn_et_springseep_v as select   2    g.objectid, s.stn_id, s.stn_stts_cd, g.shape   3  from   4    stn_et_geom g INNER JOIN v_mv_springseep_stn s ON g.stn_id=s.stn_id; View created. SQL> select count(1) from stn_et_geom;   COUNT(1) ----------      53652 SQL> select count(distinct stn_id) from stn_et_geom; COUNT(DISTINCTSTN_ID) ---------------------                 53652 SQL> select count(1) from v_mv_springseep_stn;   COUNT(1) ----------        185 SQL> select count(distinct stn_id) from v_mv_springseep_stn; COUNT(DISTINCTSTN_ID) ---------------------                   185 SQL> select count(1) from stn_et_springseep_v;   COUNT(1) ----------        185 SQL> select count(distinct stn_id) from stn_et_springseep_v; COUNT(DISTINCTSTN_ID) ---------------------                   185 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options [sde@earth ~]$ ... View more