martes, 10 de julio de 2007

Hard Parse, Soft Parse & “Softer” Soft Parse

Procesamiento de una consulta:

1) Validación Sintáctica
2) Validación Semántica
3) Optimización
4) Generación del QEP (Query Execution Plan)
5) Ejecución del QEP (Query Execution Plan)

El punto 1 al 4 forma parte del Parseo de la consulta, mientras que el punto 5 es la propia ejecución.

Cuando ejecutamos una consulta, siempre se realizan, como mínimo, los pasos 1 y 2. Luego de ejecutarse estos pasos, Oracle transforma la consulta en un valor hash y la envía a la Shared Pool y en la Library Cache se busca si existe alguna consulta con el mismo valor hash (si alguna sesión ya la utilizó en algun momento). En caso de que exista, se compara el texto de la consulta con la que se encontró en la Library Cache para validar si son exactamente iguales (este paso adicional se realiza porque puede llegar a haber varias consultas con el mismo valor hash); en caso de que lo sean, se procede a ejecutar esa consulta. Esto es lo que llamamos un Soft Parse. Si la consulta no existe, Oracle realiza los pasos 3 y 4. Esto es conocido como un Hard Parse. El Hard Parse es muy costoso para Oracle Server ya que implica realizar varios latches (loqueos) en la SGA y consume mucha CPU.
Como bien sabemos, cada consulta que ejecutamos implica la utilización de un cursor (un cursor es un espacio de memoria destinado a la ejecución de nuestra consulta). Lo ideal, es que nuestra aplicación abra los cursores que vaya a utilizar, ejecute las sentencias x veces y luego los cierre. Muchas aplicaciones como Forms no suelen ejecutar los cursores de esta forma, lo que implica que no podamos reutilizar los cursores y siempre tengamos que abrir nuevamente los que ya ejecutamos.
Para reducir éste problema, podemos utilizar el parámetro de inicialización SESSION_CACHED_CURSORS que nos va a permitir realizar un "Softer" Soft Parse. Si setemos el parámetro en 100, Oracle mantendrá 100 cursores abiertos para que los podamos reutilizar y evitarnos tener que abrirlos cada vez. Este espacio de memoria destinado al manejo de cursores, se mantiene con una lista LRU.
Oracle recomiendo que el parámetro se setee en una primera instancia en 50 e ir monitoreandolo para verificar si conviene incrementar su valor. Este parámetro debe setearse considerando el valor de OPEN_CURSORS.

select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from ( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' ) ;

CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
----------------- ----------- -----------
59.11% 39.49% 1.39%


select
'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
( select
max(s.value) used
from
sys.v_$statname n,
sys.v_$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
sys.v_$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from
( select
max(sum(s.value)) used
from
sys.v_$statname n,
sys.v_$sesstat s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
sys.v_$parameter
where
name = 'open_cursors'
) ;

PARAMETER VALUE USAGE
---------------------- --------------- -----
session_cached_cursors 100 100%
open_cursors 300 57%


Si el valor del SESSION_CACHED_CURSORS se encuentra en el 100%, deberíamos incrementar el valor del parámetro con normalidad.

10 comentarios:

Germán dijo...

Muy útil, muchísimas gracias. Pero tengo una inquietud que no he podido resolver...al decir que se comprueba si las consultas ejecutadas son iguales a las q se van a ejecutar, Oracle discrimina basado en si esta escrito en mayusculas o minusculas???

Es decir, para Oracle es igual "select * from tabla" que "Select * from TABLA"?? o son consideradas consultas distintas???

Gracias por su respuesta.

Leonardo Horikian dijo...

Hola Germán,

Cuando Oracle realiza un Hard Parse de la consulta, convierte esa consulta (caracter por caracter) a su respectivo valor ASCII. Luego, convierte mediante una función interna, todos esos valores a un único valor hasheado. Con ese único valor (Ej: 213846743), se fija en la Library Cache si el valor hash ya existe o no. Si ya existe, utiliza el plan de ejecución que ya tiene almacenado en memoria, sino tiene que realizar un Hard Parse de la consulta (para obtener el plan de ejecución) ya que no existe en memoria.

Si tenemos una consulta del tipo "select * from tabla" y otra del tipo "Select * from TABLA", al convertirlas a su respectivo valor hash vamos a obtener valores distintos, por lo cual, las consultas NO son iguales.
Para que sean iguales, tienen que ser exactamente iguales (a menos que se modifique el parámetro cursor_sharing).

Voy a publicar próximamente un post para aclarar éste tema.

Saludos

Germán dijo...

Hola Leonardo. En alguna oportunidad un DBA que nos dictó una capacitación nos dijo eso, pero nunca nos explicó el por qué...y pues la verdad no soy de las personas que admiten algo que no es argumentado. Entendí perfectamente su explicación y pues le estoy muy agradecido. Bienvenido sea su nuevo post y pues creame que seré un usuario bastante frecuente de su blog, el cual ya se encuentra entre mis listas de frecuentes. Gracias y un abrazo desde Colombia.

Roque dijo...

una pregunta , porque los cursores cuando los abro de la forma

open cursor_xx for
select col from table

y lo meto dentro de un for , nunca me lo comparte , siempre en la vista v$sqlarea la columna parse_calls es igual a executions.

desde ya muchas gracias-

Leonardo Horikian dijo...

Hola Roque,

Supongo que la consulta que estás utilizando para abrir el cursor es una consulta dinámica, cierto?
En este caso, como estás utilizando cursores del tipo REF CURSOR, esta clase de cursores NO se comparten en PL/SQL! Por lo cual, cada vez que ejecutes la consulta, vas a estar parseandola nuevamente; y depende de cómo estés escribiendo la consulta dinámica, estarás realizando Soft Parses o Hard Parse.
Para evitar realizar Hard Parses en consultas dinámicas, lo ideal es utilizar Bind Variables; pero como PL/SQL no cachea los cursores del tipo REF CURSORS, a lo sumo, vamos a estar viendo que se producen Soft Parses cada vez que ejecutamos la consulta.

Te muestro un claro ejemplo...


CURSOR DINÁMICO (SIN Bind Variables):


SQL_11gR1> DECLARE
2 TYPE ref_cur IS REF CURSOR ;
3 l_cur ref_cur ;
4 BEGIN
5 FOR i IN 1 .. 10 LOOP
6 OPEN l_cur FOR 'SELECT /*NO_BIND*/ texto FROM test WHERE id = '|| i ;
7 CLOSE l_cur ;
8 END LOOP ;
9 END ;
10 /

PL/SQL procedure successfully completed.

SQL_11gR1> SELECT sql_text, parse_calls FROM v$sqlarea WHERE sql_text LIKE 'SELECT /*NO_BIND*/ texto FROM test WHERE id = %';

SQL_TEXT PARSE_CALLS
-------------------------------------------------- -----------
SELECT /*NO_BIND*/ texto FROM test WHERE id = 5 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 6 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 8 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 7 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 4 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 10 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 3 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 9 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 1 1
SELECT /*NO_BIND*/ texto FROM test WHERE id = 2 1

10 rows selected.

En este caso, hicimos 10 Hard Parses.


CURSOR DINÁMICO (CON Bind Variables):


SQL_11gR1> DECLARE
2 TYPE ref_cur IS REF CURSOR ;
3 l_cur ref_cur ;
4 BEGIN
5 FOR i IN 1 .. 10 LOOP
6 OPEN l_cur FOR 'SELECT /*BIND*/ texto FROM test WHERE id = :i' USING i ;
7 CLOSE l_cur ;
8 END LOOP ;
9 END ;
10 /

PL/SQL procedure successfully completed.

SQL_11gR1> SELECT sql_text, parse_calls FROM v$sqlarea WHERE sql_text LIKE 'SELECT /*BIND*/ texto FROM test WHERE id = %';

SQL_TEXT PARSE_CALLS
-------------------------------------------------- -----------
SELECT /*BIND*/ texto FROM test WHERE id = :i 10

En este caso, hicimos 10 Soft Parses.


CURSOR ESTÁTICO (CON Bind Variables):


SQL_11gR1> DECLARE
2 CURSOR l_cur (v_id NUMBER) IS
3 SELECT /*BIND_2*/ texto FROM test WHERE id = v_id ;
4 BEGIN
5 FOR i IN 1 .. 10 LOOP
6 OPEN l_cur (i);
7 CLOSE l_cur ;
8 END LOOP ;
9 END ;
10 /

PL/SQL procedure successfully completed.

SQL_11gR1> SELECT sql_text, parse_calls FROM v$sql WHERE sql_text LIKE 'SELECT /*BIND_2*/ texto FROM test WHERE id = %';

no rows selected


Saludos

Axel dijo...

Hola Leonardo

Consulta: Hay algún "hint" o "evento" para obligar al optimizador a hacer un "Hard Parse" siempre, sin alterar código y sin hacer uso de parámetros no documentados, ni alteración de configuración de estadísticas e histogramas?

Algo así como "_optim_peek_user_binds", pero de forma pública y soportada?

De paso este, es un excelente blog. Muchas gracias.

Nicolás dijo...

Leonardo, excelente tu blog! Estoy con un escenario en el que he determinado que el problema es el "parseo", es un ambiente en RAC 10.2.0.3. Al igual que las notas de Tom Kyte, tus conclusiones me confirman que estoy en el buen camino. Saludos, desde Córdoba, Argentina. Nicolás

Leonardo Horikian dijo...

Hola Axel,

Para qué necesitas obligar al optimizador a realizar siempre Hard Parses?
Jamás me vi en la necesidad de realizar esto. Todo lo contrario, lo que siempre se trata de evitar en ambientes OLTP es realizar Hard Parses constantemente ya que esto degrada en GRAN MEDIDA la performance de todo el ambiente.

Saludos

Aldo dijo...

Hola Leonardo,
Te comento que nuestro DBA siempre recomienda que las consultas sean parametrizadas, es decir los valores de los filtros siempre los coloquemos en variables. Como desarrollodor me resulta engorroso establecer los valores de los filtros del WHERE, cuyos valores conozco son fijos, como variables en la seccion DECLARE. Por favor confirmame si esta recomendación es correcta.

Me explico con codigo
Dados los siguientes segmentos de codigo:
CASO I
-----------------
DECLARE
CURSOR c_datos IS
select COL1 from TABLA
where COL1= 50;

variable INT;
BEGIN
FOR datos IN c_datos
LOOP
variable := datos.COL1;
END LOOP;
END;

CASO II
--------------------
DECLARE
valorCOL1 INT:=50
CURSOR c_datos IS
select COL1 from TABLA
where COL1= valorCOL1;

variable INT;
BEGIN
FOR datos IN c_datos
LOOP
variable := datos.COL1;
END LOOP;
END;


Suponiendo que estas se encuentran en procedimiento almacenado,la pregunta es si ORACLE determina un hard parse cada vez que se invoque la procedimiento del primer caso.
Gracias de antemano por tu respuesta.

Leonardo Horikian dijo...

Hola Aldo,

Para el caso I la base de datos realizará solamente un hard parse debido a que el valor harcodeado en el WHERE (en este caso 50) siempre es el mismo.

Para el caso II la base de datos realizará solamente un hard parse debido a que, como estas utilizando variables en el WHERE, estás utilizando bind variables.

RECOMENDACIÓN: Siempre utiliza variables en las consultas; por más que una determinada consulta se ejecute siempre con el mismo valor en el WHERE.

Saludos