ODP.NET Core problem with timeout — oracle-tech

    Forum Stats

  • 3,715,657 Users
  • 2,242,821 Discussions
  • 7,845,481 Comments

Discussions

Howdy, Stranger!

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

ODP.NET Core problem with timeout

livioc
livioc Member Posts: 7
edited May 2019 in ODP.NET

hi, on a remote machine i have an asp.net core 2.2 app with odp.net core 2.18.6 and the last EF beta 3 packages.

i get a timeout error randomly that i cannot reproduce on my local dev machine, the strange thing is that from the first line of the log it seems that the total duration of the command is <1s (958,523ms) but the timeout threshold is infinite (timeout=0).

note: the query execution is always immediate, it select 2 record from a 100 rows table.

how can i fix/debug the problem?

thanks

this is the full log

Microsoft.EntityFrameworkCore.Database.Command | Failed executing DbCommand (958,523ms) [Parameters=[], CommandType='Text', CommandTimeout='0']

SELECT "x"."ID", "x"."CODICE", "x"."NOME"

FROM "ANAG"."ITEMS" "x"

WHERE "x"."CODICE" IN (N'201', N'205') | Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12570: Network Session: Unexpected packet read error ---> OracleInternal.Network.NetworkException (0x80004005): ORA-12570: Network Session: Unexpected packet read error ---> OracleInternal.Network.NetworkException (0x80004005): ORA-12570: Network Session: Unexpected packet read error ---> System.Net.Sockets.SocketException (110): Connection timed out

   at OracleInternal.Network.ReaderStream.ReadIt(OraBuf OB, Int32 len)

   at OracleInternal.Network.ReaderStream.ReadIt(OraBuf OB, Int32 len)

   at OracleInternal.Network.ReaderStream.ReadwithCrypto(OraBuf OB)

   at OracleInternal.Network.ReaderStream.ReadwithCrypto(OraBuf OB)

   at OracleInternal.Network.ReaderStream.Read(OraBuf OB)

   at OracleInternal.TTC.OraBufReader.GetDataFromNetwork()

   at OracleInternal.TTC.OraBufReader.Read(Boolean bIgnoreData)

   at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)

   at Oracle.ManagedDataAccess.Client.OracleException.HandleError(OracleTraceLevel level, OracleTraceTag tag, OracleTraceClassName className, OracleTraceFuncName funcName, Exception ex, OracleLogicalTransaction oracleLogicalTransaction)

   at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)

   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)

   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)

   at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)

   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()

   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)

   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

   //my code invoking EF...

   at lambda_method(Closure , Object , Object[] )

   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()

   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()

   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)

   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)

   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()

   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()

   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)

   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)

   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()

   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()

   at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)

   at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext)

   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)

   at MyWS.WebUtils.JsonExceptionMiddleware.Invoke(HttpContext context) in C:\Users\*\Source\Workspaces\Workspace\MyWS\1_DEV\DEV\MyWS\WebUtils\JsonExceptionMiddleware.cs:line 38    at Oracle.ManagedDataAccess.Client.OracleException.HandleError(OracleTraceLevel level, OracleTraceTag tag, OracleTraceClassName className, OracleTraceFuncName funcName, Exception ex, OracleLogicalTransaction oracleLogicalTransaction)

   at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)

   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)

   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)

   at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)

   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()

   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)

   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

   //my code invoking EF...

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited May 2019

    It is odd that you are encountering a connection timeout when executing a command. When you say the problem happens randomly does that mean it happens on random queries or does it happen for one specific query, just not every time? If it's the latter, is there anything notable about that query when you run it by itself against the DB outside your app?

  • livioc
    livioc Member Posts: 7
    edited May 2019

    it happens about 1/2 times a day only for one specific query which is executed a lot of times by our application so i don't know if the problem is in that specific statement or if it's only more probable that happens there.

    the query is a simple select on a 100 rows table which normally is executed immediately (either by odp.net or sql developer)

    SELECT "x"."ID", "x"."CODICE", "x"."NOME"

    FROM "ANAG"."ITEMS" "x"

    WHERE "x"."CODICE" IN (N'201', N'205')

    can i enable some other log to improve the debug info?

    thanks

    update: i've checked other logs and the same problem happened with other queries too, also the other queries are simply instantaneous select (either by odp.net or sql developer)

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited May 2019

    You can enable tracing by adding the following namespace to your app:

    using Oracle.ManagedDataAccess.Client;

    Then, when your app starts up, be sure to add something equivalent to these two lines of code:

    OracleConfiguration.TraceFileLocation = @D:\traces;

    OracleConfiguration.TraceLevel = 7;

    You can email the log to dotnet_us (at) oracle.com for us to take a look.

Sign In or Register to comment.