¿Cuál es la forma correcta de configurar el parámetro de ajuste del costo del índice del optimizador para Oracle? Como desarrollador, he observado enormes mejoras en el rendimiento a medida que se reduce este parámetro. Las consultas comunes se reducen de 2 segundos a 200 ms. Hay muchas advertencias en la red de que reducir este valor causará problemas graves con la base de datos, pero no se dan detalles sobre qué empezará a salir mal.
Actualmente solo veo una ventaja, un rendimiento de la aplicación muy mejorado y ningún inconveniente. Necesito comprender mejor las posibles repercusiones negativas de ajustar estos parámetros.
Respuesta1
La razón por la que no se recomienda cambiar estos parámetros es que tienen un impacto en toda la base de datos en el optimizador, por lo que cuando lo cambia para ajustar una consulta específica, probablemente tendrá algún impacto en muchas otras consultas. Por lo tanto, cambiarlo en producción sin probar cuidadosamente toda la aplicación es peligroso.
Sin embargo:
- Configurarlo en un entorno de desarrollo/prueba y mantener el mismo valor en producción podría ser aceptable (solía ser una práctica común en los sistemas OLTP). Sin embargo, ¿puedes estar seguro de que tu aplicación se ejecutará en una base de datos dedicada? ¿Y nunca se consolidará en otra base de datos con un conjunto de parámetros predeterminado?
- Los parámetros ayudan porque Oracle usa algunas heurísticas sobre el costo relativo de E/S versus CPU y, en su caso, las heurísticas no son lo suficientemente buenas, por lo que Oracle elige planes de ejecución subóptimos. La forma recomendada de corregir la heurística es dejar que Oracle recopileestadísticas del sistemapara su máquina de base de datos: qué tan rápida es la CPU, cuánto tiempo lleva obtener un bloque único/bloque múltiple de su sistema de E/S durante la carga normal del sistema, etc.Ver documentación de Oracle.
Si desea utilizar tanto las estadísticas del sistema como los parámetros del optimizador, búsquelo en Google, escribió Jonathan Lewis al respecto (lo siento, el sitio no me permite publicar más de un enlace).
Espero que eso ayude
Respuesta2
El parámetro no debe cambiarse en un entorno de producción. El uso principal es forzar un cambio de plan sólo para verificar el rendimiento con diferentes planes de ejecución. Básicamente, le está sugiriendo al optimizador que todos los índices de su base de datos sean más baratos de usar que otras rutas de acceso. Y esto puede ser cierto para algunos SQL y puede ser falso para otros.
Una vez que tenga un buen plan de rendimiento, debe comprender por qué el optimizador no lo usa e intentar solucionarlo (es decir, no hay estadísticas nuevas/precisas disponibles -> recopilar estadísticas nuevas y más precisas).
Espero que esto ayude, Stefano.
Respuesta3
Los valores predeterminados para estos 2 parámetros son terribles para los sistemas OLTP, que son el tipo de base de datos más común. Conducen a escaneos de tablas más completos y consultas incorrectas. Generalmente querrás configurar estos parámetros ANTES de publicar. Lo haces en la fase de prueba.
Si los cambia después de la puesta en marcha, corre el riesgo de cambiar otras consultas que se ajustaron a configuraciones incorrectas. Parece que no sabes mucho sobre el ajuste de la base de datos, ya que mencionas el tiempo de respuesta en lugar de los planes de consulta. No debes tocar estos parámetros.
La mayoría de los administradores de bases de datos no comprenden la diferencia de conceptos entre reparación y diseño. Una vez que esté en vivo, estará arreglando y es entonces cuando debe tener cuidado al cambiar estos parámetros. Antes de entrar en funcionamiento, se encuentra en la fase de diseño y desarrollo. Ahí es cuando ajustas parámetros como este.
Por cierto, un buen lugar para comenzar con estos parámetros (¡tenga en cuenta ANTES DE IR A PRODUCCIÓN Y SÓLO SI SABE LO QUE ESTÁ HACIENDO!)
optimizador_index_cost_adj=10 almacenamiento en caché del optimizador=90
Esto es para OLTP. Para el procesamiento por lotes, las configuraciones con las que desea comenzar son muy diferentes. Modifico un poco estas configuraciones, pero esas configuraciones me brindan los mejores resultados generales el 99% de las veces en un OLTP. Sin embargo, NO los toco después de que pasamos a producción. Si son malos los dejo malos y afino las consultas.