You are hereCariño, encogi los indices - Parte 3

Cariño, encogi los indices - Parte 3


By OracleDisected - Posted on 10 May 2008

Compresion de Llaves en Indices ¿Es bueno o malo?

Voy a dividir esta exposición en dos secciones, la primera describira que sucede en el interior del indice (si, a nivel de bloque) cuando se comprimen las llaves; de esta forma obtendremos detalles valiosos de la naturaleza interna de esta caracteristica de Oracle y su tendencia al mal o al bien. En la segunda parte establecemos una introducción a los mecanismos para medir los efectos de la compresión de indices.

¿Qué pasa dentro del indice?

He trabajado con indices lo suficientemente grandes como para ahorrar gigabytes de espacio, desafortunadamente no son muy buenos ejemplos didacticos de lo que sucede. Por lo tanto voy a mostrarlo utilizando una pequeña prueba de concepto, que es un caso de prueba reducido y muy controlado.

Primero creamos una tabla y un indice sobre esta tabla.

SQL> create table scott.t2 ( c1 varchar2(10), c2 varchar2(15), c3 number);

Table created.

SQL> create index scott.indx2 on scott.t2 ( c1, c2 );

Index created.

Entonces insertamos algunos registros que nos puedan proveer la naturaleza de "repetitibilidad" requerida para la compresion de llaves en indices.

SQL> insert into scott.t2 (c1,c2,c3) values ('America','Brasil',1);
insert into scott.t2 (c1,c2,c3) values ('America','Colombia',2);
insert into scott.t2 (c1,c2,c3) values ('America','Canada',3);
insert into scott.t2 (c1,c2,c3) values ('America','Costa Rica',4);
insert into scott.t2 (c1,c2,c3) values ('America','Ecuador',5);
insert into scott.t2 (c1,c2,c3) values ('America','Dominica',6);
insert into scott.t2 (c1,c2,c3) values ('America','Dominicana',7);
insert into scott.t2 (c1,c2,c3) values ('America','Peru',8);
insert into scott.t2 (c1,c2,c3) values ('America','Chile',9);
insert into scott.t2 (c1,c2,c3) values ('America','Venezuela',10);
insert into scott.t2 (c1,c2,c3) values ('America','Paraguay',11);
insert into scott.t2 (c1,c2,c3) values ('America','Uruguay',12);
insert into scott.t2 (c1,c2,c3) values ('America','Guatemala',13);
insert into scott.t2 (c1,c2,c3) values ('America','Nicaragua',14);
insert into scott.t2 (c1,c2,c3) values ('America','Bolivia',15);
insert into scott.t2 (c1,c2,c3) values ('America','Suriname',16);
insert into scott.t2 (c1,c2,c3) values ('America','Panama',17);
insert into scott.t2 (c1,c2,c3) values ('America','El Salvador',18);
insert into scott.t2 (c1,c2,c3) values ('America','Honduras',19);
commit;

De los registros insertados, se puede notar que "America" es un valor comun para cada registro, y repetido en la columna c1, como para establecer la siguiente premisa: el indice scott.indx2 puede ser comprimido en la primera columna c1

Ahora veamos como esta almacenada la informacion dentro de los bloques del indice; para este proceso necesitaremos un par de herramientas que mostraremos a continuación: un script que nos proporciona los numeros de datafile y de bloque que pertenecen a nuestro indice y una sentencia para vaciar el contenido del bloque en un archivo de texto en formato legible.

Con este script podran ver que datafile y bloque corresponden al desplazamento o posición indicado, de un objeto dado.

SELECT
header_file,
header_block + &disp header_block
FROM  dba_segments
WHERE
owner = UPPER('&owner')
AND segment_name = UPPER('&object');

Cuando ejecuten esto en el prompt de sqlplus, mostrara una secuencia como la siguiente:

Enter value for disp: 0
old   2: header_file, header_block + &disp header_block
new   2: header_file, header_block + 0 header_block
Enter value for owner: scott
old   6: owner = UPPER('&owner')
new   6: owner = UPPER('scott')
Enter value for object: indx2
old   7: and segment_name = UPPER('&object')
new   7: and segment_name = UPPER('indx2')

HEADER_FILE HEADER_BLOCK
----------- ------------
    4         8859

Ahora que tenemos exactamente el numero de datafile y a partir de que bloque queremos comenzar a vaciar, es tiempo de proporcionar los valores anteriores a una sentencia ALTER SYSTEM DUMP por rango, como a continuación se muestra:

ALTER SYSTEM DUMP DATAFILE 4 BLOCK min 8859 block max 8866;

La solicitud anterior generará un archivo de texto ascii en la ubicación definida por el parametro USER_DUMP_DEST (usen show parameter USER_DUMP_DEST para obtener el valor actual).

En un vistazo al archivo anterior, podemos identificar facilmente las entradas del indice que recientemente insertamos, que para este ejemplo encontramos en el bloque 0 o inicial del objeto indx2 (recuerden que esta ubicacion y resultados dependeran de los objetos que decidan analizar).

B7D3CFD0 00000000 00000000 00000000 41070200  [...............A]
B7D3CFE0 6972656D 48086163 75646E6F 06736172  [merica.Honduras.]
B7D3CFF0 8F220001 02001200 656D4107 61636972  [.."......America]
B7D3D000 206C450B 766C6153 726F6461 22000106  [.El Salvador..."]
B7D3D010 0011008F 6D410702 63697265 61500661  [......America.Pa]
B7D3D020 616D616E 22000106 0010008F 6D410702  [nama..."......Am]
B7D3D030 63697265 75530861 616E6972 0106656D  [erica.Suriname..]
B7D3D040 008F2200 0702000F 72656D41 07616369  [."......America.]
B7D3D050 696C6F42 06616976 8F220001 02000E00  [Bolivia...".....]
B7D3D060 656D4107 61636972 63694E09 67617261  [.America.Nicarag]
B7D3D070 01066175 008F2200 0702000D 72656D41  [ua..."......Amer]
B7D3D080 09616369 74617547 6C616D65 00010661  [ica.Guatemala...]
B7D3D090 0C008F22 41070200 6972656D 55076163  ["......America.U]
B7D3D0A0 75677572 01067961 008F2200 0702000B  [ruguay..."......]
B7D3D0B0 72656D41 08616369 61726150 79617567  [America.Paraguay]
B7D3D0C0 22000106 000A008F 6D410702 63697265  [..."......Americ]
B7D3D0D0 65560961 757A656E 06616C65 8F220001  [a.Venezuela...".]
B7D3D0E0 02000900 656D4107 61636972 69684305  [.....America.Chi]
B7D3D0F0 0106656C 008F2200 07020008 72656D41  [le..."......Amer]
B7D3D100 04616369 75726550 22000106 0007008F  [ica.Peru..."....]
B7D3D110 6D410702 63697265 6F440A61 696E696D  [..America.Domini]
B7D3D120 616E6163 22000106 0006008F 6D410702  [cana..."......Am]
B7D3D130 63697265 6F440861 696E696D 01066163  [erica.Dominica..]
B7D3D140 008F2200 07020005 72656D41 07616369  [."......America.]
B7D3D150 61756345 06726F64 8F220001 02000400  [Ecuador...".....]
B7D3D160 656D4107 61636972 736F430A 52206174  [.America.Costa R]
B7D3D170 06616369 8F220001 02000300 656D4107  [ica..."......Ame]
B7D3D180 61636972 6E614306 06616461 8F220001  [rica.Canada...".]
B7D3D190 02000200 656D4107 61636972 6C6F4308  [.....America.Col]
B7D3D1A0 69626D6F 00010661 01008F22 41070200  [ombia..."......A]
B7D3D1B0 6972656D 42066163 69736172 0001066C  [merica.Brasil...]
B7D3D1C0 00008F22 00000000 00000000 00000000  ["...............]
B7D3D1D0 00000000 00000000 00000000 00000000  [................]

Ahora es tiempo de ver que sucede cuando aplicamos la compresión de llaves es aplicada a nuestro indice. ¿Recuerda del articulo anterior Cariño, encogi a los indices - Parte 2 los pasos para determinar el grado de compresion, el ahorro esperado y cual es la sentencia apropiada? de acuerdo a ese texto, nuestro ejemplo se ve como sigue:

SQL> analyze index scott.indx2 validate structure offline;

Index analyzed.

SQL> select opt_cmpr_count, opt_cmpr_pctsave from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
        1               26

SQL> alter index scott.indx2 rebuild compress 1;

Index altered.

De esta forma nuestro indice ya ha sido comprimido, veamos que ha pasado con los datos a nivel de bloque. Antes de eso, necesitamos volver a determinar el datafile y el bloque de nuestro objeto, ya que la sentencia ALTER INDEX...REBUILD de hecho mueve o cambia los extents asignados al indice. Basta con ejecutar el script utilizado parrafos arriba y ejecutar la sentencia ALTER SYSTEM DUMP con los nuevos valores obtenidos.Advertencia: si has utilizado la misma sesion de sqlplus para todo el ejercicio, la ultima operación de vaciado añadirá contenido al final del archivo previamente generado.

El vaciado resultante ahora nos muestra que "America" ya no se repite para cada entrada del indice, sino que solo esta presente una sola vez, veamos:

B7DB2060 00000000 00000000 00000000 6E655609  [.............Ven]
B7DB2070 65757A65 0106616C 008F2200 07000009  [ezuela..."......]
B7DB2080 67757255 06796175 8F220001 00000B00  [Uruguay...".....]
B7DB2090 72755308 6D616E69 00010665 0F008F22  [.Suriname..."...]
B7DB20A0 50040000 06757265 8F220001 00000700  [...Peru...".....]
B7DB20B0 72615008 61756761 00010679 0A008F22  [.Paraguay..."...]
B7DB20C0 50060000 6D616E61 00010661 10008F22  [...Panama..."...]
B7DB20D0 4E090000 72616369 61756761 22000106  [...Nicaragua..."]
B7DB20E0 000D008F 6F480800 7275646E 01067361  [......Honduras..]
B7DB20F0 008F2200 09000012 74617547 6C616D65  [."......Guatemal]
B7DB2100 00010661 0C008F22 450B0000 6153206C  [a..."......El Sa]
B7DB2110 6461766C 0106726F 008F2200 07000011  [lvador..."......]
B7DB2120 61756345 06726F64 8F220001 00000400  [Ecuador...".....]
B7DB2130 6D6F440A 63696E69 06616E61 8F220001  [.Dominicana...".]
B7DB2140 00000600 6D6F4408 63696E69 00010661  [.....Dominica...]
B7DB2150 05008F22 430A0000 6174736F 63695220  ["......Costa Ric]
B7DB2160 00010661 03008F22 43080000 6D6F6C6F  [a..."......Colom]
B7DB2170 06616962 8F220001 00000100 69684305  [bia..."......Chi]
B7DB2180 0106656C 008F2200 06000008 616E6143  [le..."......Cana]
B7DB2190 01066164 008F2200 06000002 73617242  [da..."......Bras]
B7DB21A0 01066C69 008F2200 07000000 696C6F42  [il..."......Boli]
B7DB21B0 06616976 8F220001 00080E00 656D4107  [via..."......Ame]
B7DB21C0 61636972 00000000 00000000 00000000  [rica............]
B7DB21D0 00000000 00000000 00000000 00000000  [................]

Si comparamos el espacio utilizado entre los bytes representados en ambos vaciados, el ahorro se acerca al 26% pronosticado por la sentencia ANALYZE y comprueba las promesas de la documentación de Oracle respecto a la compresión de llaves en indices.

Medir las ganancias... o perdidas de la compresion de llaves en indices

Hay dos perspectivas para esta pregunta:
1) Teoricamente a traves de estimaciones
2) Practicamente a través de pruebas

Estimaciones basadas en la reducción de tamaño, son sustentadas con resultados mostrados en consultas a ALL_INDEXES, DBA_SEGMENTS o INDEX_STATS. Es importante tomar muestras antes de la compresion a manera de "linea base", tomar otra muestra después de efectuar la compresión y entonces comparar entre muestras el número total de bloques, el número de bloques rama y el número de bloques hoja.

Estimaciones basadas en planes de ejecución, requieren la recolección de todo el código SQL que involucre a la tabla del indice en cuestión, tomar muestras de los planes de ejecución antes y después de la compresión, y verificar si la compresion del indice genera un beneficio o aumenta el costo.

Esta tarea parece ser tediosa, sin embargo tiene la ventaja de que les permitira conocer mejor las consultas que se ejecutan en la base de datos, inclusive identificar problemas actuales cuyas propuestas de afinación de performance tengan mayor beneficio que la compresion de indices.

Pruebas Practicas o 'pruebas de ejecución' podrán mostrar los resutados con mayor precisión, ya que puede considerar la activación del trace de la sesión, observar planes de ejecución reales (que pueden diferir de los generados con explain plan), obtener estadisticas precisas sobre el uso del indice consultando la vista V$SEGMENT_STATISTICS, la cual esta disponible desde Oracle 9i.

Solo recuerden, que es muy importante tener una linea base para comparar; ya sea tomada de producción, o de un ambiente de pruebas donde se puede comparar el antes y después, pero en la misma base de datos.

¿Cuando es realmente malvada la compresion de llaves en indices? precisamente desde el momento en que se ejecuta ALTER INDEX ... REBUILD. Si, el mero acto de reconstruir nuestro indice lo reorganizará, de tal forma que los bloques seran llenados con llaves hasta el valor PCTUSED del indice, esto incrementará sustancialmente la probabilidad de eventos como la "división de hojas", los cuales implican un esfuerzo adicional que, nosotros como DBAs, debemos evitar se presente...¿Alguna idea de como evitar esta situación? por favor dejen sus comentarios, vamos a interactuar y discutir el tema...

No se pierdan la próxima entrega, vamos a mostrar ejemplos de mediciones practicas.


Visita mi pagina en Oracle Community
Visita mi blog en Blogger

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 1 guest online.

Estadisticas

Locations of visitors to this page

hidden hit counter