El costo de un query no depende solo de tus índices...
Level300
"El costo de un query no depende solo de tus índices, depende también de tu storage".
Te ha pasado que de pronto tienes el indice correcto, las estadísticas están recientes porque corriste dbms_stats, el plan de ejecución lleva meses estable y sin embargo un día cualquiera...aparece el "misterio"...tu query que antes corría en serial ahora aparece con paralelismo que nadie pidió y un tiempo de ejecución elevado que oscila sin explicación aparente.
Revisas el "select" y no cambio, revisas indices todo ok, revisas estadísticas esta ok también , es decir revisas todo lo un dba aprende con el tiempo a revisar ante esta situacion; sin embargo el optimizador no mira solo eso, antes de decidir el grado de paralelismo mira la vista DBA_RSRC_IO_CALIBRATE y esta tabla no habla de indices ni de cardinalidad. habla de cuantos IOPs y cuantos MB/s tu storage puede brindarte, si alguien ejecuto calibrate_io alguna vez en ese ambiente el optimizador ya tiene una opinión sobre tu hardware de disco.
Y como funciona el mecanismo de paralelismo en este contexto?
Cuando el parámetro parallel_degree_policy esta en AUTO o ADAPTATIVE no en MANUAL, el cual es el defecto en la mayoría de las instalaciones el optimizador evalúa el costo estimado de la operación y lo compara con el parámetro parallel_min_time_threshold , si es mayor Oracle decide paralelizar y para decir con cuantos procesos necesita saber la capacidad del storage, allí es donde aparece el calibrate_io. Si nadie calibro nunca ese ambiente. el optimizador no se queda sin decidir, usa un valor de calibración por defecto, esto significa que un DOP "raro" puede tener 2 orígenes: una calibración que quedo desactualizada (el storage cambio y la calibración no) o el uso del default porque nunca se calibro.
Y como realizamos la calibración?
Ejecutamos el procedimiento: DBMS_RESOURCE_MANAGER.CALIBRATE_IO
**Antes validamos si hay una ejecución previa:
Según vemos en la imagen no se ha ejecutado antes.
Procedemos a ejecutarlo:
SET SERVEROUTPUT ON
DECLARE
lv_iops PLS_INTEGER;
lv_mbps PLS_INTEGER;
lv_latency PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO ( num_physical_disks =>4, max_latency => 20, max_iops => lv_iops, max_mbps => lv_mbps, actual_latency => lv_latency );
DBMS_OUTPUT.PUT_LINE('max_iops = ' || lv_iops); DBMS_OUTPUT.PUT_LINE('max_mbps = ' || lv_mbps); DBMS_OUTPUT.PUT_LINE('latency = ' || lv_latency);
END;
/
Para ver el avance de la ejecución :
SELECT status, calibration_time FROM v$io_calibration_status;
Una vez finalizado procedemos a ver el resultado?
**En mi ambiente de prueba demoró 3min y 4 seg, el tiempo en un ambiente de producción podría ser mas, asi que hay que hacerlo con precaución y en en periodos de poca carga para que no interfiera ni con la operación ni con la calibración.
Luego de finalizado la ejecución validamos que la vista tenga la información actualizada:
Con eso ya tenemos calibrado la bd a nivel de io.
Acá la descripción oficial de los campos de la documentación de oracle:
Fuente: Oracle
**Los parámetros para calibra son el numero de discos y la latencia que podemos tolerar en nuestros procesos, como referencia se pudo 20ms sin embargo esto puede variar según el contexto y necesidad de cada base de datos.
Y para que que sirve Orion?
Oracle también tiene una herramienta llamada Orion que ya viene como parte de los binarios de Oracle para medir la performance de los discos, un detalle con Orion es que se realiza sin existir aún base de datos y no se recomienda ejecutar sobre discos que ya tienen base de datos porque puede corromperla.
Entonces cuando usamos cual?
Conclusión y recomendación: El plan de ejecución no solo depende de tus índices y estadísticas, Auto DOP consulta DBA_RSRC_IO_CALIBRATE, así que revisa esa vista y tu PARALLEL_DEGREE_POLICY antes de buscar el problema en otro lado. Calibra solo si vas a usar Auto DOP documenta cuándo lo hiciste y vuelve a calibrar si el storage cambia, una calibración antigua podría ser peor que ninguna.