UMN-Mapserver Oracle Spatial 10g - Performance

[Fernando Simon]

Hi Gregor,
    The time to execute and retrieve the data from database have many 
variables. The login time, network delay, database usage (memmory, 
processor, connections, hard disk). As Frank suggested if you have more 
that one Oracle Spatial layer in your Mapfile it's recommended to use 
the FastCGI support.
    For you data in your layer you used a subquery, if you don't want to 
have a more retrict information for you data you can define the table 
directly, like: "GEOM FROM kreise_oracle". You can check all the SQL 
that Mapserver perform in database using the DEBUG option for Mapserver, 
to do this you need is compile Maperver with debug flag and add the 
degub parameter for your mapfile and for your layer.
    If you don't use the 3D data you can compile the Mapserver without Z 
support, without the Z Mapserver is more fast to manage and draw the 
data. Another hint is check the explain plan for your query, you can 
take the same query the Mapserver used and explain it. You can use this 
way in sqlplus:
            set markup html preformat on
            select plan_table_output from 
table(dbms_xplan.display('plan_table',null,'serial'));
            explain plan for <Mapserver query>
    This steps will show the costs to execute the query, so using this 
you can manage your index, tablespace....
    Just a little questions, how you added the data in your database, 
using shp2sdo? If yes did you execute the SDO_MIGRATE.TO_CURRENT after 
import the data? Did you already analyse your data using the tunning 
function in your espatial data (tunning chapter in User Guide).
    You can check the complete Mapserver documentation for Oracle 
Spatial in this link: http://ms.gis.umn.edu/docs/howto/oracle_spatial_howto
     Regards.

------------------------------------------------------------------------
Fernando Simon
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada - Brazil
http://www.univali.br/g10 - UNIVALI/CTTMAR
------------------------------------------------------------------------


Frank Warmerdam wrote:

>On 10/26/05, Gregor Fikoczek <gfikoczek at kartago.de> wrote:
>  
>
>>Dear All,
>>
>>I got a Oracle Spatial 10g Database connected to the UMN-Mapserver
>>(Mapbender),
>>but the presentation/performance of the oracle-data is very poor : 10
>>sec. or more.
>>    
>>
>...
>  
>
>>[The table got 418 rows]
>>
>>What can I do to speed up the performance ?
>>    
>>
>
>Gregor,
>
>Do you have any idea what the connection time is for this database?
>418 rows is tiny, so unless the data is incredible complex it is hard
>to imagine it taking much time beyond the initial connection which can
>be ... substantial ... for Oracle.
>
>Do you have more than one layer coming from Oracle?  If so, then
>at the very least you will want to add the following line to each layer
>to allow using the same connection for all layers:
>
>  PROCESSING "CLOSE_CONNECTION=DEFER"
>
>If one connection is still too slow, then you could consider FastCGI
>support which preserves the connection between cgi requsts but it is
>a bit complex to setup.
>
>PS. I'm not sure this question belonged on the mapserver-dev
>list instead of mapserver-users.
>
>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    | Geospatial Programmer for Rent
>
>  
>