Hello,
when I execute a SELECT via a PreparedStatement, on a table being "DEFAULT COLLATION EBCDIC", with a large volume of data (several million rows), I notice that the query is very slow.
For example, on a table with 400 million rows, the SELECT of a single row takes 11 minutes!
If I make an extract from this table (3 million rows), I get the following execution times:
- 5s (preparedStatement to table with COLLATION)
- 0.2s (same query, but Statement, to table with COLLATION)
- 0.006s (preparedStatement to table without COLLATION)
Tested in several ways: with JDBC drivers ojdbc7 (version 12.1.0.2.0) and ojdbc8 (19.21.0.0.0), from a Windows workstation and from a SLES15 server, to an Oracle database 19.17.0.0.0 or 19.21.0.0.0. Statistics are up to date on all tables. The performance problem was observed in all configurations.
We ran the test in pure SQL, and found that the execution plan is not the same via the JDBC driver:
via JDBC (index range => generate filter access):
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TESTEBCDIC | 1 | 137 | 24 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ITESTEBCDIC1 | 1 | | 24 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((NLSSORT("LIB6",'nls_sort=''EBCDIC''')=NLSSORT(:5,'nls_sort=''EBCDIC''') AND
NLSSORT("LIB7",'nls_sort=''EBCDIC''')=NLSSORT(:6,'nls_sort=''EBCDIC''') AND
NLSSORT("LIB10",'nls_sort=''EBCDIC''')=NLSSORT(:9,'nls_sort=''EBCDIC''') AND
NLSSORT("LIB8",'nls_sort=''EBCDIC''')=NLSSORT(:7,'nls_sort=''EBCDIC''') AND
NLSSORT("LIB4",'nls_sort=''EBCDIC''')=NLSSORT(:3,'nls_sort=''EBCDIC''') AND
NLSSORT("LIB3",'nls_sort=''EBCDIC''')=NLSSORT(:2,'nls_sort=''EBCDIC''')))
2 - access("LIB2"=:1 AND "LIB5"=:4 AND "LIB9"=:8)
filter(("LIB5"=:4 AND "LIB9"=:8))
SQL (correct access):
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTEBCDIC | 1 | 313 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | ITESTEBCDIC1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LIB2"=:CLE2 AND "TESTEBCDIC"."SYS_NC00011$"=NLSSORT(:CLE3,'nls_sort=''
EBCDIC''') AND "TESTEBCDIC"."SYS_NC00012$"=NLSSORT(:CLE4,'nls_sort=''EBCDIC''') AND
"LIB5"=:CLE5 AND "TESTEBCDIC"."SYS_NC00013$"=NLSSORT(:CLE6,'nls_sort=''EBCDIC''')
AND "TESTEBCDIC"."SYS_NC00014$"=NLSSORT(:CLE7,'nls_sort=''EBCDIC''') AND
"TESTEBCDIC"."SYS_NC00015$"=NLSSORT(:CLE8,'nls_sort=''EBCDIC''') AND
"LIB9"=TO_DATE(:CLE9,'YYYY-MM-DD') AND "TESTEBCDIC"."SYS_NC00016$"=NLSSORT(:CLE10,'nl
s_sort=''EBCDIC'''))
Thanks for help!