I use an application, which is built with ODP.NET.
This application works with binding variables, but for some reason it uses new names of these variable in each sql call by adding a sequential number at the end of each binding variable:
For example:
select ename from emp where id=:id_001
select ename from emp where id=:id_002
select ename from emp where id=:id_003
This leads to hard parse time for each sql call.
I cannot change this application.
Is there a parameter in the database, which tell oracle to assume those sql with only other names of binding variables as equaland do not parse they every time?
Thank You very much!
Sorry, I don't think any such parameter exists.
The only thing I can think of is something SQL translation, see my answer in
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531536600346185281 for an example of that. But I'm *not* recommending that, because that would be an enormous amount of effort, and the translation could quite possibly be more expensive than the parsing anyway.
cursor_sharing doesn't assist here either
SQL> variable b1 number
SQL> variable b2 number
SQL> variable b3 number
SQL> variable b4 number
SQL> variable b5 number
SQL> variable b6 number
SQL>
SQL> exec :b1 := 10
PL/SQL procedure successfully completed.
SQL> exec :b2 := 10
PL/SQL procedure successfully completed.
SQL> exec :b3 := 10
PL/SQL procedure successfully completed.
SQL> exec :b4 := 10
PL/SQL procedure successfully completed.
SQL> exec :b5 := 10
PL/SQL procedure successfully completed.
SQL> exec :b6 := 10
PL/SQL procedure successfully completed.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing = exact;
Session altered.
SQL>
SQL> select ename, 'abc' x from scott.emp where empno = :b1;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b2;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b3;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b4;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b5;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b6;
no rows selected
SQL>
SQL> select sql_text from v$sql where sql_text like 'select%abc%' and sql_text not like '%sql_text%';
SQL_TEXT
----------------------------------------------------------------
select ename, 'abc' x from scott.emp where empno = :b1
select ename, 'abc' x from scott.emp where empno = :b3
select ename, 'abc' x from scott.emp where empno = :b6
select ename, 'abc' x from scott.emp where empno = :b4
select ename, 'abc' x from scott.emp where empno = :b2
select ename, 'abc' x from scott.emp where empno = :b5
6 rows selected.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing = force;
Session altered.
SQL>
SQL> select ename, 'abc' x from scott.emp where empno = :b1;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b2;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b3;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b4;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b5;
no rows selected
SQL> select ename, 'abc' x from scott.emp where empno = :b6;
no rows selected
SQL>
SQL> select sql_text from v$sql where sql_text like 'select%scott.emp%' and sql_text not like '%sql_text%';
SQL_TEXT
----------------------------------------------------------------
select ename, :"SYS_B_0" x from scott.emp where empno = :b4
select ename, :"SYS_B_0" x from scott.emp where empno = :b5
select ename, :"SYS_B_0" x from scott.emp where empno = :b2
select ename, :"SYS_B_0" x from scott.emp where empno = :b6
select ename, :"SYS_B_0" x from scott.emp where empno = :b3
select ename, :"SYS_B_0" x from scott.emp where empno = :b1
6 rows selected.
SQL>
SQL>
SQL>
SQL>