You are hereOracle / Porque Oracle no usa mi indice - Parte 1

Porque Oracle no usa mi indice - Parte 1


By admin - Posted on 15 November 2010

¡¿Porque Oracle no usa mi índice?!

Autor: Jonathan Lewis, traducido del original en Ingles

La pregunta en el titulo de este trabajo es probablemente la pregunta que ocurre más frecuentemente en los foros de Metalink y los grupos de noticias de Usenet. Este artículo usa un caso de prueba que usted puede reconstruir en su propio sistema para demostrar los asuntos más fundamentales con el como la optimización basada en el costo funciona. Y al final del artículo, usted deberá estar mucho mejor equipado para dar una respuesta la próxima ocasión que escuche tan temida pregunta.

A causa de la amplia variedad de opciones que se encuentran disponibles al instalar Oracle, no es muy seguro predecir exactamente lo que sucederá cuando alguien corra un guión que usted les dictó, pero me arriesgaré, con la esperanza de que su base de datos sea una simple instalación común, con los valores por defecto para los parámetros más comúnmente modificados. El ejemplo ha sido construido y probado en una base de datos 8.1.7 con el db-block-size puesto en el valor comúnmente usado de 8K y el db-file-multiblock-read-count puesto al igualmente usado valor de 8. Los resultados pueden ser un poco diferentes bajo Oracle 9.2

Ejecute el script listado a continuación, el cual crea un par de tablas, luego las indexa y analiza.


create table t1 as
select
trunc((rownum-1)/15) n1,
trunc((rownum-1)/15) n2,
rpad('x', 215) v1
from all_objects
where rownum <= 3000;


create table t2 as
select
mod(rownum,200) n1,
mod(rownum,200) n2,
rpad('x',215) v1
from all_objects
where rownum <= 3000;


create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);


analyze table t1 compute statistics;
analyze table t2 compute statistics;

Script 1 Los conjuntos de prueba.

Una vez tenga estos datos en su lugar, quizás quiera convencerse que los dos juegos de datos son idénticos. En particular el que las columnas N1 en ambos juegos de datos tienen valores con rangos desde 0 hasta 199, con 15 ocurrencias de cada valor. Debería intentar la siguiente comprobación:


select n1, count(*)
from t1
group by n1;

Y la correspondiente consulta contra T2 para probar el punto.
Si se ejecutan las consultas:


select * from t1 where n1 = 45;
select * from t2 where n1 = 45;

Encontrará que cada consulta regresa 15 filas. Sin embargo, sí

set autotrace traceonly explain

descubrirá que las dos consultas tienen diferentes rutas de ejecución. La consulta en la tabla T1 usa el índice, pero la consulta en la tabla T2 realiza un escaneo a toda la tabla.

Así que tiene dos juegos de datos idénticos, con rutas de acceso dramáticamente diferentes para la misma consulta.

¿Qué le sucedió al índice?

Nota – sí alguna vez se cruza con alguno de estos “números mágicos” acerca del uso de índices, ejemplo: “Oracle usará un índice por menos de 23%, 10%, 2% (escoja un número al azar) de los datos” entonces puede en este punto empezar a dudar de su validez. En este ejemplo Oracle ha usado un escaneo de la tabla de 15 filas de las 3,000 en el ejemplo para solo la mitad del uno por ciento de los datos!

Para investigar problemas como estos, hay un sencillo ardid que siempre intento como el primer paso. Pongo algunas insinuaciones para hacer que Oracle haga lo que yo pienso debería estar haciendo, y ver si me da algunas pistas.

En este caso, una simple sugerencia:
/*+ index(t2, t2_i1) */

es suficiente para cambiar a Oracle de un escaneo completo de la tabla a la ruta de acceso indexada. Las tres rutas con costos (abreviadas C=nnn) se muestran en la figura 2.


select * from t1 where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T1 (C=2)
  INDEX(RANGE SCAN) OF T1_I1 (C=1)


select * from t2 where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS FULL OF T2 (C=15)


select /*+ index(t2 t2_i1) */
*
from t1
where n1 = 45;

EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
  INDEX(RANGE SCAN) OF T2_I1 (C=1)

Figura 2 Las consultas y sus costos.

Entonces por qué Oracle no ha usado el índice predeterminado para la consulta T2? Fácil – como muestra el plan de ejecución, el costo de hacer un escaneo de la tabla es más barato que el costo de usar el índice.

¿Por qué es más barato el escaneo de la tabla?

Esto, por supuesto, es simplemente una petición de principio. Por qué es el costo del escaneo de la tabla más barato que el costo de usar el índice?

Observando esta pregunta se descubren los mecanismos clave (y suposiciones críticamente erradas) del Optimizador en Base a Costo.

Examinemos los índices ejecutando la consulta:

select
table_name,
blevel,
avg_data_blocks_per_key,
avg_leaf_blocks_per_key,
clustering_factor
from user_indexes;

Los resultados se encuentran en la tabla siguiente:

T1 T2
Blevel 1 1
Data block / key 1 15
Leaf block / key 1 1
Clustering factor 96 3000

Note particularmente el valor para “bloques de datos por clave”. Este es el número de diferentes bloques en la tabla que Oracle piensa tendrá que visitar si se ejecuta una consulta que contenga una prueba de igualdad en una clave de valor completo para este índice.

Así que de donde vienen los costos para nuestras consultas? En lo que concierne a Oracle, si comenzamos en el valor clave 45 tendremos los datos de la tabla T1 acertando un bloque hoja del índice y un bloque de la tabla-dos bloques, así que un costo de dos.

Sí intentamos lo mismo en la tabla T2, tenemos que acertar un bloque hoja del índice y quince bloques de tabla- un total de 16 bloques, para un costo de 16.

Claramente, de acuerdo a este punto de vista, el índice en la tabla T1 es mucho más deseable que el índice en la tabla T2. Esto deja dos preguntas sobresalientes.

De donde viene el costo de la tabla, y porqué los resultados para avg_data_blocks_per_key son tan diferentes entre las dos tablas?

La respuesta a la segunda pregunta es sencilla. Observe nuevamente la definición de la tabla T1- usa la función trunc() para generar los valores N1, dividiendo el “rownum – 1” entre 15 y truncando.


Trunc(675/15) = 45
Trunc(676/15) = 45
...
Trunc(689/15) = 45

Todas las filas con el valor 45 actualmente aparecen una detrás de la otra en un grupo compacto (probablemente todas en un bloque de datos) en la tabla.

La tabla T2 usa la función mod() para generar los valores N1, usando módulos 200 en el rownum:


mod(45,200) = 45
mod(245,200) = 45
...
mod(2845,200) = 45

Las filas resultantes con el valor 45 aparecen cada doscientas posiciones en la tabla (probablemente resultando en no más de una fila en cada bloque relevante).

Al realizar el análisis, Oracle fue capaz de obtener una descripción perfecta de los datos dispersos en nuestra tabla. Así que el optimizador logro conocer exactamente cuántos bloques tendría que visitar Oracle para contestar nuestra consulta – y, en casos simples, el número de visitas a bloques es el costo de la consulta.

¿Pero por qué un escaneo de tabla?

Como podemos ver que un acceso indexado a T2 es más costoso que la misma ruta en T1, pero por qué Oracle cambio a el escaneo de tabla?

Esto nos lleva a las dos ingenuas y bastante inapropiadas, suposiciones que realiza Oracle.
La primera es que cada adquisición de bloques equivale a una lectura física de disco, y la segunda es que una lectura de multibloque es tan rápida como una lectura de bloque sencillo.>/p>

Así que como impactan estas suposiciones nuestro experimento?

Si se consulta la vista de user_tables con el siguiente SQL:
select
table_name,
blocks
from user_tables;

Se encontrará que nuestras dos tablas cubren 96 bloques cada una.

Al comienzo del artículo, se señalo que esta prueba corría en un sistema versión 8 con el valor 8 para el db_file_multiblock_read_count.

Hablando llanamente, Oracle ha decidido que puede leer toda la tabla de 96 bloques en 96/8=12 solicitudes de lectura de disco.

Dado que toma 16 bloques (=lectura de disco) solicitudes de acceso a la tabla por índice, es claramente más rápido (a partir de la tristemente engañada perspectiva de Oracle) escanear la tabla – después de todo 12 es menos que 16.

Voila! Si los datos que se buscan están convenientemente dispersos por la tabla se obtienen escaneos de tabla inclusive para un pequeño porcentaje de los datos – un problema que puede ser exagerado en el caso de bloques muy grandes y filas muy pequeñas.

Continua en la siguiente entrega...

Autor: Jonathan Lewis, traducido del original en Ingles

Suscripcion a Contenido Sindicado(RSS)

Suscribir a Databases Hispamerica por Email



Syndicate

Syndicate content

Follow DatabasesLA on Twitter

Who's online

There are currently 0 users and 0 guests online.

Estadisticas

Locations of visitors to this page

hidden hit counter