procedimientos - procedure oracle ejemplos
Comprender los resultados de Ejecutar plan de explicación en Oracle SQL Developer (5)
Aquí hay una referencia para usar EXPLAIN PLAN con Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm ), con información específica sobre las columnas que se encuentran aquí: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i18300
Su mención de ''FULL'' me indica que la consulta está realizando un escaneo de tabla completa para encontrar sus datos. Esto está bien, en ciertas situaciones, de lo contrario, es un indicador de pobre escritura de indización / consulta.
En general, con los planes de explicación, quiere asegurarse de que su consulta utiliza claves, por lo que Oracle puede encontrar los datos que está buscando con acceso al menor número posible de filas. En última instancia, en algún momento puede llegar tan lejos con la arquitectura de sus tablas. Si los costos siguen siendo demasiado altos, es posible que tenga que pensar en ajustar el diseño de su esquema para que esté más basado en el rendimiento.
Estoy tratando de optimizar una consulta, pero no entiendo bastante la información devuelta por Explain Plan . ¿Alguien puede decirme la importancia de las columnas OPCIONES y COSTOS? En la columna OPCIONES, solo veo la palabra FULL. En la columna COST, puedo deducir que un costo menor significa una consulta más rápida. Pero, ¿qué representa exactamente el valor de costo y qué es un umbral aceptable?
El CBO construye un árbol de decisión, estimando los costos de cada ruta de ejecución posible disponible por consulta. Los costos se establecen mediante el parámetro CPU_cost o I / O_cost establecido en la instancia. Y la CBO estima los costos lo mejor que puede con las estadísticas existentes de las tablas y los índices que usará la consulta. No debe ajustar su consulta solo en función del costo. El costo le permite comprender POR QUÉ el optimizador está haciendo lo que hace. Sin costo podría averiguar por qué el optimizador eligió el plan que hizo. Menor costo no significa una consulta más rápida. Hay casos en que esto es cierto y habrá casos en que esto sea incorrecto. El costo se basa en las estadísticas de la mesa y, si son incorrectas, el costo será incorrecto.
Al ajustar su consulta, debe echar un vistazo a la cardinalidad y el número de filas de cada paso. ¿Tienen sentido? ¿La cardinalidad que el optimizador está asumiendo es correcta? ¿Las filas están siendo razonables? Si la información presente es incorrecta, es muy probable que el optimizador no tenga la información adecuada que necesita para tomar la decisión correcta. Esto podría deberse a estadísticas obsoletas o desactualizadas en la tabla y el índice, así como a las estadísticas de CPU. Es mejor tener estadísticas actualizadas al ajustar una consulta para aprovechar al máximo el optimizador. Conocer su esquema también es de gran ayuda al sintonizar. Saber cuándo el optimizador eligió una decisión realmente mala y señalarla en el camino correcto con una pequeña pista puede ahorrarle tiempo.
El resultado de EXPLAIN PLAN es un resultado de depuración del optimizador de consultas de Oracle. El COSTO es el resultado final del Optimizador basado en el costo (CBO), cuyo propósito es seleccionar cuál de los muchos planes posibles diferentes debe usarse para ejecutar la consulta. El CBO calcula un Costo relativo para cada plan, luego elige el plan con el costo más bajo.
(Nota: en algunos casos, la CBO no tiene tiempo suficiente para evaluar todos los planes posibles, en estos casos simplemente selecciona el plan con el costo más bajo encontrado hasta ahora)
En general, uno de los mayores contribuyentes a una consulta lenta es el número de filas leídas para dar servicio a la consulta (bloques, para ser más precisos), por lo que el costo se basará en parte en el número de filas que las estimaciones del optimizador necesitarán ser leido.
Por ejemplo, supongamos que tiene la siguiente consulta:
SELECT emp_id FROM employees WHERE months_of_service = 6;
(La columna months_of_service tiene una restricción NOT NULL y un índice ordinario en ella).
Hay dos planes básicos que el optimizador podría elegir aquí:
- Plan 1: lea todas las filas de la tabla "empleados", para cada una, verifique si el predicado es verdadero (
months_of_service=6). - Plan 2: lea el índice donde
months_of_service=6(esto da como resultado un conjunto de ROWID), luego acceda a la tabla en función de los ROWID devueltos.
Imaginemos que la tabla de "empleados" tiene 1,000,000 (1 millón) de filas. Imaginemos además que los valores de months_of_service van del 1 al 12 y que se distribuyen de manera bastante uniforme por algún motivo.
El costo del Plan 1 , que implica un ESCANEO COMPLETO, será el costo de leer todas las filas en la tabla de empleados, que es aproximadamente igual a 1,000,000; pero dado que Oracle a menudo podrá leer los bloques usando lecturas de múltiples bloques, el costo real será menor (dependiendo de cómo esté configurada su base de datos); por ejemplo, imaginemos que el recuento de lecturas de varios bloques es 10, el costo calculado de el escaneo completo será de 1,000,000 / 10; Costo general = 100.000.
El costo del Plan 2 , que implica una EXPLORACIÓN DE LA ESCALA DE ÍNDICES y una búsqueda de tabla por ROWID, será el costo de escanear el índice, más el costo de acceder a la tabla por ROWID. No entraré en el modo de calcular el costo del escaneo del rango de índice, pero imaginemos que el costo del escaneo del rango de índice es 1 por fila; esperamos encontrar una coincidencia en 1 de 12 casos, por lo que el costo del escaneo del índice es de 1,000,000 / 12 = 83,333; más el costo de acceder a la tabla (supongamos que 1 bloque lee por acceso, no podemos usar lecturas de múltiples bloques aquí) = 83,333; Costo total = 166,666.
Como puede ver, el costo del Plan 1 (exploración completa) es MENOR que el costo del Plan 2 (exploración de índice + acceso por hilera), lo que significa que la OBC elegiría la exploración COMPLETA.
Si las suposiciones hechas aquí por el optimizador son verdaderas, entonces, de hecho, el Plan 1 será preferible y mucho más eficiente que el Plan 2, lo que refuta el mito de que los escaneos COMPLETOS son "siempre malos".
Los resultados serían bastante diferentes si el objetivo del optimizador fuera FIRST_ROWS (n) en lugar de ALL_ROWS, en cuyo caso el optimizador favorecería el plan 2 porque a menudo devolverá las primeras filas más rápido, a costa de ser menos eficiente para toda la consulta .
En las últimas versiones de Oracle, COST representa la cantidad de tiempo que el optimizador espera que tome la consulta, expresada en unidades del tiempo requerido para una sola lectura de bloque.
Entonces, si una lectura de bloque único tarda 2 ms y el costo se expresa como "250", la consulta podría tardar 500 ms en completarse.
El optimizador calcula el costo en función del número estimado de lecturas de bloque único y de bloque múltiple, y el consumo de CPU del plan. lo último puede ser muy útil para minimizar el costo al realizar ciertas operaciones antes que otras para intentar evitar operaciones de alto costo de CPU.
Esto plantea la pregunta de cómo el optimizador sabe cuánto tiempo duran las operaciones. Las versiones recientes de Oracle permiten las colecciones de "estadísticas del sistema", que definitivamente no deben confundirse con las estadísticas en tablas o índices. Las estadísticas del sistema son mediciones del rendimiento del hardware, principalmente lo más importante:
- ¿Cuánto tiempo dura la lectura de un solo bloque?
- Cuánto tarda una lectura de varios bloques
- Qué tan grande es una lectura de varios bloques (a menudo diferente del máximo posible debido a que las extensiones de la tabla son más pequeñas que el máximo, y otras razones).
- Rendimiento de la CPU
Estos números pueden variar mucho según el entorno operativo del sistema, y se pueden almacenar diferentes conjuntos de estadísticas para las operaciones "OLTP durante el día" y las operaciones de "informe de lotes durante la noche" y para el "informe de fin de mes" si lo desea.
Dados estos conjuntos de estadísticas, un plan de ejecución de consultas determinado puede evaluarse en función del costo en diferentes entornos operativos, lo que podría promover el uso de escaneos completos de tablas en algunos momentos o escaneos de índices en otros.
El costo no es perfecto, pero el optimizador mejora con el autocontrol en cada lanzamiento, y puede retroalimentar el costo real en comparación con el costo estimado para tomar mejores decisiones en el futuro. esto también hace que sea bastante más difícil de predecir.
Tenga en cuenta que el costo no es necesariamente el tiempo del reloj de pared, ya que las operaciones de consulta en paralelo consumen una cantidad total de tiempo en varios hilos.
En versiones anteriores de Oracle, el costo de las operaciones de la CPU se ignoraba, y los costos relativos de las lecturas de uno y varios bloques se corrigían de manera efectiva de acuerdo con los parámetros init.
FULL probablemente se refiere a un escaneo de tabla completo, lo que significa que no hay índices en uso. Esto generalmente indica que algo está mal, a menos que se suponga que la consulta utiliza todas las filas en una tabla.
El costo es un número que indica que la suma de las diferentes cargas, procesador, memoria, disco, IO y números altos son típicamente malos. Los números se suman cuando se mueve a la raíz del plan, y cada rama debe examinarse para localizar los cuellos de botella.
También es posible que desee consultar v $ sql y v $ session para obtener estadísticas sobre las declaraciones SQL, y esto tendrá métricas detalladas para todo tipo de recursos, tiempos y ejecuciones.