3 Replies Latest reply: Jul 29, 2014 12:13 PM by Gary Graham-Oracle RSS

    SQL Developer gets 100% CPU when querying SQL, do not understand why.

    1041351

      Was posted in SQL Forum before: Re: SQL Developer gets 100% CPU when querying SQL, do not understand why.

       

      Hello,

       

      I have an SQL and when firing in SQL Developer 4 it works. First 50 rows are shown immediately. But now when using [CTRL] + [End] to load all Datasets it runs and runs and...

      And CPU usage (of sql developer client) is on 100% (straight Line, no pulse).

       

      The SQL consists of a sub-query, which unions all base data, and an outer join, where I join a specific ID to all base datasets.

      When querying only the inner sub-query using [CTRL] + [End] it returns (! so it does it!) about 220.000 rows in under 30 seconds.

      Well may that are much rows, but SQL Developer returns them within 30 seconds!

       

      When now querying the whole SQL with my ID join, it runs and runs and...

      When I abort it after any time it only loaded about 120.000 rows within that time.

       

      There are several things I do not understand:

      - why is it a problem to join a ID to 220.000 datasets?

         - every of the base datasets does exactly have one ID which I am going to join to

      - why is this a problem of my local CPU? I would expect a slow down on the database but not on my client?

         - It was able to show 220.000 datasets before, why is now only able to load 120.000 datasets within that time?

      - why does sql developer keep 100% CPU and hence kill my client after aborting the query?

       

      Heres my SQL:

       

      
      
      SELECT *  
      FROM TABLE_ID t_id_1  
      LEFT JOIN table_hop_2 t_hop_2 ON t_hop_2.TH2_ID = t_id_1.T1_ID  
      LEFT JOIN table_hop_3 t_hop_3 ON t_hop_3.TH3_ID = t_hop_2.TH2_ID  
      LEFT JOIN  
      (  
        SELECT  
        'STRING_TYPE_1' AS TEST_TYPE  
        , sub_t_4.COLL2 AS COLL2  
        , sub_t_4.COLL3 AS COLL3  
        , sub_t_4.COLL4 AS COLL4  
        , sub_t_4.COLL5 AS COLL5  
        , sub_t_4.COLL6 AS COLL6  
        FROM SUB_TABLE_4 sub_t_4  
        
        UNION ALL  
        
        SELECT  
        'STRING_TYPE_2' AS TEST_TYPE  
        , sub_t_5.COLL2 AS COLL2  
        , sub_t_5.COLL3 AS COLL3  
        , sub_t_5.COLL4 AS COLL4  
        , sub_t_5.COLL5 AS COLL5  
        , sub_t_5.COLL6 AS COLL6  
        FROM SUB_TABLE_5 sub_t_5  
        
        UNION ALL  
        
        SELECT  
        'STRING_TYPE_3' AS TEST_TYPE  
        , sub_t_6.COLL2 AS COLL2  
        , sub_t_6.COLL3 AS COLL3  
        , sub_t_6.COLL4 AS COLL4  
        , sub_t_6.COLL5 AS COLL5  
        , sub_t_6.COLL6 AS COLL6  
        FROM SUB_TABLE_6 sub_t_6  
      ) sub_union_7 ON sub_union_7.COLL2 = t_hop_3.COLL2_equivalent  
       
      
      

      SELCT COUNT (*) from SQL above returns 183131 within 1ms.

        • 1. Re: SQL Developer gets 100% CPU when querying SQL, do not understand why.
          rp0428
          The SQL consists of a sub-query, which unions all base data, and an outer join, where I join a specific ID to all base datasets.

          No - there is NO outer join in that query. And we can't tell what columns you are adding since you are using * to select them all from the first table; that could be one column or 100.

           

          In the other thread you were ask about the execution plan - what does it show for the query?

           

          Any large dataset requires memory - check the amount of memory your client has, how much is being used, how much swap space is available and how much is being used.

           

          What testing/troubleshooting have you been doing in the hours since you posted your question? Post info about the tests you conducted and the results.

           

          Restrict the query to return only ten thousand rows and test the results. Test with fifty thousand rows and get the results.

          .

          • 2. Re: SQL Developer gets 100% CPU when querying SQL, do not understand why.
            1041351

            Well, "outer" wasnt a good choice to use. I meant the "outer" SQL, the sql outside of the parentheses.

            Does it matter if it were oder 100 collumns? If yes why (looking on CPU usage).

             

            I did not look on the execution plan, yet. I am not going for it because I now recognized the same behavior for other SQLs at well.

            Memory is on 80% use and does not change when executing the SQL and even not when moving (or try it) to the end of resultlist.

            The client got 2GB RAM and one 2.4Ghz CPU. Where can I see the amount of swap space? Is it the "kernel-memory paged"? Paged are 232 and 46 not paged.

             

            Well I tried the inside-sql wich returned within 25 seconds, moving to end of resultlist -> fine

            Trying the outside-sql crashed my client about 8 times now. I gave up to test it further. All time CPU goes immediately on 100% (it has max 10% idle).

            It doenst matter if the client ist started long or short time ago. All other applications were closed.

            Since I do not really know what the problem is (thats my main question here) I also dont know even what I should test to get it.

            I am now relatively sure, thats its not the SQL.

             

            How do I let SQL developer show only 50k datasets?

            Using order by or where rownum <= will work but only show first 500 (maximum settable in sql developer as far as i know) results.

             

            Another observation i made is: when clicking [CTRL] + [End] and then look on the progress bar showing me that its loading. this bar immediately lags. Even at start it is not fluent. A second later taskmanager shows 100% CPU.

             

            I am going to do two things: request more CPU for client and request reinstallation of SQL Developer.

            For other colleagues now I know the SQL works fine moving to end of resultlist. So its definately not a SQL problem.

            • 3. Re: SQL Developer gets 100% CPU when querying SQL, do not understand why.
              Gary Graham-Oracle

              As rp0428 suggests, by asking the tool to hold a large query result set all in memory for display, eventually that resource will become a bottleneck. As SQL Developer runs inside a Java virtual machine, eventually the JVM's garbage collector will start to spend all its time trying to garbage collect unused objects as its heap memory allocation reaches the Xmx limit -- 100% cpu (with luck, only 100% of 1 cpu core) to gc, and 0% to doing any useful work.

               

              The question you ultimately must ask yourself:  what is the business requirement for scrolling through 100K+ rows?  If you want to read more on this topic, here are a couple of earlier discussions from this forum:

              Re: Memory problems with Oracle sql developer

              SQL Developer High Memory Consumption 3.2.20.09.87

               

              Your question on how to restrict the number of rows is best asked on the SQL and PL/SQL forum

               

              Regards,
              Gary

              SQL Developer Team