0

I am having problem with multiple long running session happening in Oracle DB causing performance issues. Can anyone point out what parameter I need to change to alleviate this pain in the Database front. Or point out what is the problem.

We recently upgraded to jdk1.8 and Jboss 7.1.1EAP server from JDK1.6 and Jboss4.3.0GA and from Oracle SuSE to Linux Environment. The Database has not changed at all. Before this upgrade everything was working fine.

The standalone.xml has the following entries for datasource:

<datasource jndi-name="java:jboss/datasources/icamDataSrc" pool-name="icamDataSrc" enabled="true" use-java-context="true">
    <connection-url>xxxxxxxxxx</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <driver>oracle</driver>
    <pool>
       <min-pool-size>0</min-pool-size>
       <max-pool-size>500</max-pool-size>
    </pool>
    <security>
      <user-name>xxxxx</user-name>
      <password>xxxxxx</password>
    </security>
    <validation>
       <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
       <background-validation>true</background-validation>
       <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
       <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
    </validation>

enter image description here

enter image description here

DBA Comments : We have been noticing that there are session locks happening resulting into such slowness as there is application level concurrency happening.

Today, I have noticed that there were sessions locking/blocking each other.

CAM_APP@cghcts000000822.corporate.ge.com ( SID=1031 ) is blocking CAM_APP@cghcts000000817.corporate.ge.com ( SID=587 )
CAM_APP@cghcts000000822.corporate.ge.com ( SID=1031 ) is blocking CAM_APP@cghcts000000818.corporate.ge.com ( SID=587 )
@pwdbl0927pp02 ( SID=1031 ) is blocking
CAM_APP@cghcts000000817.corporate.ge.com ( SID=587 )
@pwdbl0927pp02 ( SID=1031 ) is blocking
CAM_APP@cghcts000000818.corporate.ge.com ( SID=587 )
CAM_APP@cghcts000000822.corporate.ge.com ( SID=1031 ) is blocking CAM_APP@cghcts000000822.corporate.ge.com ( SID=392 )
@pwdbl0927pp02 ( SID=1031 ) is blocking
CAM_APP@cghcts000000822.corporate.ge.com ( SID=392 )

Updates/Inserts/Deletes are all happening at the same time on the same table, and this pattern is seen since yesterday. Please review the jobs running and understand why such difference. This is an application locking that is happening, I do not see any issues with the database here.

APC
  • 144,005
  • 19
  • 170
  • 281
sromit
  • 900
  • 3
  • 16
  • 43
  • Does this answer your question? [How to check Oracle database for long running queries](https://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries) – OldProgrammer Jan 24 '20 at 17:14
  • There is no code change or query change in the application during the upgrade. Its just the env upgrade. After that these long running sessions have popped up making the application slow after the env upgrade. – sromit Jan 24 '20 at 17:19
  • Some thoughts: You say you have "recently" upgraded. The database problem started immediately with the upgrade? From JBoss 4.0 to EAP 7 a lot of things have changed, e.g. Hibernate. Default transaction behaviour on MDBs has changed too. Are you using the latest Oracle JDBC library? – Laertes Jan 24 '20 at 17:54
  • Let me check on that..Initially we were using ojdbc14.jar but now I think we should use ojbdc7.jar – sromit Jan 24 '20 at 18:43
  • The jboss server is currently using ojbc14.jars..if I change it to ojdbc7.jar..will the pain go away?? Yeah..the session pain started after we upgraded the env. – sromit Jan 24 '20 at 20:13
  • ojdbc14 is for Java 1.4, which means that it is a very old version of the library. I'd recommend using a ojdbc8 for the version of your database (or a more recent version, as newer drivers also support older databases). – Mark Rotteveel Jan 25 '20 at 15:29

0 Answers0