利用 ODP.NET 优化数据访问性能

本教程描述如何利用 ODP.NET 优化 .NET 应用程序的性能。

大约 20 分钟

本教程包括下列主题:

将鼠标置于此图标上可以加载和查看本教程的所有屏幕截图。(警告:因为此操作会同时加载所有屏幕截图,所以网速较慢时,响应时间可能会比较长。)

注:此外,您还可以在下列步骤中将鼠标放在每个单独的图标上,从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。

Oracle 数据库有多种优化从客户端进行的查询执行和数据检索的方法。ODP.NET 可实现这些性能优化,使 .NET 开发人员能构建更有效的数据库程序。本教程描述了 ODP.NET 开发人员最常用的一些性能优化方法。

第一部分讨论语句缓存。有了语句缓存就不需要通过缓存首次执行语句时创建的服务器游标来重新分析每条已执行过的 SQL 或 PL/SQL 语句。后面再执行同一语句时可重复使用游标中已分析的信息,不必重新分析即可执行该语句,从而提高了性能。为了看到由语句缓存带来的性能提高,Oracle 建议只缓存那些被反复执行的语句。此外,SQL 或 PL/SQL 语句应使用参数,不应使用文字型数值。这样可充分利用语句缓存的优势,因为即使参数值在后面的执行中有所变更,也可以重复使用参数化语句中的分析信息。

第二部分显示如何使用 ODP.NET 中的 FetchSize 和 RowSize 属性控制每次数据库往返检索的数据量。进行多次往返来检索一组不同的数据效率很低。开发人员可利用这两个属性对从数据库服务器检索的数据量进行微调,从而减少数据库往返次数。

第三部分演示如何在 ODP.NET 和 Oracle 数据库之间传递 PL/SQL 关联数组。PL/SQL 关联数组允许 Oracle 传递大量同一类型的数据到 .NET 数组。这种方法提供了一种传递数据的灵活、简便的方式,它用单个参数将数据集中到一起并根据需要在两个层之间进行传递。

第四部分显示如何批处理 SQL、如何使用 Oracle REF 游标以及如何演示多活动结果集 (MARS)。批处理 SQL 可使开发人员在单次数据库往返中执行多个 SQL 语句。Oracle REF 游标是 Oracle 数据库特有的一种数据类型。它提供独特的结果集处理功能,允许灵活处理和调整数据。MARS 是 Oracle 一直支持的一个特性。它允许单个连接同时激活多个结果集。

注: 本演示中看到的性能提高不一定能反映在生产环境中看到的结果。有时,由于安装、硬件、数据库配置等差异,实际结果可能比演示结果更好或更差。例如,如果您使用位于同一机器上的客户端和服务器运行本演示,那么由于减少数据库往返次数而获得的性能提高将比在客户端和服务器位于两个不同机器上的生产环境下获得的性能提高更加明显。

开始本教程之前,您应该:

安装并创建 Oracle 数据库 11g 或 Oracle 数据库 10g 数据库服务器

安装 Visual Studio .NET 2003 或更高版本

从 Oracle 数据库客户端或 OTN 安装 ODP.NET

ODP.NET 最佳实践.zip 下载并解压缩到工作目录

当您需要反复执行同一 SQL 或 PL/SQL 语句时,使用语句缓存很有好处。ODP.NET 会缓存最近使用最多的语句。开发人员决定要缓存多少以及哪些最近使用的语句。缓存的语句保留了语句的分析树且可以对其进行快速查寻,因而提高了性能。要使用 ODP.NET 语句缓存,请执行以下步骤:

1.

打开 Visual Studio。选择 File > Open > Project/Solution。选择 ODP.NET 性能最佳实践解决方案并单击 Open

2.

选择 View > Solution Explorer

3.

双击 ODP.NET 最佳实践.cs 打开此代码,以供本教程使用。

4.

查看连接字符串,允许 ODP.NET 应用程序连接到 Oracle 的示例 HR 模式。您需要修改 conString 字符串变量的口令和数据源值。

注:如果您正在访问远程 Oracle 数据实例,则需要以 //<主机名>/<SID> 格式指定数据源。

向下滚动到代码的 Demo 1 部分。注意,代码文件带有一些注释,用于标记在后续步骤中需要输入代码的区域。因此,您要确保在相关注释下输入相关的代码。这样做是为了确保以正确顺序输入代码,同时避免文件中出现代码混淆。

5.

要查看语句缓存的工作方式,首先要在语句缓存大小设置为 0 的情况下执行一条语句 10000 次。

将语句缓存大小设置为 0。将下面的代码添加到 con1.ConnectionString 语句中,如以下屏幕截图所示:

"Statement Cache Size=0";

6.

在同一文件中继续向下,执行相同的代码,但保持语句缓存为开启状态。将语句缓存大小设置为 1,然后重新执行上面的语句 10000 次。向下滚动,将下面的代码添加到 con2.ConnectionString 语句中,如以下屏幕截图所示:

"Statement Cache Size=1";

应用程序计算语句在缓存启用状态下的性能提高百分比。

?

7.

检查完代码之后,可以执行应用程序以查看结果。选择 Build > Build Solution

输出窗口不应显示任何错误或警告信息。代码应编译成功。

8.

选择 Debug > Start Debugging 或按 F5 键。

将打开一个命令窗口。等待输出。不要关闭窗口。

9.

注意,您的结果可能与屏幕截图中的结果稍微有些出入。

如您所见,对反复执行的 SQL 或 PL/SQL 语句使用语句缓存后,性能得到显著的提高。要对所有 ODP.NET 应用程序启用语句缓存,也可以在 Windows 注册表中启用语句缓存。

不要按 Enter 键。要继续执行程序,在后面的主题中为程序添加代码。关闭命令窗口。

对每次数据库往返取回的数据量进行控制可以优化应用程序的性能。例如,当最终用户需要整行数据时,如果每次往返只检索半行数据,则效率很低。与进行两次往返相比,一次往返应该能获得最佳性能。ODP.NET 允许开发人员自动发现查询行大小并指定每次往返检索的行数。该特性为 .NET 程序员极大地简化了数据检索的优化过程。

在本主题中,您将运行一个查询,一次取回一行数据,然后再运行同一查询,一次取回 100 行数据,以此查看由于减少往返次数而带来的性能提高。执行以下步骤:

1.

首先在 OracleDataReader 上的 FetchSize 设置为 1 的情况下执行一个查询。

注:当 ODP.NET 执行 OracleCommand 时,它首先按行大小检索元数据并填写 RowSize 属性。之后,开发人员可以用行数乘 RowSize 设置每次往返从数据库检索的数据量。在本例中,每次往返仅检索一行。

向下滚动到代码的 Demo 2 部分。定位到 for 循环。输入以下代码:

reader.FetchSize = cmd.RowSize * 1;

2.

因为 RowSize 在运行时决定,所以查询或基础模式可以更改,但是每次往返将仍然检索相同的行数。您可以比较此步骤中 FetchSize 设置为 100 行与 FetchSize 设置为 1 行时的时间差异。

向下滚动到下一个 for 循环。输入以下代码:

reader.FetchSize = cmd.RowSize * 100;

3. 注:您可以在上一主题中运行的 Demo 1 部分中去掉 for 循环 的注释,以避免在已经执行过的演示上浪费时间。仅去掉循环注释不会影响剩余教程主题的结果,只会节省由于重复执行循环而浪费的时间。

如果不对 Demo 1 部分的 for 循环 进行注释,则在得到 Demo 1 部分的结果后,按 Enter 键以继续执行此部分的程序。

检查完代码之后,可以执行应用程序以查看结果。选择 Build > Build Solution

?

4.

选择 Debug > Start Debugging 或按 F5 键。

5.

注意,您的结果可能与屏幕截图中的结果稍微有些出入。

不要按 Enter 键。要继续执行程序,则在后面的主题中添加代码。关闭命令窗口。

返回主题列表

在本主题中,您将学习如何在 .NET 与 Oracle 之间传递数组参数。数组参数允许大量数据作为单个参数进行传递。本例使用一个存储过程来获取一个输入数组参数并返回一个输出数组参数。

1.

在 Solution Explorer 中,右键单击 array.sql 并选择 Open

2.

检查过程。注意,PL/SQL 程序包 MYPACK 使用 PL/SQL 存储过程 MYSP 创建。因为使用了 PL/SQL 关联数组,所以必须使用 PL/SQL。存储过程具有 PL/SQL 关联数组输入参数,此参数只是将其值作为输出 PL/SQL 关联数组传递回来。选择文件中的所有代码,按 ctrl+c 键将其复制到剪贴板。

3.

选择 View > Server Explorer

4.

您需要为 HR 用户创建一个数据连接。您可以在 Oracle Developer Tools for Visual Studio .NET 教程中查看具体方法。右键单击 HR.ORCL 连接,并选择 Query Window。在本例中,ORCL 是数据库别名,它可能与您系统上的数据库别名不同。

5.

按 [Ctrl]+V 键将代码粘贴到 Query Window 中。选择 Query Window 中的所有文本,然后单击 Execute

6.

存储过程成功执行后,切换回 ODP.NET 最佳实践.cs 代码,检查代码。

7.

向下滚动至 Demo 3 部分。输入以下代码:

cmd = new OracleCommand("MYPACK.MYSP", con);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2);
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

注:ODP.NET 设置对 MYPACK.MYSP 存储过程的调用,并绑定其数组参数。数组的元素包含数据类型 Varchar2 的值。为表明数组已被传入和传出存储过程,ODP.NET 将参数设置为 PL/SQL 关联数组(一种 Oracle 数据库集合数据类型)。

8.

现在,设置参数方向(输入或输出),然后指定每个参数的值。将 param2 设置为 NULL。

输入以下代码:

param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Output;
param1.Value = new string[3]{"Oracle", "Database", "Rules"};
param2.Value = null;

9.

然后指定数组中元素的最大数量(在本例中为 3)和 varchar2 的最大长度(在本例中为 20 个字符)。

输入以下代码:

param1.Size = 3;
param2.Size = 3;
param1.ArrayBindSize = new int[3]{20,20,20};
param2.ArrayBindSize = new int[3]{20,20,20};

要执行存储过程并输出结果,输入以下代码:

cmd.ExecuteNonQuery();

for(int i=0; i<3; i++)
{
Console.Write((param2.Value as OracleString[])[i]);
Console.WriteLine();
}

10. 注:您可以在前面主题中运行的 Demo 1 和 Demo 2 部分中去掉 for 循环 注释,以避免在已经执行过的演示上浪费时间。仅去掉循环注释不会影响剩余教程主题的结果,只会节省由于重复执行循环而浪费的时间。

如果不对 Demo 1 和 Demo 2 部分的 for 循环 进行注释,则在屏幕显示出前面演示的结果后,按两次 Enter 键以继续执行此部分的程序。

检查完代码之后,可以执行应用程序以查看结果。选择 Build > Build Solution

?

11.

选择 Debug > Start Debugging 或按 F5 键。

12.

检查结果。

不要按 Enter 键。要继续执行程序,则在后面的主题中为程序添加代码。关闭命令窗口。

返回主题列表

很多时候,您希望分批执行多个 SQL 语句,以减少数据库往返次数。在 ODP.NET 中,这可通过匿名 PL/SQL 实现。匿名 PL/SQL 可用于批处理任意次数的查询、更新、插入和删除操作。

在本主题中,您将使用匿名 PL/SQL 在一次数据库往返中执行三个查询。要检索结果,可使用 Oracle REF 游标显示如何从数据库灵活检索数据。借助 REF 游标,您可以选择只从结果集中检索您需要的数据。最后,您将看到:ODP.NET 可以为同一连接打开多个活动结果集。执行以下步骤:

1.

为了让 ODP.NET 批处理 SQL,使用匿名 PL/SQL。

注: 匿名 PL/SQL 就是一个以“BEGIN”开头,以“END;”结尾的字符串,中间是要在单次往返中执行的 SQL。

在本例中,您将批处理三个查询,返回三个结果集。参数被绑定,这样 ODP.NET 才能向客户端返回查询结果。Oracle 像执行任何其他文本命令一样执行匿名 PL/SQL。同样地,该命令被绑定为 CommandType.Text。

向下滚动到代码的 Demo 4 部分。输入以下代码:

string cmdtxt = "BEGIN " +
"OPEN :1 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 10; " +
"OPEN :2 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 20; " +
"OPEN :3 for select FIRST_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = 30; " +
"END;";
         

cmd = new OracleCommand(cmdtxt, con);
cmd.CommandType = CommandType.Text;

2.

然后将三个输出 REF 游标绑定到命令上。在本例中,使用不同的 REF 游标分别选择部门 10、20 和 30 的员工。

输入以下代码:

OracleParameter p1 = cmd.Parameters.Add("refcursor1", OracleDbType.RefCursor);
p1.Direction = ParameterDirection.Output;

OracleParameter p2 = cmd.Parameters.Add("refcursor2", OracleDbType.RefCursor);
p2.Direction = ParameterDirection.Output;

OracleParameter p3 = cmd.Parameters.Add("refcursor3", OracleDbType.RefCursor);
p3.Direction = ParameterDirection.Output;



3.

执行经过批处理的语句,然后从第二和第三个参数检索数据,无需从第一个参数取回结果。这是使用 REF 游标的一个优势,通过它,您可以只在客户端要求时检索数据。借助 REF 游标,您可以选择性地在结果集中选择数据并取回。借助 MARS,您可以观察到:两个 OracleDataReader 同时都在主动读取数据。

在相关的命令后面输入以下代码:

cmd.ExecuteNonQuery();

OracleDataReader dr1 = ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();
OracleDataReader dr2 = ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();

添加一个 while 循环一次检索两个 DataReader,以便测试 MARS 是否生效输入以下代码,以输出两个 REF 游标的结果:

while (dr1.Read() && dr2.Read())
{
Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " + 
           "Employee Dept:" + dr1.GetDecimal(1));
Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " + 
           "Employee Dept:" + dr2.GetDecimal(1)); 
Console.WriteLine();
}

4.

检查完代码之后,可以执行应用程序以查看结果。选择 Build > Build Solution

5.

选择 Debug > Start Debugging 或按 F5 键。

6.

检查结果。

返回主题列表

在本教程中,您学习了如何:

使用语句缓存
使用 FetchSize 和 RowSize
传递数组参数
使用批处理 SQL、REF 游标和多活动结果集 (MARS)

返回主题列表

将鼠标移到该图标上可以隐藏所有屏幕截图。

?