Bind Variables

Mucho se ha dicho sobre los bind variables y los problemas que pueden causar en una aplicación.

Hay muchas entradas en asktom que ejemplifican perfectamente las ventajas sobre el uso de bind variables.

Me he llegado a encontrar con muchas aplicaciones que fueron desarrolladas para SQL server y que los proveedores simplemente cambian el código de la aplicación sin tener en cuenta las consideraciones mínimas sobre las mejores prácticas de programación en Oracle.

Oracle tiene un mecanismo para convertir los valores literales en variables bind. La forma de hacerlo es alterando el sistema o sesión para poner el parámetro "CURSOR_SHARING" en similar o en force. Antes de intentar este cambio, consulten bien la documentación de oracle para saber las implicaciones que esto pueda tener.


La forma en que funciona es la siguiente...

SQL> SELECT /*+PRUEBA 1*/ 1234 from dual;

     1234
----------
     1234

SQL> SELECT /*+PRUEBA 1*/ 4321 from dual;

     4321
----------
     4321

SQL> select sql_text,executions
 2  from v$sql
 3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Ahora ponemos el cursor_sharing en similar...

SQL> alter session set cursor_sharing=similar;

Sesión modificada.

SQL> SELECT /*+PRUEBA 2*/ 1234 from dual;

     1234
----------
     1234

SQL> SELECT /*+PRUEBA 2*/ 4321 from dual;

     4321
----------
     4321

SQL> select sql_text,executions
 2  from v$sql
 3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 2*/ :"SYS_B_0" from dual                   2
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Como se puede ver, funciona de maravilla, no?. La realidad es que no siempre sucede esto...

SQL> DECLARE numero NUMBER;
 2  BEGIN
 3    SELECT
 4     /*+PRUEBA 3*/ 1234
 5    INTO numero
 6    FROM dual;
 7  END;
 8  /

Procedimiento PL/SQL terminado correctamente.

SQL> select sql_text,executions
 2  from v$sql
 3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 2*/ :"SYS_B_0" from dual                   2
SELECT /*+PRUEBA 3*/ 1234 FROM DUAL                         1
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Como se puede observar, a pesar de que se tiene cursor_sharing en similar o incluso force, el código no cambia de literal a bind variable.

Para esto existe una solución...

SQL> create or replace procedure usa_bind(num number)
 2  is
 3  numero number;
 4  BEGIN
 5    SELECT
 6     /*+PRUEBA 4*/ num
 7    INTO numero
 8    FROM dual;
 9  END;
10  /

Procedimiento creado.

SQL> begin
 2  usa_bind(4444);
 3  end;
 4  /

Procedimiento PL/SQL terminado correctamente.

SQL> select sql_text,executions
 2  from v$sql
 3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 2*/ :"SYS_B_0" from dual                   2
SELECT /*+PRUEBA 3*/ 1234 FROM DUAL                         1
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 4*/ :B1 FROM DUAL                          1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Como se puede observar, la entrada como "PRUEBA 4" ya está usando un bind variable a través de un procedimiento. Pero esto nos genera un nuevo problema...

SQL> begin
 2  usa_bind(1234);
 3  end;
 4  /

Procedimiento PL/SQL terminado correctamente.

SQL>
SQL> exec usa_bind(3214);

Procedimiento PL/SQL terminado correctamente.

SQL> select sql_text,executions
 2  from v$sql
 3  where upper(sql_text) like '%USA_BIND%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
begin usa_bind(4444); end;                                  1
BEGIN usa_bind(3214); END;                                  1
begin usa_bind(1234); end;                                  1

Así que esto nos pone en el mismo lugar en el que empezamos. Aparentemente esta es una funcionalidad de oracle y no un bug. Todo está documentado en la nota de Metalink 285447.1. Donde nos indica que el código que está entre un begin y un end, no será modificado de literal a bind.

Pero existe aún una solución a este problema.

SQL> call usa_bind(4444);

Llamada terminada.

SQL> call usa_bind(1234);

Llamada terminada.

SQL> call usa_bind(1111);

Llamada terminada.

SQL> call usa_bind(2222);

Llamada terminada.

SQL> select sql_text,executions
 2  from v$sql
 3  where upper(sql_text) like '%CALL USA_BIND%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
call usa_bind(:"SYS_B_0")                                   4

De esta forma estaremos evitando un hard parse a la hora de mandar llamar nuestras ejecuciónes. Desgraciadamente, esto sugiere un cambio en la programación y por lo mismo muchas veces imposible si el código no está disponible.