Oracle. DataAccess 10.2 problems? Oracle cannot be indexed

The server is Oracle
9i, nhib.pdf uses nhib.pdf. Driver. OracleDataClientDriver (Oracle. DataAccess. dll, and ODTwithODAC1020221 for installation. Because the official deployment is intended to use Oracle
10 Gb, so the client NHibernate uses this driver, and also to avoid problems when System. Data. OracleClient. dll is operating Clob/Nclob), many strange problems are found one after another. It is not clear that this driver and Oracle
The compatibility between 9i is still caused by other reasons, but it is basically solved.

1. Today we have solved two performance problems because of date fields.

1.1
The first query is to perform a single table query on a more than 0.6 million table. An index is created for a date field, which is used for the query. The query is slow at the beginning. It is estimated that the SQL statements generated by nhib.pdf are not indexed (the SQL statements in the log are analyzed, and indexes are used in the query plan ). The solution is to create a custom date type by using nhibloud, Oracle Clob/NClob cannot be inserted, garbled, and set OracleDbType of the corresponding parameter in IDbCommand to OracleDbType. date, and then set the Date field to this type in nhib.pdf. In this way, the index can be used for queries.

1.2 is basically similar to the first question.
It is also a query involving Association of multiple tables, with a table with more than 4 million data. It is also estimated that the index of a date field is inefficient because it does not take effect (similarly, the SQL statement is captured and analyzed using the query plan, and the index is used ), however, the method in 1.1 does not work. The only difference is that 1.1 uses ICriteria, and this query uses IQuery.
The solution is to change the SQL statement to B. CreateDate> to_date (: startDate,
'Yyyy-MM-DD '), and then use the IQuery. SetString () method instead of IQuery. SetDateTime (), So OK.

The first problem is easy to understand. If the parameter type is not set correctly, indexes cannot be used, but this method cannot be used on the second similar problem.

2. There are many problems when using Oracle. DataAccess 10.2 to call the Function/Procedure of Oracle 9i.
2.1 normally, the call should look like this:
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 deletedrows = cmd. ExecuteNonQuery ();

Con. Close ();
But on Oracle 9i, the parameter type declared by the function or stored procedure is Number (which is something of the old system and cannot be changed), while ODAC
10.2 OracleDbType does not have the Number type. If you set the parameter to OracleDbType. Double, OracleDbType. Decimal, and other types, an error is returned and cannot be converted to a numeric type.
This method can be called successfully only when it is changed to the following method:
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 deletedrows = 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 If Function/Procedure has an out-type parameter of string type such as Varchar2, you must specify the Size attribute of the parameter; otherwise, a buffer overflow error is reported.

3. In a few cases, the client has deleted a piece of data, but the SQL query database does not find this piece of data, but nhib.pdf can still get this object.
Make sure that no secondary cache is enabled for NHibernate. Make sure that NHibernate has submitted this SQL statement to the server and tracked the NHibernate. The SQL statement generated when the DbCommand query is executed is correct and the parameter settings are correct, the records in the logs are correct, but this data is indeed returned after nhib.pdf is executed. The database server is monitored to check that the server has not executed this SQL statement at all. So identify the problem in ODAC
10.2 above the driver. But what's more strange is that the client (Web
Server, which is configured with Oracle Network Service). The problem persists. The database Server restarts the machine and the problem persists. You can also find the data. This was no longer the case, and we could not find it.

2008.01.06
The two problems above are that Oracle does not have a correct index. It is really important to set the parameter information accurately at the program level.
In addition, during the optimization process, we constantly find that Oracle cannot find the index. In many queries, we have added the specified index, or even the hints of join type. I forgot another important aspect: the test database often imports a large amount of test data and requires analysis and maintenance of Oracle indexes!