0

In my team, we need to connect to Oracle, Sybase and MSSQL very frequently... We use Oracle's SQLDeveloper 3.3.2 Version to connect all 3 (using third party libs). This tool often has a problem that select queries never ends... Even if we get the results, it will keep on running... And because of this we receive database alerts for long running queries...

E.g.

Select * from products

If products has million records, then SQLDeveloper will show top records but in background the query will keep on running.

How Can this problem be solved? Or Is there a better product which can fulfill our need.

Arpit Jain
  • 13
  • 3
  • _How Can this problem be solved?_ Stop retrieving millions of records. It's unlikely that this is really what you need. – mustaccio Jun 19 '17 at 13:29
  • Sometimes you just run the queries with no where clause... Then the tool keeps on running in query in background... – Arpit Jain Jun 19 '17 at 14:18

1 Answers1

0

Your query - select * from products - is asking the database engine to send millions of records to your client application (SQLDeveloper in this case).

While SQLDeveloper (and many other GUIs of a similar design) will show you the first 30 (or 50, or 100, etc) rows, as far as the database engine is concerned you're still asking to see millions of rows hence your query continues to 'run' in the database engine.

For example, in Sybase ASE the query will show up with a status of 'send sleep' meaning the database engine is waiting for the client application to request the next batch of records to send down the connection.

To 'solve' this issue you have a few options:

  • using SQLDeveloper: scroll through (ie, display on your monitor) the rest of the multi-million row result set [likely not what you want to do; likely you don't have the time/desire to hit the 'Next' button 100's of thousands of times]

  • kill off your query after you've received/viewed the first set of records [not recommended as there will likely be times when you 'forget' to kill of your query, thus earning the wrath of your DBA]

  • write your query to pull back only the records you REALLY want/need to see (eg, add a WHERE clause to limit the set of rows)

  • see if SQLDeveloper has any sort of configuration option to auto-kill any 'long running' queries [I have no idea if this is even doable in a client application]

  • see if the DBA can configure your login with a resource limit (eg, auto-kill queries if they run for more than XX seconds)

markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • Thanks for explaining... I am just wondering how come I am not facing issue with Oracle... is it because SQLDeveloper is also provided by Oracle? – Arpit Jain Jun 19 '17 at 14:48
  • Assuming the Oracle copy of the `products` table also has millions of rows ... my guess is that either a) SQLDeveloper is configured to auto-kill an Oracle-based query after a period of time or b) the Oracle database engine is issuing an auto-kill based on a resource limit. NOTE: I don't work with Oracle; I do work with Sybase ASE, and ASE resource limits have to be explicitly setup by the DBA. – markp-fuso Jun 19 '17 at 15:01
  • you see the exact same thing in oracle - in the plumbing of the database that is - the results are fetched (50 or 100 at a time), and the server process(es) are sitting there until you get the rest of the records. There's no 'auto kill this query' feature – thatjeffsmith Jun 19 '17 at 17:12