Extremely slow schema list load on oracle

[vitali.v.c]

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

[Serge]

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?

[vitali.v.c]

[Serge]

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!

[vitali.v.c]

Thanks a lot, will be waiting

Happy holidays!

[Serge]

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

Thanks!

[vitali.v.c]

New checkbox works great, thanks!