I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle driver, ODP.NET can't be used in the near future), and NHibernate runs on top of ADO.NET.
This driver creates parameterized SQL with the size of (n)varchar parameters set to the actual string size, which, of course, varies:
select * from People where Name=:p0; :p0 = 'John' [Type: String (4)] select * from People where Name=:p0; :p0 = 'James' [Type: String (5)]
Doing this with SQL Server would create a huge number of query plans, one for each combination of string sizes (when multiple string parameters), which is of course highly inefficient. I always thought it's the same in Oracle, but now I've got doubt.
I applied a fix to the NHibernate driver, which would send always the max parameter size, e.g. if the above Name column was 32 chars wide, it would always send [Type: String (32)]. My code does not use dedicated Prepare statements, but sends parameterized SQL together with the values (similar to calling EXECUTE IMMEDIATE).
I then looked at the statement (actually an INSERT) in Oracle Enterprise Manager, and the old version did not appear in the duplicate queries list. The statement itself showed a parse for each call (total of more than 1000 after some testing), but few hard parses. Thus, I could see no difference in performance between the fixed and the variable varchar length. Does this mean the fix is futile, and different query plans for different parameter sizes occur only in SQL Server (and maybe other DBMS)? Does Oracle check only for SQL string equality, but not for equality in parameter size settings?
I also noticed that duplicate SQL was almost only found where parameter values were concatenated into the SQL string, instead of using bind parameters.
EDIT: cursor_sharing in Oracle is set to EXACT.