Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Concurrency waits - Delete & Insert running same time

DBA112Apr 2 2015 — edited Apr 6 2015

Dear DBA Friends,

DB version - 11.1.0.7

We have a table that receives  Inserts 24/7 ( nearly 2 Million rows inserted each day) and we have a purge (Delete  - Row ID based ) that runs every 4 hours and deletes (anything older than 2 days)...Total data volume in this table is always b/w 3 to 4 Million. Table is non-partitioned, but has 3 indices (All indices are Reverse key, partitioned with 128 partitions for each index)


Now the problem is, we are seeing huge concurrency waits whenever the purge is colliding with insert activity. The wait duration is usually b/w 5 - 20 min and is self resolved. But when concurrency occurs, app is seeing failures with data load activity...


Question is - If purge is deleting older data and inserts are new data that is being loaded, why concurrency waits? Table does not acquire an exclusive lock when its deleting rows... so why waits ?


Trying to understand how to troubleshoot the issue and avoid app failures.


Here's how Delete and Insert statements look for our application -








Comments

obrienmi8
Hi,
EclipseLink is the JPA provider for TopLink, you may also use the native TopLink or native EclipseLink API. There is a post below that describes our JPA strategy in more detail in the last comment - I quote.... "In Oracle TopLink 11g we upgraded our JPA implementation to be EclipseLink JPA. This is our strategic persistence solution going forward"

907185

Here is the link to our corporate JPA page, quote.... "As of Oracle TopLink 11g, TopLink Essentials has been replaced with EclipseLink JPA. EclipseLink JPA implements JPA 1.0 and is responsible for delivering the persistence framework."

http://www.oracle.com/technology/products/ias/toplink/jpa/index.html

EclipseLink JPA is also the reference implementation for JPA 2.0 / EJB 3.1 as part of JSR-317 in JEE6 for Eclipse.

http://www.eclipse.org/org/press-release/20080317_Eclipselink.php


Here is a tutorial that details how to get setup running an EE container application on WebLogic Server using EclipseLink JPA as the EJB 3.0 provider.

http://wiki.eclipse.org/EclipseLink/Examples/JPA/WebLogic_Web_Tutorial

For caching, in addition to the EclipseLink cache see TopLink Grid
http://www.oracle.com/technology/products/ias/toplink/tl_grid.html


thank you
/michael
www.eclipselink.org
User756798-Oracle
Thank for responding.

Sounds good, if Eclipselink is the supported implementation for EJB3/JPA and since TopLink essentials is replaced by Eclipselink - It seems like I will not be able to use many of the advanced features of Oracle TopLink as mentioned in the TopLink FAQ at http://www.oracle.com/technology/products/ias/toplink/jpa/resources/faq.html#13.

TopLink Essentials is a subset of the functionality available in Oracle TopLink. The initial goal of TopLink Essentials is to provide a commercial quality reference implementation of JPA. They share the same core Object-Relational Mapping capabilities that have been key infrastructure of many enterprise Java application for over a decade.

Oracle TopLink offers additional functionality beyond TopLink Essentials and the JPA specification. Some of the key features include:

* Advanced ORM Capabilities
o Coordinated caching to support clustered application deployments
o Additional non-intrusive optimistic locking policies
o Oracle DB features
+ Virtual Private Database (VPD)
+ XMLType mapping and SQLX query generation
+ Hints
+ Hierarchical querying
+ Object-Relational mappings Arrays, Structures, Object references, and nested tables
+ Custom LOB, TIMESTAMP*, and double-byte data types
o Platform independent stored procedure and function support
o Historical mapping and point-in-time querying
o JMX MBeans allowing management and monitoring of TopLink sessions and their caches
* Object-XML mapping implementing JAXB 1.0 and early support for JAXB 2.0 functionality
* Support for mapping to EIS using JCA resource adapters

Do I really need TopLink Grid edition when distributed caching is already supported by Oracle TopLink which I already purchased from Oracle?
Am I right by stating that If I need to use EJB3/JPA with WLS 10, then I cannot use any of the advanced features of Oracle TopLink?

THX again.
obrienmi8
Hi,
Everything that was available in commercial TopLink is still available except the JPA provider has been changed to EclipseLink JPA which is a currently enhanced version of TopLink JPA which was donated as EclipseLink in 2007. You may want to discuss this with product management because there are several solutions available to you besides TopLink Grid.

WebLogic includes a full version of TopLink, also EclipseLink is shipped as part of TopLink as its' JPA provider - it is not a replacement for TopLink Essentials - it is a change to the JPA provider only.

You should find versions of the two jars in the modules directory similar to the following.

com.oracle.toplink_1.0.0.0_11-1-1-1-0.jar
org.eclipse.persistence_1.0.0.0_1-1-0.jar

If you are running TopLink in WebLogic then you have full access and support for TopLink - this includes the complete TopLink native API and TopLink JPA using the EclipseLink JPA provider.

I cannot comment on the reference IAS page on Essentials linked to from the page I quoted you but it looks to be written for OAS in 2006 - it is more geared to OC4J - thanks for bringing it to our attention.

For your last question, you do not need to use TopLink Grid if you do not need Coherence API functionality. You do however, have access to all previous TopLink native APIs like EIS,hints, extended DB functionality etc.. in addition to using EclipseLink JPA as your EJB 3 provider.

thank you
/michael
User756798-Oracle
Michael,

I followed the instructions at http://wiki.eclipse.org/EclipseLink/Examples/JPA/WebLogic_Web_Tutorial and was able to create and deploy an EJB3/JPA application on WLS 10gR3 connected to MySQL.

However, the modules you mentioned are no longer available from within WLS.

Using SmartUpdate I applied patch 9J3A which installed eclipselink.jar & toplink.jar under patches directory.
I copied both these jar files along with javax.persistence_1.0.0.0_1-0.jar from <WLS_HOME>/modules into to <DOMAIN_HOME>/lib.


THX for all your comments.
obrienmi8
Hi,
Thank you very much for validating the smart update patch mechanism for WebLogic 10.3 and for help with the configuration of patch jars.
I have updated and refactored the following section of the tutorial that details several methods of obtaining libraries for commercial customers and running them on WebLogic 10.3.

http://wiki.eclipse.org/EclipseLink/Examples/JPA/WebLogic_Web_Tutorial#Option_1:_Apply_Smart_Update_Patch_-_Recommended

In the first scenario I validated everything as-is after patching 10.3.0 with +9J3A+, where the javax.persistence specification jar stays in modules and the patch jars are appended to the end of the classpath after the weblogic.jar by the smart update tool using the MANIFEST-only empty jar weblogic_ext_patch.jar containing path references to the actual 3 jars in the patch directory outside the domain. Moving the jars into each domain is another usefull scenario.

thank you
/michael
james_sutherland
"As of Oracle TopLink 11g, TopLink Essentials has been replaced with EclipseLink JPA. EclipseLink JPA implements JPA 1.0 and is responsible for delivering the persistence framework."
Just a note that as of TopLink 11g, EclipseLink replaces the "entire" TopLink class library, not just the JPA part that TopLink Essentials implemented. EclipseLink contains the complete functionality of TopLink, including advanced ORM, XML, SDO and EIS support. The TopLink product basically bundles EclipseLink and adds some extended integration to the Oracle product stack, such as the Coherence TopLink Grid integration. Although the old toplink.jar is still provided in TopLink 11g, this is only for backward compatibility for applications that are unable to rename their TopLink package usage to the new EclipseLink package naming, this toplink.jar is not TopLink, TopLink is the eclipselink.jar.

---
James : http://www.eclipselink.org
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 4 2015
Added on Apr 2 2015
19 comments
11,104 views