Ouch... the culprit shows up now in 2.3.4 as well... so upgrading won't solve
the problem... at best it will reduce the problem:
SELECT NULL AS table_cat,/n o.owner AS table_schem,/n o.object_name
AS table_name,/n o.object_type AS table_type,/n NULL AS remarks/n
FROM all_objects o/n WHERE o.owner LIKE :1 ESCAPE '/'/n AND o.object_name
LIKE :2 ESCAPE '/'/n AND o.object_type IN ('xxx', 'TABLE', 'VIEW',
'SYNONYM')/n ORDER BY table_type, table_schem, table_name/n
Is the above query only executed once per layer fetched in a WMS call? In our
case it is 3 times per layer...
Regards,
Pieter
Van: [email protected] [mailto:[email protected]] Namens Andrea Aime
Verzonden: vrijdag 25 oktober 2013 14:26
Aan: Pieter Roggemans
CC: [email protected]
Onderwerp: Re: [Geoserver-users] Oracle vector JNDI: bad performance due to 16
database calls per layer fetched
On Fri, Oct 25, 2013 at 10:27 AM, Pieter Roggemans
<[email protected]<mailto:[email protected]>>
wrote:
Hello everyone,
We noticed while having a look at database activity from geoserver to Oracle
there seem to be quite some inefficiencies...
Ah, another interesting find from 2.3.4, this time I've published a SQL View
(just noticed that your sql pointed at the usage of one,
not a simple table), this time the queries went up to 7:
select * from (select * from GRAFO_EXP) VTABLE where 1 = 0
select 'TABLE' as table_type from dual/nunion select 'VIEW' as table_type from
dual/nunion select 'SYNONYM' as table_type from dual/n
select 1 from dual
SELECT info from "GEOSERVER".MDRT_6757$ where rowid = :rid
select count(*) from mdsys.geodetic_srids where srid = :srid
SELECT NULL AS table_cat,/n o.owner AS table_schem,/n o.object_name
AS table_name,/n o.object_type AS table_type,/n NULL AS remarks/n
FROM all_objects o/n WHERE o.owner LIKE :1 ESCAPE '/'/n AND o.object_name
LIKE :2 ESCAPE '/'/n AND o.object_type IN ('xxx', 'TABLE', 'VIEW',
'SYNONYM')/n ORDER BY table_type, table_schem, table_name/n
SELECT GEOMETRY as GEOMETRY FROM (select * from GRAFO_EXP) VTABLE WHERE
SDO_FILTER(GEOMETRY, :1, 'mask=anyinteract querytype=WINDOW') = 'TRUE'
So there is extra inefficiency in using sql tables, I believe some of which
could be eliminated by caching some of the informations
the code is trying to gather about the structure of the result
Cheers
Andrea
--
==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
-------------------------------------------------------
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
