problem with ogr oracle spatial layer

[Mark Adams]

An update on my OGR OCI woes: I have got it working. When originally
setting up the test to connect to my oracle spatial layer I'd followed
instructions on the mapserver website that called for the creation of 3
environment variables, for ORACLE_HOME, ORACLE_SID, and TNS_ADMIN. I
deleted those environment variables, rebooted the machine, and now I can
connect through ogrinfo and mapserver.

However, I am finding the performance of all ogr approaches to my oracle
data to be very slow. I'd started out with using OVF virtual data
connections, and found that to be too slow to be workable: about 6
seconds to draw 6000 geometries but timing out on querybyrectangle
functions. So the move to oracle spatial was a test to see if
performance is better. With the OCI connection it now takes 3 or 4
seconds to display the layer, which I'd consider to still be very slow,
but at least the queries don't time out. 

A shapefile with the same geometries displays in half a second. I'd
understood that the OVF approach meant no spatial indexing is involved,
but the same shouldn't be true for the oracle spatial approach - there
is an rtree index on the layer. 

Is there a way to improve the OGR OCI performance? Alternately, would
the mapserver native oracle spatial support be a better way to go?

Thanks for any and all advice.

------------------------------
Mark Adams
Senior Analyst & Project Manager
Cuesta Systems (DPRA Canada)
5230 South Service Road
Burlington, ON L7L 5K2
Phone: 905-333-4544 x14
Fax: 905-333-0455
Email: mark.adams at dpra.com


-----Original Message-----
From: Frank Warmerdam [mailto:fwarmerdam at gmail.com] On Behalf Of Frank
Warmerdam
Sent: June 7, 2006 10:18 AM
To: Mark Adams
Cc: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] problem with ogr oracle spatial layer

Mark Adams wrote:
> Yikes. Can you give any pointers as to what I should be looking at? In
> terms of setting up the environment, all I've done is install the
Oracle
> 9i client on the web server, and mapserver/mapscript with a version of
> gdal compiled with the ogr_oci dll as a plugin. To have the plugin
> recognized I set the GDAL_DRIVER_PATH variable (until then ogrinfo
> didn't recognize OCI as a supported format) to the Windows\system32
> folder where this dll resides.
> 
> What do you mean by other OCI client programs? Oracle client itself
has
> no troubles connecting to the database. Also, 2083788472 doesn't look
> like an Oracle error code? (Aren't they usually "ORA-XXXXX"?)

Mark,

I'm afraid I don't really know where to start.  The error is very
unusual
as it is just the most basic initialization step, before even any
attempt
is made to contact the database or anything like that.

Errors formatted by the Oracle error reporting functions normally start
with ORA-, but in this case we don't even have error report services
working
yet, and so just have a raw 32bit integer status code.

I read through the OCIEnvCreate() docs for Oracle 9 OCI and it does not
give any clues about what might cause a failure.

I really suspect it is some sort of runtime problem ... perhaps picking
up the wrong oci shared library or something like that.

I'm sorry I'm not more help.

Best regards,
-- 
---------------------------------------+--------------------------------
------
I set the clouds in motion - turn up   | Frank Warmerdam,
warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGF,
http://osgeo.org