sql - una - ¿Cómo elimino el caché del plan de ejecución Oracle para el benchmarking?
optimizar una base de datos sql (3)
Ha pasado un tiempo desde que trabajé con Oracle, pero creo que los planes de ejecución se almacenan en caché en el grupo compartido. Prueba esto:
alter system flush shared_pool;
La memoria caché del búfer es donde Oracle almacena los datos usados recientemente para minimizar el disco io.
En Oracle 10gr2, tengo varias consultas SQL que estoy comparando el rendimiento, pero después de la primera ejecución, la tabla v $ sql tiene el plan de ejecución almacenado para el almacenamiento en caché, por lo que para una de las consultas voy de 28 segundos en la primera ejecución a .5 segundos después.
He intentado
ALTER SYSTEM FLUSH BUFFER_CACHE; - Después de ejecutar esto, la consulta se ejecuta de manera consistente en 5 segundos, lo cual no creo que sea preciso.
tal vez se haya eliminado la línea de pedido de la memoria caché: elimine de v $ sql donde sql_text como ''seleccionar * de .... pero aparezca un error sobre la imposibilidad de eliminar de la vista.
Hemos estado trabajando mucho últimamente con consultas de ajuste del rendimiento, y uno de los culpables del rendimiento inconsistente de las consultas es la memoria caché del sistema de archivos en la que Oracle está trabajando.
Es posible que mientras esté limpiando la memoria caché de Oracle, el sistema de archivos todavía tenga los datos que su consulta está solicitando, lo que significa que la consulta aún regresará rápidamente.
Lamentablemente, no sé cómo borrar el caché del sistema de archivos; solo uso un script muy útil de nuestros administradores de sistemas muy útiles.
Peter te dio la respuesta a la pregunta que hiciste.
alter system flush shared_pool;
Esa es la declaración que usaría para "eliminar declaraciones preparadas del caché".
(Las declaraciones preparadas no son los únicos objetos eliminados del grupo compartido, la declaración hace más que eso).
Como indiqué en mi comentario anterior (sobre su pregunta), v$sql no es una tabla. Es una vista de rendimiento dinámico, una conveniente representación en forma de tabla de las estructuras de memoria interna de Oracle. Solo tiene el privilegio SELECT en las vistas de rendimiento dinámico, no puede eliminar filas de ellas.
limpiar el pool compartido y la memoria caché?
Lo siguiente no responde su pregunta directamente. En cambio, responde una pregunta fundamentalmente diferente (y tal vez más importante):
¿Deberíamos normalmente purgar el grupo compartido y / o la memoria caché del búfer para medir el rendimiento de una consulta?
En resumen, la respuesta es no.
Creo que Tom Kyte aborda esto bastante bien:
http://www.oracle.com/technology/oramag/oracle/03-jul/o43asktom.html
http://www.oracle.com/technetwork/issue-archive/o43asktom-094944.html
<extracto>
En realidad, es importante que una herramienta de ajuste no haga eso. Es importante ejecutar la prueba, ignorar los resultados y luego ejecutarla dos o tres veces y promediar esos resultados. En el mundo real, la memoria caché del búfer nunca carecerá de resultados. Nunca. Cuando sintonice, su objetivo es reducir la E / S lógica (LIO), porque entonces la E / S física (PIO) se encargará sola.
Considere esto: enjuagar el grupo compartido y la memoria caché del búfer es incluso más artificial que no descargarlos. La mayoría de la gente parece escéptica de esto, sospecho, porque va en contra de la sabiduría convencional. Te mostraré cómo hacer esto, pero no para que puedas usarlo para probar. Más bien, lo usaré para demostrar por qué es un ejercicio inútil y totalmente artificial (y por lo tanto conduce a suposiciones erróneas). Acabo de iniciar mi PC, y he ejecutado esta consulta en una gran mesa. "Enjuago" la memoria caché del búfer y la vuelvo a ejecutar:
</ excerpt>
Creo que Tom Kyte tiene toda la razón. En términos de abordar el problema del rendimiento, no creo que "borrar el caché del plan de ejecución del oráculo" sea normalmente un paso para una evaluación comparativa confiable.
Vamos a abordar la preocupación sobre el rendimiento.
Nos dice que ha observado que la primera ejecución de una consulta lleva mucho más tiempo (~ 28 segundos) en comparación con las ejecuciones posteriores (~ 5 segundos), incluso cuando se vacía (todos los bloques de índice y datos) de la memoria caché del búfer.
Para mí, eso sugiere que el análisis detallado está haciendo un trabajo pesado. Es mucho trabajo o tiene muchas esperas. Esto puede ser investigado y ajustado.
Me pregunto si tal vez las estadísticas no existen, y el optimizador está gastando mucho tiempo reuniendo estadísticas antes de preparar un plan de consulta. Esa es una de las primeras cosas que verificaría, que las estadísticas se recopilan en todas las tablas, índices y columnas indexadas a las que se hace referencia.
Si su consulta se une a una gran cantidad de tablas, la CBO puede considerar una gran cantidad de permutaciones para la orden de unión.
Una discusión sobre el rastreo de Oracle va más allá del alcance de esta respuesta, pero es el siguiente paso.
Estoy pensando que probablemente querrás rastrear los eventos 10053 y 10046.
Aquí hay un enlace a un debate "evento 10053" de Tom Kyte que puede serle útil:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:63445044804318
historia anecdótica relacionada tangencialmente re: rendimiento de parse duro
Hace unos años, vi una consulta que había transcurrido en términos de minutos en la primera ejecución, ejecuciones posteriores en términos de segundos. Lo que descubrimos fue que la gran mayoría del tiempo para el primer tiempo de ejecución se gastó en el análisis detallado.
Esta consulta de problema fue escrita por un desarrollador de CrystalReports que inocentemente (ingenuamente) unió dos enormes vistas de informes.
Una de las vistas era una combinación de 62 tablas, la otra vista era una combinación de 42 tablas.
La consulta usó el Optimizador Basado en Costos. El seguimiento reveló que no era el tiempo de espera, sino todo el tiempo de CPU dedicado a evaluar posibles rutas de combinación.
Cada una de las vistas suministradas por el proveedor no fue tan mala en sí misma, pero cuando se unieron dos de ellas, fue terriblemente lenta. Creo que el problema fue la gran cantidad de permutaciones de unión que el optimizador estaba considerando. Hay un parámetro de instancia que limita el número de permutaciones consideradas por el optimizador, pero nuestra solución fue volver a escribir la consulta. La consulta mejorada solo se unió a la docena de tablas que realmente necesitaba la consulta.
(La solución inicial inmediata de "ayuda de banda" fue programar una ejecución de la consulta más temprano en la mañana, antes de que se ejecutara la tarea de generación de informes. Eso hizo que la generación de informes fuera "más rápida", porque la ejecución de generación de informes utilizó el declaración preparada en el grupo compartido, evitando el análisis detallado.
La corrección de banda no era una solución real, simplemente movió el problema a una ejecución preliminar de la consulta, cuando el tiempo de ejecución largo no se notó.
Nuestro siguiente paso probablemente haya sido implementar un "esquema almacenado" para la consulta, para obtener un plan de consulta estable.
Por supuesto, la reutilización de sentencias (evitando el análisis detallado, utilizando variables de vinculación) es el patrón normativo en Oracle. Mejora el rendimiento, la escalabilidad, yada, yada, yada.
Este incidente anecdótico puede ser completamente diferente al problema que está observando.
HTH