¿Por qué la base de datos Oracle no utiliza índices para buscar datos?
Pasos de ejecución de sentencias SQL
El procesamiento de sentencias SQL pasa por los siguientes pasos.
1 El análisis gramatical analiza si la gramática de un enunciado se ajusta a la especificación y mide el significado de cada expresión del enunciado.
El análisis semántico comprueba si todos los objetos de la base de datos involucrados en la declaración existen y si el usuario tiene los permisos correspondientes.
3 La conversión de vistas convierte declaraciones de consulta que involucran vistas en declaraciones de consulta correspondientes en la tabla base.
4 La conversión de expresiones convierte expresiones SQL complejas en expresiones de unión equivalentes más simples.
5 Seleccione el optimizador. Diferentes optimizadores generalmente producen diferentes "planes de ejecución"
6 Seleccione el método de conexión. ORACLE tiene tres métodos de conexión. Para conexiones de múltiples tablas, ORACLE puede elegir. conexión adecuada.
7Seleccione el orden de conexión para conectar varias tablas. ORACLE elige qué par de tablas unir primero y cuál de las dos tablas usar como tabla de datos de origen.
8 Seleccione la ruta de búsqueda de datos Seleccione la ruta de búsqueda de datos adecuada según las condiciones anteriores, como búsqueda de tabla completa, índice u otros métodos.
9 Ejecute el "Plan de ejecución"
Optimizador ORACLE
ORACLE tiene dos optimizadores: optimizador RBO (optimizador basado en reglas) y CBO (optimizador basado en costos). ).
RBO se ha adoptado desde la versión 6 de ORACLE y tiene un conjunto estricto de reglas de uso. Siempre que escriba declaraciones SQL de acuerdo con él, no importa lo que haya en la tabla de datos, no afectará su "plan de ejecución", es decir, no es "sensible" a los datos. Oracle ha dejado de desarrollar la tecnología.
CBO se introdujo a partir de la versión 7 de Oracle. Muchas tecnologías nuevas adoptadas por ORACLE a partir de la versión 7 se basan en CBO, como la consulta de unión en estrella, la consulta de unión hash y la consulta paralela. CBO calcula el "costo", es decir, el gasto, de varios "planes de implementación" posibles y selecciona el plan de menor costo como plan de operación real. La base para calcular el costo de cada "plan de ejecución" depende de la distribución estadística de los datos en la tabla de datos, que no está clara para la propia base de datos ORACLE. Es necesario analizar esta tabla y los indicadores relacionados para recopilar los datos requeridos por la CBO.
En general, el "plan de ejecución" seleccionado por CBO no es peor que el de RBO y, en términos relativos, los requisitos de CBO para los programadores no son tan exigentes como los de RBO, lo que evita que los programadores seleccionen una solución óptima. para el tiempo de depuración en el "Plan de ejecución", pero pueden ocurrir problemas en algunos casos.
Los problemas típicos incluyen: A veces, aunque hay un índice, el índice relevante obviamente no se utiliza durante el proceso de consulta, lo que resulta en un proceso de consulta largo y un uso enorme de recursos. ¿Cuál es el problema? De acuerdo con la siguiente secuencia, básicamente puede encontrar el motivo.
Pasos para encontrar la causa
Primero debemos determinar en qué modo de optimización se está ejecutando la base de datos y el parámetro correspondiente es: optimizador_modo. Puede ejecutar "mostrar parámetro optimizador_modo" en svrmgrl para verlo. A partir de ORACLE V7, la configuración predeterminada debe ser "elegir", es decir, se selecciona CBO al consultar la tabla analizada; de lo contrario, se selecciona RBO. Si este parámetro se establece en "regla", se seleccionará RBO independientemente de si la tabla se ha analizado, a menos que se utilice una sugerencia en la declaración para forzarlo.
En segundo lugar, verifique si la columna de índice o la primera columna del índice compuesto aparece en la cláusula WHERE de la declaración PL/SQL. Esta es una condición necesaria para que el "plan de ejecución" se utilice en el. índice relacionado.
En tercer lugar, fíjate qué tipo de conexión se utiliza. * * * de ORACLE incluye combinación de clasificación (SMJ), unión hash (HJ) y unión de bucle anidado (NL). Cuando se unen dos tablas y hay un índice en la columna de destino de la tabla interna, solo los bucles anidados pueden usar el índice de manera efectiva.
Incluso si hay un índice en la columna relevante, SMJ puede, en el mejor de los casos, evitar el proceso de clasificación de datos debido a la existencia del índice. Debido a que HJ tiene que realizar operaciones hash, la existencia del índice tiene poco impacto en la velocidad de consulta de datos.
En cuarto lugar, compruebe si la secuencia de conexión permite el uso de índices relacionados. Supongamos que hay un índice en la columna deptno de la tabla emp, no hay ningún índice en la columna deptno de la tabla dept y la declaración WHERE tiene una condición EMP. Deptno = Dept.Deptno. Al realizar una unión NL, se accede primero a emp como apariencia. Debido al mecanismo de conexión, el método aparente de acceso a los datos es un escaneo completo de la tabla, que obviamente no requiere el índice en emp.deptno. Puede someterse como máximo a un escaneo de índice completo o a un escaneo de índice completo rápido.
En quinto lugar, si se deben utilizar tablas o vistas del diccionario de datos del sistema. Debido a que no se analiza la tabla del diccionario de datos del sistema, el "plan de ejecución" puede ser muy deficiente. Pero no analice la tabla del diccionario de datos sin autorización, de lo contrario puede provocar un punto muerto o una degradación del rendimiento del sistema.
Sexto, si la columna de índice es un parámetro de la función. Si es así, el índice no se utiliza en la consulta.
En séptimo lugar, si existe una posible conversión del tipo de datos. Si los datos de caracteres se comparan con datos numéricos, ORACLE utilizará automáticamente la función to_number() para convertir los datos de caracteres, lo que dará como resultado el sexto fenómeno.
En octavo lugar, si se han recopilado suficientes datos estadísticos para la tabla y los índices relacionados. Para tablas en las que se agregan, eliminan y modifican datos con frecuencia, es una buena idea analizar e indexar la tabla con regularidad. Declaración SQL "Analizar las estadísticas de la computadora para todos los índices en la tabla xxxx;". Oracle sólo puede tomar la decisión correcta si cuenta con estadísticas que reflejen plenamente la realidad.
En noveno lugar, la selectividad de las columnas del índice no es alta.
Asumimos una situación típica, hay una tabla emp, * * hay un millón de filas de datos, pero la columna emp.deptno tiene solo cuatro valores diferentes, como 10, 20, 30, 40 . Aunque hay muchas filas de datos emp, los valores enumerados en la tabla predeterminada de ORACLE se distribuyen uniformemente entre todas las filas de datos, lo que significa que cada valor de deptno tiene 250.000 filas de datos correspondientes. Suponiendo que la condición de búsqueda SQL DEPTNO = 10, la eficiencia de la búsqueda de datos utilizando el índice en la columna DEPTNO a menudo no es mayor que la de un escaneo completo de la tabla. Naturalmente, Oracle "hace la vista gorda" ante el índice y cree que el índice no es selectivo.
Pero consideremos otra situación. Si un millón de filas de datos en realidad no están distribuidas uniformemente entre los cuatro valores de deptno, donde 990.000 filas corresponden al valor 10, 5.000 filas corresponden al valor 20, 300 filas corresponden al valor 30 y 200 filas corresponden al valor 40. Bajo este patrón de distribución de datos, no hay duda de que si se puede aplicar un índice, será mucho más eficiente a la hora de buscar valores de deptno distintos de 10. Podemos analizar la columna de índice individualmente o usar la declaración de análisis para crear un histograma para la columna y recopilar suficientes estadísticas para la columna para que Oracle pueda usar el índice cuando busque valores altamente selectivos.
Décimo, si el valor de la columna del índice puede estar vacío. Si el valor de la columna del índice puede ser nulo, las operaciones que requieren devolver valores NULL en declaraciones SQL no utilizarán el índice, como COUNT(*), sino que escanearán toda la tabla. Esto se debe a que los valores almacenados en el índice no pueden estar completamente vacíos.
Undécimo, ver si se utiliza la consulta paralela (PQO). Las consultas paralelas no utilizan índices.
Duodécimo, compruebe si se utilizan variables de vinculación en la instrucción PL/SQL. Debido a que la base de datos no conoce el valor específico de la variable de enlace, al realizar una conexión desigual, como "
Si no puede descubrir el motivo de los aspectos anteriores, debe forzar a ORACLE agregando sugerencias a la declaración. Utilice el "plan de ejecución" óptimo.
El mensaje utiliza comentarios, incluidos comentarios de línea y comentarios de párrafo.
Si queremos utilizar el índice IND_COL1 de la tabla A, podemos utilizar el siguiente método:
" SELECT/*+INDEX(A IND _ col 1)*/* FROM A WHERE col 1 = XXX;"
Tenga en cuenta que el carácter del comentario debe seguir a SELECT, y el "+" en el comentario debe seguir al carácter inicial del comentario "/*" o "-", de lo contrario, la sugerencia aparecerá Se considera un comentario general y no tiene ningún impacto en la ejecución de declaraciones PL/SQL.
Dos métodos efectivos de seguimiento y depuración
ORACLE proporciona dos herramientas efectivas para rastrear y depurar el plan de ejecución de declaraciones PL/SQL.
Uno es el modo de mesa de interpretación. Los usuarios primero deben crear una tabla PLAN_TABLE en su SCHEMA, y cada paso del plan de ejecución se registrará en la tabla. El script SQL que crea esta tabla es utlxplan.sql en ${ORACLE_HOME}/rdbms/admin/.
Abra SQL*PLUS, ingrese "SET AUTOTRACE ON" y luego ejecute la instrucción SQL que desea depurar. Una vez proporcionados los resultados de la consulta, ORACLE mostrará el "plan de ejecución" correspondiente, incluido el tipo de optimizador, el costo de ejecución, el modo de conexión, la secuencia de conexión, la ruta de búsqueda de datos y los costos de recursos correspondientes, como lectura continua, lectura física, etc.
Si no podemos determinar las declaraciones SQL específicas que deben rastrearse, por ejemplo, después de que una aplicación se ha utilizado durante un período de tiempo, la velocidad de respuesta disminuye repentinamente. En este momento, podemos utilizar TKPROF, otra poderosa herramienta proporcionada por ORACLE, para rastrear todo el proceso de ejecución de la aplicación.
Primero debemos encontrar el SID y el SERIAL# correspondientes en la vista del sistema V$SESSION según USERID o MACHINE.
Conecte la base de datos a SYS u otros usuarios que hayan ejecutado el paquete DBMS_SYSTEM y ejecute "Ejecutar DBMS_system. Set_SQL_trace_in_session (SID, SERIAL#, true)". .
Luego ejecuta la aplicación. En este momento, en el lado del servidor, se generará el archivo ora__xxxx.trc en el directorio indicado por el parámetro de la base de datos "USER_DUMP_DEST", donde xxxx es el número de proceso del sistema operativo de la aplicación rastreada.
Después de ejecutar la aplicación, utilice el comando tkprof para analizar el archivo. Ejemplo de comando: "archivo de salida del archivo de seguimiento tkprof explica = ID de usuario/contraseña". En el sistema operativo ORACLE, ingrese "tkprof" y habrá ayuda de comando detallada. El archivo de salida analizado contiene información importante como el "plan de ejecución", el uso de la CPU, la cantidad de lecturas físicas, la cantidad de lecturas lógicas y la duración de la ejecución de cada declaración PL/SQL. Según la información del archivo de salida, podemos descubrir rápidamente qué declaración PL/SQL en la aplicación es el meollo del problema.