Was posted in SQL Forum before: Re: SQL Developer gets 100% CPU when querying SQL, do not understand why.
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.