OracleDataReader.Read()耗时过高问题的解决办法
一、背景
C#连接Oracle数据库，使用的是以下的方式。项目运行了一段时间，突然就出现下面的while循环需要消耗大概100秒左右的时间，但是SQL在Oracle中执行只需要1秒以内。
OracleConnection conn = new OracleConnection(connectString); OracleCommand comm = new OracleCommand(sb.ToString(), conn);
OracleDataReader dr = comm1.ExecuteReader(); var j = 0; while (dr.Read()) { LocationResult LocationResult = new LocationResult(); //LocationResult.uid = dr["LOCATION_RESULT_UID"].ToString(); Location location = Location.LoadFromLocationID(dr["LOCATION_ID"].ToString()); LocationResult.uniquepartId = dr["UNIQUEPART_ID"].ToString(); LocationResult.lineNo = location.LineNo; LocationResult.location = location.LineNo + "." + location.StatNo + "." + location.StatIdx + "." + location.FuNo + "." + location.WorkPos + "." + location.ToolPos; LocationResult.locationId = location.LocationID; LocationResult.typeNo = dr["TYPE_NUMBER"].ToString(); LocationResult.typeVar = dr["TYPE_VERSION"].ToString(); LocationResult.processNo = dr["PROCESS_NUMBER"].ToString(); LocationResult.resultState = dr["RESULT_STATE"].ToString(); LocationResult.timeStamp = dr["RESULT_DATE"].ToString(); System.Diagnostics.Debug.Write(" -----" + (j++) + "\n"); if (j == 24)//调试使用 { // break; } if ("51".Equals(location.LineNo.Substring(0, 2)) && !"110".Equals(location.StatNo)) { } else { GetParameters(LocationResult); } info.locationResults.Add(LocationResult); }
二、解决方案（本解决方案是初步解决方案，最终解决方案请看最后）
增加一个变量j，每次循环的时候自增1，然后再循环的时候打印出来。
观察输出发现，每次都是打印到24的时候会卡个100秒，然后突然打印25-100以后的数值。
然后调试的时候，发现是在while(dr.Read()) 第25次的时候会消耗大量的时间。由于无法进入dr.Read()观察到底是哪里在消耗时间，所以无法继续调试。东搞西搞，一直百度，发现没有人遇到类似的问题。搞了两天左右都搞不定，用户都嗷嗷叫了，使用系统的时候，每次查询都需要等1-2分钟，体验非常糟糕。
经过各种观察，变换参数，变换查询条件，都是第25次的时候卡住。咨询了现在专门做C#的大学同学，他认为是硬件问题，不像是软件问题。所以我给服务器加了2G的内存，发现问题仍然存在。
最后在昨天晚上想起来，自己刚刚做过的分页。因为查询的数据不是很多，最多200条以内，所以索性就在数据库分好页，每次只查询20条，不达到25条这个bug。代码完成后，查询简直飞快，感动得我和用户都哭了。。。
下面上代码：
OracleConnection conn1 = new OracleConnection(connectString);
try
{
string count = "";
conn1.Open();
string countsql = "SELECT COUNT(1) TOTAL FROM (" + sb.ToString() + ")A";
OracleCommand comm2 = new OracleCommand(countsql, conn1);
OracleDataReader dr2 = comm2.ExecuteReader();//直接查询 dr.Read()到25次的时候会消耗100秒左右的时间，所以此处分页处理,每次只向数据库查询20记录,跳过25这个bug
if (dr2.Read())
{
count = dr2["TOTAL"].ToString();
}
var countInt = Convert.ToInt32(count);
var page = 20;
for(int i = 0; i < countInt / page + 1; i++)
{
OracleCommand comm1 = new OracleCommand("SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ("+sb.ToString()+ ")TMP_PAGE WHERE ROWNUM <= "+((i+1)* page) +") WHERE ROW_ID >"+(i* page), conn1);
OracleDataReader dr = comm1.ExecuteReader();
//System.Diagnostics.Debug.Write("SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM (" + sb.ToString() + ")TMP_PAGE WHERE ROWNUM <= " + ((i + 1) * page) + ") WHERE ROW_ID >" + (i * page));
var j = 0;
while (dr.Read())
{
LocationResult LocationResult = new LocationResult();
//LocationResult.uid = dr["LOCATION_RESULT_UID"].ToString();
Location location = Location.LoadFromLocationID(dr["LOCATION_ID"].ToString());
LocationResult.uniquepartId = dr["UNIQUEPART_ID"].ToString();
LocationResult.lineNo = location.LineNo;
LocationResult.location = location.LineNo + "." + location.StatNo + "." + location.StatIdx + "." + location.FuNo + "." + location.WorkPos + "." + location.ToolPos;
LocationResult.locationId = location.LocationID;
LocationResult.typeNo = dr["TYPE_NUMBER"].ToString();
LocationResult.typeVar = dr["TYPE_VERSION"].ToString();
LocationResult.processNo = dr["PROCESS_NUMBER"].ToString();
LocationResult.resultState = dr["RESULT_STATE"].ToString();
LocationResult.timeStamp = dr["RESULT_DATE"].ToString();
//System.Diagnostics.Debug.Write(" -----" + (j++) + "\n");
if (j == 24)//调试使用
{
// break;
}
if ("51".Equals(location.LineNo.Substring(0, 2)) && !"110".Equals(location.StatNo)
else
GetParameters(LocationResult);
info.locationResults.Add(LocationResult);
}
}
}
catch (Exception error)
{
Console.Write(error);
conn1.Close();
}
finally
{
conn1.Close();
}
三、总结
1、使用如下代码查出总数据量，sb.ToString()里面是原来的查询sql，拼接而成的比较长，这里就不贴了。
string countsql = "SELECT COUNT(1) TOTAL FROM (" + sb.ToString() + ")A";
OracleCommand comm2 = new OracleCommand(countsql, conn1);
OracleDataReader dr2 = comm2.ExecuteReader();//直接查询 dr.Read()到25次的时候会消耗100秒左右的时间，所以此处分页处理,每次只向数据库查询20记录,跳过25这个bug
if (dr2.Read())
{
count = dr2["TOTAL"].ToString();
}
var countInt = Convert.ToInt32(count);
2、使用下面的方式进行分页查询，每次只查20条数据，并用dr.Read()读取出来。成功避免了第25次循环消耗时间过长的问题。
var page = 20;
for(int i = 0; i < countInt / page + 1; i++)
{
OracleCommand comm1 = new OracleCommand("SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ("+sb.ToString()+ ")TMP_PAGE WHERE ROWNUM <= "+((i+1)* page) +") WHERE ROW_ID >"+(i* page), conn1);
OracleDataReader dr = comm1.ExecuteReader();
//System.Diagnostics.Debug.Write("SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM (" + sb.ToString() + ")TMP_PAGE WHERE ROWNUM <= " + ((i + 1) * page) + ") WHERE ROW_ID >" + (i * page));
var j = 0;
while (dr.Read())
{
//TODO
info.locationResults.Add(LocationResult);//每次查询到的东西添加到List里面
}
}
3、本次问题其实并没有根本解决，只是通过取巧的方式暂时避免了问题。假如这个问题是每次查询第一个结果就会消耗大量的时间，那么这个解决方案就失效了。最终的解决方案应该是，阅读OracleDataReader.Read()的源代码，找出根本问题，并重写OracleDataReader.Read()，从而根本解决问题。后面再看看吧。
---------------------------------------------------------------------------------------------------------------------------
2020年10月29日更新
程序运行了两天，问题又出现了。仔细阅读了微软官方的文档，发现之前离职的同事写的代码有问题，没有写下面标注的部分。将项目中相关的OracleDataReader 代码使用完成后都Close()掉，去除本篇文章前面的分页，问题消失。再观察观察。
---------------------------------------------------------------------------------------------------------------------------
2020年10月30日更新
问题又出现了，我日。都不知道是什么原因。今天再找找原因，实在不行的话我就用java写个接口，通过java来查了.
OracleDataReader 一直出问题，我今天换成了DataTable的形式承载数据，具体实现如下，需要观察几天看看。
DataTable dt = new DataTable();
int all = new OracleDataAdapter(sb.ToString(), conn).Fill(dt);
foreach (DataRow row in dt.Rows)
{
//TODO
}
---------------------------------------------------------------------------------------------------------------------------
2020年11月9日更新
问题依旧。今天试了下查询的SQL 使用Oracle的强制索引。更新程序之后发现确实变快了一点。
猜测原因是，自己在PLSQL中，或者使用java接口查询数据库的时候，可能凑巧所有的schema都走索引，所以就比较快，程序自己运行的时候，有些时候查询的50个schema中有几个是不走索引的，所以程序偶尔会出现卡很久，基本上要卡60s左右，但是使用强制索引之后，就统一都走索引了。只能说再观察观察吧。下图是强制索引之后，Explain Plan显示的过程。
本文链接：https://www.cnblogs.com/CryOnMyShoulder/p/13889350.html
------------------ 微信支付扫码赞赏我--------------
本文来自博客园，作者：迷神图卷，转载请注明原文链接：https://www.cnblogs.com/CryOnMyShoulder/p/13889350.html
