Resumen El rendimiento de la base de datos siempre ha sido el foco de los tomadores de decisiones y el personal técnico, y un factor importante que afecta el rendimiento de la base de datos es la ineficiencia de las declaraciones de consulta SQL. Este artículo primero analiza cuatro causas comunes del bajo rendimiento de las declaraciones de consulta SQL y los pasos generales para el ajuste de SQL. Luego analiza cómo reducir las operaciones de E/S, cómo evitar operaciones de alto costo en los resultados de la consulta en las declaraciones de consulta y cómo mejorarlas. Eficiencia de consultas múltiples al conectar tablas.
Palabras clave Oracle;; optimización de SQL; conexión
1 Introducción
Con el desarrollo continuo de las aplicaciones de red, el rendimiento del sistema ha atraído cada vez más atención por parte de los tomadores de decisiones. fabricantes. Hay muchos factores que afectan el rendimiento del sistema y las declaraciones SQL ineficientes son una de las razones importantes que no se pueden ignorar. Este artículo primero analiza las causas comunes del bajo rendimiento de SQL, luego analiza los pasos generales que se deben seguir para el ajuste de SQL y, finalmente, estudia cómo reducir la E/S, evitar operaciones costosas en los resultados de las consultas y cómo mejorar el rendimiento de SQL en múltiples. -uniones de mesa. En vista del dominio actual de ORACLE en el mercado de bases de datos, este artículo solo discutirá ORACLE.
Dos factores que afectan el rendimiento de SQL
Hay muchos factores que afectan el rendimiento de SQL, como parámetros de inicialización irrazonables, estadísticas inexactas del sistema y del modo, y que afectan la precisión del optimizador (CBO ). Sentencia, etc. Estos factores suelen estar estrechamente relacionados con el DBA. Desde la perspectiva de las declaraciones SQL, el autor cree que hay cuatro razones importantes que afectan el rendimiento de SQL:
(1) Realizar operaciones de alto costo en grandes conjuntos de registros, como el uso de predicados que conducen a clasificación.
(2) Hay demasiadas operaciones de E/S (incluidas E/S físicas y E/S lógicas). La más típica es que no se establece un índice adecuado, lo que da como resultado un escaneo completo de la tabla. la tabla de consultas.
(3) Se procesaron demasiados registros inútiles. Por ejemplo, cuando se conectan varias tablas, la colocación inadecuada de las condiciones de filtro da como resultado demasiados registros inútiles en el conjunto de resultados intermedio.
(4) Las funciones proporcionadas por la base de datos no se utilizan en su totalidad, como el procesamiento paralelo de consultas.
La cuarta razón es relativamente sencilla de abordar. Este artículo discutirá cómo mejorar el rendimiento de las declaraciones de consulta SQL por las tres primeras razones.
3 Pasos generales de la optimización de SQL
La optimización de SQL generalmente requiere varios pasos, como descubrir problemas, analizar problemas, proponer soluciones, aplicar medidas y probar el rendimiento, como se muestra en la Figura 1. "Encontrar el problema es la mitad de la solución", por lo que localizar el SQL problemático es un paso muy importante en el proceso de ajuste de SQL. Generalmente, se puede llevar a cabo con la ayuda de las herramientas de optimización del rendimiento propias de ORACLE, como STATSPACK, TKPROF, AUTOTRACE,. etc. Al mismo tiempo, se debe prestar atención a la investigación sobre vistas de rendimiento dinámico como V$SQL, V$MYSTAT, V$SYSSTAT, etc.
Figura 1 Pasos generales de la optimización de SQL
4 Optimización de sentencias SQL
4.1 Optimización de las operaciones de clasificación
El costo de clasificación es muy alto Cuando los predicados que hacen que el conjunto de resultados se ordene se utilizan en declaraciones de consulta, es probable que el rendimiento de SQL se vea afectado.
4.1.1 Análisis del proceso de clasificación
Cuando el conjunto de datos a ordenar no es demasiado grande, el servidor completa la operación de clasificación en la memoria (área de clasificación). Si la clasificación requiere más espacio de memoria, el servidor realizará las siguientes operaciones:
(1) Divida los datos en varios conjuntos pequeños y clasifique cada conjunto.
(2) El servidor solicita espacio temporal del disco, escribe los resultados intermedios ordenados en el segmento temporal y luego ordena otras colecciones.
(3) Después de ordenar todas las colecciones, el servidor las fusionará para obtener el resultado final. Si el área de clasificación es demasiado pequeña para fusionarla a la vez, se hará varias veces.
Del análisis anterior, podemos ver que la clasificación es una operación muy costosa, que consumirá mucho tiempo de CPU y memoria, y desencadenará operaciones de paginación e intercambio de disco, así que trate de evitarla en declaraciones SQL. Operaciones de clasificación.
4.1.2 Operaciones que provocan la clasificación en SQL
Las operaciones que provocan la clasificación en declaraciones de consulta SQL generalmente incluyen: cláusulas ORDER BY y GROUP BY; modificadores únicos de unión, intersección y resta; operadores; ordenar y fusionar conexiones al conectar varias tablas, etc.
4.1.3 Cómo evitar la clasificación
1) Cree un índice adecuado.
Campos de índice para ordenar y unir con frecuencia. Una vez establecido el índice, cuando el servidor envía una solicitud de clasificación a estos campos, hará referencia directamente al índice sin ordenar; al ejecutar una consulta de unión igual, si no se establece el índice del campo conectado, el servidor realizará una combinación de clasificación; conexión El proceso de la operación de unión es el siguiente:
Realizar un escaneo completo en dos o más tablas conectadas;
Ordenar todos los conjuntos de filas en cada tabla por separado;
Resultado de ordenación combinada.
Si el campo utilizado para establecer la conexión ya está indexado, el servidor realiza una unión en bucle anidado, que no requiere ninguna clasificación, y el proceso es el siguiente:
Escaneo completo del puente;
p>Utilice el valor del campo de unión para realizar un análisis único de índice en cada fila devuelta;
Utilice el valor ROWID devuelto por el análisis de índice para localizar el registro en la tabla esclava;
Fusiona la tabla maestra y los registros coincidentes de la tabla.
Por lo tanto, la indexación puede evitar la mayoría de las operaciones de clasificación.
2) Reemplace UNION por UNION ALL.
UNION filtrará los registros duplicados después de vincular la tabla, por lo que después de vincular la tabla, el conjunto de resultados generado se ordenará, los registros duplicados se eliminarán y se devolverán los resultados. En la mayoría de aplicaciones no se generarán registros duplicados, y lo más común es la unión de la tabla de procesos y la tabla de historial. Por lo tanto, utilice el operador UNION ALL en lugar de UNION porque la operación UNION ALL simplemente combina dos resultados y devuelve.
4.2 Optimizar E/S
Las operaciones de E/S excesivas consumirán tiempo de CPU, consumirán mucha memoria y ocuparán demasiados pestillos, por lo que es necesario optimizar la E/S de SQL. optimización optimizada. La forma más eficaz de optimizar la E/S es utilizar exploraciones de índice en lugar de exploraciones de tablas completas.
Aplicaciones de índices basados en funciones
Los índices basados en funciones (FBI) brindan la capacidad de indexar columnas calculadas y utilizar estos índices en consultas. La esencia del FBI es preprocesar los resultados intermedios necesarios para la consulta. Si el FBI coincide exactamente con la función incorporada en la declaración de consulta, CBO habilitará automáticamente un escaneo de rango de índice para reemplazar el escaneo completo de la tabla al generar el plan de consulta. Examine el siguiente fragmento de código y utilice AUTOTRACE para observar los cambios en el plan de ejecución antes y después de crear el FBI.
seleccione * de EMP donde Upper(ename)= ' SCOTT '
Antes de que se creara el FBI, obviamente era un escaneo completo de la tabla.
Plan de ejecución
......
1 0 acceso a la tabla (completo) para "empleados" (coste=2 tarjetas=1 bytes=22 )
p>
idle gtCrear índice EMP _ UPPER _ FIRST _ NAME en EMPLOYEES(UPPER(FIRST_NAME))
El índice ha sido creado.
Ejecute la misma consulta nuevamente,
Plan de ejecución
...
1 0 acceso a la tabla para "empleados" (por índice ROWID )(costo=1tarjeta=1byte=22)
2 1 ' EMP _ UPPER _ FIRST _ NAME ' índice (no único) (escaneo de rango) (costo=1tarjeta=1)
Este sencillo ejemplo ilustra completamente el papel del FBI en la optimización de consultas SQL. Las funciones utilizadas por el FBI pueden ser funciones creadas por los propios usuarios.
Cuanto más compleja sea la función, más obvio será el efecto de optimización en el rendimiento de las consultas SQL al crear un FBI basado en esta función.
4.2.2 Aplicación de vistas materializadas y reescritura de consultas
Las vistas materializadas son conjuntos de resultados precalculados que generalmente incluyen operaciones complejas como agregación y uniones de varias tablas. Las vistas materializadas se mantienen automáticamente en la base de datos y se actualizan según los requisitos del usuario. El mecanismo de reescritura de consultas utiliza un objeto sustituto en la base de datos (como una vista materializada) para reescribir la consulta enviada por el usuario en una consulta completamente diferente pero funcionalmente equivalente. La reescritura de consultas es transparente para los usuarios. Los usuarios escriben declaraciones de consulta de acuerdo con rutinas para acceder a la base de datos y el optimizador (CBO) decide automáticamente si reescribe la consulta enviada por el usuario. La reescritura de consultas es un método muy eficaz para mejorar el rendimiento de las consultas, especialmente para operaciones de alto costo, como resúmenes y uniones de varias tablas en un entorno de almacén de datos.
El siguiente es un ejemplo muy simple que demuestra el papel de las vistas materializadas y la reescritura de consultas en la optimización del rendimiento de las consultas SQL.
Seleccione departamento, número de departamento, nombre de departamento, recuento (*)
De empleado, departamento
Donde número de departamento de empleado = número de departamento de departamento
Agrupar por número de departamento, nombre de departamento
El plan de consulta y las estadísticas principales son las siguientes:
Plan de implementación:
-
...
2 1 uniones hash (coste=5 tarjetas=14 bytes=224)
3 2 accesos a tablas (completos) para "departamento" (coste=2 cards =4 bytes = 52)
4^2 acceso a la tabla (completo) para "EMP" (coste = 2 tarjetas = 14 bytes = 42)
Estadísticas principales:
-
305 llamadas recursivas
46 resultados consistentes
Crear vista materializada EMP_DEPT:
Crear vista materializada emp_dept Construir ahora
Actualizar según demanda
Habilitar reescritura de consultas
Igual que
Seleccionar departamento, número de departamento, nombre de departamento, recuento(*)
De empleado, departamento
donde número de departamento de empleado = número de departamento de departamento
Agrupar por número de departamento de departamento, nombre de departamento
/
Ejecutar la consulta nuevamente. El plan de ejecución y las principales estadísticas son las siguientes:
Plan de implementación:
-
...
1 0 acceso a la tabla de 'EMP_DEPT' (Full) (coste=2 tarjetas=327 bytes=11445)
Estadísticas principales:
-
79 llamadas recursivas
28 resultados consistentes
Se puede ver que antes de establecer la vista materializada, primero se realiza un escaneo completo de las dos tablas, luego se realiza una conexión hash y luego se agrupa. se realizan operaciones de clasificación y selección; una vez establecida la vista materializada, CBO convierte automáticamente las operaciones complejas anteriores en un escaneo completo de la vista materializada EMP_DEPT Las estadísticas relevantes también se han mejorado enormemente. El número de llamadas recursivas se ha reducido de 305. a 79, y el número de E/S lógicas (GETS CONSISTENTES) se ha reducido de 46 a 28. individuales.
4.2.3 Leer en la caché las tablas pequeñas a las que se accede con frecuencia.
La E/S lógica siempre es más rápida que la E/S física. Si hay tablas pequeñas en la base de datos a las que las aplicaciones acceden con frecuencia, se puede forzar la lectura de estas tablas en el grupo KEEP para evitar E/S físicas.
4.3 Optimización de la conexión de varias tablas
La conexión de varias tablas es la mayor manifestación de la complejidad de las consultas. Las operaciones de conexión de varias tablas a menudo consumen mucho tiempo de CPU y memoria, por lo que las operaciones de conexión de varias tablas suelen consumir mucho tiempo de CPU y memoria. Rendimiento de consultas de conexión de tablas La optimización es a menudo el foco y la dificultad de la optimización de SQL.
4.3.1 Eliminación de uniones externas
Al eliminar las uniones externas, las consultas no solo son más fáciles de leer, sino que a menudo también se puede mejorar el rendimiento. La idea general es tener una consulta de la forma:
SELECT..., tabla de unión externa.
Cilindro
DE SOME_TABLE, OUTER_JOINED_TO_TABLE
Donde...=OUTER_JOINED_TO_TABLE( )
se puede convertir a la siguiente forma de consulta:
SELECCIONAR..., (SELECCIONAR COLUMNA DE EXTERIOR _ UNIÓN _ A _ TABLA DONDE...) DE ALGUNA _ TABLA
4.3.2 Los predicados se avanzan para optimizar los resultados intermedios.
El bajo rendimiento de las uniones de varias tablas se debe principalmente al orden irrazonable de las operaciones de unión y de filtrado. Al escribir consultas de unión de varias tablas, la mayoría de los usuarios siempre realizan operaciones de unión antes de aplicar las condiciones de filtro, lo que hace que el servidor realice demasiado trabajo inútil. Para este tipo de problema, la idea de optimización es impulsar el predicado del filtro lo más adelante posible para que los registros que no cumplan las condiciones se filtren por adelantado y solo se conecten unos pocos registros que cumplan las condiciones. eficiencia de las consultas SQL.
La consulta de conexión estándar es la siguiente:
Seleccione a.prod_name, sum(b.sale_quant),
sum(c.sale_quant), sum( d.sale_quant )
Del producto a, ventas telefónicas b, ventas en línea c, ventas en tienda d
donde a. identificación de producción = b.
Y a.prod_id=d.prod_id y a.order_date gt system date-90
Agrupar por a.prod_id
Habilitar vista incrustada. Y establezca la condición a.order_date>; Sysdate-90 se avanza, el código optimizado es el siguiente:
Seleccione a.prod_name, b.tele_sale_sum, c.online_sale_sum, d.store_sale_sum< del producto a. /p>
(seleccione suma(sal_quant)tele_sale_sum del producto,tele_sale
Donde product.order_date gtsysdate-90 y product.prod_id = tele_sale. prod_id)b,
( seleccione sum(sal_quant)online_sale_sum
de producto, ventas telefónicas
donde product.order_ date gtsysdate-90 y product.prod_id = online_sale.prod_id)c,
(seleccione sum(sal_quant)store_sale_sum
de producto, store_sale
donde product.order_date gtsysdate-90 y product.prod_id = store_sale.prod_id)d,
donde a.prod_id=b .prod_id y
a.prod_id=c.prod_id y a.prod_id = d.prod_id;
5 Conclusión
SQL El lenguaje en la base de datos La aplicación juega un papel muy importante y su rendimiento afecta directamente la disponibilidad de todo el sistema de información.
Este artículo analiza cómo optimizar la E/S de consultas SQL, evitar operaciones de clasificación de alto costo y optimizar las conexiones de varias tablas desde los tres aspectos principales que afectan el rendimiento de SQL. Cabe enfatizar que comprender el problema resuelto por la declaración SQL es más importante que el ajuste de SQL en sí, por lo que el ajuste de SQL requiere una estrecha cooperación entre analistas de sistemas, desarrolladores y administradores de bases de datos.
Referencias
Thomas Kate. Diseño eficaz de Oracle: diseño y creación de aplicaciones Oracle de alto rendimiento [M], McGral-Hill Companies, 2003
[2] Kevin Roney, George Koch, Oracle 9i: referencia completa [M], McGrath-Hill Corporation, 2002
[3] Referencia SQL de Oracle9i, versión 2(9.2)[OL/M], 2002.10. /Tecnología/
[4] Guía de almacenamiento de datos de Oracle9i versión 2(9.2) [OL/M], 2002.03./Tecnología/
[5] Alexey Danchenkov, Donald Burleson, Oracle Tuning: la referencia definitiva [OL/M], Rampant Techpress, 2006.
[6] Conceptos de la base de datos Oracle9i versión 2 (9.2) [OL/M], 2002.08./Technology/
[7] El paquete plsql y la referencia de tipo proporcionada por Oracle9i versión 2 (9.2) [OL/M], 2002.12. /tecnología/