Improve SQL developer performance at query builder

sqldeveloper

I use Oracle SQL Developer (version 17.3.1.279) in Windows 10 64 bit.
It's connected to an Oracle database in the network through TNS.

Whenever I open "query builder" first time for a query, or after some changes in the SQL code it takes a lot of time (30-60 s) to load.

Also "completion insights" appear very slow (3-4 s), so I generally just finish typing table and column names before they appear (setting is 0.6 s for them to appear).

Based on above issues, I suspect SQL developer each time reads information from the database. Is there a way to make it store information locally instead? (structure of the database is quite permanent, it changes less then once a year)


I've had a look in task manager too, and what's strange is that even if SQL developer is unresponsive when it prepares the "query builder" it doesn't use many resources

Resources used by SQL developer during preparing the view:

  • 0-1.5 % of my CPU (jumping up to 7 % for a few seconds)
  • 600 – 650 MB of RAM (I've a total of 16 GB, from which 9 GB is free, so I don't think it would be a bottleneck).
  • 0 MB/s disk and 0% network

In general performance is not affected this

  • CPU use 10-30%
  • memory: 7-8 GB used, 8-10 GB committed, 8-9 GB free
  • disk usage: 0-5%

Best Answer

  • A possible explanation is that query builder needs to get all the database tables and all their column information before allowing access to the query builder itself, and that loading all this data is what takes the time. For this problem I can only think of the workaround of using a custom data source schema where only the required table information is included, if this is possible in your case.

    For your question about a local cache, here are some references, although it looks like they only affect queries, and your problem might be with the loading of the tables metadata :

    You may also use the Alter Table command to change the residence of the tables you use to Cache. This is only suitable if you have enough RAM to hold the tables in memory, where they will be read bit-by-bit and kept when accessed. But I don't really know if the metadata of the tables from the system catalogs is also read and kept in memory.

    Below are listed other general tips which might also help :

    • Disable unused features in Tools / Features

    • Disable extensions
      In Preferences, disable all unneeded extensions except maybe the SearchBar (need to re-start SQL Developer). This will also clean out the main menu from unused items.

    • Set Look and Feel preference to host OS
      This will help to farm-out the GUI to the host, rather than drawing everything via Java. Other Java parameters could be tweaked, but this is only for the adventurous.

    • Close grids and files when done

    • Set SQL History Limit to a low value

    • If running via Remote Desktop, tune down the graphic settings

    • Optimize the virus scanner
      SQL-Developer is a Java program consisting of large .jar archives, which are just .zip archives, so no point in scanning the whole archive for only some files. You could exclude the SQL-Developer folder from virus scans.

    • Execute SQL-Developer as Administrator

    • If all else fails, there are Free Alternatives to Oracle SQL Developer.