'Late bind attribute' on Oracle 12g

[sminten]

[Serge]

To properly visualize resultset DBeaver needs metadata information about underlying tables/types.
In most cases "late binding" is not required because tables consists from simple columns (numbers, varchars). But if you use objects/structures, arrays, references and other complex data types then DBeaver needs to read information about these types from Oracle system tables. It could take some time. Usually it is performed in 100-500ms. But if you are working with remote database with poor connection - it can take much more time. But what choices do we have?

Did you notice what exactly query works so long? To see what actual query is executing:
1. Go to Preferences->Common->Query Manager. Enable all checkboxes in "Query Types" and "Object Types".
2. Open Query Manager view (in main menu "Window").
3. Restart DBeaver. Execute your select statement.
4. See what metadata queries are executed during late binding.

If you'll find problem query - please post it here.

[sminten]

[Serge]

Yes, it seems that first two queries could work really slow on some 12g systems (generally systems with relatively low memory).
I'll try to improve them somehow..

[sminten]

That sounds great, thank you.

I do want to remark that my system has 12GB of memory and I have about 1GB to 3GB memory available during software development/testing. I'm not sure what you think is relatively low, but to me it sounds like it should be enough to execute simple SELECT statements within a reasonable time.

[Serge]

I just compared this queries performance on a server with 32Gb memory (and all of these 32Gb are given to Oracle server) with workstations with 16Gb (where only 1Gb is actually used by Oracle).

BTW these system tables/views are not regular Oracle tables. And they have extremely complex internal structure. If you'll see on execution plan of any system view you will be surprised how sophisticated it is. That's why sometimes even simple selects from these views can take enormous amount of time.
Another interesting thing that exactly the same queries working really fast on 10g and 11g.

Anyhow, I have a clue how to improve performance of these queries. At least I improved performance of the first one in almost 20 times. I'll make more testing on different versions of Oracle and then include this fix in the next version.

BTW thanks for bringing this topic up. I wrote Oracle plugin about 3 years ago and maybe it's time to revisit some aspects of it.

[sminten]

That's a nice result in such a short time.

You're welcome by the way. It's nice to see that you picked this up and found the issue very quickly. I'm looking forward to the next release with these fixes!

[sminten]

[lawl]

I compared SQLDeveloper and DBeaver Oracle execution times (I use 11g, but I dont think it has anything to do with oracle, but with DBeaver).
DBeaver takes 2006ms when SQLDev takes 306ms

And the query is as simple as "select column1 form table".

I'm on DBeaver 3.5.8