I accidentally blogged this…twice. Here’s the other take. This happens more often than I care to admit, sorry.
Forgive the inelegant post title – but it gets to the point, and I reckon it might be closer to what folks will be Googling.
I get questions from time to time around getting SQL Developer to load or respond quicker.
Here’s the advice/tips I generally lead with:
Look and Feel
You can tell SQL Developer to use a custom ‘look and feel’ that we ship with the product called ‘Oracle,’ or you can say, just let the Operating System take care of that.
The OS look and feel is ‘cheaper.’ It could be A LOT cheaper.
I personally prefer the Oracle look and feel on my Mac, and it runs fine with NO performance problems. My Macbook Air is from 2013, has an Intel i5 processor, with 8 GB of RAM and a 500GB SSD.
Some of you have newer, better Macs, and this preference has helped you. So give it a shot.
Unused Features
Most of you will probably not be working on 3rd party RDBMS migrations to Oracle. Data Miner is a popular feature, but again, many of you aren’t using it today.
You can disable these features. It means there will be less files to open, less code to load, less stuff in the UI to worry about.
Don’t go crazy here. Some of the features might include things you don’t think you use, that you actually use. But, you can always turn them back on as necessary.
Close Stuff When You’re Done
I’m mostly talking about data grids. If you query something, and then query something else, and … N -> you’ll eventually end up with several dozen tabs open. This CAN take both client and server resources. Close things when you’re done looking at them. Same goes for documents/object editors as well.
Tweaking the JVM
This is not recommended for folks that don’t know what they’re doing. But, you can give Java more memory. You can tell Java to take more memory right away. You can tell Java to handle garbage collection differently. There are many dozens of JVM flags you can set, that will impact the way SQL Developer runs and operates. You make these changes in the product.conf file.
In Windows, this will be in the AppData directory.
I would try these things, in this exact order. Try the easy things first, then worry about the JVM. Also, use a newer version of Java.
Two last things…
1. If you set your SQL History limit to something really high, it will eventually maybe cause your start-time to increase. If you set it to say, 30,000, at some point, you’ll be asking SQL Developer to find 30,000 XML files, open them, and read their contents into memory. You ask, you get. Adjust as necessary.
2. For folks running SQL Developer on Citrix or via Remote Desktop, try tuning down the graphic settings – lower resolutions/DPI type things. I’ve heard from users that this can help with the response time as well in terms of the UI drawing/reacting to mouse-clicks, etc.
25 Comments
Any advice for this issue happening in sqldev 20.4 on windows 10?
https://stackoverflow.com/questions/62156575/sql-developer-file-open-export-very-slow-to-display-dialog-box
The OP says I can remove a preference file to resolve, but I can’t find any such file. It takes a really long time to File > Open and Save, like a full minute or more.
Java(TM) Platform 11.0.2
Oracle IDE 20.4.1.407.0006
Tried the first thing on your list, LAF = Windows, and it seems to have cleared up the problem. Thank you for the tips!
**Edit** it seems to have cleraed up most of the problem. There’s still a noticeable delay, over 40 seconds, after choosing Export in context menu of Query Results panel.
Mmm, it might help to add this to your product.conf file
AddVMOption -DIndexedPreferencesCommand=false
And/or going into preferences – environment – persist file names and directory paths => DISABLE
I have done both of these:
## in sqldeveloper.conf
AddVMOption -DIndexedPreferencesCommand=false
Preferences – environment – persist file names and directory paths => DISABLE
And still it is incredibly slow every time a Save or Open dialog opens, like minutes pass and then it appears.
Latest time was after clicking on a blob field in a query result and then clicking the Download link to download the file. Several minutes until Save dialog appears, and then navigating within the Save dialog to directories on local disk is sooooooo slow.
you may need to put those in the product.conf file not the product.conf…but in newer version of SQLDev, simply uncheck ‘Persist file names and directory paths’ in Preferences.
Finally found product.conf (for the uninitiated, go to Help > About > Properties > search for product.conf to find the path) and added the VM option. ‘Persist file names and directory paths’ unchecked in Preferences. No difference.
Version 20.4.1.407
Build 407.0006
When I start up, it takes about 20 seconds for the UI to become responsive, and then anything to do with Save or Open dialog takes forever. I’ve tried all the suggestions in this post. About ready to give up.
Got some thread stacks with jstat and here’s where it seems to be spending all that time (255.91s, and it wasn’t done yet!), in java.io.WinNTFileSystem.getBooleanAttributes. Perhaps I need to try a different jdk than openjdk 11?
“AWT-EventQueue-0” #26 prio=6 os_prio=0 cpu=15125.00ms elapsed=255.91s allocated=2111M defined_classes=5135 tid=0x0000000052899000 nid=0x6300 runnable [0x000000006cc6a000]
java.lang.Thread.State: RUNNABLE
at java.io.WinNTFileSystem.getBooleanAttributes([email protected]/Native Method)
at java.io.File.exists([email protected]/File.java:820)
at java.io.WinNTFileSystem.lambda$listRoots$1([email protected]/WinNTFileSystem.java:600)
at java.io.WinNTFileSystem$$Lambda$363/0x0000000100d05840.test([email protected]/Unknown Source)
at java.util.stream.ReferencePipeline$2$1.accept([email protected]/ReferencePipeline.java:176)
at java.util.stream.IntPipeline$1$1.accept([email protected]/IntPipeline.java:180)
at java.util.BitSet$1BitSetSpliterator.forEachRemaining([email protected]/BitSet.java:1283)
at java.util.Spliterator$OfInt.forEachRemaining([email protected]/Spliterator.java:699)
at java.util.stream.AbstractPipeline.copyInto([email protected]/AbstractPipeline.java:484)
at java.util.stream.AbstractPipeline.wrapAndCopyInto([email protected]/AbstractPipeline.java:474)
at java.util.stream.AbstractPipeline.evaluate([email protected]/AbstractPipeline.java:550)
at java.util.stream.AbstractPipeline.evaluateToArrayNode([email protected]/AbstractPipeline.java:260)
at java.util.stream.ReferencePipeline.toArray([email protected]/ReferencePipeline.java:517)
at java.io.WinNTFileSystem.listRoots([email protected]/WinNTFileSystem.java:601)
at java.io.File.listRoots([email protected]/File.java:1791)
at oracle.ideimpl.net.LocalFileSystemRootProvider.getRoots(LocalFileSystemRootProvider.java:19)
at oracle.ideimpl.net.RootProviderHook$LazyURLFileSystemRootProvider.getRoots(RootProviderHook.java:68)
at oracle.ide.net.URLFileSystem.listRoots(URLFileSystem.java:1097)
at oracle.ide.net.URLFileSystem.isRoot(URLFileSystem.java:1071)
at oracle.ide.net.URLNode.(URLNode.java:107)
at oracle.ide.net.URLChooser.setSelectedURLImpl(URLChooser.java:1825)
at oracle.ide.net.URLChooser.setSelectedURLImpl0(URLChooser.java:1785)
at oracle.ide.net.URLChooser.setSelectedURLImpl0(URLChooser.java:1778)
at oracle.ide.net.URLChooser.setSelectedURLImpl0(URLChooser.java:1778)
at oracle.ide.net.URLChooser.setSelectedURLImpl0(URLChooser.java:1778)
at oracle.ide.net.URLChooser.setSelectedURLImpl0(URLChooser.java:1778)
at oracle.ide.net.URLChooser.setSelectedURLImpl(URLChooser.java:1725)
at oracle.ide.net.URLChooser.setSelectedURL(URLChooser.java:1207)
at oracle.ide.net.URLChooser.refreshAll(URLChooser.java:2051)
at oracle.ide.net.URLChooser.setSelectionScope(URLChooser.java:1259)
at oracle.ide.cmd.OpenCommand.open(OpenCommand.java:175)
at oracle.ide.cmd.OpenCommand.doit(OpenCommand.java:122)
at oracle.ide.IdeController.handleEvent(IdeController.java:612)
at oracle.ide.controller.IdeAction.performAction(IdeAction.java:664)
at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:1169)
at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:619)
at javax.swing.AbstractButton.fireActionPerformed([email protected]/AbstractButton.java:1967)
at javax.swing.AbstractButton$Handler.actionPerformed([email protected]/AbstractButton.java:2308)
at javax.swing.DefaultButtonModel.fireActionPerformed([email protected]/DefaultButtonModel.java:405)
at javax.swing.DefaultButtonModel.setPressed([email protected]/DefaultButtonModel.java:262)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased([email protected]/BasicButtonListener.java:279)
at java.awt.AWTEventMulticaster.mouseReleased([email protected]/AWTEventMulticaster.java:298)
at java.awt.AWTEventMulticaster.mouseReleased([email protected]/AWTEventMulticaster.java:297)
at java.awt.Component.processMouseEvent([email protected]/Component.java:6632)
at javax.swing.JComponent.processMouseEvent([email protected]/JComponent.java:3342)
at java.awt.Component.processEvent([email protected]/Component.java:6397)
at java.awt.Container.processEvent([email protected]/Container.java:2263)
at java.awt.Component.dispatchEventImpl([email protected]/Component.java:5008)
at java.awt.Container.dispatchEventImpl([email protected]/Container.java:2321)
at java.awt.Component.dispatchEvent([email protected]/Component.java:4840)
at java.awt.LightweightDispatcher.retargetMouseEvent([email protected]/Container.java:4918)
at java.awt.LightweightDispatcher.processMouseEvent([email protected]/Container.java:4547)
at java.awt.LightweightDispatcher.dispatchEvent([email protected]/Container.java:4488)
at java.awt.Container.dispatchEventImpl([email protected]/Container.java:2307)
at java.awt.Window.dispatchEventImpl([email protected]/Window.java:2772)
at java.awt.Component.dispatchEvent([email protected]/Component.java:4840)
at java.awt.EventQueue.dispatchEventImpl([email protected]/EventQueue.java:772)
at java.awt.EventQueue$4.run([email protected]/EventQueue.java:721)
at java.awt.EventQueue$4.run([email protected]/EventQueue.java:715)
at java.security.AccessController.doPrivileged([email protected]/Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege([email protected]/ProtectionDomain.java:85)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege([email protected]/ProtectionDomain.java:95)
at java.awt.EventQueue$5.run([email protected]/EventQueue.java:745)
at java.awt.EventQueue$5.run([email protected]/EventQueue.java:743)
at java.security.AccessController.doPrivileged([email protected]/Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege([email protected]/ProtectionDomain.java:85)
at java.awt.EventQueue.dispatchEvent([email protected]/EventQueue.java:742)
at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)
at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)
at java.awt.EventDispatchThread.pumpOneEventForFilters([email protected]/EventDispatchThread.java:203)
at java.awt.EventDispatchThread.pumpEventsForFilter([email protected]/EventDispatchThread.java:124)
at java.awt.EventDispatchThread.pumpEventsForHierarchy([email protected]/EventDispatchThread.java:113)
at java.awt.EventDispatchThread.pumpEvents([email protected]/EventDispatchThread.java:109)
at java.awt.EventDispatchThread.pumpEvents([email protected]/EventDispatchThread.java:101)
at java.awt.EventDispatchThread.run([email protected]/EventDispatchThread.java:90)
Using the latest sqldeveloper (21.2.1) Windows 64-bit with JDK 8 included, imported preferences, and all problems solved. Seems like an openjdk 11 bug, perhaps this one https://bugs.openjdk.java.net/browse/JDK-7177996
Thanks for the tips and glad hope this helps someone else facing the same issue.
One of the reasons I say “we do not support the OpenJDK, although it SHOULD work”
Glad to hear you got it sorted! And thanks for sharing so others can benefit!
Hi Jeff,
I tried all the tweaks mentioned here on a Win7-64bit with sqldeveloper 64-bit and native jdk in Version 17.2.
Everything works well and very fast, but one particular usecase is extremely slow:
Directly after startup sqldeveloper:
1. connect to DB and go to / Scheduler / DBMS Jobs
2. Open first out of my 50 db-jobs. ==> Tab with “Details” opens.
Up to this point, everything is amazing fast!
Now click on “All Your Jobs”… ==> Now waiting about 45 secs until grid is opened. (Slow but acceptable).
On my screen not all jobs are shown at once, so trying to scroll. But there is no reaction for next 20 seconds each. Even if I try to scroll back to what I’ve seen previously it takes again 20 secs.
It feels like hanging.
I’ve seen this behavior also on my previous machine with Win7-32bit and Sqldeveloper Version 4.2.0 and much less memory. Now after HW-upgrade and recent version I have the same situation.
During hanging I don’t see any significant CPU usage or increasing memory usage.
Currently I have i5-CPU with 8GB, 4,7GB used. CPU load is below 10% all the time.
My drastically increased JVM-Parameters (in product.conf):
Add64VMOption -Xmx1800m
Add64VMOption -Xms1100m
Look and Feel from Oracle to Windows doesn’t help!
Any ideas?
almost sounds more like a SQL issue, query is bottle-necking the screen for 45 seconds
view > log > statements – do you see a query there taking a long time to run?
There are no long running queries seen.
I’ve opened the connected SQL-Worksheet and executed simple “select * From dual”.
Resonse: some ms.
Query is seen in “Statements” tab.
Then clicked tab of “All Your Jobs” again and I have to wait again ~13 secs until screen appears.
Meanwhile no additional statement seen in “Statements” tab.
Clicking back to SQL-Worksheet is responding immediately. Click back to jobs needs again ~13 secs
and again no new sql
For me it sounds not as a question of SQL
Probably not. I don’t have any DBMS_JOBS to play around with. You might want to open an SR with MOS. Obviously a query is running, else where would the data be coming from, but I believe you that it’s not a sql issue, we’re probably caching that data, which we don’t normally do. Can you send me a screenshot of your ‘all jobs’ screen – the one that is hanging?
Hi Jeff,
Is there a way to remove, for example, the DBA option in View –> DBA permanently? We are trying to standardise our ‘installation’ that is pushed out to users. I understand that the actual stuff that can be done is controlled by database privs / roles, but we just want to cut down things they think they should be doing…
Thanks,
Vic
Tools > Features, uncheck stuff. If you disable the DBA, it will be removed from the View menu.
Aside from running in 64-bit OS, throwing a SSD at it really helped with the response time. The UI changes nor disabling the features helped much in terms of improving load-time on a SSD.
The UI/LAF stuff seems to help more for Mac users. SSD is something every program will enjoy. Theoretically disabling features shouldn’t help at all since we don’t load those jars until they’re actually used in the product, but but but – one of these things invariably help just about every user that has performance issues on their machine with SQLDev.
Thanks for sharing your experience.
I just downloaded and installed the latest version, made the tweeks suggested, and not it starts up perfectly.
Awesome, thanks for sharing your experience here!
Hm, there are only “Metal” and “Oracle” look and feel styles in Linux; no ohter option. Ist that because of some general Java settings?
Other Java-based applications (e.g. Netbeans) offer some os-based look-and-feel settings.
Oh, not to forget – I’m running Linux 🙂
There are problems with the GTK or NIX native look and feels with the JDev framework…thankfully 96% of our users are on Windows and Macs, and even for most of them, SQL Developer runs just fine with the Oracle LAF
Yes, so far, I haven’t experienced any slowdown that could be related to UI itself.
On the other hand, I rather use the non-native LAF with other tools, because they look awfull with my GTK setup.
Yup, I run the Oracle LAF with SQL Dev on both my Mac and work Windows machine. I just think they look better.