ODP.Net Connection request timed out in multithread — oracle-tech

    Forum Stats

  • 3,716,127 Users
  • 2,242,960 Discussions
  • 7,845,836 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

ODP.Net Connection request timed out in multithread

_E_T_
_E_T_ Member Posts: 2
edited April 2019 in ODP.NET

Hi,

Sometimes we encounter exceptions like this on our WebApi application with Oracle Poolmanager :

à OracleInternal.ConnectionPool.PoolManager3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, CriteriaCtx criteriaCtx, String affinityInstanceName, Boolean bForceMatch)

à OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, CriteriaCtx criteriaCtx, String affinityInstanceName, Boolean bForceMatch)

à OracleInternal.ConnectionPool.OracleConnectionDispenser3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, CriteriaCtx criteriaCtx)

à Oracle.ManagedDataAccess.Client.OracleConnection.Open()

This problem is reproducible using ODP.Net 2.2 in .net 4.5.1 and .net Core 2.2.

Here is sample code to reproduce it :

<span class="kwd">private</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">ParallelConnection</span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> nbConnection</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> connectionString</span><span class="pun">)</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> watch </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Stopwatch</span><span class="pun">.</span><span class="typ">StartNew</span><span class="pun">();</span><span class="pln"><br/>   </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">WriteLine</span><span class="pun">(</span><span class="pln">$</span><span class="str">"Testing {nbConnection} Connections"</span><span class="pun">);</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> taskList </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">List</span><span class="pun">&lt;</span><span class="typ">Task</span><span class="str">&lt;string&gt;</span><span class="pun">&gt;();</span><span class="pln"><br/>   </span><span class="kwd">for</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> i </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pun">;</span><span class="pln"> i </span><span class="pun">&lt;</span><span class="pln"> nbConnection</span><span class="pun">;</span><span class="pln"> i</span><span class="pun">++)</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>  taskList</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="typ">OpenConnectionSimple</span><span class="pun">(</span><span class="pln">i </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> connectionString</span><span class="pun">));</span><span class="pln"><br/>   </span><span class="pun">}</span><span class="pln"><br/>   </span><span class="typ">Task</span><span class="pun">.</span><span class="typ">WaitAll</span><span class="pun">(</span><span class="pln">taskList</span><span class="pun">.</span><span class="typ">ToArray</span><span class="pun">());</span><span class="pln"><br/>  taskList</span><span class="pun">.</span><span class="typ">ForEach</span><span class="pun">(</span><span class="pln">t </span><span class="pun">=&gt;</span><span class="pln"> </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">WriteLine</span><span class="pun">(</span><span class="pln">t</span><span class="pun">.</span><span class="typ">Result</span><span class="pun">));</span><span class="pln"><br/>   </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">WriteLine</span><span class="pun">(</span><span class="pln">$</span><span class="str">"Total elapsed {watch.ElapsedMilliseconds}"</span><span class="pun">);</span><span class="pln"><br/>   </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">ReadLine</span><span class="pun">();</span><span class="pln"><br/>   </span><span class="pun">}</span><span class="pln"><br/><br/></span><span class="kwd">private</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">async</span><span class="pln"> </span><span class="typ">Task</span><span class="str">&lt;string&gt;</span><span class="pln"> </span><span class="typ">OpenConnectionSimple</span><span class="pun">(</span><span class="kwd">int</span><span class="pln"> index</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">string</span><span class="pln"> connectionString</span><span class="pun">)</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>   </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">await</span><span class="pln"> </span><span class="typ">Task</span><span class="str">&lt;string&gt;</span><span class="pun">.</span><span class="typ">Factory</span><span class="pun">.</span><span class="typ">StartNew</span><span class="pun">(()</span><span class="pln"> </span><span class="pun">=&gt;</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> error </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">string</span><span class="pun">.</span><span class="typ">Empty</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> startTime </span><span class="pun">=</span><span class="pln"> </span><span class="typ">DateTime</span><span class="pun">.</span><span class="typ">Now</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> watch </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Stopwatch</span><span class="pun">.</span><span class="typ">StartNew</span><span class="pun">();</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> date </span><span class="pun">=</span><span class="pln"> </span><span class="typ">DateTime</span><span class="pun">.</span><span class="typ">MinValue</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">long</span><span class="pln"> timeCreated </span><span class="pun">=</span><span class="pln"> </span><span class="pun">-</span><span class="lit">1</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">long</span><span class="pln"> timeOpen </span><span class="pun">=</span><span class="pln"> </span><span class="pun">-</span><span class="lit">1</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">try</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>   </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">var</span><span class="pln"> cnx </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">OracleConnection</span><span class="pun">(</span><span class="pln">connectionString</span><span class="pun">))</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>  timeCreated </span><span class="pun">=</span><span class="pln"> watch</span><span class="pun">.</span><span class="typ">ElapsedMilliseconds</span><span class="pun">;</span><span class="pln"><br/>  cnx</span><span class="pun">.</span><span class="typ">Open</span><span class="pun">();</span><span class="pln"><br/>  timeOpen </span><span class="pun">=</span><span class="pln"> watch</span><span class="pun">.</span><span class="typ">ElapsedMilliseconds</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">using</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">var</span><span class="pln"> cmd </span><span class="pun">=</span><span class="pln"> cnx</span><span class="pun">.</span><span class="typ">CreateCommand</span><span class="pun">())</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>  cmd</span><span class="pun">.</span><span class="typ">CommandType</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Data</span><span class="pun">.</span><span class="typ">CommandType</span><span class="pun">.</span><span class="typ">Text</span><span class="pun">;</span><span class="pln"><br/>  cmd</span><span class="pun">.</span><span class="typ">CommandText</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"SELECT SYSDATE FROM DUAL"</span><span class="pun">;</span><span class="pln"><br/>  date </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DateTime</span><span class="pun">)</span><span class="pln">cmd</span><span class="pun">.</span><span class="typ">ExecuteScalar</span><span class="pun">();</span><span class="pln"><br/>   </span><span class="pun">}</span><span class="pln"><br/>   </span><span class="pun">}</span><span class="pln"><br/>   </span><span class="pun">}</span><span class="pln"><br/>   </span><span class="kwd">catch</span><span class="pln"> </span><span class="pun">(</span><span class="typ">Exception</span><span class="pln"> ex</span><span class="pun">)</span><span class="pln"><br/>   </span><span class="pun">{</span><span class="pln"><br/>  error </span><span class="pun">=</span><span class="pln"> ex</span><span class="pun">.</span><span class="typ">Message</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="pun">}</span><span class="pln"><br/>  watch</span><span class="pun">.</span><span class="typ">Stop</span><span class="pun">();</span><span class="pln"><br/>   </span><span class="kwd">var</span><span class="pln"> state </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">string</span><span class="pun">.</span><span class="typ">IsNullOrWhiteSpace</span><span class="pun">(</span><span class="pln">error</span><span class="pun">))</span><span class="pln"> </span><span class="pun">?</span><span class="pln"> </span><span class="str">"OK"</span><span class="pln"> </span><span class="pun">:</span><span class="pln"> $</span><span class="str">"KO :{error}"</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="kwd">return</span><span class="pln"> $</span><span class="str">"[{index}]\t start: {startTime:HH:mm:ss.fff} duration:{watch.ElapsedMilliseconds}ms\t Ctor Connection:{timeCreated}ms \t open Connection:{timeOpen}ms \t SYSDATE Oracle: {date}\t State:{state}"</span><span class="pun">;</span><span class="pln"><br/>   </span><span class="pun">});</span><span class="pln"><br/>   </span><span class="pun">}</span>

NDqCK.png

As you can see, calling ParallelConnection method with 20 connections make the 4 first threads in Timeout over default 15 seconds, and connection time of other threads are not reasonable !

The common workaround found on the web is to make connection timeout bigger, but in our case, response time of our web api is not reasonable.

Does someone can Explain why opening first connection in ODP.Net take this enormous time in multithread ?

Is there a workaround to avoid this connection timeout exception in Web Api application ?

Thanks in advance.

Answers

  • _E_T_
    _E_T_ Member Posts: 2
    edited April 2019

    Hi,

    Can someone reproduce this behaviour ?

    is there a workaround to make my WebApi working under load ?

    Thanks,

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

    It's possible you are encountering a connection storm scenario.

    One way to minimize the effects of a connection storm is to make sure your pool has enough connections ahead of time ready to dispense. You do this by connecting to the DB ahead of the storm to prime the pool and set the Min Pool Size high enough so that ODP.NET can dispense enough existing connections from the pool rather than create new ones, which is much more expensive.

    Another way to limit connection is to use the RATE_LIMIT parameter in the listener configuration so that the DB does not get overwhelmed with connection requests.

Sign In or Register to comment.