One way to do this should be to turn off the insight on the connection. This looks like a query for code insight and as such should be on a separate connection, thus not affecting your query or switched off. If its not on the insight thread, then its a bug and we will look into that. In the meantime, switch off insight and see does that help.
Thanks for your reply.
Have checked Tools -> Preferences -> Code Editor -> Completion Insight and both check boxes are un-checked. However, saying that my initial assumption about <any_app_table> may not be correct. I have since found that
- The "table" I was originally querying turned out to be a synonym!
- Querying directly on a table owned by the schema I'm logged into actually DOES return within a timely manner
- But querying on objects in the same schema that are SYNONYMS does indeed produce the pre-query referred to in the initial post.
Don't know if that makes any difference?
It seems SQL Developer's Code Insight and PL/SQL Parser are the primary callers of the SQL you mention:
select table_owner, table_name
where owner in (user,'PUBLIC')
and not exists
(select user, object_name
and object_type in ('TABLE','VIEW'))
When I ran this through SQL Tuning Advisor, it recommended gathering statistics on SYS.SYN$. After that, rather than getting a wide range of query times between 0.6 to 0.08 seconds, it narrowed a bit into a 0.2 to 0.09 second range. This is all on a laptop with Oracle 22.214.171.124.
You never say what you mean by "awful", but the more awful it is, the more the responsibility falls on your DBA to fix it. Aside from stale statistics, you may wish to run SQL Tuning Advisor yourself and see what it says.
SQL Developer Team
Is your RDBMS version 12.1? How long
select * from user_synonyms where synonym_name= 'POTATO';
does it take to run?
I'm not able to confirm your assertion that this dictionary query is fired before user query. It is triggered either by code insight, or semantic analysis info tip; both can be opted out.
It seems that the semantic analysis info tip checkbox was the eventual culprit, and un-checking this solved the problem!
@Gary - thanks for your reply - apologies for not qualifying "awful": by that I meant the query was taking around 3-4 mins to execute, truly awful if you're just performing a straightforward select I'm sure you'll agree!
Thanks for all your help guys - it's been interesting to find out what queries are executed in the background for certain features of SQL Developer.
Vadim's answer is best, but the following discussion from early 2013 comes close to the same conclusion:
The lesson, of course, for all is: search first, ask questions later. Those who cannot remember the past are condemned to repeat it, especially me!
I absolutely agree with you - and indeed I always attempt to search the NET and these forums for previous answers before posting a question. I actually typed in NO_SQL_TRANSLATION into the search box on this site and it didn't come up with any hits resembling that string, let alone the helpful article you've posted. Maybe I incorrectly thought that [quite unique] search term would yield the correct hits but alas it didn't, hence my posting the question....
I've definitely hammered this lesson into my head though for next time!