This discussion is archived
7 Replies Latest reply: Mar 19, 2013 12:39 PM by Gary Graham RSS

Unusable cause very slow automatic query

787390 Newbie
Currently Being Moderated
SQLdeveloper frequently freeze up for minutes in our development instances (EE 11.2.0.3), which have an huge amount of schema, objects and synonyms.

Freezing is due to following query, I suppose automatically issued in order to discover accessible objects:

/***
/*+ NO_SQL_TRANSLATION */
SELECT 'SCHEMA' type, username owner, username object_name, null column_name, null column_id, null data_type
FROM all_users
WHERE rownum <=50 and username like :1 union all /*+ NO_SQL_TRANSLATION */
SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
FROM all_objects
WHERE object_type ='TABLE' and object_name not like 'BIN$%' and rownum <=50 and object_name like :2 union all /*+ NO_SQL_TRANSLATION */
SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
FROM all_objects
WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50 and object_name like :3 union all /*+ NO_SQL_TRANSLATION */
select 'TABLE' type, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') owner, synonym_name object_name, null column_name, null column_id, null data_type
from all_synonyms
where synonym_name like :4 and rownum <=50 and owner in (SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ,'PUBLIC')
***/

The query is from a SQLDeveloper v. 3.2.09, but we had similar problems with older versions.

I tried ad-hoc profile with EM's sql tuning advisor, but the gain in minimal. Looking at execution plain seems like the major problem is the union with the all_synonyms views, actually slow by itself (a known issue with our architecture since Oracle DB 10.2.0.4, as far as I remember).

Are there any workarounds to avoid the query or any parameter to force it using instead "user_synonyms"? Synonyms of other users are rarely intresting in object tree panel, we gladly gladly renounce to them...
  • 1. Re: Unusable cause very slow automatic query
    787390 Newbie
    Currently Being Moderated
    So no hint, no solution, not a simple flag to stop the continuous useless crawling of objects in the main thread?
  • 2. Re: Unusable cause very slow automatic query
    riedelme Expert
    Currently Being Moderated
    This forum is actually for SQL*Developer questions. Your qustion is about SQL and performance and should probably be posted in the SQL and PL/SQL forum.

    You can verify if it is SQL*Developer issue (possible but unlikely) by running it in another evironment like SQL*PLUS.

    However, since we are here anyway ...

    I performed searches in Google, My Oracle Support, and the 11gR2 documentation for NO_SQL_TRANSLATION and found nothing of any use. Did you mean NO_QUERY_TRANSFORMATION? If the hint you are using does not exist it will be ignored and by itself cause no problems. If it does exist (unlikely because there are lists of undocumented hints to be found on the internet) it shoud probably not be used since nothing is known about it. Does anything happen if you remove it and run the query?

    Post an execution plan of your SQL.

    A visual scan of your query (hard because it was unformatted)0
    1. lever SQL joined by unions. hard to read since the UNION operators were randomly placed in the SQL
    2. Multiple queries against data dictionary. If not queried by keys reading data dictionary tables can be slow
  • 3. Re: Unusable cause very slow automatic query
    riedelme Expert
    Currently Being Moderated
    .

    Edited by: riedelme on Mar 2, 2013 8:21 AM
  • 4. Re: Unusable cause very slow automatic query
    Gary Graham Expert
    Currently Being Moderated
    Hi Denis,

    I recall hearing one of the developers say he tested SQL Developer against larger instances (Oracle APPS or something similar) and actively focused on tuning the dictionary queries to achieve acceptable response times -- apparently it can be quite challenging.

    You say your environment is a development instance. Do you have a periodic tuning procedure you follow for it, similar to what you likely have for your production environment? Something like gather_dictionary_statistics?

    See the following thread from another of our forums, Oracle Database -> General Questions
    GATHER_DICTIONARY_STATS or  GATHER_FIXED_OBJECT_STATS  for slow dba_segment

    and this blog referenced therein...
    http://oracleappstechnology.blogspot.com/2008/09/difference-between-gatherfixedobjectsta.html

    Maybe this will help you and avoid the pursuit of an enhancement request.

    Regards,
    Gary
    SQL Developer Team
  • 5. Re: Unusable cause very slow automatic query
    787390 Newbie
    Currently Being Moderated
    As I told, the problem is about a weird query that = SQL*Developer = frequently executes by itself. And yes it's slow in any way you try it, but I don't need to optimize it neither I have any way to modify it: I just need SQL*Developer to stop using it, or at least bring it in backgroud instead of freezing for minuts the whole app. So I still suppose this is the right place to post the issue; your advices are useful, I hope some of SQL*Developer developers would read them... ;)

    I don't know what is the original query issued by SQL*Developer: I just pasted what Oracle engine shows me is executed by freezed session.
  • 6. Re: Unusable cause very slow automatic query
    787390 Newbie
    Currently Being Moderated
    Gary, all statistic are fine, I also forced a full gather but nothing changed. SQL Tuning Advisor found a little better profile, but response time is still to slow.

    The problem is with the "ALL_SYONONYMS" view, very slow since 10g - Oracle Support told is a known issue but they have no plan to change it - when you have many synonyms... and our application infrastructure has tons of them.

    Anyway, keep scanning all the accessibile synonyms by current SQL*Developer session doesn't seem so useful; furthermore, doing it in main application thread so all freeze when it wait the database looks not so smart. A little option to prevent the access of the known slow view whould be very useful. Some developer locally sobstituted the ALL_SYNONYMS view with a copy of the USER_SYNONYMS, but it could bring nasty side effects...
  • 7. Re: Unusable cause very slow automatic query
    Gary Graham Expert
    Currently Being Moderated
    Denis,

    Glad to hear you were able to follow-up with Oracle Support. I did a little research and, unless I am misreading the notes, it is possible there is at least some relief for this performance issue in DB 11.2.0.3. One of the notes also provides a script to change the ALL_SYNONYMS definition for 11.2.0.1 and 11.2.0.2. That sort of resolution would be up to you depending on which version of Oracle you use.

    From a strictly SQL Developer perspective, the query is called from the code Completion Insight feature. Completion insight can pop-up automatically or be invoked manually via Ctrl-Space. Disabling the automatic pop-up for the Worksheet and Code Editor from
    Tools -> Preferences -> Code Editor -> Completion Insight
    should help for each user not requiring that feature.

    Regards,
    Gary

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points