Hi,
I'm looking for some general hints and ideas.
A 3rd party application creates a long running Insert statement. The software vendor recommended to work around the issue but setting up a SQL profile and provided a few screenshots on how to do it.
We created a SQL Profile (EM 12c, Oracle 11.2.0.3, 64bit AIX) and it did the trick (for about 2 months).
Suddenly (last week) the issue reappaered. We tried to implent the SQL profile again but it has no effect. We tried a couple of times - INSERT still long running (> 5-20min). It should be executed in less than 1 min.
We can see that the INSERT in question is executed with the SQL profile.
We do not know why the issue is back (no software update, no (obvoius) changes in database) nor do we know why reimplementing the SQL profile doesn't help.
On a different Oracle database (11.2.0.1, W2008R2 - but with less schemas) the issue does not occur at all.
Additional information:
When we implented the SQL profile the first time we had some issues getting it done. Somehow my colleague mentioned to set it up. Unfortunately he can't remeber in detail what he did differently.
The SQL statement in question is:
insert into mdsys.sdo_geom_metadata_table(sdo_OWNER, sdo_table_name, sdo_column_name, sdo_DIMINFO, sdo_SRID) select 'ROBERT_TEST2', 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='ROBERT_TEST2'
Many thanks, Rob