5

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%
Máté Juhász
  • 21,403
  • 6
  • 54
  • 73

2 Answers2

2

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.

harrymc
  • 455,459
  • 31
  • 526
  • 924
  • It seems that mate quite explicitly wants to cache the information from the DB - this seems like just a laundry list of unrelated tips that might 'speed up' the program. Not sure that it meets either the intent of the question or what mate's hoping for in putting up the bounty as is. – Journeyman Geek Feb 26 '18 at 13:53
  • @JourneymanGeek I'm still trying the suggestions. Although neither offers a quick solution, they seems promising. And it's a legitimate answer, definitely not worth downvoting. – Máté Juhász Feb 26 '18 at 13:57
  • @MátéJuhász: If this doesn't improve enough the performance, it will be useful to gather some performance measures to better pinpoint the problem area. As you are having slow-downs of 30-60 seconds, this is enough time to observe in Task Manager or Resource Monitor whether it concerns CPU, disk or network. – harrymc Feb 26 '18 at 14:06
  • @harrymc: I had the exact same thought, please have a look on my update – Máté Juhász Feb 26 '18 at 14:11
  • Have you looked at the general computer statistics or only SQL-Developer? Some of these large products are made of several processes. Network waits are also unmeasurable. – harrymc Feb 26 '18 at 14:14
  • Those are for SQL developer, added general numbers too – Máté Juhász Feb 26 '18 at 14:19
  • The CPU use is significative - what is using it? How much RAM do you have? – harrymc Feb 26 '18 at 14:25
  • Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/73714/discussion-between-mate-juhasz-and-harrymc). – Máté Juhász Feb 26 '18 at 14:33
  • I've followed your suggestions, after disabling majority of features and switching to Windows style: moving and resizing tables in query builder is much quicker now, but preparing the query builder is still the same. I don't know where / how to disable extensions. – Máté Juhász Feb 26 '18 at 14:38
  • I don't have SQL-Developer to test, but [this image](http://thatjeffsmith.wpengine.com/wp-content/uploads/2012/02/sqldev_extensions.png) might help. – harrymc Feb 26 '18 at 14:42
  • A possible explanation is maybe 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. – harrymc Feb 26 '18 at 14:52
1

After several unsuccessful tries I've contacted my companies Oracle manager, who mentioned the problem to Oracle.

They've acknowledged it's indeed too slow, and now in version 3.2.20.10 the issue is fixed, it's really fast!

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73