Como evitar HARD PARSE al momento de que nuestro sistema ejecuta sentencias SQL que no usan Variables Bind?
Recordemos que al existir gran cantidad HARD PARSES, el tiempo de respuesta y rendimiento de la base de datos se degrada, ademas del llenado del area SQL LIBRARY del SGA. Lo ideal es que no existan demasiados Hard Parse si no Soft Parse.
En mi nota anterior se explicó la forma de optimizar el rendimiento y tiempo de respuesta al momento de ejecutar una sentencia SQL usando variables bind en la programación, pero en ocasiones no es fácil aplicarlo ya que los DBA o Lideres de progrmadores no desarrollamos toda la aplicación o no realizamos un control de calidad para ver que se cumplan estos estandares. Otro motivo es que así compramos el sistema y no lo desarrollamos!
Para esto debemos modificar la programación. Una tarea muy dura, no?
Pues la respuesta a este problema es cambiar el parámetro de base de datos CURSOR_SHARING.
Oracle tiene el parámetro de base de datos CURSOR_SHARING que por defecto esta configurado con EXACT.
Y esto en que me ayuda? El CURSOR_SHARING admite 3 valores: EXACT, SIMILAR y FORCE
EXACT Valida que la sentencia SQL a ejecutarse sea exactamente igual a la que existe en la librería SQL para que no se realice un HARD PARSE. Ejemplo
Las siguietes dos sentencia hacen consulta sobre el mismo objeto, son casi iguales pero el valor de filtro para el campo cédula son diferentes por lo que hace que las dos sentencias sean diferentes y cada una se ejecute por un HARD PARSE
--Sentencia en la librería SQL
select * from persona where cedula='0123456789';
--Sentencia a ejecutar
select * from persona where cedula='0000000000';
SIMILAR
Valida que la sentencia SQL sean similares a nivel de select, from y where pero los valores constantes son reemplazados con variables, es decir la tarea que no realizamos anivel de programación.
Con el parámetro en SIMILAR estas dos sentencias son iguales y no se realizará HARD PARSE al momento de ejecutarse.
--Sentencia en la librería SQL
select * from persona where cedula='0123456789'; => select * from persona where cedula="VAR_1";
--Sentencia a ejecutar
select * from persona where cedula='0000000000'; => select * from persona where cedula="VAR_1";
FORCE
Es parecido al parámetro SIMILAR si no que la diferencia es que SIMILAR no registrará en la librería SQL la sentencia evaluada evitando el llenado de la misma.
Si no quedó claro, imaginense que ejecutamos 10000 consultas de personas por el número de de cédula y estos número de cédula son diferentes y no usan variable bind. en CUSRSOR_SHARING igual SIMILAR solo se registraría una vez la sentencia en la librería SQL la sentencia. En FORCE se registrarían
10000 veces la sentecia en la librería SQL.
Veamos el ejemplo
Consultas sin variables bind con parámetro CURSOR_SHARING=EXACT
--Vemos que este configurado el parámetro como EXACT
show parameter cursor_sharing;
cursor_sharing string EXACT
-- Liberamos la memoria shared pool
Alter System Flush Shared_Pool;
System Switch Log Altered.
Elapsed: 00:00:00:01
--Escript que realizará las consultas sin variables BIND
Declare
C_Cedula Varchar2(20);
N_Can Number;
C_Sql Varchar2(200);
Begin
For N_Id In 1..10000 Loop
C_Cedula:=N_Id;
C_Sql:='SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION='''||N_Id||'''';
Execute Immediate C_Sql Into N_Can;
End Loop;
End;
Elapsed: 00:00:04:68
-- Revisamos la cantidad de sentencias almacenadas en la librería SQL
Select Count(*) Cantidad From V$Sql
Where Sql_Text Like 'SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION=%';
Cantidad
----------
10000
Elapsed: 00:00:00:15
Ahora analicemos como esta la memoria
Gráfica de uso de la memoria SHARED POOL
Vemos como se vio afectada el tamaño disponible del Share Pool y el tamaño de la Sql Area aumentó por las 10,000 consultas SQL almacenadas en sentencias dentro de la Sql Library, esto representa 10,000 Hard Parse y 0 Soft Parse (Esto es malo para el Oracle Server).. El tiempo de ejecución fue de 4.68 segundos
Consultas sin variables bind con parámetro CURSOR_SHARING=SIMILAR
--Cambiamos el parámetro de base de datos CURSOR_SHARING=SIMILAR
alter system set cursor_sharing = SIMILAR scope=BOTH;
connect vendara/vendara
--Vemos que este configurado el parámetro como SIMILAR
show parameter cursor_sharing;
cursor_sharing string SIMILAR
-- Liberamos la memoria del la shared pool
Alter System Flush Shared_Pool;
System switch log altered.
set timing on;
--Escript que realizará las consultas sin variables BIND
Declare
C_Cedula Varchar2(20);
N_Can Number;
C_Sql Varchar2(200);
Begin
For N_Id In 1..10000 Loop
C_Cedula:=N_Id;
C_Sql:='SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION='''||N_Id||'''';
Execute Immediate C_Sql Into N_Can;
End Loop;
End;
/
Elapsed: 00:00:01:93
-- Revisamos la cantidad de sentencias almacenadas en la librería SQL
Select Count(*) Cantidad From V$Sql
Where Sql_Text Like 'SELECT COUNT(*) FROM PERSONAS WHERE IDENTIFICACION=%';
CANTIDAD
----------
1
Elapsed: 00:00:00:01
Ahora analicemos como esta la memoria
Gráfica de uso de la memoria SHARED POOL
Vemos que ahora el tamaño disponible de la Shared Pool no se disminuyó tanto y de igual forma el Sql Area no aumento demasiado ya que solo se guardó una sentencia en la Sql Area aunque se hayan ejecutado 10,000 consultas con diferente identificación, lo que representa 1 Hard Parse y 9,999 Soft Parse (Esto es bueno para el Oracle Server). El tiempo de ejecución disminuyó a 1.93 segundos.
Conclusiones
Vemos que los mejores valores en resultados son con el parámetro de base de datos CURSOR_SHARING en SIMILAR para obtener mejores tiempos de respuesta, menos uso en MB de las Sql Library y dejando mas espacio disponible para la Shared Pool y reduciendo los Hard Parse.
Recomendaciones
Es cierto que este parámetro mejor los tiempo de respuesta, pero a que costo? el costo es que el motor de base de datos tiene mas trabajo para procesar cada sentencia.
Solo recomedaría cambiar el parámetro de forma temporal hasta que se logre modificar los programas para que usen variables bind, o en casos extremos, pero de ahi no lo recomedaría.
Este parámetro CURSOR_SHARING=SIMILAR lo aplique en mi base de datos Oracle RAC de dos nodos montados en servidores WS2008 R2, 2 procesadores con 4 nucleos de 2.93 Ghz de y con 24GB de memoria Ram, pero la medicina fue peor que la enfermedad se presentaba error :
ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], [].
Para este error en el metalink de Oracle recomendaba cambiar el parametro CURSOR_SHARING a EXACT.
Este error sólo se me presentó en Oracle RAC pero no en Oracle Single Instance, lo que me hace pensar que el parámetro CURSOR_SHARING=SIMILAR no funca para RAC.
