JDBC 12.1.2 ojdbc7.jar Driver Generates Excessive Parsing with Select * Before INSERT Execution (Doc ID 2530434.1)

Last updated on MARCH 12, 2024

Applies to:

Symptoms

JDBC driver version 12.1.0.2.0 and JDK 1.7.0_75 is used for database access from a Java application which executes an insert with sequence NEXTVAL command like INSERT INTO ... (... seq.NEXTVAL).
The application has high-load functionality and generates about 350 rows per second from many WAS (WebSphere Application Server) nodes by different database connection pools.
After AWR report analysis, it is seen that there is huge parse operation like SELECT * without execution. Additional tests show that the issue happens from internal class JDBC driver. This causes additional memory to be used for useless operations when on a high-load system.

<span class="sBugInternalContent">The problem does not reproduce with 12.2.0.1 or 18.3 JDBC drivers.</span>

To reproduce:
1. Connect to any test schema in any test database and create the following database objects:
create table testKeys (key_column VARCHAR2(10) primary key, value_columnVARCHAR2(10));
create sequence testSeq;
 
2. Insert one row to TESTKEYS table in JDBC application:

"insert into testKeys..."

3. Execute the following query in your database: 

select * from v$sqlarea where upper(sql_text) like '%TESTKEYS%'

You will see 2 database commands:
- the insert command like this: "insert into testKeys..." executed from Java code -> expected.
- a strange query "SELECT * FROM TESTKEYS" which is automatically generated from Java.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.