Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA INSERT caused Oracle Version_Count increase High(an Insert data from eclipselink JPA causing performance issues after Oracle version_count increased high)
JPA INSERT caused Oracle Version_Count increase High [message #1842424] Fri, 18 June 2021 09:07 Go to next message
WingHong Wong is currently offline WingHong WongFriend
Messages: 1
Registered: June 2021
Junior Member
We have a system using eclipselink as JPA connect to Oracle DB, and having a table with 176 fields, under this table have 87 VARCHAR2 fields. After some time the process of insert data become slow.
After investigate, in Oracle it show there are more than 100 (or few hundreds) of "VERSION_COUNT" for the INSERT statement into this table, which means the same INSERT statement into this 176 fields table have generated more than 100 of patterns in Oracle, we further investigate found that the INSERT statement created by eclipselink is something like below:
INSERT INTO TableName (Field1, Field2, Field3, .... Field175, Field176)
VALUES (?, ?, ?, .....?, ?)
And the variable that created to be set into above SQL (?) are always different, never follow the entity field length, such:
First time create insert statement for Field1 maybe declare @Field1 VARCHAR2(32) (but in entity set as length=50);
Second time create insert statement for Field1 maybe declare @Field1 VARCHAR2(128)
Third time maybe another length or maybe back to the same length as the first time (randomly)
Can I know whether we can fixed the datatype (or the length) of the field variables? Or is there any solution can resolve this problem?
Re: JPA INSERT caused Oracle Version_Count increase High [message #1842851 is a reply to message #1842424] Fri, 02 July 2021 20:11 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
Can you provide more detail on the problem and what you are looking for? I don't know what you mean by there being 100 Version_Count fields in an insert.
Varchar2 column sizes are maximums, not a fixed length column size - the length=50 specification on the column annotation is something that is used for DDL table generation, but has no play in the runtime - EclipseLink will pass through the values as they are in your entity. Within those 87 fields, all should be <= the maximum column size or the insert statement will fail.

Could you be asking about parameter binding? EclipseLInk should use parameter binding, which would allow prepared statements, especially for inserts, to be reused. This should be enabled by default as described here https://www.eclipse.org/eclipselink/documentation/2.5/jpa/extensions/q_bindparameters.htm

Best Regards,
Chris
Previous Topic:Using EclipseLink with latest Wildfly Version (24) ?
Next Topic:lazy instantiation of collections and the cache
Goto Forum:
  


Current Time: Sat Jul 24 01:58:13 GMT 2021

Powered by FUDForum. Page generated in 0.01892 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top