Anuncios en tutorial de programación PLSQL

jueves, 24 de febrero de 2022

Puesta a punto de sentencias SQL (Tuning PLSQL 1)

Si la siguiente sentencia SQL SELECT:

SELECT *
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil = 'S' -- Soltero

Tuning PLSQL

Nos está dando tiempos de ejecución largos, esto querrá decir que:

   1. Obviamente la tabla empleados es de un tamaño considerable.

   2. La tabla no está adecuadamente indexada o que, aún habiéndose creado el índice adecuado, lógicamente un índice sobre la columna "nombre", el optimizador SQL decide utilizar el índice sobre otra columna.

Si tenemos problemas de "performance" con la sentencia SELECT de arriba y suponiendo que tenemos un índice sobre la columna "nombre", esto nos debe llevar a pensar que existe otro índice sobre la columna "estado_civil" que es el que el optimizador está utilizando para ejecutar la sentencia.

Los índices sobre columnas de este tipo, con un rango de valores pequeño (soltero, casado, viudo,...) y con una distribución de valores más o menos homogénea para algunos de estos valores (podemos pensar que el número de solteros va a ser similar al de casados y bastante mayor que el de viudos), van a causar problemas en los tiempos de ejecución. Este tipo de índices sólo son interesantes cuando uno de los posibles valores que puede tomar la columna es mucho menos numeroso que el resto y la sentencia SQL la vamos a limitar en base a dicho valor. Por ejemplo, si nuestra sentencia fuera:

SELECT *
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil = 'V' -- Viudo

Podemos tener la certeza de que los tiempos de ejecución no serían tan malos. En cambio, cuando hacemos la comparación "estado_civil = 'S'", estamos practicamente haciendo un "full scan" sobre un índice y esto es incluso peor que hacer un "full scan" sobre una tabla.

¿Qué tenemos que hacer para arreglar la primera sentencia? Debemos forzar al optimizador a usar el índice sobre la columna "nombre", para ello tenemos dos opciones:

SELECT *
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil||'' = 'S' -- Soltero

Al añadir ||'' el optimizador no podrá utilizar el índice sobre la columna estado_civil.

También, suponiendo que el identificador del índice sobre la columna "nombre" es INXEMPLEADOSNOMBRE, podemos añadir el siguiente "hint" en nuestra sentencia SELECT:

SELECT * /*+ INDEX(empleados INXEMPLEADOSNOMBRE) */
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil = 'S' -- Soltero

Esto hace que el optimizador utilice el índice sobre la columna "nombre" aunque exista otro índice sobre la columna "estado_civil".

10 comentarios:

Unknown dijo...

Buenas,

buen blog el tuyo,eso lo primero

en el caso de que en el FROM hubiese dos tablas y estas se unieran de forma correcta...el orden de las tablas en el FROM influye en algo? y el orden de las condiciones del WHERE?

Por ej,

FROM empleados e , departamento d
WHERE e.apellidos='martinez'
e.id_dept = d.dept_id ;

es mejor poner el "e.id_dept = d.dept_id" antes del e.apellidos='martinez'?

es que tengo esta duda y no se si haras un "SQL TUNING 2 " o algo asi jeje

saludos!

JLPM dijo...

Gracias Evilblues.

El order de las tablas en el FROM es importante cuando se utiliza el hint ORDERED (SELECT /*+ ORDERED */), en cuyo caso el optimizador intentará unir las tablas siguiendo el orden en que aparecen las tablas en el FROM. Esta es una de las formas más utilizadas a la hora de optimizar una sentencia SQL o PL/SQL.

Si no se utiliza este hint entonces el optimizador utilizará el orden que tenga un coste menor (si estamos utilizando el optimizador basado en costes) o el que tenga una menor prioridad (si utilizamos el optimizador SQL basado en normas). En este caso, el orden de las tablas en el FROM sólo influiría si existiesen dos planes de ejecución con el mismo coste o la misma prioridad, en cuyo caso el optimizador Oracle eligiría el que más se aproxime al orden del FROM.

En el ejemplo que propones el orden no es importante, el optimizador accederá primero a la tabla empleados buscando los empleados que se apellidan Martínez y luego procederá a unir dichos registros con la tabla departamento.

Erika dijo...

hola.

como puede reducir el costo de una sentencia si en el where utilizo un substr o un soundex

JLPM dijo...

Marel,

Tu pregunta no resulta sencillo de contestar con los datos que aportas.

Si estas enlazando dos tablas y para enlazarlas necesitas utilizar una función sin poder utilizar ningún índice, entonces podrías crear un índice de tipo función, o bien crear una columna con el resultado de la función, crear un índice sobre dicha columna y después enlazar las tablas utilizando esa columna.

En cuanto a los índices de tipo función sólo sé que existen pero nunca los he utilizado. Aquí te dejo un ejemplo:

CREATE INDEX nombre_mayuscula
ON tabla_nombres (UPPER(nombre));

Este tipo de índices requieren que el parámetro QUERY_REWRITE_ENABLED este puesto a TRUE.

Erika dijo...

hola nuevamente pepelu.

Gracias x haber contestado a mi pregunta y pues realmente tienes razón ya que no fuí del todo explicita en lo que te quería preguntar.

Mi consulta la estoy realizando sobre una sola tabla y mi objetivo es utilizarla en un procedimiento para obtener de acuerdo a un nombre tecleado los registros que más se le parezcan a dicho nombre. La consulta es la siguiente:

SELECT /*+first_rows*/ /*+ INDEX(CYT_USUARIO_IDX1) */ UPPER (FIRST_NAME), UPPER(LAST_NAME), UPPER (SECOND_LAST_NAME), OPRID FROM CYT_USUARIOS
WHERE FIRST_NAME = nombres_aux
AND LAST_NAME = paterno_aux
OR SUBSTR(FIRST_NAME,1,4) = SUBSTR(nombres_aux,1,4)
AND SUBSTR(LAST_NAME,1,4) = SUBSTR(paterno_aux,1,4)
OR SUBSTR(FIRST_NAME,1,1) = DECODE(instr(nombres_aux,' '),0,'',SUBSTR(nombres_aux,(INSTR(nombres_aux,' ')+1),1))
AND SUBSTR(LAST_NAME,1,4) = SUBSTR(paterno_aux,1,4)
OR SUBSTR(FIRST_NAME,(INSTR(FIRST_NAME,' ')+1),1) = SUBSTR(nombres_aux,1,1)
AND LAST_NAME = paterno_aux
OR SOUNDEX(FIRST_NAME) = SOUNDEX(nombres_aux)
AND SOUNDEX(LAST_NAME) = SOUNDEX(paterno_aux)

El problema que tengo es de concurrencia ya que como te comente la utilizo en un procedimiento y hay ocasiones que es utilizado hasta por 10 personas.

La he analizado mediante el explain plan y cuando utilizo el substr o el soundex hace un full table y aumenta el costo.

Espero haber sido explicita y de antemano agradezco tu respuesta y te felicito por tu blogg, esta super chido y no sabes la gran ayuda que aportas a los no tan expertos como tu

JLPM dijo...

Primero intentaría modificar la consulta utilizando en todos los OR alguna columna pura (sin función) sobre la que existiese un índice (first_name o last_name) para ver si de alguna manera me valen los resultados entregados.

Si necesitas algún OR donde ambos AND utilicen funciones sobre campos de la tabla, entonces yo añadiría nuevas columnas a la tabla cuyo contenido sean los valores de las funciones y luego crearía índices para las parejas de datos que comparas en los diferentes OR.

No es una solución muy "limpia" pero es la única forma en la que puedes evitar el full scan.

Ten en cuenta que a lo mejor el optimizador no es lo suficientemente inteligente para aplicar un índice diferente a cada OR, pero lo que si puedes hacer es dividir la consulta en varias, una para cada OR, y posteriormente utilizar el comando UNION para unirlas, de esta manera el optimizador podrá usar diferentes índices.

Creo que si tienes más dudas lo mejor es que usemos el email (pepelublog[arroba]gmail[punto]com).

Anónimo dijo...

Hola Pepelu
quisiera que me ayudes con este select para hacerlo mas rapida su consulta de mi db ya que esta muy lenta
Ejem:
select
nvl(sum(decode(a.t_doc,101,a.cantidad,0))+sum(decode(a.t_doc,103,a.cantidad,0)),0)
,nvl(sum(decode(a.t_doc,102,a.cantidad,0)),0)
into t_ingresos, t_salidas
from movs_almacen_item a
where a.idorganizacion = 1002
and estado = 0
and to_char(fecha_movimiento,'yyyymm') = wperiodo
and idbienser = tc1.idbienser;
Gracias por tu ayuda

JLPM dijo...

Hola Jesús,

Mejorar el rendimiento de una sentencia PL/SQL sin conocer el entorno que la rodea es complicado. El rendimiento debe mejorar con sólo crear los índices adecuados.

De todas formas a bote pronto se me ocurre que el rendimiento debe mejorar dividiendo la sentencia en dos SELECT:

SELECT SUM(a.t_doc)
INTO t_ingresos
FROM movs_almacen_item a
WHERE a.t_cod IN (101, 103)
AND a.idorganizacion = 1002
AND estado = 0
AND to_char(fecha_movimiento,'yyyymm') = wperiodo
AND idbienser = tc1.idbienser;

SELECT SUM(a.t_doc)
INTO t_salidas
FROM movs_almacen_item a
WHERE a.t_cod = 102
AND a.idorganizacion = 1002
AND estado = 0
AND to_char(fecha_movimiento,'yyyymm') = wperiodo
AND idbienser = tc1.idbienser;

Latosso dijo...

Antes que nada una felicitacion por tu blog, tiene muchos tips utiles.
Ahora mi duda
Me podrias explicar cuando afecta al performance consulta que en el where tengan :
AND campo1 = NVL(:P_parametro1,campo1 )
AND campo2 = NVL(:P_parametro2,campo2 )

Y si afecta cual seria el mejor metodo para optimizar cuando se tenga la necesida de este tipo de consultas.

Saludos y Gracias.

JLPM dijo...

Hola Latosso, el optimizador de la base de datos Oracle empleará un plan de ejecución un otro dependiendo de los valores que tomen los parámetros. Si alguno es nulo simplemente eliminará esa condición del WHERE.