Richie

Sometimes at night when I look up at the stars, and see the whole sky just laid out there, don't you think I ain't remembering it all. I still got dreams like anybody else, and ever so often, I am thinking about how things might of been. And then, all of a sudden, I'm forty, fifty, sixty years old, you know?

Oracle.DataAccess 10.2的问题?Oracle找不准索引

服务器为Oracle 9i,NHibernate使用NHibernate.Driver.OracleDataClientDriver(Oracle.DataAccess.dll,使用ODTwithODAC1020221这个版本安装。因为正式部署打算用Oracle 10g,所以客户端NHibernate用了这个驱动,另外也是为了避免System.Data.OracleClient.dll操作Clob/Nclob时的问题),陆续发现不少奇怪的问题。不清楚是这个驱动跟Oracle 9i之间的兼容性造成还是其它原因,不过最终基本都解决了。

1. 今天解决了2个性能方面的问题,原因都是日期类型的字段造成。
1.1 第一个查询是对一个60多万的表执行单表查询,有一个日期字段建了索引,查询会使用这个日期字段。一开始查询很慢,估计是NHibernate生成的SQL语句没有使用到索引(把日志中的SQL语句拿出来分析,查询计划中显示是使用了索引的)。解决方法是按照使用NHibernate, Oracle Clob/NClob无法插入、乱码问题的方式创建一个用户自定义日期类型,将IDbCommand中对应参数的OracleDbType设置为OracleDbType.Date,然后在NHibernate中把日期字段配置为这个类型。这样查询就能使用到索引了。

1.2 跟第一个问题基本类似。
也是一个查询,涉及到多个表的关联,有个表数据400多万。同样估计是一个日期字段的索引没有发挥作用而导致的低效(同样,SQL语句抓出来用查询计划分析,显示索引是使用上了),但是按照1.1中的方法之后还是不起作用。唯一不同之处是1.1用的ICriteria,而这个查询用的是IQuery。
解决方法是把sql改成类似b.CreateDate>to_date(:startDate, 'YYYY-MM-DD')这种形式,然后使用IQuery.SetString()方法而不是IQuery.SetDateTime(),这样就OK了。

第一个问题还好理解,如果参数类型没有设置正确无法利用索引,比较正常,但这个方法无法运用在第二个类似的问题上就比较费解了。

2. 用Oracle.DataAccess 10.2这个版本调用Oracle 9i的Function/Procedure时问题比较多。
2.1 正常情况下调用应当是这个样子:
OracleConnection con = new OracleConnection("connection string");
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "function / procedure name"; cmd.Parameters.Add(new OracleParameter("param name", OracleDbType.Double).Value = 12.1);
int effectedRows = cmd.ExecuteNonQuery();
con.Close();
但在Oracle 9i上函数或存储过程声明的参数类型为Number(老系统的东西,不敢改动),而ODAC 10.2的OracleDbType是没有Number这个类型的,把参数设置成OracleDbType.Double、OracleDbType.Decimal等各种类型,均报错无法转换成数字类型。
只有改成下面这种方式才能成功调用:
OracleConnection con = new OracleConnection("connection string");
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"
Declare v_value Number;
    Begin v_value:=function or procedure name(:p1, :p2, :returnValue);
End;";
cmd.Parameters.Add(new OracleParameter("p1", OracleDbType.Double).Value = 12.1);
cmd.Parameters.Add(new OracleParameter("p2", OracleDbType.Varchar2).Value = "Test");
cmd.Parameters.Add(new OracleParameter("returnValue", OracleDbType.Int32, ParameterDirection.Output).Value = 0);
int effectedRows = cmd.ExecuteNonQuery();
con.Close();
if (cmd.Parameters[2].Value != null && cmd.Parameters[2].Value != DBNull.Value)
{
    int returnValue = Convert.ToInt32(cmd.Parameters[2].Value);
    /*....*/
}

2.2 如果Function/Procedure有out型的参数,参数类型为Varchar2等字符串型的,必须指定参数的Size属性,否则报缓冲区溢出错误。

3. 极个别的情况下,客户端已经把一条数据删除了,用SQL查询数据库确实查不到这条数据,但是NHibernate仍然能够获取到这个对象。
确认NHibernate没有开二级缓存;确认NHibernate有向服务器提交这个SQL语句,跟踪到NHibernate,在执行DbCommand查询时生成的SQL语句正确,参数设置正确,日志中记录的这些也是正确的,但NHibernate执行完之后的确返回了这条数据;监控数据库服务器,确认服务器根本没有执行这个SQL。所以把问题确定在ODAC 10.2这个驱动上面。但更奇怪的是客户端(Web Server,上面配置了Oracle网络服务)重起机器,问题还在;数据库服务器重起机器,问题还在,还能查出那条数据。后来再没有发生这种情况,也无从查起。

2008.01.06
上面两个问题都是Oracle没有找准索引,从程序层面将参数信息设置准确确实比较重要。
另外优化过程中不断发现Oracle找不准索引的情况,在不少查询中添加了指定索引,甚至是JOIN TYPE的hints,倒忘了另外一个重要方面:测试库经常大量的导入测试数据,需要分析维护Oracle索引!

posted on 2007-11-29 22:23  riccc  阅读(2028)  评论(3编辑  收藏  举报

导航