¿Existe una métrica de selectividad de índice estándar en Oracle?

El índice de árbol B es un índice universal en Oracle y es el tipo de índice predeterminado al crear un índice. Puede incluir hasta 32 columnas.

Índice de mapa de bits Oracle crea un mapa de bits para cada clave única y luego guarda el ROWID asociado con el valor de la clave como un mapa de bits. Puede incluir hasta 30 columnas.

En términos generales, la mayoría de los usuarios solo crean tipos ordinarios de índices de árbol B, por lo que no creamos índices para columnas con cardinalidad más baja, porque es posible que los índices de árbol B no aumenten la velocidad de consulta, o incluso aumenten la inserción. Los comandos de actualización y eliminación toman tiempo.

Los índices de mapa de bits suelen funcionar mejor que los índices de árbol B al cargar tablas (operaciones de inserción). Generalmente, los índices de mapa de bits son de 3 a 4 veces más rápidos que los índices de árbol B con cardinalidad baja (algunos valores diferentes), pero si los valores recién agregados representan más del 70% de las filas insertadas, los índices de árbol B generalmente son más rápido. Los índices de árbol B son tres veces más rápidos que los índices de mapa de bits al agregar nuevos valores a cada registro.

No se recomienda el uso de índices de mapa de bits en algunas aplicaciones de procesamiento de transacciones en línea (OLTP). El valor de índice de un índice de árbol B contiene el ROWID para que Oracle pueda bloquear el índice a nivel de fila. Los índices de mapa de bits se almacenan como valores de índice comprimidos que contienen un rango de ID de fila, por lo que ORACLE debe bloquear todos los rangos de ID de fila para un valor determinado. Este bloqueo puede provocar interbloqueos en determinadas declaraciones DML. Las declaraciones SELECT no se ven afectadas por este problema de bloqueo.

Los índices de mapas de bits tienen muchas limitaciones:

1. El optimizador basado en reglas no considerará los índices de mapas de bits.

2. Cuando se ejecuta la instrucción ATLER TABLE y se modifica la columna que contiene el índice del mapa de bits, el índice del mapa de bits entrará en vigor.

3. Los índices de mapa de bits almacenan valores de clave de índice en bloques de índice, sin embargo, no pueden utilizar ningún tipo de verificación de integridad.

4. El índice de mapa de bits no se puede declarar como un índice único.

Los anteriores son algunos conceptos simples citados. Lo siguiente es lo que resumí del trabajo real:

Quiero hacer una consulta que involucre dos tablas T_sym_dict y T _sym_operlog. La estructura de la tabla es la siguiente:

El índice de Oracle se utiliza utilizando el índice de Oracle.

El índice de t_sym_operlog es el siguiente:

El índice de Oracle utiliza

las tres columnas anteriores con una cardinalidad relativamente pequeña para crear un índice de mapa de bits.

El índice de t_sym_dict es el siguiente:

Uso del índice de Oracle

La declaración de consulta es la siguiente:

Seleccione (seleccione c .dict_name

Rama de t_sym_dict c

donde c.dict_typeid = 'Información de la ciudad SYM'

y c.dict_id = t.memo),

T .staff_id número de trabajo

De t_sym_operlog t

donde t.operlog_subtype = '103 '

y t.obj_type = 'CUSTLINKINFO '

t.memo = '200 '

-y t.extsys_code = ' '

-y t.staff_id = ' '

y t.oper _ date & gt= to _ date(' 20110501000000 ', ' yyyymmddhh24miss ')

y t.oper _ date & lt= to _ date(' 20110530000000 ', ' yyyymmddhh24miss ')

-Presiona t.memo, t.oper_date para ordenar

Entonces ocurrió el siguiente fenómeno extraño (no hay problema en crear el índice)

1. El índice se utiliza normalmente.

Uso del índice de Oracle

2. No puede ver el uso del índice oper_date a continuación.

Uso del índice de Oracle

3. El uso del índice de la tabla t_sym_operlog no se puede ver a continuación.

Uso del índice de Oracle

Solución probada:

Recopilar tablas de datos es análisis.

Inicio

DBMS_stats.gather_table_stats(own name = > 'CSID', tabname = >t_sym_oper log_back');

Fin;

El motivo de la especulación:

Cuando utiliza el lenguaje SQL para emitir una declaración de consulta a la base de datos, ORACLE generará un "plan de ejecución", es decir, qué plan de búsqueda de datos ejecutará la declaración, ya sea de forma completa. escaneo de tabla o búsqueda de índice. La elección del plan de búsqueda está estrechamente relacionada con el optimizador ORACLE.

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 Ejecutar "Plan de ejecución"

Análisis:

Problemas típicos del optimizador CBO de Oracle:

A veces muestra que hay un índiceSin embargo, el índice relevante obviamente no se utiliza durante el proceso de consulta, lo que resulta en un proceso de consulta largo y con enormes 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), combinación de hash (HJ) y combinació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 por separado 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 de í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 está en forma de comentarios, incluidos comentarios de línea y comentarios de segmento

Si queremos utilizar el índice IND_COL1. de la tabla A, podemos usarlo con el siguiente método:

" SELECT * FROM A WHERE col 1 = XXX;"

Tenga en cuenta que el carácter de comentario debe seguir al SELECT, y el "+" en el comentario debe seguir inmediatamente al comentario Después del carácter inicial "/*" o "-"; de lo contrario, la sugerencia se considerará un comentario general y no tendrá 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

Una es interpretar la tabla. Los usuarios primero deben crear un PLAN_TABLE en su propia tabla SCHEMA, cada paso del plan de ejecución se registrará en la tabla. El script SQL para crear la tabla es utlxplan.sql en ${ORACLE_HOME}/rdbms/admin/ <. /p>

Abra SQL*PLUS e ingrese "SET. AUTOTRACE ON", y luego ejecute la instrucción SQL que se va a depurar. Una vez que se proporcionen los resultados de la consulta, ORACLE mostrará el "plan de ejecución" correspondiente, incluido el tipo de optimizador. costo de ejecución, modo de conexión, secuencia de conexión, ruta de búsqueda de datos y costo de recursos correspondiente, como lectura continua, lectura física, etc.

Si no estamos seguros de las declaraciones SQL específicas que deben rastrearse, por ejemplo Por ejemplo, después de que una aplicación se ha utilizado durante un período de tiempo, la velocidad de respuesta se ralentiza repentinamente. En este momento, podemos usar ORACLE Otra herramienta poderosa es TKPROF para rastrear todo el proceso de ejecución de la aplicación. p>Primero debemos encontrar el SID y SERIAL# correspondientes en la vista del sistema V$SESSION según USERID o MACHINE

Conectar la base de datos a SYS u otros usuarios que hayan ejecutado el paquete DBMS_SYSTEM y ejecutar "Ejecutar". DBMS_sistema. Establezca _ SQL _ trace _ en _ sesión (SID, SERIAL#, true)".

Luego ejecute la aplicación. En este momento, en el lado del servidor, en el directorio indicado por el parámetro de la base de datos "USER_DUMP_DEST ", se generará el archivo ora__xxxx.trc, donde xxxx es el número de proceso del sistema operativo de la aplicación rastreada.

Después de ejecutar la aplicación, use el comando tkprof para analizar el archivo. Ejemplo de comando: "tkprof tracker archivo de salida del archivo explicación = ID de usuario/contraseña ". En el sistema operativo ORACLE, ingrese "tkprof", habrá ayuda de comando detallada. El archivo de salida analizado contiene el "plan de ejecución", el uso de la CPU, el número de lecturas físicas y las lecturas lógicas. de cada declaración PL/SQL Información importante como tiempos y duración de ejecución.

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.