Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Profile stopped working - new one doesn't work

R o bJun 26 2014 — edited Jul 1 2014

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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 29 2014
Added on Jun 26 2014
6 comments
3,167 views