C# Dapper 連接 Oracle 資料庫 Internal Error 錯誤

今天在客戶家部屬/測試的時候很快就遇到很多個問題,正在焦頭爛額 Debug 的時候,有一個錯誤比較特別:Internal Error
    
System.Exception: Internal Error
   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 clientInitialLOBFetchSize, Int64 initialLOBFetchSize, Int64 initialJSONFetchSize, 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, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, 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 Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1075
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1094
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    

一開始就想到可能是連線問題,是不是客戶的 Oracle 資料庫爆了?但是還可以連線阿,看起來像是我使用的資料庫連線套件 Dapper 的問題,但是又更像是 Oracle 的問題?

研究了一翻發現很可能是因為舊版 Oracle 的 ODP.NET 的 SelfTuning 有問題,只要停用就可以了,透過修改連接字串很簡單就可以達成:

在 Oracle 的連接字串中加上 Self Tuning = False; 即可,範例:
    
"DATA SOURCE=192.168.1.100:1521/orcl;USER ID=user;PASSWORD=123;PERSIST SECURITY INFO=True;Self Tuning = False;
    

後來回家後在網路上衝浪研又究了半小時後還是沒有找到具體的原因,只是了解到 SelfTuning 是 Oracle 自動的效能調整工具,停用就不會自動調整會比較慢, 還是似懂非懂,不過 oracle 11g 這麼舊的版本(現在是 21c)希望不要有人遇到同樣的問題。

參考資料:
oracle.doc - 3.3 Connecting to Oracle Database
Stack Overflow - ODAC seems to be caching table schema?
GitHub Issues - DapperLib/Dapper "Internal error" query after adding new column to table/view #1751
oracle.doc - Automatic SQL Tuning
oracle.doc - SelfTuning
IEEE Xplore - Self-Tuning for SQL Performance in Oracle Database 11g

留言