Oracle.DataAccess (ODP.NET) Array绑定最佳块大小

Question

是否有一种方法来计算使用数组绑定插入大量数据时的最佳块大小?

Answer 1

我是这样做的:当我的应用程序第一次运行命令时，我调用这个函数:

Public Function GetRowSize(ByVal cmd As OracleCommand) As Integer
  Dim dr As OracleDataReader
  dr = cmd.ExecuteReader()
  Return CInt(dr.GetType.GetField("m_rowSize", Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic).GetValue(dr))
End Function

我将结果存储在一个类变量中以供以后使用。它是这样执行的:

Dim cmd As OracleCommand
Dim da As OracleDataAdapter, dt As New DataTable
Dim expectedNumberOfRows As Integer
cmd = New OracleCommand("BEGIN :res := GetData(:expectedRows); END;"), server.con)
cmd.Parameters.Add("res", OracleDbType.RefCursor, ParameterDirection.ReturnValue)
cmd.Parameters.Add("expectedRows", OracleDbType.Int32, ParameterDirection.Output)
cmd.Parameters("expectedRows").DbType = DbType.Int32
If rowSize = 0 Then rowSizes = GetRowSize(cmd)
cmd.ExecuteNonQuery()
expectedNumberOfRows = CInt(cmd.Parameters("expectedRows").Value)
cmd.FetchSize = rowSize * expectedNumberOfRows
da = New OracleDataAdapter(cmd)
da.Fill(dt)

在我的PL/SQL中，我运行EXPLAIN PLAN以获得估计的行数，它看起来像这样:

FUNCTION GetData(estimatedRows OUT NUMBER) RETURN SYS_REFCURSOR IS
res SYS_REFCURSOR;
BEGIN
    OPEN res FOR SELECT * FROM MY_TABLE;
    DELETE FROM PLAN_TABLE;
    EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM MY_TABLE';
    SELECT CARDINALITY
    INTO estimatedRows
    FROM PLAN_TABLE
    WHERE ID = 0;
    RETURN res;
END;