Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

1041351Jul 28 2014 — edited Jul 29 2014

Was posted in SQL Forum before:

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:



    

  1. SELECT *  
    2. 

  2. FROM TABLE_ID t_id_1  
    3. 

  3. LEFT JOIN table_hop_2 t_hop_2 ON t_hop_2.TH2_ID = t_id_1.T1_ID  
    4. 

  4. LEFT JOIN table_hop_3 t_hop_3 ON t_hop_3.TH3_ID = t_hop_2.TH2_ID  
    5. 

  5. LEFT JOIN  
    6. 

  6. (  
    7. 

  7.   SELECT  
    8. 

  8.   'STRING_TYPE_1' AS TEST_TYPE  
    9. 

  9.   , sub_t_4.COLL2 AS COLL2  
    10. 

  10.   , sub_t_4.COLL3 AS COLL3  
    11. 

  11.   , sub_t_4.COLL4 AS COLL4  
    12. 

  12.   , sub_t_4.COLL5 AS COLL5  
    13. 

  13.   , sub_t_4.COLL6 AS COLL6  
    14. 

  14.   FROM SUB_TABLE_4 sub_t_4  
    15. 

  15.   
    16. 

  16.   UNION ALL  
    17. 

  17.   
    18. 

  18.   SELECT  
    19. 

  19.   'STRING_TYPE_2' AS TEST_TYPE  
    20. 

  20.   , sub_t_5.COLL2 AS COLL2  
    21. 

  21.   , sub_t_5.COLL3 AS COLL3  
    22. 

  22.   , sub_t_5.COLL4 AS COLL4  
    23. 

  23.   , sub_t_5.COLL5 AS COLL5  
    24. 

  24.   , sub_t_5.COLL6 AS COLL6  
    25. 

  25.   FROM SUB_TABLE_5 sub_t_5  
    26. 

  26.   
    27. 

  27.   UNION ALL  
    28. 

  28.   
    29. 

  29.   SELECT  
    30. 

  30.   'STRING_TYPE_3' AS TEST_TYPE  
    31. 

  31.   , sub_t_6.COLL2 AS COLL2  
    32. 

  32.   , sub_t_6.COLL3 AS COLL3  
    33. 

  33.   , sub_t_6.COLL4 AS COLL4  
    34. 

  34.   , sub_t_6.COLL5 AS COLL5  
    35. 

  35.   , sub_t_6.COLL6 AS COLL6  
    36. 

  36.   FROM SUB_TABLE_6 sub_t_6  
    37. 

  37. ) sub_union_7 ON sub_union_7.COLL2 = t_hop_3.COLL2_equivalent  
    38. 

  38.  
    39.

SELCT COUNT (*) from SQL above returns 183131 within 1ms.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 26 2014
Added on Jul 28 2014
#sql-developer
3 comments
2,532 views