Jump to content
emolloy17

SQL Developer slow to load some user's table lists

Recommended Posts

I hope I'm not repeating, I tried searching the forum for similar posts. I have SQL Developer version 4.0.2.15. My client complained that when he logs in as a certain user and tries to expand the tables list from the connections navigation window, it takes 6-8 minutes to display the list. I confirmed the same problem. Once I expand the list, I can expand/collapse and it is instantaneous. If I disconnect and then reconnect, I have to wait the 6-8 minutes to expand again.

 

However, if I log on as system, navigate down to "other users", find the user and then expand the tables list, it is almost instant. The user found the same was true if he selected another user to log on as and navigate to the same tables.

 

Also, during the wait, it appears no other queries can be run. I tried running a few queries in the worksheet while I waited and they appeared to stack up and wait until the list loaded and then each of the queries I tried to run fired off one after the other.

 

I also tried using the SQL window to query select table_name from all_tables where owner = 'xyz'; (where xyz is the username in question). The response is instantaneous (immediately after logging on and after expanding the list).

 

The database version is Oracle 11.2.0.4 on HP-UX.

 

I'm not sure where to look for reasons for the issue. While the expand was running, I tried looking in "v$session_longops" without success. That makes sense to me because it should run quickly. Also, this same issue happened a few months ago with different user on their system. I am unaware how the issue was resolved... or if it just went away.

 

Any ideas? Thanks so much.

Share this post


Link to post
Share on other sites

Hi Emol, and welcome to the forum!

 

In SQL developer, the source code is hidden, but if you get fast response time on sqlplus, then it must be an issue with the SQL developer code!

 

There are a bunch of complaints about SQL Developer performance, check Google!

 

You to report this, and see if there is a patch:

 

http://support.oracle.com

 

Good luck!

Share this post


Link to post
Share on other sites

Thanks, makes sense... but seems so weird it only happens to some users and not others. I was hoping there may be something I wasn't thinking of.

 

I'll check in with Oracle and update with the response.

 

 

Share this post


Link to post
Share on other sites

Update: I was running version 4 of SQL Developer. Oracle support recommended that I install version 17.3.1 and retry. The times greatly improved. One of the table list expansions went from 40+ minutes to 4 minutes, another went from 6-8 minutes to 5 seconds. However, I still can't understand why it takes 4 minutes to expand when querying directly is instantaneous. Oracle support then recommended I make a parameter change:

 

"Take a look at Describes Of Dictionary And Fixed Objects Very Slow Using SQL Developer Against 12c Database ( Doc ID 2147939.1 ) as I have had a few customers that completed this solution and it resolved their issue."

 

This doc recommends setting optimizer_capture_sql_plan_baselines to FALSE in hopes of speeding up the query. Unfortunately, our parameter is already set to false. So, now they are waiting for my user to install the latest version and see what his testing reveals. I'm happy with the improvement, but I don't think they have a complete explanation/solution.

 

If anything else comes of this, I'll be sure to update again.

Share this post


Link to post
Share on other sites

Hi

I had the same problem with Oracle 12c and SQL Developer 17.4.1.054
There is default filter "Exclude recycle Bin" set on for Tables list. Since I removed that filter everything goes very fast.
I hope it will help.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×