Extremely slow schema list load on oracle

Note: please leave bug reports and feature requests on

Extremely slow schema list load on oracle

Postby vitali.v.c » Thu Dec 29, 2011 4:58 pm

It takes up to 150 seconds to load schema list on some oracle instances (I'm using DBeaver v1.4.7 with Oracle 11G)
I've checked query manager, the following query eats all time:
SELECT U.USERNAME FROM SYS.ALL_USERS U WHERE (U.USERNAME IN (SELECT DISTINCT OWNER FROM SYS.ALL_OBJECTS))

The problem is in "SELECT DISTINCT OWNER FROM SYS.ALL_OBJECTS". On small instance it takes ~1 second, but on big one it takes 80-150 seconds (depending on instance size).

So, is there any possibility to skip SYS.ALL_OBJECTS check? As I can see, sql developer (and some other tools) shows full list of users. It would be great to have such possibility in DBeaver too.

thanks in advance
vitali.v.c
 
Posts: 7
Joined: Thu Dec 29, 2011 4:38 pm
Location: Minsk

Re: Extremely slow schema list load on oracle

Postby Serge » Thu Dec 29, 2011 7:25 pm

Hi!

That's a good question.

Yes, reading from ALL_OBJECTS could take a lot of time. But unfortunatelly there is not better way to get schemas in Oracle.

In theory Oracle assumes that "user"="schema" however in fact there are differences.
For example we tested DBeaver on databases with a lot (tens of thousands) of users. And only a few of them (less than 0.1%) have at least one object (table, view, etc). So it doesn't make sence to show all these users as schemas in database browser. At least it is very inconvenient and eats tons of resources (SQL developer just halts forever when I try to see schemas in such database).
Another case - databases with small number of users and big number of objects (your case). Pottentially we can simply show all users as schemas and do not test for content existance.

I do not see any really good solution to handle both cases. May be the best solution is to add "Check schema content" checkbox in Oracle connection dialog..

Thank you for this report!

PS Could you say how many rows do you have in ALL_OBJECTS?
PPS Did you try to use schema filters?
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: Extremely slow schema list load on oracle

Postby vitali.v.c » Thu Dec 29, 2011 9:05 pm

vitali.v.c
 
Posts: 7
Joined: Thu Dec 29, 2011 4:38 pm
Location: Minsk

Re: Extremely slow schema list load on oracle

Postby Serge » Sun Jan 01, 2012 1:40 pm

Yep, I think we'll add checkbox in new version (will be released at the end of January).
Probably filters will be improved also.

Yes, we have plans to make DBeaver open-source. It is not open-source yet due to several reasons. Some of them are described at "About" page.
Additionally DBeaver is not yet stable, we have a lot of changes in every version, sometimes entire API is refactored. Open-source assumes that people may write their own plugins and extensions - and it leaves us much less freedom in product refactoring.
Now we going to add non-JDBC drivers (new WMI plugin) and make some native (like OCI) drivers additional support. After that API will be quite stable.
Generally, we planning to make DBeaver open-source in next 6 months.

Thank you and happy new year! :)
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: Extremely slow schema list load on oracle

Postby vitali.v.c » Sun Jan 01, 2012 8:10 pm

Thanks a lot, will be waiting

Happy holidays!
vitali.v.c
 
Posts: 7
Joined: Thu Dec 29, 2011 4:38 pm
Location: Minsk

Re: Extremely slow schema list load on oracle

Postby Serge » Mon Jan 30, 2012 6:08 pm

Additional connection configuration added in version 1.5.0. You may disable "hide empty schemas" chackbox to work with such databases.

Thanks!
Serge
 
Posts: 1526
Joined: Sat Feb 26, 2011 8:24 pm
Location: SPb

Re: Extremely slow schema list load on oracle

Postby vitali.v.c » Mon Jan 30, 2012 9:26 pm

New checkbox works great, thanks!
vitali.v.c
 
Posts: 7
Joined: Thu Dec 29, 2011 4:38 pm
Location: Minsk


Return to Support



Who is online

Users browsing this forum: No registered users and 30 guests