application is unresponsive which is running in tomcat connecting with jdbc8 to oracle db

Hello Fulvio!

 Greetings.

 I agree with your observation! Here is an example from your thread dump which confirms your theory. 

Thread 'https-jsse-nio-8443-exec-90' is stuck waiting for response from Oracle DB from 07:41:30 - 08:24:30. 

java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at oracle.net.ns.Packet.receive(Packet.java:313)
at oracle.net.ns.DataPacket.receive(DataPacket.java:126)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:310)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:252)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:172)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:90)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:130)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:86)
at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:393)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:733)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
- locked <0x00000006e578f1a8> (a oracle.jdbc.driver.T4CConnection)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)

Here are my thoughts to resolve this problem:

1. Apparently this Oracle DB call is originated from the below code path:

at CPSql.Query(cpsql.java:356)
at CPSql.Query(cpsql.java:353)
at CPSql.Query(cpsql.java:350)
at CPSecurity$RoleAssignementMethod.CacheSecurityTables(cpsecurity.java:776)
at CPSecurity$AuthorizationMethod.GetAuthorization(cpsecurity.java:1704)
at CPSecurity$AuthorizationMethod.GetAuthorization(cpsecurity.java:1682)
at CPSecurity$AuthorizationMethod.CanAccess(cpsecurity.java:2219)
at CPLicenseAuthorize.CanAccess(Unknown Source)
at VMNMenuLoader.CheckAccess(vmnmenuloader.java:975)
at SPXMLMenuProvider.ParserMenuItems(spxmlmenuprovider.java:291)
at SPXMLMenuProvider.doProcess(spxmlmenuprovider.java:162)

Can you check whether the issued SQL query takes long time to complete? Sometimes problem could be because of slow running SQL query as well. If so can you optimize it?

undefined 

2. Are you setting timeout for your JDBC connections? It doesn't looks like. If timeout settings are set, calls wouldn't be stuck for this long period. Here are few options:

a. There should certain timeout parameters which you can set at Oracle Connection pool level. 

b. You can consider passing following system properties to your JVM. More details about these properties can be found here

• -Dsun.net.client.defaultConnectTimeout
• -Dsun.net.client.defaultReadTimeout
• -Doracle.jdbc.ReadTimeout

3. This sort of problem could also surface due to wrong settings at the OS/kernel/network  parameters. Can you have your System administrator validate the settings. But most often times, System administrator will say settings are normal (even if there are abnormal things).  

You can use the open source yCrash script which will capture 360-degree application level artifacts (like GC logs, 3 snapshots of thread dumps, heap dumps) and system level artifacts (like top, top -H, netstat, vmstat, iostat, dmesg, diskusage, kernel parameters...). Once you have these data, either you can manually analyze them or upload it to yCrash tool, which will analyze all these artifacts and generate one unified root cause analysis marrying all these artifacts. Thus instead of relying on system administrators you yourself can validate the settings.

4. Can you validate whether server in which Oracle DB is running has sufficient memory, CPU capacity. Also validate the OS/kernel/network settings on the Oracle side.