Oracle_fdw takes a long time to query data and has slow query efficiency

[ruralqiu]

Hello，Querying Oracle data through oracle_fdw takes 70 seconds for the first query to return the result set, and 29 milliseconds for subsequent queries. The query plan is the same when viewed in pg. Can this issue be resolved in the code of the oracle_fdw extension? SQL： EXPLAIN ANALYZE VERBOSE select key, value FROM gc_dict_commons WHERE isdel = 1 and parentid = 72 order by sort; First query plan： Foreign Scan on sdslgcxxxttest.gc_dict_commons (cost=10000.00..20000.00 rows=1000 width=468) (actual time=5.096..5.169 rows=16 loops=1) Output: key, value, sort Filter: ((gc_dict_commons.isdel)::double precision = '1'::double precision) Oracle query: SELECT /588f1cd538f73850528c7b3ec8922e3a/ r1."PARENTID", r1."KEY", r1."VALUE", r1."ISDEL", r1."SORT" FROM "SDSLGCJYPTTEST"."GC_DICT_COMMONS" r1 WHERE (r1."PARENTID" = 72) ORDER BY r1."SORT" ASC NULLS LAST Oracle plan: SELECT STATEMENT Oracle plan: SORT ORDER BY Oracle plan: TABLE ACCESS FULL GC_DICT_COMMONS (filter "R1"."PARENTID"=72) Planning Time: 70156.017 ms Execution Time: 5.219 ms

Second query plan： Foreign Scan on sdslgcxxxttest.gc_dict_commons (cost=10000.00..20000.00 rows=1000 width=468) (actual time=3.645..3.717 rows=16 loops=1) Output: key, value, sort Filter: ((gc_dict_commons.isdel)::double precision = '1'::double precision) Oracle query: SELECT /588f1cd538f73850528c7b3ec8922e3a/ r1."PARENTID", r1."KEY", r1."VALUE", r1."ISDEL", r1."SORT" FROM "SDSLGCJYPTTEST"."GC_DICT_COMMONS" r1 WHERE (r1."PARENTID" = 72) ORDER BY r1."SORT" ASC NULLS LAST Oracle plan: SELECT STATEMENT Oracle plan: SORT ORDER BY Oracle plan: TABLE ACCESS FULL GC_DICT_COMMONS (filter "R1"."PARENTID"=72) Planning Time: 7.217 ms Execution Time: 3.753 ms

[laurenz]

The first execution plan has

Planning Time: 70156.017 ms
Execution Time: 5.219 ms

and the second one

Planning Time: 7.217 ms
Execution Time: 3.753 ms

When oracle_fdw plans the foreign scan, it connects to the Oracle database and gets the table structure from Oracle.

If getting the table structure took a long time, the planning time would be high for the second query as well. Also, oracle_fdw keeps the connection to Oracle open for the whole time of the database session, so it only connects to Oracle when the first query is planned.

So it is almost certain that it is the connection to Oracle which takes absurdly long. You can verify that by connecting to the Oracle database from the PostgreSQL machine using sqlplus. That should take just as long.

If my theory is correct, this is a problem with Oracle and/or your network, and there is little I can do for you.

[ruralqiu]

Thank you, it did take me one minute to access Oracle using sqlplus on the PG server. Additionally, can oracle_fdw queries improve SQL query speed and enhance the query performance of oracle_fdw extensions?

[laurenz]

I don't understand your question.

[ruralqiu]

I don't understand your question.

Oracle_fdw improves the performance of connection queries on the result set returned by Oracle

[mkgrgis]

Oracle_fdw improves the performance of connection queries on the result set returned by Oracle

No, @ruralqiu. Oracle_fdw for OracleDB is only client program such as other client programs. Query execution and data transport doesn't depends on client program. Oracle_fdw as C program can have only fast result data processing for PostgreSQL environment.

[laurenz]

I don't know what a "connection query" is, and I don't understand how you can have a query on a result set.

Anyway, your problem is the slow speed of connecting to the Oracle server. You'll have to debug that with Oracle and/or networking techniques.

[laurenz]

How are things? Do you need more help from my side or can we close this issue?

[ruralqiu]

How are things? Do you need more help from my side or can we close this issue?

The issue has been resolved and can be closed. Thank you very much