Dot-Net

OracleDataReader.Read 方法超時

  • August 6, 2013

ODP.NET OracleCommand 類有一個 CommandTimeout 屬性，可用於強制執行命令超時。此屬性似乎在 CommandText 是 SQL 語句的情況下起作用。範常式式碼用於說明此屬性的實際作用。在程式碼的初始版本中，CommandTimeout 設置為零 - 告訴 ODP.NET 不要強制超時。

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace ConsoleApplication3
{
   class Program
   {
       static void Main(string[] args)
       {
           using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;"))
           using (OracleCommand cmd = new OracleCommand())
           {
               con.Open();                
               cmd.Connection = con;

               Console.WriteLine("Executing Query...");

               try
               {
                   cmd.CommandTimeout = 0;

                   // Data set SQL:
                   cmd.CommandText = "<some long running SQL statement>";
                   cmd.CommandType = System.Data.CommandType.Text;

                   Stopwatch watch1 = Stopwatch.StartNew();
                   OracleDataReader reader = cmd.ExecuteReader();
                   watch1.Stop();
                   Console.WriteLine("Query complete.  Execution time: {0} ms", watch1.ElapsedMilliseconds);

                   int counter = 0;
                   Stopwatch watch2 = Stopwatch.StartNew();
                   if (reader.Read()) counter++;
                   watch2.Stop();
                   Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds);

                   Stopwatch watch3 = Stopwatch.StartNew();
                   while (reader.Read())
                   {
                       counter++;
                   }
                   watch3.Stop();
                   Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds);
                   Console.WriteLine("Records read: {0}", counter);
               }
               catch (OracleException ex)
               {
                   Console.WriteLine("Exception was thrown: {0}", ex.Message);
               }

               Console.WriteLine("Press any key to continue...");
               Console.Read();
           }
       }
   }
}

上述程式碼的範例輸出如下所示：

Executing Query...
Query complete.  Execution time: 8372 ms
First record read: 3 ms
Records 2..n read: 1222 ms
Records read: 20564
Press any key to continue...

如果我將 CommandTimeout 更改為 3 之類的…

cmd.CommandTimeout = 3;

…然後執行相同的程式碼會產生以下輸出：

Executing Query...
Exception was thrown: ORA-01013: user requested cancel of current operation
Press any key to continue...

不過，呼叫返回 ref 游標的儲存過程是另一回事。考慮下面的測試過程（純粹用於測試目的）：

PROCEDURE PROC_A(i_sql VARCHAR2, o_cur1 OUT SYS_REFCURSOR)
is
begin

   open o_cur1
   for
   i_sql;

END PROC_A;

下面的範常式式碼可用於呼叫儲存過程。請注意，它將 CommandTimeout 設置為值 3。

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace ConsoleApplication3
{
   class Program
   {
       static void Main(string[] args)
       {
           using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;"))
           using (OracleCommand cmd = new OracleCommand())
           {
               con.Open();                
               cmd.Connection = con;

               Console.WriteLine("Executing Query...");

               try
               {
                   cmd.CommandTimeout = 3;

                   string sql = "<some long running sql>";
                   cmd.CommandText = "PROC_A";
                   cmd.CommandType = System.Data.CommandType.StoredProcedure;
                   cmd.Parameters.Add(new OracleParameter("i_sql", OracleDbType.Varchar2) { Direction = ParameterDirection.Input, Value = sql });
                   cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output });

                   Stopwatch watch1 = Stopwatch.StartNew();
                   OracleDataReader reader = cmd.ExecuteReader();
                   watch1.Stop();
                   Console.WriteLine("Query complete.  Execution time: {0} ms", watch1.ElapsedMilliseconds);

                   int counter = 0;
                   Stopwatch watch2 = Stopwatch.StartNew();
                   if (reader.Read()) counter++;
                   watch2.Stop();
                   Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds);

                   Stopwatch watch3 = Stopwatch.StartNew();
                   while (reader.Read())
                   {
                       counter++;
                   }
                   watch3.Stop();
                   Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds);
                   Console.WriteLine("Records read: {0}", counter);
               }
               catch (OracleException ex)
               {
                   Console.WriteLine("Exception was thrown: {0}", ex.Message);
               }

               Console.WriteLine("Press any key to continue...");
               Console.Read();
           }
       }
   }
}

上面程式碼的範例輸出如下所示：

Executing Query...
Query complete.  Execution time: 34 ms
First record read: 8521 ms
Records 2..n read: 1014 ms
Records read: 20564
Press any key to continue...

請注意，執行時間非常快（34 毫秒），並且沒有引發超時異常。我們在這裡看到的性能是因為引用游標的 SQL 語句直到第一次呼叫 OracleDataReader.Read 方法時才執行。當第一次呼叫 Read() 以從 refcursor 讀取第一條記錄時，會導致長時間執行的查詢對性能造成影響。

我已經說明的行為意味著 OracleCommand.CommandTimeout 屬性不能用於取消與引用游標關聯的長時間執行的查詢。在這種情況下，我不知道 ODP.NET 中有任何屬性可用於限制引用游標 SQL 的執行時間。有人對長時間執行的引用游標 SQL 語句的執行如何在一定時間後短路有任何建議嗎？

這是我最終採用的解決方案。它只是 OracleDataReader 類的擴展方法。該方法有一個超時值和一個回調函式作為參數。回調函式通常（如果不總是）是 OracleCommand.Cancel。

namespace ConsoleApplication1
{
   public static class OracleDataReaderExtensions
   {
       public static bool Read(this OracleDataReader reader, int timeout, Action cancellationAction)
       {
           Task<bool> task = Task<bool>.Factory.StartNew(() => 
               {
                   try
                   {
                       return reader.Read();
                   }
                   catch (OracleException ex)
                   {
                       // When cancellationAction is called below, it will trigger 
                       // an ORA-01013 error in the Read call that is still executing.
                       // This exception can be ignored as we're handling the situation
                       // by throwing a TimeoutException.
                       if (ex.Number == 1013)
                       {
                           return false;
                       }
                       else
                       {
                           throw;
                       }
                   }
               });

           try
           {
               if (!task.Wait(timeout))
               {
                   // call the cancellation callback function (i.e. OracleCommand.Cancel())
                   cancellationAction();

                   // throw an exception to notify calling code that a timeout has occurred
                   throw new TimeoutException("The OracleDataReader.Read operation has timed-out.");
               }
               return task.Result;
           }
           catch (AggregateException ae)
           {
               throw ae.Flatten();
           }
       }
   }
}

這是一個如何使用它的範例。

namespace ConsoleApplication1
{
   class Program
   {
       static string constring = "User ID=xxxx; Password=xxxx; Data Source=xxxx;";

       static void Main(string[] args)
       {
           using (OracleConnection con = new OracleConnection(constring))
           using (OracleCommand cmd = new OracleCommand())
           {
               cmd.Connection = con;
               con.Open();

               Console.WriteLine("Executing Query...");

               string sql = "<some long running sql>";
               cmd.CommandText = "PROC_A";
               cmd.CommandType = System.Data.CommandType.StoredProcedure;
               cmd.Parameters.Add(new OracleParameter("i_sql", OracleDbType.Varchar2) { Direction = ParameterDirection.Input, Value = sql });
               cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output });

               try
               {
                   // execute command and get reader for ref cursor
                   OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                   // read first record; this is where the ref cursor SQL gets evaluated
                   Console.WriteLine("Reading first record...");
                   if (reader.Read(3000, cmd.Cancel)) { }

                   // read remaining records
                   Console.WriteLine("Reading records 2 to N...");
                   while (reader.Read(3000, cmd.Cancel)) { }
               }
               catch (TimeoutException ex)
               {
                   Console.WriteLine("Exception: {0}", ex.Message);
               }

               Console.WriteLine("Press any key to continue...");
               Console.Read();
           }
       }
   }
}

這是輸出的一個例子。

Executing Query...
Reading first record...
Exception: The OracleDataReader.Read operation has timed-out.
Press any key to continue...

引用自：https://stackoverflow.com/questions/16614035

相關問答

Dot-Net

託管 ODP.NET 驅動程序未顯示在“數據源”對話框中

  • April 4, 2022
檢視問答
Dot-Net

對於 ODP.NET，有沒有辦法強制 OracleCommand.BindByName 預設為真？

  • August 26, 2019
檢視問答
Dot-Net

LINQ to SQL for Oracle.ODP

  • April 17, 2012
檢視問答
Dot-Net

OracleBulkCopy 與 SQL*Loader 性能對比

  • February 17, 2012
檢視問答
Dot-Net

從 SQL Server 獲取數據到 SqlDataReader 的工作原理是什麼？

  • May 17, 2011
檢視問答
Dot-Net

.NET 應用程序中的 Oracle 查詢很慢（或失敗），但 SQL Developer 中的查詢速度很快

  • October 29, 2010
檢視問答