7 Replies Latest reply on Jul 10, 2014 4:17 PM by FFS

    SQL submitted in worksheet perform Data Dictionary pre-query

    FFS
    FFS Jul 9, 2014 8:51 AM

      Hi All,

       

      SQL Developer version: 4.0.0.13 (Windows)

       

      Upon submitting a simple SELECT * FROM <any_app_related_table> query in a worksheet the response time to return results have been awful - this is especially apparent when running the query upon opening the worksheet as a new connection.

       

      I decided to track the activity of my session and found that upon submitting the query the following SQL was executed BEFORE the actual query I wanted is run:

       

      /*+ NO_SQL_TRANSLATION */
      select table_owner, table_name from all_synonyms where owner in (user,'PUBLIC') and synonym_name=:1
      and not exists (select user, object_name from user_objects where object_name=:2 and object_type in ('TABLE','VIEW'))

       

      Because a couple of our DB's have massive data dictionarys, the above SQL is taking it's time in executing. We have gathered stats on the DD before but this does not make any difference.

       

      Is there any way this kind of "pre-query" behaviour can be switched off within SQL Developer? My colleagues using tools such as TOAD laugh in my face as their queries execute instaneously. I have tried searching for this behaviour on the web but could not find anything.....

       

      PS - I seem to think I've asked this question before as I've seen this behaviour on previous versions also but I think the answer was simply words to the effect of "that's the way SQL Developer operates I'm afraid".

       

      Thanks in advance

      • 1935 Views
      • Tags:
        • 1. Re: SQL submitted in worksheet perform Data Dictionary pre-query
          Barry Mcgillin-Oracle
          Barry Mcgillin-Oracle Jul 9, 2014 2:15 PM (in response to FFS)

          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

          Barry

          1 person found this helpful
          • 2. Re: SQL submitted in worksheet perform Data Dictionary pre-query
            FFS
            FFS Jul 9, 2014 4:01 PM (in response to Barry Mcgillin-Oracle)

            Hi Barry,

             

            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?

             

            Cheers

            • 3. Re: SQL submitted in worksheet perform Data Dictionary pre-query
              Gary Graham-Oracle
              Gary Graham-Oracle Jul 10, 2014 12:36 AM (in response to FFS)

              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

              from all_synonyms

              where owner in (user,'PUBLIC')

                and synonym_name=:1

                and not exists

                  (select user, object_name

                   from user_objects

                   where object_name=:2

                     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 11.2.0.1.

               

              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.

               

              Regards,
              Gary

              SQL Developer Team

              1 person found this helpful
              • 4. Re: SQL submitted in worksheet perform Data Dictionary pre-query
                Vadim Tropashko-Oracle
                Vadim Tropashko-Oracle Jul 10, 2014 1:09 AM (in response to FFS)

                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.

                • 5. Re: SQL submitted in worksheet perform Data Dictionary pre-query
                  FFS
                  FFS Jul 10, 2014 8:31 AM (in response to Vadim Tropashko-Oracle)

                  Thanks Vadim,

                   

                  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.

                  • 6. Re: SQL submitted in worksheet perform Data Dictionary pre-query
                    Gary Graham-Oracle
                    Gary Graham-Oracle Jul 10, 2014 3:16 PM (in response to FFS)

                    Vadim's answer is best, but the following discussion from early 2013 comes close to the same conclusion:

                    Unusable cause very slow automatic query

                     

                    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!

                    • 7. Re: SQL submitted in worksheet perform Data Dictionary pre-query
                      FFS
                      FFS Jul 10, 2014 4:17 PM (in response to Gary Graham-Oracle)


                      Hi Gary,

                       

                      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!

                       

                      Many Thanks