Thursday 13 March 2014

Creating Security Role takes a very long time

We ran into the following issue: it was not possible to create a security role as the process took a long time and eventually we had to kill SecurityAdminitrator before it finished. Whilst the process is running SecurityAdministrator doesn't respond anymore and it looks like the application has crashed. But SecurityAdmin is just waiting that the following SQL statement finishes in Oracle:

insert into mdsys.sdo_geom_metadata_table(sdo_OWNER, sdo_table_name, sdo_column_name, sdo_DIMINFO, sdo_SRID) select 'WT_EW_05_INFO', s.synonym_name, m.column_name, m.DIMINFO, m.SRID from all_sdo_geom_metadata m, all_synonyms s where m.owner=s.table_owner and m.TABLE_NAME = s.TABLE_NAME and s.owner='WT_EW_05_INFO'

We had that issue with Oracle 11.2.0.3 on AIX.

Autodesk Support suggested using SQL Tuning Advisor to find a better execution plan. So we did and it resolved the issue. The security role is now created within seconds. Drawback is - you need an extra license for Oracle's Tuning Package (luckily we have one).  You need to use Enterprise Manager to run the advisor.


AutoCAD Map 2013, SP2




No comments:

Post a Comment