Un artículo interesante sobre estas dos utilidades:
http://db2tutorial.net/db2diag-and-db2dart-2-life-saver-utilities/
DB2 en español | BlogDB2
blog sobre DB2 LUW en español
jueves 15 de septiembre de 2011
viernes 13 de mayo de 2011
DB2 Express-C 9.7.4
Se ha liberado la versión 9.7.4 de DB2 Express-C
Las novedades aunque no son muchas con respecto a la versión 9.7.2, son, a mi parecer, interesantes, y lo aún más que haya desarrollado sobre DB2.
La primera son los triggers compuestos (compound triggrs). Ahora, en lugar de crearse un trigger para cada operación (INSERT, UPDATE y DELETE), se pueden controlar todas las operaciones sobre un sólo trigger. Aunque no marca una diferencia espectacular, lo hace realmente más comodo.
La segunda novedad es que se pueden usar campos de una tabla en la parte derecha de la operación LIKE, cuando hasta ahora sólo se podían usar constantes. Este cambio si que me parece realmente potente, permitiendo hacer joins con patrones, o, aunque de forma un tanto rudimientaria, usar una lista constantes, lo que nos ahorra escribir muchas condiciones en determinadas búsquedas.
Para buscar las entradas que empiecen por a, b, c o d Se puede escribir:
SELECT count(*) from A,table(values ('a%'),('b%'),('c%'),('d%'),('e%')) as t(a) where A.a like t.a;
o
with t(a) as(values ('a%'),('b%'),('c%'),('d%'),('e%'))
SELECT count(*) from A,t where A.a like t.a;
en lugar de:
SELECT count(*) from A where A.a like 'a%' or A.a like 'b%' or A.a like 'c%' or A.a like 'd%' or A.a like 'e%';
Por último aporta mejoras en el uso de Text Search.
Las novedades aunque no son muchas con respecto a la versión 9.7.2, son, a mi parecer, interesantes, y lo aún más que haya desarrollado sobre DB2.
La primera son los triggers compuestos (compound triggrs). Ahora, en lugar de crearse un trigger para cada operación (INSERT, UPDATE y DELETE), se pueden controlar todas las operaciones sobre un sólo trigger. Aunque no marca una diferencia espectacular, lo hace realmente más comodo.
La segunda novedad es que se pueden usar campos de una tabla en la parte derecha de la operación LIKE, cuando hasta ahora sólo se podían usar constantes. Este cambio si que me parece realmente potente, permitiendo hacer joins con patrones, o, aunque de forma un tanto rudimientaria, usar una lista constantes, lo que nos ahorra escribir muchas condiciones en determinadas búsquedas.
Para buscar las entradas que empiecen por a, b, c o d Se puede escribir:
SELECT count(*) from A,table(values ('a%'),('b%'),('c%'),('d%'),('e%')) as t(a) where A.a like t.a;
o
with t(a) as(values ('a%'),('b%'),('c%'),('d%'),('e%'))
SELECT count(*) from A,t where A.a like t.a;
en lugar de:
SELECT count(*) from A where A.a like 'a%' or A.a like 'b%' or A.a like 'c%' or A.a like 'd%' or A.a like 'e%';
Por último aporta mejoras en el uso de Text Search.
martes 22 de febrero de 2011
Block based bufferpools
En la versión 8 de DB2 UDB apareció una nueva característica, los bufferpools basados en bloques. Se supone que estos bufferpools reservan un espacio para que las lecturas asíncronas se hagan en bloque, según el parámetro BLOCKSIZE del bufferpool. Se tratarán de leer tantas páginas simultáneas como indique este valor. Al realizar una lectura asíncrona se leerá un extent del tablespace, y si este cabe en un bloque, lo guardará.
¿Y qué ventaja nos aporta esto? Pues realmente, la principal ventaja no es que se lea en bloque, porque eso ya lo hacen los bufferpools normales con Vectored IO, sino que se delimita un espacio en el bufferpool para lecturas asíncronas, de forma que no se van a desplazar páginas usadas frecuentemente cuando se haga una lectura muy grande. De esta forma no es necesario separar bufferpool para lectura síncrona y asíncrona, y si alguna tabla tiene accesos de ambos tipos, se tratarán ambos de forma independiente.
En resumen:
-No se desplazan páginas muy usadas del bufferpool
-No es necesario separar bufferpools para tablas de acceso síncrono y asíncrono
-Una tabla puede tener accesos síncronos y asíncronos sin ser perjudicada
Configuración de un block based bufferpool:
Al crear un bufferpool basado en bloques se definen dos parámetros, el BLOCKSIZE y el NUMBLOCKPAGES. El primero indica el número de página que contendrá cada bloque, y serán el número de páginas que se leerán simultáneamente. Es importante que este sea igual o mayor que el tamaño de extent de los tablespaces que usen este bufferpool, de lo contrario no se usará el área de bloques. El segundo es el número de páginas del área de bloques. Este debería ser un múltiplo del tamaño de bloque. Se recomienda que el tamaña del area sea entre un 2% y un 3% del tamaño total del bufferpool.
El comando para crear el bufferpool sería:
CREATE BUFFERPOOL <nombre_bp> SIZE <numpaginas> NUMBLOCKPAGES <paginas_area_bloques> BLOCKSIZE <tamaño_bloque> PAGESIZE <tamaño_pagina>
Si se quiere modificar uno ya existente:
ALTER BUFFERPOOL <nombre_bp> NUMBLOCKPAGES <paginas_area_bloques> BLOCKSIZE <tamaño_bloque>
Hay que tener en cuenta que los cambios en el área de bloques requieren reinicio de la base de datos (desactivarla y desconectar todos los usuarios).
Monitorización:
Para comprobar el uso que se está haciendo del área de bloques, se usa el snapshot de bufferpool:
get snapshot for bufferpools on <database>
Los valores importantes serán:
Asynchronous pool data page reads
Asynchronous pool index page reads
Asynchronous data read requests
Asynchronous index read requests
Block IOs
Pages from block IOs
A partir de estos valores podemos calcular las siguientes métricas:
Páginas leídas por bloque:
Pages from block IOs / Block IOs
Debe acercarse lo más posible al tamaño de bloque.
% de lecturas asíncronas leídas por bloques:
Block IOs / (Asynchronous data read requests + Asynchronous index read requests)
Debe tener un valor igual o muy cercano a 100. De lo contrario significaría que hay lecturas asíncronas que no se están resolviendo por bloques.
% de páginas asíncronas leídas por bloques:
Pages from block IOs / (Asynchronous pool data page reads + Asynchronous pool index page reads)
El igual que en la métrica anterior, el valor debe ser lo más cercano a 100 posible.
¿Y qué ventaja nos aporta esto? Pues realmente, la principal ventaja no es que se lea en bloque, porque eso ya lo hacen los bufferpools normales con Vectored IO, sino que se delimita un espacio en el bufferpool para lecturas asíncronas, de forma que no se van a desplazar páginas usadas frecuentemente cuando se haga una lectura muy grande. De esta forma no es necesario separar bufferpool para lectura síncrona y asíncrona, y si alguna tabla tiene accesos de ambos tipos, se tratarán ambos de forma independiente.
En resumen:
-No se desplazan páginas muy usadas del bufferpool
-No es necesario separar bufferpools para tablas de acceso síncrono y asíncrono
-Una tabla puede tener accesos síncronos y asíncronos sin ser perjudicada
Configuración de un block based bufferpool:
Al crear un bufferpool basado en bloques se definen dos parámetros, el BLOCKSIZE y el NUMBLOCKPAGES. El primero indica el número de página que contendrá cada bloque, y serán el número de páginas que se leerán simultáneamente. Es importante que este sea igual o mayor que el tamaño de extent de los tablespaces que usen este bufferpool, de lo contrario no se usará el área de bloques. El segundo es el número de páginas del área de bloques. Este debería ser un múltiplo del tamaño de bloque. Se recomienda que el tamaña del area sea entre un 2% y un 3% del tamaño total del bufferpool.
El comando para crear el bufferpool sería:
CREATE BUFFERPOOL <nombre_bp> SIZE <numpaginas> NUMBLOCKPAGES <paginas_area_bloques> BLOCKSIZE <tamaño_bloque> PAGESIZE <tamaño_pagina>
Si se quiere modificar uno ya existente:
ALTER BUFFERPOOL <nombre_bp> NUMBLOCKPAGES <paginas_area_bloques> BLOCKSIZE <tamaño_bloque>
Hay que tener en cuenta que los cambios en el área de bloques requieren reinicio de la base de datos (desactivarla y desconectar todos los usuarios).
Monitorización:
Para comprobar el uso que se está haciendo del área de bloques, se usa el snapshot de bufferpool:
get snapshot for bufferpools on <database>
Los valores importantes serán:
Asynchronous pool data page reads
Asynchronous pool index page reads
Asynchronous data read requests
Asynchronous index read requests
Block IOs
Pages from block IOs
A partir de estos valores podemos calcular las siguientes métricas:
Páginas leídas por bloque:
Pages from block IOs / Block IOs
Debe acercarse lo más posible al tamaño de bloque.
% de lecturas asíncronas leídas por bloques:
Block IOs / (Asynchronous data read requests + Asynchronous index read requests)
Debe tener un valor igual o muy cercano a 100. De lo contrario significaría que hay lecturas asíncronas que no se están resolviendo por bloques.
% de páginas asíncronas leídas por bloques:
Pages from block IOs / (Asynchronous pool data page reads + Asynchronous pool index page reads)
El igual que en la métrica anterior, el valor debe ser lo más cercano a 100 posible.
martes 23 de noviembre de 2010
Tablas de staging (I) - Introducción
Una tabla de staging consiste en una tabla intermedia en una materialized query table(MQT), donde se almacenan los cambios que se han realizado en la tabla origen. Con esta tabla intermedia se consigue poder hacer refrescos de MQT incrementales, de forma que sólo se refrescan los datos nuevos o que hayan sido modificados.
La ventaja que aporta sobre las MQT gestionadas por el sistema es que permite realizar operaciones en la MQT sin producir bloqueos sobre la tabla origen, y sobre las tablas gestionadas por usuario que se puede hacer el refresco de únicamente los datos modificados.
Esto es ideal para logs e historificaciones, ya que permite hacer consultas sobre los datos sin afectar al proceso de escritura.
Este artículo se dividirá en tres partes:
Esta primera parte donde explico el funcionamiento básico de las tablas de staging, su creación y su operativa ; una segunda parte donde se analiza el impacto que tiene sobre la escritura con respecto a una tabla convencional ; y una parte final donde se analiza el impacto del refresco, tanto en la tabla origen como en la destino.
CREACIÓN DE TABLAS DE STAGING
Supongamos una tabla T1, que se quiere historificar en la tabla T1_HIS.
CREATE TABLE T1(
FECHA TIMESTAMP NOT NULL PRIMARY KEY,
DATO1 INTEGER,
DATO2 INTEGER
);
La tabla de histórico se creará de la siguiente forma:
CREATE TABLE T1_HIS AS (SELECT * FROM T1) DATA INITIALLY DEFERRED REFRESH DEFERRED;
De esta forma se historificarán todos los registros de la tabla T1.
Ahora se crearía la tabla de staging:
CREATE TABLE T1_STG FOR T1_HIS PROPAGATE IMMEDIATE;
SET INTEGRITY FOR T1_STG STAGING IMMEDIATE UNCKECHED;
Y se refrescarían los datos:
REFRESH TABLE T1_HIS NOT INCREMENTAL;
Desde este momento los datos quedarían sincronizados, y cualquier cambio en T1 quedaría registrado en T1_STG.
Si insertamos un registro en T1:
INSERT INTO T1 VALUES(CURRENT TIMESTAMP, 100,10000);
Podemos ver como queda guardado en T1_STG:
SELECT * FROM T1_STG;
FECHA DATO1 DATO2 GLOBALTRANSID GLOBALTRANSTIME OPERATIONTYPE
-------------------------- ----------- ----------- ----------------------------- --------------------------------- -------------
2010-11-23-09.47.19.742507 100 10000 x'00000000002015B9' x'20101123084719742595000000' 1
1 record(s) selected.
Ahora podemos hacer un refresco incremental:
REFRESH TABLE T1_HIS INCREMENTAL ALLOW WRITE ACCESS;
El comando REFRESH TABLE permite realizar el refresco permitiendo accesos en la MQT de escritura, de lectura o no permitiendo accesos.
Una vez ejecutado podemos ver que la tabla T1_STG está vacía, y que la tabla T1_HIS contiene el registro que se insertó en T1.
CONSIDERACIONES A TENER EN CUENTA
Haciendo pruebas he podido comprobar que para la actualización de la tabla MQT se realizan búsquedas por la clave primaria de la tabla origen, por lo que es importante añadirle a posteriori la misma clave primaria que a T1, ya que esta no se crea de forma automática. Las tablas MQT no permiten índices únicos, por lo que el índice debe ser regular.
CREATE INDEX T1_HIS_IDX ON T1_HIS(FECHA);
Sin este índice, el rendimiento se degrada considerablemente a medida que las tablas crecen.
OPERACIONES SOBRE TABLAS
Con el procedimiento arriba indicado se consigue mantener una copia de la tabla sobre la que se pueden hacer operaciones mientras en la tabla original se insertan registros, pero llegará un momento que se deberán vaciar las tablas, bien porque sólo se mantiene un determinado periodo de retención para estas tablas, o porque los datos ya se han historificado desde la MQT.
Para esto se pueden vaciar las tablas origen y destino con un LOAD en vacío normal:
LOAD FROM /dev/null OF DEL REPLACE INTO T1 NONRECOVERABLE;
LOAD FROM /dev/null OF DEL REPLACE INTO T1_HIS NONRECOVERABLE;
Para vaciar la tabla de stating se hará con:
SET INTEGRITY FOR T1_STG PRUNE;
Después de esto las tablas quedarán en CHECK PENDING STATE. Para volver a su estado normal:
SET INTEGRITY ON T1 IMMEDIATE CHECKED;
SET INTEGRITY ON T1_HIS MATERIALIZED QUERY IMMEDIATE UNCHECKED;
SET INTEGRITY ON T1_STG STAGING IMMEDIATE UNCHECKED;
De esta forma las tablas volverán a estar operativas y vacías. Para sincronizar cualquier posible cambio que haya entrado sincronizamos la tabla completa.
REFRESH TABLE T1_HIS NOT INCREMENTAL;
En el próximo artículo se verá como afecta el uso de tablas staging a la inserción en la tabla origen, ya que cada operación realizada efectuará una inserción en la tabla de staging.
La ventaja que aporta sobre las MQT gestionadas por el sistema es que permite realizar operaciones en la MQT sin producir bloqueos sobre la tabla origen, y sobre las tablas gestionadas por usuario que se puede hacer el refresco de únicamente los datos modificados.
Esto es ideal para logs e historificaciones, ya que permite hacer consultas sobre los datos sin afectar al proceso de escritura.
Este artículo se dividirá en tres partes:
Esta primera parte donde explico el funcionamiento básico de las tablas de staging, su creación y su operativa ; una segunda parte donde se analiza el impacto que tiene sobre la escritura con respecto a una tabla convencional ; y una parte final donde se analiza el impacto del refresco, tanto en la tabla origen como en la destino.
CREACIÓN DE TABLAS DE STAGING
Supongamos una tabla T1, que se quiere historificar en la tabla T1_HIS.
CREATE TABLE T1(
FECHA TIMESTAMP NOT NULL PRIMARY KEY,
DATO1 INTEGER,
DATO2 INTEGER
);
La tabla de histórico se creará de la siguiente forma:
CREATE TABLE T1_HIS AS (SELECT * FROM T1) DATA INITIALLY DEFERRED REFRESH DEFERRED;
De esta forma se historificarán todos los registros de la tabla T1.
Ahora se crearía la tabla de staging:
CREATE TABLE T1_STG FOR T1_HIS PROPAGATE IMMEDIATE;
SET INTEGRITY FOR T1_STG STAGING IMMEDIATE UNCKECHED;
Y se refrescarían los datos:
REFRESH TABLE T1_HIS NOT INCREMENTAL;
Desde este momento los datos quedarían sincronizados, y cualquier cambio en T1 quedaría registrado en T1_STG.
Si insertamos un registro en T1:
INSERT INTO T1 VALUES(CURRENT TIMESTAMP, 100,10000);
Podemos ver como queda guardado en T1_STG:
SELECT * FROM T1_STG;
FECHA DATO1 DATO2 GLOBALTRANSID GLOBALTRANSTIME OPERATIONTYPE
-------------------------- ----------- ----------- ----------------------------- --------------------------------- -------------
2010-11-23-09.47.19.742507 100 10000 x'00000000002015B9' x'20101123084719742595000000' 1
1 record(s) selected.
Ahora podemos hacer un refresco incremental:
REFRESH TABLE T1_HIS INCREMENTAL ALLOW WRITE ACCESS;
El comando REFRESH TABLE permite realizar el refresco permitiendo accesos en la MQT de escritura, de lectura o no permitiendo accesos.
Una vez ejecutado podemos ver que la tabla T1_STG está vacía, y que la tabla T1_HIS contiene el registro que se insertó en T1.
CONSIDERACIONES A TENER EN CUENTA
Haciendo pruebas he podido comprobar que para la actualización de la tabla MQT se realizan búsquedas por la clave primaria de la tabla origen, por lo que es importante añadirle a posteriori la misma clave primaria que a T1, ya que esta no se crea de forma automática. Las tablas MQT no permiten índices únicos, por lo que el índice debe ser regular.
CREATE INDEX T1_HIS_IDX ON T1_HIS(FECHA);
Sin este índice, el rendimiento se degrada considerablemente a medida que las tablas crecen.
OPERACIONES SOBRE TABLAS
Con el procedimiento arriba indicado se consigue mantener una copia de la tabla sobre la que se pueden hacer operaciones mientras en la tabla original se insertan registros, pero llegará un momento que se deberán vaciar las tablas, bien porque sólo se mantiene un determinado periodo de retención para estas tablas, o porque los datos ya se han historificado desde la MQT.
Para esto se pueden vaciar las tablas origen y destino con un LOAD en vacío normal:
LOAD FROM /dev/null OF DEL REPLACE INTO T1 NONRECOVERABLE;
LOAD FROM /dev/null OF DEL REPLACE INTO T1_HIS NONRECOVERABLE;
Para vaciar la tabla de stating se hará con:
SET INTEGRITY FOR T1_STG PRUNE;
Después de esto las tablas quedarán en CHECK PENDING STATE. Para volver a su estado normal:
SET INTEGRITY ON T1 IMMEDIATE CHECKED;
SET INTEGRITY ON T1_HIS MATERIALIZED QUERY IMMEDIATE UNCHECKED;
SET INTEGRITY ON T1_STG STAGING IMMEDIATE UNCHECKED;
De esta forma las tablas volverán a estar operativas y vacías. Para sincronizar cualquier posible cambio que haya entrado sincronizamos la tabla completa.
REFRESH TABLE T1_HIS NOT INCREMENTAL;
En el próximo artículo se verá como afecta el uso de tablas staging a la inserción en la tabla origen, ya que cada operación realizada efectuará una inserción en la tabla de staging.
miércoles 3 de noviembre de 2010
db2exfmt - análisis de planes de acceso - lecturas
La salida del comando db2exfmt da una información muy detallada de los planes de acceso que se han capturado, como vimos en este post.
Vamos a ver cada una de las secciones:
En esta primera parte se pueden ver algunos datos de la configuración de la base de datos en la que se ha capturado el plan. Algunos son parámetros de instancia, como CPU y Comm Speed, y otros de base de datos, como Bufferpool size, Sort Heap Size, etc.
Además nos indica el tipo de ejecución (dinámica en este caso), y el nivel de optimización (5) y nivel de aislamiento (CS).
En esta sección nos muestra la query original y la query optimizado que ha calculado el optimizador.
Ahora ya llegamos a lo interesante, el plan de acceso:
En este gráfico se pueden ver las operaciones que se realizan, con su coste, su consumo de I/O y su cardinalidad estimada. En este caso se puede ver que se realiza un TBSCAN (leer la tabla entera), y tiene un coste de 71.2888 timerones, realizará 59 operaciones de I/O, y devolverá 418 registros.
Más adelante se muestra la información detallada de cada uno de los nodos del gráfico según su número (el que va entre paréntesis). Se mostrarían los datos de cada una de los nodos, el coste de CPU, de I/O, etc, así como detalles de los nodos entrantes y salientes.
A continuación veríamos los datos de las tablas, columnas, estadísticas, distribuciones…
Vamos a ver algunas de las operaciones más importantes, y cuales son los datos más relevantes mostrados, para no perdernos entre tanta información.
TBSCAN: Consiste en leer la tabla completa, desde el principio hasta el final. Para este tipo de lectura se usa lectura asíncrona, es decir, los encargados de la lectura son los prefetchers. Hay que evitarlo siempre que sea posible, incluso en tablas pequeñas.
Para una tabla que ocupe tres páginas una lectura por índice supone leer una o dos páginas. Si esta query se ejecuta miles de veces, con un índice se ahorra la lectura de miles de páginas. Esto es interesante incluso cuando las páginas están en memoria.
El punto clave es el de predicados. Si una tabla se filtra por un predicado de tipo Sargable y tiene una selectividad suficiente, un índice podría mejorar la lectura. En este caso nos dice que cumplen esta condición el 33% de las filas, lo que supondría leer un 66% menos. Al hacer la comprobación sobre datos reales podemos ver que esto es falso, ya que no hay ninguna fila que cumpla esta condición (CARD>1000). Esto se debe a que no se han pasado estadísticas a las tablas.
Después de pasar estadísticas a TODAS las tablas de la base de datos, vemos que la salida ha cambiado:
Ahora nos dice que cumplen la condición el 2.4% de las filas. Comprobando el la tabla vemos que la información es correcta. Por lo tanto, este campo sería un buen candidato para un índice en esta consulta, ya que pasaríamos de leer toda la tabla a leer unas pocas filas, con un ahorro del 97.6 % de lecturas.
En este caso, es importante el operador. Al ser “mayor que” y tener una selectividad muy baja, se debe crear el índice descendente, o habilitar el REVERSE SCAN, para que empiece a buscar por el final. De esta forma solo leería las páginas imprescindibles del índice.
IXSCAN: Se produce cuando la tabla se lee de un índice. Es la situación ideal, pero hay que tener cuidado. Que una tabla se lea a través de un índice no significa que el acceso sea óptimo. Puede ser que se esté llevando a cabo usando campos muy poco selectivos, obligando a leer más filas de las necesarias, cuando podríamos tener la opción de usar otros campos más selectivos. También puede darse el caso de que el índice contenga campos que no son necesarios. En ese caso, dependiendo del orden en el que estén dentro del índice, podría provocar la lectura del índice completo.
En un IXSCAN hay tres opciones (al menos):
- Que las columnas necesarias estén contenidas en el índice, y solamente se use el índice.
- Que las columnas necesarias no estén en el índice, por lo que se leerán de la tabla.
- Que el optimizador decida que es más rápido hacer una lectura asíncrona, por lo que extraerá una lista de row ids ($RID$) del índice, que se leerán de forma asíncrona. Se reconoce porque, en el gráfico, por encima del IXSCAN hay un RIDSCAN sobre la tabla.
Evidentemente la mejor es la primera, pero a veces no es posible. La tercera se debe evitar, pero también hay veces que no se puede, como cuando el número de registros a recuperar es un porcentaje importante del total de registros de la tabla, pero no los suficientes como para que un TBSCAN tenga un coste menor.
En definitiva, los campos clave son: si los campos necesarios están contenidos en el índice, y si los campos usados tienen selectividad suficiente.
En este caso vemos que la selectividad es mala. El 90% de los registros de la tabla cumplen la condición. Si el tipo de lectura el del segundo o tercer tipo (hace FETCH en la tabla), que debe revisar los predicados del FETCH:
En este caso la selectividad es buena, por lo que añadir este campo al índice mejoraría los accesos sustancialmente.
Vamos a ver cada una de las secciones:
Connecting to the Database.
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.1
SOURCE_NAME: SQLC2H20
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2010-11-03-09.00.51.845392
EXPLAIN_REQUESTER: BBDD
Database Context:
----------------
Parallelism: None
CPU Speed: 2.361721e-07
Comm Speed: 0
Buffer Pool size: 1256
Sort Heap size: 1024
Database Heap size: 2274
Lock List size: 1024
Maximum Lock List: 60
Average Applications: 1
Locks Available: 19660
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
En esta primera parte se pueden ver algunos datos de la configuración de la base de datos en la que se ha capturado el plan. Algunos son parámetros de instancia, como CPU y Comm Speed, y otros de base de datos, como Bufferpool size, Sort Heap Size, etc.
Además nos indica el tipo de ejecución (dinámica en este caso), y el nivel de optimización (5) y nivel de aislamiento (CS).
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 16
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
Executing Connect Reset -- Connect Reset was Successful.
select tabname
from syscat.tables
where tabname='SYSTABLES'
Optimized Statement:
-------------------
SELECT Q1.NAME AS "TABNAME"
FROM SYSIBM.SYSTABLES AS Q1
WHERE (Q1.NAME = 'SYSTABLES')
En esta sección nos muestra la query original y la query optimizado que ha calculado el optimizador.
Ahora ya llegamos a lo interesante, el plan de acceso:
Access Plan:
-----------
Total Cost: 71.2888
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
418
TBSCAN
( 2)
71.2888
59
|
418
TABLE: SYSIBM
SYSTABLES
Q1
En este gráfico se pueden ver las operaciones que se realizan, con su coste, su consumo de I/O y su cardinalidad estimada. En este caso se puede ver que se realiza un TBSCAN (leer la tabla entera), y tiene un coste de 71.2888 timerones, realizará 59 operaciones de I/O, y devolverá 418 registros.
Más adelante se muestra la información detallada de cada uno de los nodos del gráfico según su número (el que va entre paréntesis). Se mostrarían los datos de cada una de los nodos, el coste de CPU, de I/O, etc, así como detalles de los nodos entrantes y salientes.
A continuación veríamos los datos de las tablas, columnas, estadísticas, distribuciones…
Vamos a ver algunas de las operaciones más importantes, y cuales son los datos más relevantes mostrados, para no perdernos entre tanta información.
TBSCAN: Consiste en leer la tabla completa, desde el principio hasta el final. Para este tipo de lectura se usa lectura asíncrona, es decir, los encargados de la lectura son los prefetchers. Hay que evitarlo siempre que sea posible, incluso en tablas pequeñas.
Para una tabla que ocupe tres páginas una lectura por índice supone leer una o dos páginas. Si esta query se ejecuta miles de veces, con un índice se ahorra la lectura de miles de páginas. Esto es interesante incluso cuando las páginas están en memoria.
El punto clave es el de predicados. Si una tabla se filtra por un predicado de tipo Sargable y tiene una selectividad suficiente, un índice podría mejorar la lectura. En este caso nos dice que cumplen esta condición el 33% de las filas, lo que supondría leer un 66% menos. Al hacer la comprobación sobre datos reales podemos ver que esto es falso, ya que no hay ninguna fila que cumpla esta condición (CARD>1000). Esto se debe a que no se han pasado estadísticas a las tablas.
Predicates:
----------
2) Sargable Predicate,
Comparison Operator: Less Than (<)
Subquery Input Required: No
Filter Factor: 0.333333
Predicate Text:
--------------
(1000 < Q1.CARD)
Después de pasar estadísticas a TODAS las tablas de la base de datos, vemos que la salida ha cambiado:
Predicates:
----------
2) Sargable Predicate,
Comparison Operator: Less Than (<)
Subquery Input Required: No
Filter Factor: 0.0239698
Predicate Text:
--------------
(1000 < Q1.CARD)
Ahora nos dice que cumplen la condición el 2.4% de las filas. Comprobando el la tabla vemos que la información es correcta. Por lo tanto, este campo sería un buen candidato para un índice en esta consulta, ya que pasaríamos de leer toda la tabla a leer unas pocas filas, con un ahorro del 97.6 % de lecturas.
En este caso, es importante el operador. Al ser “mayor que” y tener una selectividad muy baja, se debe crear el índice descendente, o habilitar el REVERSE SCAN, para que empiece a buscar por el final. De esta forma solo leería las páginas imprescindibles del índice.
IXSCAN: Se produce cuando la tabla se lee de un índice. Es la situación ideal, pero hay que tener cuidado. Que una tabla se lea a través de un índice no significa que el acceso sea óptimo. Puede ser que se esté llevando a cabo usando campos muy poco selectivos, obligando a leer más filas de las necesarias, cuando podríamos tener la opción de usar otros campos más selectivos. También puede darse el caso de que el índice contenga campos que no son necesarios. En ese caso, dependiendo del orden en el que estén dentro del índice, podría provocar la lectura del índice completo.
En un IXSCAN hay tres opciones (al menos):
- Que las columnas necesarias estén contenidas en el índice, y solamente se use el índice.
Rows
RETURN
( 1)
Cost
I/O
|
1.09116
IXSCAN
( 2)
45.5974
6
- Que las columnas necesarias no estén en el índice, por lo que se leerán de la tabla.
FETCH
( 2)
67.9507
8.95575
/---+----\
3 418
IXSCAN TABLE: SYSIBM
( 3) SYSTABLES
45.5974 Q1
6
- Que el optimizador decida que es más rápido hacer una lectura asíncrona, por lo que extraerá una lista de row ids ($RID$) del índice, que se leerán de forma asíncrona. Se reconoce porque, en el gráfico, por encima del IXSCAN hay un RIDSCAN sobre la tabla.
Evidentemente la mejor es la primera, pero a veces no es posible. La tercera se debe evitar, pero también hay veces que no se puede, como cuando el número de registros a recuperar es un porcentaje importante del total de registros de la tabla, pero no los suficientes como para que un TBSCAN tenga un coste menor.
En definitiva, los campos clave son: si los campos necesarios están contenidos en el índice, y si los campos usados tienen selectividad suficiente.
Predicates:
----------
2) Sargable Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.90261043
Predicate Text:
--------------
(Q1.NAME = 'CONSTANTE')
En este caso vemos que la selectividad es mala. El 90% de los registros de la tabla cumplen la condición. Si el tipo de lectura el del segundo o tercer tipo (hace FETCH en la tabla), que debe revisar los predicados del FETCH:
Predicates:
----------
2) Sargable Predicate,
Comparison Operator: Less Than (<)
Subquery Input Required: No
Filter Factor: 0.0239698
Predicate Text:
--------------
(1000 < Q1.NUM)
En este caso la selectividad es buena, por lo que añadir este campo al índice mejoraría los accesos sustancialmente.
Suscribirse a:
Entradas (Atom)