• Replies 9 replies
  • Subscribers 57 subscribers
  • Views 4627 views
  • Users 0 members are here

GSA Performance with Oracle

IanTidy

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

Parents Reply
  • Offline in reply to IanTidy

    When you select "Features owned by other users" the registration process looks for all feature sets in the entire database instance. Thus it takes a bit to discover everything.

    When you select related features, then it also looks for any child or sub-features of the primary features. Also increasing the scan time and complexity of the registration process.

    If you give the user rights to just certain feature tables, objects, etc., then turn off the Features Owned by Others, the process takes a lot less time.

     

Children
  • in reply to Jerry Walter - Bentley

    Thanks Jerry,

    Is there anyway we can speed up the query?

    We use a generic login for our users performing edits (a seperate login for each department), and this allows us to see how makes any changes (and what PC they used to make the change) which is recorded in an audit table with every post.  This has enable us to to create subject related database schemas (i.e. schemas for assets, property, hazards, audit, etc).

    I understand that using a generic login requires the GSA to scan for all tables which the user has access to and then all the table relationships and sub-feature tables which are also required.  But I wonder if there is a better way of doing this than the current query.  The current query does return all the necessary information, but it is killing our Oracle server while it is running, and taking more than 10 minutes (with the CPUs at 100%) can create real issues.

    Cheers, Ian

  • Offline in reply to IanTidy

    Yes, create roles for the various objects and then assign the roles to the user, so they have access to only those objects you assigned. Then turn off the 'Features owned by other users'. This will greatly speed up the feature discovery process.

    This sounds like a lot of work, but adding a new user is then relatively simple.

     

  • in reply to Jerry Walter - Bentley

    Thanks Jerry,

    I will have a look at creating to roles and assigning them to the users.  Can I get away with just creating the roles and assigning them to my existing users, or do I need to create additional users as well?

    I know this can all be scripted, I just need to know exactly what I am scripting.

    Cheers, Ian

  • Offline in reply to IanTidy

    Just create the roles and apply them to the existing users.  As you create new users, add the roles to those users.

    i.e.

    Oracle Docs

    docs.oracle.com/.../statements_6012.htm

    Examples

    psoug.org/.../roles.html

    www.techonthenet.com/.../roles.php

    HTH

    Jerry

     

  • in reply to Jerry Walter - Bentley

    Hi Jerry,

    I tried this but the GSA presents nothing in the feature list to select from (this user doesn't own any tables).

    When I went to Register the features within the GSA, I left the 'Features owned by Others' unticked.

    And because this user doesn't own any tables, nothing was listed.

    We have also noticed that it is performing differently for different PCs (which I don't understand why).  When I run the process from my laptop it takes a minute or two and when I run the same process with the same Oracle user on another PC it will run for hours (we have killed the process after 4 hours).  We have also tried different GSA versions to see if that made any difference.

    Regards, Ian

Communities
Support and Services
Training and Learning
Social Media