Improvement - Oracle - Very slow retrieval of foreign keys and references in Table Information Panel

[steromano87]

Using Oracle 11g, the time needed to retrieve foreign keys and references for a table is far greater than the time required to do the same operation on other databases.

I tried using MySQL and Sybase ASE and the retrieval was matter of seconds. Using Oracle it takes up to 30 seconds and above. Doing the same operation with another tool (i.e. TOAD) on the same table, the operation takes less than 2 seconds.

[serge-rider]

Ah, it is a neverending story about Oracle system schemas reading performance.
Please check this: http://dbeaver.jkiss.org/forum/viewtopic.php?f=2&t=1870&p=4484
It looks like Oracle 10/11/12 have some significantly differences here.

Could you also check Query Manager to see what metadata queries work so long?

[steromano87]

Hi Serge,

inspecting the Query Manager it seems that the execution time of the background queries (launched by DBeaver when the References tab is opened) is very short (1255ms to fetch almost 20000 records).
However, the GUI still shows the progress bar scrolling and the real duration of the query seems far greater:

After the complete execution of the queries, the time span between one execution and another seems unrelated to the shown execution times:

The longest metadata queries are the following:

SELECT /*+USE_NL(cc)*/ c.*,cc.COMMENTS
FROM SYS.ALL_TAB_COLS c
LEFT OUTER JOIN SYS.ALL_COL_COMMENTS cc ON CC.OWNER=c.OWNER AND cc.TABLE_NAME=c.TABLE_NAME AND cc.COLUMN_NAME=c.COLUMN_NAME
WHERE c.OWNER=?
ORDER BY c.COLUMN_ID

SELECT /*+RULE*/
c.TABLE_NAME, c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,c.STATUS,c.SEARCH_CONDITION,col.COLUMN_NAME,col.POSITION
FROM SYS.ALL_CONSTRAINTS c
LEFT OUTER JOIN SYS.ALL_CONS_COLUMNS col ON c.OWNER=col.OWNER AND c.CONSTRAINT_NAME=col.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE<>'R' AND c.OWNER=?
ORDER BY c.CONSTRAINT_NAME,col.POSITION

The strange thing is this...
If you open another tab that requires a query on SYS tables like Triggers, the overall execution is very fast, so I don't know if this issue is reallly related to bindings as indicated in the topic you pointed me to... the binding delay should apply to these queries too, but they seem not affected by it.

[serge-rider]

When you making a select from a table from some schema for the first time DBeaver caches all tables, columns an constraints in that schema. In the progress bar you see it as "binding". It takes time. In case of SYS schema it takes pretty much time. But all consequent queries should work fast.

Although 30sec is more than too much. Unless you are working with remote Oracle using slow network.
I don't see (from QM log) what could take so much time. Or maybe there are a lot of short metadata queries in it?

[steromano87]

Hi Serge,

yes, at work we have a remote Oracle server, but I don't suppose our network is so slow...
The Oracle DB resides on an Exadata machine and our LAN is 1Gbps grade.

I (hopefully) posted all the metadata queries that DBeaver performs :-(

[serge-rider]

I'll add a few metadata loading performance improvements.

[serge-rider]

Please check performance in 3.5.3

[steromano87]

Hi Serge,

retrieval time for Foreign Keys has dramatically improved, however DBeaver is still a little slow when retrieving References (on a table it took up to a minute to complete the retrieval query, whether the retrieval of Foreign Keys took less than 5 seconds).

[silverjim12]

Same problem in informix.
Dbeaver version 6.0.4