UMN-Mapserver Oracle Spatial 10g - Performance

[Greg]

Hi Daniel, hi Fernando, hi List !

Thxs  for the quick answers and all the tipps.
I gues I'm realy in the wrong list, by the next mail I will change.

I got a couple of layers so I  included the "PROCESSING 
"CLOSE_CONNECTION=DEFER" - Statement and
using now directly "GEOM FROM kreise_oracle", but it is still to slow.

I get the data using the shp2sdo-Converter and the SDO_MIGRATE.TO_CURRENT-Execute is allready done. 
Maybe the network and/or the server is the reason, how can I found the connection time to the database?
And what for tunning function should I use ?
Sorry, for the many questions, but I'm new in the topic.

Regard.




Fernando Simon schrieb:

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