How to break SQL Developer -or- Dr. It hurts when I do this…

I have been working with SQL Developer a lot over the last few years. Being a Toad user beforehand, it was an adjustment to say the least.  With the last few releases, I have come to understand the working much better and can see where the seams are in the product.  That being said, something I still don’t understand fully (or trust) is performance tuning with SQL Developer. When dealing with troublesome tuning, I have come to trust SQL scripts (see SQLPLUS TIMING) as a much more accurate representation of performance rather than running through a GUI system.

Why?

Because there are too many variables that are included with a GUI that are intended to HELP me.
Variables that I don’t’ always understand when or why they have been put in place and hence run a risk of giving me bad results that I then make decisions on.  Decisions that I thought were based on empirical data, but turn out making me question my own understanding.

Problem “Java Heap Space error in Query Results Window”

In this story, I was working on a particularly complex and layered performance issue that was behaving differently in SQL Developer vs. the resulting system usage. In trying to duplicate the performance issue in SQL Developer. I was able to get the high level issue to duplicate, but when breaking the parts down, I was unable to get same issue with the parts of the statement.

2014-02-setting
Array Fetch Size Setting

After investigating: There is a setting called SQL Array Fetch Size (between 50 and 200)
This will greatly change the behavior of a running query when attempting to get all rows returned.

What I found was if I turn this value up, I would be able to see the performance issue more clearly.

2014-02-results_normal
Array Fetch Size used in Query Results (adjusted to 53)

If you have done performance tuning before, you can see where this little story is heading.
When the number of rows greatly affect the performance, it is a good indicator of where the problem lies.

How to fix “Java Heap Space error in Query Results Window”

2014-02-heap_error
Java Heap Size – error message in query results tab

So.. back to the main theme of this post.
If you set the SQL Array Fetch Size to something too large – it may help you for the moment find your problem, but it can also have a side affect.  What I found was that I would get a “Java heap space” error message and a gray window in the Query Results.  Adjusting the SQL Array Fetch Size to a value in the suggested range will correct this error (after a re-start of SQLDEV).

How to debug SQL Developer settings issue

Now if you try to do a web search for SQL DEVELOPER JAVA HEAP SPACE ERROR, you will get a whole listing of issues and text to read, most of which have to do with the startup of SQL Developer. Often the most successful results people have is by re-installing SQL Developer. This can be misleading. When just replacing the binaries the settings will persist.
Keep in mind – it isn’t the main code that had this issue, as much as the SETTINGS the (l)user set.

To find out where your settings are located for SQL Developer (system agnostic) – go to HELP -> About -> Properties then search for ide.system.dir

IDE Settings
IDE Settings

Purging the settings will also purge all of your CONNECTIONS. If you are like me, that is what I didn’t want to lose.
Your settings directory can be backed up (copied, zipped, whatever) until you determine the actual cause of the IDE error.
This is the best way to determine if the issue is with something you have done, or something wrong with the IDE.

This is sound methodology with more than just SQL Developer.

Disclaimer -or- Knowing where the seams are

Just because this was the issue that I found that generated the very generic “Java heap space” error, does not mean it will be the same for you.  The modular construction of SQL Developer makes me think there is more than one way to get that error.  The take away here is more determining how to debug a packaged product before going off the deep and blaming the product.

Special thanks to Jeff Smith (@thatjeffsmith) for letting me know that it was just me.

One thought on “How to break SQL Developer -or- Dr. It hurts when I do this…”

  1. So…how about you call this post ‘Be careful what you ask for in SQL Developer,’ instead? The more data you ask for in a fetch, the more memory you’re going to consume.

    The display of the data in the GUI makes it hard to accurately test just how ‘fast’ a query is. You could use AutoTrace, but that doesn’t fetch more than the first batch of rows unless you tell us otherwise in the preferences.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>