database - rendimiento - optimizar indices sql server
¿Cómo soluciono los problemas de rendimiento con una declaración SQL de Oracle? (9)
Cuando el rendimiento de una instrucción sql no es el esperado / deseado, una de las primeras cosas que hago es verificar el plan de ejecución.
El truco es verificar cosas que no son las esperadas. Por ejemplo, puede encontrar escaneos de tablas donde cree que un escaneo de índice debe ser más rápido o viceversa.
Un punto donde el optimizador de Oracle a veces toma un giro incorrecto son las estimaciones de cuántas filas regresará un paso. Si el plan de ejecución espera 2 filas, pero sabe que tendrá más de 2000 filas, el plan de ejecución no será óptimo.
Con dos declaraciones para comparar, obviamente puede comparar los dos planes de ejecución para ver dónde difieren.
A partir de este análisis, se me ocurre un plan de ejecución que creo que debería ser más adecuado. Este no es un plan de ejecución exacto, sino solo algunos cambios cruciales, como el que encontré, como: Debe usar Index X o una Hash Join en lugar de un bucle anidado.
Lo siguiente es encontrar una manera de hacer que Oracle use ese plan de ejecución. A menudo mediante el uso de sugerencias, o la creación de índices adicionales, a veces cambiando la declaración de SQL. Luego, por supuesto, prueba que el enunciado cambiado
a) todavía hace lo que se supone que debe hacer
b) es en realidad más rápido
Con b es muy importante asegurarse de que está probando el caso de uso correcto. Una caída de pozo típica es la diferencia entre regresar la primera fila, versus devolver la última fila. La mayoría de las herramientas muestran los primeros resultados tan pronto como están disponibles, sin indicación directa, que hay más trabajo por hacer. Pero si su programa real tiene que procesar todas las filas antes de continuar con el próximo paso de procesamiento, es casi irrelevante cuando aparece la primera fila, solo es relevante cuando la última fila está disponible.
Si encuentra un mejor plan de ejecución, el paso final es hacer que su base de datos realmente lo use en el programa real. Si agregó un índice, esto a menudo funcionará de la caja. Las sugerencias son una opción, pero pueden ser problemáticas si una biblioteca crea su declaración sql, lo que no es compatible con sugerencias. Como último recurso, puede guardar y arreglar planes de ejecución para sentencias sql específicas. Evitaría este enfoque, porque es fácil olvidarse y en un año más o menos un desarrollador deficiente se rascará la cabeza por qué la declaración funciona de una manera que podría haber sido adecuada con los datos de hace un año, pero no con la actual datos ...
Tengo dos instrucciones de inserción, casi exactamente iguales, que se ejecutan en dos esquemas diferentes en la misma instancia de Oracle. El aspecto de la frase insertada no importa: estoy buscando una estrategia de solución de problemas aquí.
Ambos esquemas tienen el 99% de la misma estructura. Algunas columnas tienen nombres ligeramente diferentes, aparte de que son lo mismo. Las instrucciones de inserción son casi exactamente las mismas. El plan de explicación en uno da un costo de 6, el plan de explicación en el otro da un costo de 7. Las tablas involucradas en ambos conjuntos de declaraciones de inserción tienen exactamente los mismos índices. Las estadísticas se han recopilado para ambos esquemas.
Una instrucción de inserción inserta 12,000 registros en 5 segundos.
La otra instrucción de inserción inserta 25,000 registros en 4 minutos 19 segundos.
La cantidad de registros que se insertan es correcta. Es la gran disparidad en los tiempos de ejecución lo que me confunde. Dado que nada se destaca en el plan de explicación, ¿cómo se determinaría qué está causando esta disparidad en los tiempos de ejecución?
(Estoy usando Oracle 10.2.0.4 en una caja de Windows).
Editar: El problema terminó siendo un plan de consulta ineficiente, que implica una fusión cartesiana que no era necesario hacer. El uso juicioso de indicios de índice y una sugerencia de combinación de almohadilla resolvió el problema. Ahora toma 10 segundos. Sql Trace / TKProf me dio la dirección, ya que me mostró cuántos segundos tomaba cada paso del plan y cuántas filas se generaban. Así TKPROF me mostró:
Rows Row Source Operation
------- ---------------------------------------------------
23690 NESTED LOOPS OUTER (cr=3310466 pr=17 pw=0 time=174881374 us)
23690 NESTED LOOPS (cr=3310464 pr=17 pw=0 time=174478629 us)
2160900 MERGE JOIN CARTESIAN (cr=102 pr=0 pw=0 time=6491451 us)
1470 TABLE ACCESS BY INDEX ROWID TBL1 (cr=57 pr=0 pw=0 time=23978 us)
8820 INDEX RANGE SCAN XIF5TBL1 (cr=16 pr=0 pw=0 time=8859 us)(object id 272041)
2160900 BUFFER SORT (cr=45 pr=0 pw=0 time=4334777 us)
1470 TABLE ACCESS BY INDEX ROWID TBL1 (cr=45 pr=0 pw=0 time=2956 us)
8820 INDEX RANGE SCAN XIF5TBL1 (cr=10 pr=0 pw=0 time=8830 us)(object id 272041)
23690 MAT_VIEW ACCESS BY INDEX ROWID TBL2 (cr=3310362 pr=17 pw=0 time=235116546 us)
96565 INDEX RANGE SCAN XPK_TBL2 (cr=3219374 pr=3 pw=0 time=217869652 us)(object id 272084)
0 TABLE ACCESS BY INDEX ROWID TBL3 (cr=2 pr=0 pw=0 time=293390 us)
0 INDEX RANGE SCAN XIF1TBL3 (cr=2 pr=0 pw=0 time=180345 us)(object id 271983)
Observe las filas donde las operaciones son MERGE JOIN CARTESIAN y BUFFER SORT. Las cosas que me impulsaron a mirar esto fueron la cantidad de filas generadas (¡más de 2 millones!) Y la cantidad de tiempo invertido en cada operación (en comparación con otras operaciones).
Estoy de acuerdo con un póster anterior de que SQL Trace y tkprof son un buen lugar para comenzar. También recomiendo encarecidamente el libro Optimizing Oracle Performance , que analiza herramientas similares para rastrear la ejecución y analizar el resultado.
He puesto mi lista general de cosas para verificar para mejorar el rendimiento como respuesta a otra pregunta:
Trucos de ajuste de rendimiento favoritos
... Podría ser útil como una lista de verificación, aunque no sea específica de Oracle.
Lo primero que se debe tener en cuenta es que, como dice la documentación , el costo que se muestra es relativo a uno de los planes de consulta. Los costos de 2 explicaciones diferentes no son comparables. En segundo lugar, los costos se basan en una estimación interna. Tan difícil como lo intenta Oracle, esas estimaciones no son precisas. Particularmente no cuando el optimizador se comporta mal. Su situación sugiere que hay dos planes de consulta que, de acuerdo con Oracle, tienen un rendimiento muy similar. Pero que, de hecho, funcionan de manera muy diferente.
La información real que desea ver es el plan de explicación real en sí. Eso le dice exactamente cómo Oracle ejecuta esa consulta. Tiene un montón de gobbeldy-gook técnico, pero lo que realmente te importa es saber que funciona desde la parte más sangrienta, y en cada paso se funde según una de las pocas reglas. Eso le dirá lo que Oracle está haciendo de manera diferente en sus dos instancias.
¿Qué sigue? Bueno, hay una variedad de estrategias para ajustar malas declaraciones. La primera opción que sugeriría, si está en Oracle 10g, es probar su asesor de ajuste de SQL para ver si un análisis más detallado le indicará a Oracle el error de sus formas. Luego puede almacenar ese plan, y usará el plan más eficiente.
Si no puede hacer eso, o si eso no funciona, entonces debe entrar en cosas como proporcionar sugerencias de consulta, esquemas de consulta almacenados manualmente, y similares. Ese es un tema complejo. Aquí es donde ayuda tener un DBA real. Si no lo hace, querrá comenzar a leer la documentación , pero tenga en cuenta que hay mucho que aprender. (Oracle también tiene una clase de ajuste de SQL que es, o al menos solía ser, muy buena. Sin embargo, no es barata).
Los principales culpables de las lentitudes de los insertos son los índices, las restricciones y los desencadenantes de inserción. Haga una prueba sin tantos de estos como pueda eliminar y vea si es rápido. Luego introdúzcalos de nuevo y vea cuál está causando el problema.
He visto sistemas en los que se sueltan índices antes de inserciones en bloque y se reconstruyen al final, y es más rápido.
Otra buena referencia que presenta una técnica general para el ajuste de consultas es el libro SQL Tuning de Dan Tow.
SQL Trace y tkprof solo son buenos si tiene acceso a estas herramientas. La mayoría de las grandes empresas para las que sí trabajo no permiten a los desarrolladores acceder a nada bajo los ID de Oracle unix.
Creo que debería poder determinar el problema entendiendo primero la pregunta que se hace y leyendo los planes de explicación para cada una de las consultas. Muchas veces me parece que la gran diferencia es que hay algunas tablas e índices que no se han analizado.
Use el recurso SQL Trace y TKPROF .
analizando el OI también recomiendo encarecidamente el libro Optimizing Oracle Performance, que analiza herramientas similares para la ejecución y el procesamiento de seguimiento.