SQL con literales en lugar de bind variables

Cuando la aplicación escribe los valores en el texto del SQL, cada valor diferente se convierte en una sentencia nueva para la base de datos, con hard parse y cursor propios. El informe junta esas sentencias en una forma, y el Diagnóstico señala las formas en las que su PARSE pesa.

Síntomas

En el Diagnóstico aparece el hallazgo Sentencia #n: n variantes del mismo SQL, solo con literales diferentes (LITERAIS_SEM_BIND), por ejemplo Sentencia #2: 4.312 variantes del mismo SQL, solo con literales diferentes. Cuando el recuento de variantes es estimado, el número viene después de alrededor de, como en Sentencia #2: alrededor de 4.312 variantes del mismo SQL, solo con literales diferentes (trace con muchas sentencias).

El texto explica que la aplicación escribe los valores en el SQL en lugar de usar bind variables y muestra el costo: cuántos PARSE hicieron las variantes, cuántos de ellos fueron hard parse, cuánto tiempo tardaron y qué parte del tiempo total es eso; después, cuántas llamadas hicieron las variantes en total y cuánto suman con el SQL recursivo. Termina con la corrección, usar bind variables en la aplicación, con CURSOR_SHARING=FORCE en la base de datos solo como paliativo. El enlace es Ver sentencia #n, que abre el detalle de la forma.

El porcentaje junto al título es el tiempo de esos PARSE sobre el tiempo total: es la parte que las bind variables ahorran, porque la ejecución sigue igual con bind. Por eso el hallazgo sigue el PARSE de las variantes, y no el tiempo total de la forma.

El hallazgo aparece para las formas con muchas variantes en las que el PARSE pesa, en el tiempo total o en los hard parse de la sesión, empezando por las que más gastaron en PARSE. La gravedad es baja y pasa a media cuando ese PARSE pesa más en la sesión. No aparece donde una bind variable no resuelve: sentencia que no acepta bind, como DDL y ALTER SESSION; el muestreo dinámico del propio Oracle, que copia los literales de la sentencia padre; y número que solo aparece en un comentario o en una sugerencia (hint). Cuando el PARSE de las variantes es casi todo liviano, el caso es de un cursor reabierto en cada uso: vea un PARSE en cada ejecución.

Causa

Con bind variable, el texto del SQL es uno solo, y el cursor sirve para todos los valores. Con el valor en el texto, cada valor genera un texto nuevo en la shared pool, un hard parse y un cursor que casi nunca se reutiliza. El costo aparece en CPU de parse, en contención en la shared pool y, muchas veces, en el hallazgo de muchos hard parses.

El informe reconoce esas sentencias por la forma: el mismo texto con :s en lugar de cada literal entre comillas y :n en lugar de cada número (textos compartibles). En las listas de sentencias, la forma es una sola fila, con la etiqueta N variantes y los totales sumados, y su detalle muestra las variantes que más gastaron. Estas tres sentencias, por ejemplo:

select * from exemplo where id = 101
select * from exemplo where id = 102
select * from exemplo where id = 103

son variantes de la forma:

select * from exemplo where id = :n

Solución

1. Abra Ver sentencia #n. En el detalle de la forma, la tabla Variantes que más gastaron trae el SQL ID, las llamadas, el tiempo y los literales de cada variante: con el texto de la forma y esos valores, encuentre en la aplicación el código que arma el SQL (variantes que más gastaron).
2. Cambie el valor en el texto por una bind variable. En JDBC, use PreparedStatement con ?:

   PreparedStatement ps = conn.prepareStatement("select * from exemplo where id = ?");
   ps.setLong(1, id);

   En PL/SQL, el SQL estático ya usa binds; en el SQL dinámico, pase el valor con using:

   -- valor en el texto: cada id se convierte en un SQL nuevo
   execute immediate 'select nome from exemplo where id = ' || v_id into v_nome;
   
   -- bind: el mismo SQL para todos los id
   execute immediate 'select nome from exemplo where id = :id' into v_nome using v_id;

3. Mientras la aplicación no cambia, CURSOR_SHARING=FORCE hace que la base de datos cambie los literales por binds. Es un paliativo y puede cambiar planes: pruébelo antes y prefiera activarlo solo en la sesión de la aplicación.

   alter session set cursor_sharing = force;

4. Un literal a propósito puede ser la elección correcta en una consulta poco frecuente sobre datos muy desiguales, en la que cada valor merece su propio plan: con pocas ejecuciones, el hard parse de cada una cuesta poco.

Cómo confirmar

Genere un trace nuevo de la misma operación después del cambio. La forma debe desaparecer o quedar con pocas variantes, y en Tipos de llamada, en la fila PARSE, Misses debe bajar. El hallazgo desaparece cuando el PARSE de las variantes deja de pesar; el de muchos hard parses, si estaba, suele desaparecer junto.

En el informe

El informe de ejemplo no tiene sentencias con literales repetidos, así que este hallazgo y la sección Textos compartibles no aparecen en él. El informe del caso 1, de un trace real, tiene los dos: vea el hallazgo en el Diagnóstico del caso 1 y la forma en Textos compartibles del caso 1. Dónde mirar:

• Diagnóstico: el hallazgo y el enlace Ver sentencia #n.
• Detalle de la sentencia: la forma, con el texto en :s y :n, y la tabla Variantes que más gastaron.
• Sentencias SQL por contribución: la forma es una sola fila, con la etiqueta N variantes y las variantes sumadas.
• Textos compartibles: las formas con dos variantes o más; la sección solo aparece cuando el trace las tiene.
• Tipos de llamada: fila PARSE, Cantidad y Misses.