Monday, September 28, 2015
SQL Developer hits Java GC limit while exporting
Hello everybody, I do live! I may have taken a week off post wedding and honeymoon, but I am back now and tonight I ease into things with a rather simple issue from My Oracle Support article "GC Overhead Limit Exceeded Message In SQL Developer When Running A Query" via note ID 1634930.1 which has a deceiving title because this happens when exporting instead, but is still worth a read anyways.
Subscribe to:
Post Comments (Atom)
So from what I can tell, this refers to the scenario where the user attempts to fetch ALL the data from the database to the grid before doing the export.
ReplyDeleteThere's only one reason I can think of for doing this - the query takes a very long time to run, and you don't want to run it again to do the export.
If the amount of data is high enough to overwhelm the JVM as it's put into the Grid, then it's going to 'hurt.'
If you're doing exports, you should never run into memory issues...UNLESS...you're exporting to Excel and you're using the older XLS format instead of the newer XLSX format.
Our library for creating XLS files doesn't support letting go of the data as it's written to the spreadsheet, so after 100,000 rows or so the JVM is exhausted and the app will 'hang' - or in newer versions it will complain. So almost always:
+ don't fetch all the data down to the client first
+ use XLSX not XLS
Thanks for the feedback Jeff!! Your explanation makes perfect sense, and it is good to know a little something about the applications we use to talk with our database so it is greatly appreciated!
ReplyDeleteIn such case, how should I export the table to 'cvs' if the whole table won't come on the grid.
ReplyDeleteUse PLSQL spool command to export to file
ReplyDelete