GSA Performance with Oracle

Hi All,

We are experiencing large dealy when we are creating GSA projects for our Oracle based data.  I have found that the GSA executes the following query, which takes a long time (10+ minutes) on our Oracle server.  I am keen to know if other people have a similar issue, and what I can do to improve the performance of this query.

The query is:

SELECT objectType, owner, name, geomColumn, topoColumn
FROM
(SELECT 'Table' objectType, a.owner owner, a.Table_name name, b.column_name geomColumn, c.column_name topoColumn
FROM all_Tables a, all_sdo_geom_metadata b, all_sdo_topo_metadata c
WHERE (a.owner = b.owner(+) and a.Table_name = b.table_name(+)) AND (a.owner = c.owner(+) and a.Table_name = c.table_name(+)) AND a.owner NOT IN ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','SYS','SYSTEM','WKSYS','WMSYS','XDB') AND a.Table_name NOT LIKE '%$%' AND (b.table_name is not NULL)and (b.table_name is null or b.column_name not like '%.SPATIALEXTENT') AND (a.table_name not like '%/_AUX' ESCAPE '/') AND (a.table_name not like '%/_LT' ESCAPE '/') AND (a.table_name not like '%/_VT' ESCAPE '/') union
SELECT 'Table' objectType, a.owner owner, a.Table_name name, b.column_name geomColumn, c.column_name topoColumn
FROM all_Tables a, all_sdo_geom_metadata b, all_sdo_topo_metadata c
WHERE (a.owner = b.owner(+) and a.Table_name = b.table_name(+)) AND (a.owner = c.owner(+) and a.Table_name||'_LT' = c.table_name(+)) AND a.owner NOT IN ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','SYS','SYSTEM','WKSYS','WMSYS','XDB') AND a.Table_name NOT LIKE '%$%' AND (b.table_name is not NULL)and (b.table_name is null or b.column_name not like '%.SPATIALEXTENT') AND (a.table_name not like '%/_AUX' ESCAPE '/') AND (a.table_name not like '%/_LT' ESCAPE '/') AND (a.table_name not like '%/_VT' ESCAPE '/') union
SELECT 'View' objectType, a.owner owner, a.View_name name, b.column_name geomColumn, c.column_name topoColumn
FROM all_Views a, all_sdo_geom_metadata b, all_sdo_topo_metadata c
WHERE (a.owner = b.owner(+) and a.View_name = b.table_name(+)) AND (a.owner = c.owner(+) and a.View_name = c.table_name(+)) AND a.owner NOT IN ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','SYS','SYSTEM','WKSYS','WMSYS','XDB') AND a.View_name NOT LIKE '%$%' AND (b.table_name is not NULL)and (b.table_name is null or b.column_name not like '%.SPATIALEXTENT') AND (a.view_name not like '%/_BPKC' ESCAPE '/') AND (a.view_name not like '%/_CONF' ESCAPE '/') AND (a.view_name not like '%/_CONS' ESCAPE '/') AND (a.view_name not like '%/_DIFF' ESCAPE '/') AND (a.view_name not like '%/_LOCK' ESCAPE '/') AND (a.view_name not like '%/_MW' ESCAPE '/') AND (a.view_name not like '%/_PKC' ESCAPE '/') AND (a.view_name not like '%/_PKD' ESCAPE '/') AND (a.view_name not like '%/_PKDB' ESCAPE '/') AND (a.view_name not like '%/_PKDC' ESCAPE '/') AND (a.view_name not like '%/_LTB' ESCAPE '/') AND (a.view_name not like '%/_VTB' ESCAPE '/') AND (a.view_name not like '%/_HIST' ESCAPE '/') AND (a.view_name not like '%/_BASE' ESCAPE '/') union
SELECT 'View' objectType, a.owner owner, a.View_name name, b.column_name geomColumn, c.column_name topoColumn
FROM all_Views a, all_sdo_geom_metadata b, all_sdo_topo_metadata c
WHERE (a.owner = b.owner(+) and a.View_name = b.table_name(+)) AND (a.owner = c.owner(+) and a.View_name||'_LT' = c.table_name(+)) AND a.owner NOT IN ('CTXSYS','DMSYS','EXFSYS','MDSYS','OLAPSYS','ORDSYS','SYS','SYSTEM','WKSYS','WMSYS','XDB') AND a.View_name NOT LIKE '%$%' AND (b.table_name is not NULL)and (b.table_name is null or b.column_name not like '%.SPATIALEXTENT') AND (a.view_name not like '%/_BPKC' ESCAPE '/') AND (a.view_name not like '%/_CONF' ESCAPE '/') AND (a.view_name not like '%/_CONS' ESCAPE '/') AND (a.view_name not like '%/_DIFF' ESCAPE '/') AND (a.view_name not like '%/_LOCK' ESCAPE '/') AND (a.view_name not like '%/_MW' ESCAPE '/') AND (a.view_name not like '%/_PKC' ESCAPE '/') AND (a.view_name not like '%/_PKD' ESCAPE '/') AND (a.view_name not like '%/_PKDB' ESCAPE '/') AND (a.view_name not like '%/_PKDC' ESCAPE '/') AND (a.view_name not like '%/_LTB' ESCAPE '/') AND (a.view_name not like '%/_VTB' ESCAPE '/') AND (a.view_name not like '%/_HIST' ESCAPE '/') AND (a.view_name not like '%/_BASE' ESCAPE '/')) order by owner, name asc

Any help is much appreciated.

Regards, Ian