OracleDataReader.Read方法的超时时间

Question

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...

调用返回引用游标的存储过程就比较棘手了。考虑下面的测试过程（仅用于测试）：

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毫秒），并且没有抛出超时异常。我们看到的性能是因为ref cursor的SQL语句直到第一次调用OracleDataReader.Read方法才被执行。当第一次Read()调用读取refcursor中的第一条记录时，就会产生来自长时间运行查询的性能影响。

我所展示的行为意味着OracleCommand.CommandTimeout属性无法用于取消与ref cursor相关联的长时间运行查询。我不知道ODP.NET中是否有任何属性可用于在这种情况下限制ref cursor SQL的执行时间。有人对如何在一定时间后短路长时间运行的ref cursor SQL语句的执行有什么建议吗？

- dnickels

---

我假设你要么无法访问循环数据读取器的代码（即在业务类中），要么不想用性能相关信息污染它。 - b_levitt

我可以访问循环数据读取器的代码。在这种特定情况下，问题在于如果某个查询由于某种原因运行时间很长（即在几个大表上产生笛卡尔积的“坏”查询），我希望在N秒后中断查询。通过“短路”，我的意思是实际上取消数据库中的查询。OracleCommand.CommandTimeout属性似乎可以做到这一点，但在上面显示的执行返回ref cursor的proc的“命令”时，它不起作用。 - dnickels

Answer 1

这是我最终采用的解决方案。它只是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...

- dnickels

Answer 2

看起来你不是第一个提出这个问题的人： https://forums.oracle.com/forums/thread.jspa?threadID=2125208

在reader.Read()的循环中监控经过的时间并退出循环。这很简单，但当然只有在潜在的长时间调用Read完成后才能退出。

你最好的选择可能是在单独的线程上使用任务进行循环，监视它，然后在原始线程上调用cmd.Cancel：

[Test]
public void TimeBasicSql()
{
  using (OracleConnection con = new OracleConnection("User ID=id; Password=pass; Data Source=db;"))
  using (OracleCommand cmd = new OracleCommand())
  {
    con.Open();
    cmd.Connection = con;

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

    try
    {
      cmd.CommandTimeout = 1;
      String sql = "begin open :o_cur1 for select count(*) from all_objects, all_objects; end;";

      cmd.CommandText = sql;
      cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output });

      var task = System.Threading.Tasks.Task.Factory.StartNew(() =>
        {
          try
          {
            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);
          }

        });

      if (!task.Wait(cmd.CommandTimeout * 1000))
      {
        Console.WriteLine("Timeout exceeded.  Cancelling...");
        cmd.Cancel();
      }



    }
    catch (OracleException ex)
    {
      Console.WriteLine("Exception was thrown: {0}", ex);
    }

  }

值得注意的是，ORA-01013异常是在工作线程上抛出的，而不是在调用OracleCommand.Cancel的线程上抛出的。

- b_levitt

---

感谢您的反馈。我曾考虑过多线程的方法；这种方法将允许.NET端口被终止，但我认为查询将继续在Oracle RDBMS中执行。我希望能够取消查询执行。 - dnickels

可能会针对最后一个读取调用，但这与在Sql Developer中取消查询并等待取消完成几秒钟的情况不同吗？ - b_levitt

我需要澄清一下我上次评论中的几个问题。当我考虑使用多线程方法时，是使用Thread API而不是TPL。我认为终止执行查询的后台线程不会取消RDBMS中的查询。但是，在进行了一些快速而肮脏的测试之后，这似乎并不正确。RDBMS确实终止了会话，从而取消了查询。关于使用TPL任务和取消，问题在于我需要能够终止Read调用本身（而不是让它完成）。查询可能需要30分钟或更长时间。 - dnickels

你可能会笑 - 你的手动解决方案基本上是重现了OracleCommand.Cancel - 看起来可以正常工作。我的答案已经更新以反映这一点。 - b_levitt

抱歉回复晚了，但我的优先事项已经转移。请参见下面的答案，了解我最终使用的解决方案。它受到您发布的示例的启发，但使用扩展方法封装了任务逻辑，并将其作为OracleDataReader类的API的一部分。 - dnickels