This content has been marked as final. Show 7 replies
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
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...
Maybe this will help you and avoid the pursuit of an enhancement request.
SQL Developer Team
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.
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...
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 220.127.116.11. One of the notes also provides a script to change the ALL_SYNONYMS definition for 18.104.22.168 and 22.214.171.124. 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 Insightshould help for each user not requiring that feature.