Oracle Spatial Vs ArcSDE

[OracleDoc]

All,
I've been doing some research on the web to see if anyone has been able to tell if using Oracle Spatial gives better performance than ArcSDE default package.

As of yet I have not turned up anything. Hence the reason for this post. Anyone have any clue or preferences?

I've got an 8.1.7.4 database and I'm trying to gather fodder for the push to 9i.

[KenEwald]

I don't know about spacial, but we're doing just about everything else and we've stopped the 9i upgrades in favor for leap-frogging to 10g .. which completely ROCKS!

[learning_bee]

Depend on what industry you are in ARCSDE from ESRI is a big time player for GIS/oil and gas industry. Its support and documentation are suck.

to pick SPATIAL versus ARCSDE, it's all depend on what you are trying to accomplish, performance wise, SPATIAL is new to Oracle and they only have small development team but the product got better and better now. Overall, SPATIAL is better.

[mike9]

what do you mean by "SPATIAL is new to Oracle" ?
As far a remember the Spatial option is availble at least since Oracle 7.3. So I don't think it that you can call that something new.

[OracleDoc]

Thanks for the input guys!
My present project is doing some mapping for the Military (hush hush). They're using arcsde with the defaut letting sde do the spatial. Me being the hard core Oracle guy.. thought Oracle should handle the spatial stuff. But it doesn't seem from the SDE forums that oracle is doing a good job of spatial(lizing) if that's a good word. Like I said about looking for fodder.. I can't find anything about 9i or 10g being the **** for spatial.

I'm also looking for ways to be able to increase performance for this thing, I've got plenty of memory to shove at it, so I'm think'n if I cach the five main tables with the packages and up that .defs file I should be good.

[learning_bee]

if they already implement ARCSDE, you might want to look into performance tunning of SDE: there is a white paper of how to tune SDE. also, below is the script to analyze SDE table and indexes. Don't just analyze tables and index like you did in Oracle:

declare

CURSOR owner_cur IS

SELECT DISTINCT(OWNER) OWNER from SDE.LAYERS ORDER BY OWNER;



CURSOR index_cur IS

SELECT owner, index_name FROM dba_indexes

WHERE owner in (select distinct(owner) from sde.layers) and INDEX_TYPE = 'NORMAL'

ORDER BY owner, index_name;



SQL_STMT VARCHAR2(200);



begin

-- dbms_output.enable (100000);

-- dbms_output.put_line ('Starting SDE tuning procedure...');



FOR IndexRec in index_cur LOOP

SQL_STMT := 'alter index ' || IndexRec.owner || '.' || IndexRec.index_name || ' rebuild';

-- dbms_output.put_line (SQL_STMT);

EXECUTE IMMEDIATE SQL_STMT;

END LOOP;



FOR OwnerRec in owner_cur LOOP

-- dbms_output.put_line ('Analyzing schema: ' || OwnerRec.owner);

dbms_utility.analyze_schema (OwnerRec.owner, 'DELETE');

dbms_utility.analyze_schema (OwnerRec.owner, 'ESTIMATE');

END LOOP;





end;



/

[OracleDoc]

Learning Bee,

Thanks for the advice but your script is doing the samething I'm doing. Except with the rebuilding the indexes. (at least that's how I'm reading it. Also, Oracle now days is recommending to use the dbms_stats package instead of the utility one. Something about better global stats gathering.

I'm just using the dbms_stats.gather_schema_stats to analyze the whole schema. What's nice is that it only takes about 3 hours to compute stats for SDE. Now ITVDBA that's a different story. 34mil rows in one table *whistles*..