Lo he entendido y quiero demostrarlo!

Si has completado los posts de este capitulo puedes ponerte a prueba completando un Quiz de 10 preguntas. Puedes acceder al Quiz desde mi pagina personal en el sitio de la Comunidad Oracle Hispana.

Estructura de un indice bitmap

Composicion II - Piet Mondrian

Composicion II - Piet Mondrian

El proposito de todo indice es proveer punteros a las filas de una tabla que tienen un valor determinado. En un indice B-tree, este objetivo se logra almacenando una lista de rowids de las filas de la tabla con el valor clave. Oracle almacena cada valor clave en forma repetida para cada fila. En un indice bitmap, en vez de una lista de rowids, Oracle crea un mapa de bits para cada valor clave del indice.

Cada bit del mapa corresponde a un rowid posible. Si el bit esta en 1, significa que el rowid contiene dicho valor clave. Una funcion interna de Oracle convierte la posicion del bit en el rowid conrrespondiente, de modo tal que los indices bitmap ofrecen la misma funcionalidad que los indices B-tree, a pesar de la diferente representacion interna. Si la cantidad de valores diferentes del indice es chica, entonces el indice bitmap sera muy eficiente en cuanto al uso de espacio fisico.

Supongamos que tenemos la siguiente tabla de clientes:

CLIENTE        APELLIDO       REGION
101            PEREZ          NORTE
102            GARCIA         CENTRO
103            LOPEZ          SUR
104            SAN MARTIN     SUR
105            BROWN          CENTRO
106            CANEPA         CENTRO

La columna region tiene baja cardinalidad, ya que los valores posibles son muy pocos (NORTE, CENTRO, SUR). Hay solamente tres valores posibles para la region por lo tanto un indice bitmap seria apropiado para esta columna. Sin embargo, no es recomendable un indice bitmap para la columna CLIENTE o APELLIDO, dada su alta cardinalidad. Para estos casos un indice B-tree proveera una representacion y acceso mas eficiente.

La siguiente seria la representacion del indice bitmap para la columna REGION. El indice tiene tres mapas de bits, uno para cada region.

NORTE    CENTRO    SUR         
1        0         0
0        1         0
0        0         1
0        0         1
0        1         0
0        1         0

Cada entrada o bit en el indice bitmap se corresponde  a una sola fila en la tabla de clientes. El valor del bit dependera del valor correspondiente de la fila en la tabla. Por ejemplo, para la region NORTE el mapa de bits tiene un 1 en la primer posicion. Eso es porque la primer fila de la tabla de clientes tiene el valor NORTE en la columna REGION. Luego, el mapa de bits tiene todos ceros, indicando que el resto de las filas de la tabla no tiene clientes en la region NORTE.

Una sentencia SQL sobre esta tabla y con el indice bitmap, ser resolveria de la siguiente manera.

select count(*) from CLIENTES
    where REGION in ('NORTE','SUR');

Un indice bitmap puede resolver esta sentencia con gran eficiencia contando la cantidad de unos existentes en el mapa de bits resultante como se muestra en la siguiente figura:

NORTE   CENTRO   SUR   (NORTE O SUR)
1       0        0      1
0       1        0      0
0       0        1      1
0       0        1      1
0       1        0      0
0       1        0      0

La columna “NORTE O SUR” es el mapa de bits resultante utilizado para acceder a la tabla.

Adicionalmente y a diferencia de los indices B-tree, los indices bitmap pueden incluir filas con valore NULL dentro de la estructura del indice.  En cuanto a las tablas particionadas, los indices bitmap se pueden utilizar solo si son locales a la particion. Los indices bitmap globales no son soportados para tablas particionadas.

Parmetros de inicializacion de SQL_TRACE

El Cristo amarillo - Paul Gauguin

El Cristo amarillo - Paul Gauguin

Cuando se habilita SQL Trace para una sesion, Oracle genera un archivo de trace que contiene estadisticas de las sentencias SQL de dicha sesion. Cuando se habilita SQL Trace para la instancia, Oracle crea un archivo por cada proceso. Como dijimos en el post Como utilizar SQL Trace, el primer paso es verificar algunos parametros de inicializacion:

TIMED_STATISTICS

Este parametro habilita o deshabilita la colecta de estadisticas referidas a tiempos (de CPU y de ejecucion). Dado que la habilitacion de la colecta de tiempos implica tareas extra para el motor de base de datos, por defecto esta deshabilitado. De todos modos, por tratarse de un parametro dinamico, no es necesario bajar y subir la base para activarlo. Incluso puede habilitarse a nivel de sesion.

Para habilitarlo a nivel de sesion:

ALTER SESSION SET TIMED_STATISTICS=TRUE;

Para habilitarlo a nivel de sistema (tarea del DBA):

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

USER_DUMP_DEST

Permite indicar el destino del archivo generado por SQL Trace. Este parametro tambien es dinamico y puede modificarse a nivel de sesion. El archivo de trace se genera en el servidor de base de datos. Si el usuario no tiene acceso al servidor debera solicitar el archivo al DBA.

Para habilitarlo a nivel de sesion:

ALTER SESSION SET USER_DUMP_DEST=/u01/oradata/dumps;

Para habilitarlo a nivel de sistema (tarea del DBA):

ALTER SYSTEM SET USER_DUMP_DEST=/u01/oradata/dumps;

 MAX_DUMP_FILE_SIZE

Cuando se activa SQL Trace a nivel de instancia, cada invocacion al servidor de base de datos hara crecer alguno de los archivos de texto generados por SQL Trace. Para evitar que el rapido crecimiento de los archivos termine ocupando todo un file system o disco se puede limitar el tamaño maximo de cada archivo especificandolo en el parametro MAX_DUMP_FILE_SIZE. El valor por defecto es 500 bloques de sistema operativo. Cuando se llega al limite el archivo no sigue creciendo; aunque la sesion de usuario seguira funcionando sin inconvenientes. Este parametro tambien es dinamico y se puede modificar a nivel de sesion.

Para habilitarlo a nivel de sesion:

ALTER SESSION SET MAX_DUMP_FILE_SIZE=1000;

Para habilitarlo a nivel de sistema (tarea del DBA):

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=1000;

Uso de AUTOTRACE

Cafe nocturno - Vincent Van Gogh

Cafe nocturno - Vincent Van Gogh

Como dijimos en el post Como ver el plan de ejecucion Oracle provee diversas herramientas para visualizar el plan de ejecucion de una sentencia SQL.

Para los usuarios de SQL*Plus Oracle ofrece AUTOTRACE.

AUTOTRACE es una variable de entorno de SQL*Plus que nos permite obtener un reporte del plan de ejecucion y algunos datos estadisticos de la sentencia ejecutada.

A continuacion vemos un ejemplo del uso de AUTOTRACE.

SQL> set autotrace on

SQL> select emp_apellido from empleados;
EMP_APELLIDO
------------------------------
PEREZ
GARCIA
GOMEZ

Execution Plan
----------------------------------------------------------

Plan hash value: 3220727562
--------------------------------------------------------------------
|Id | Operation         | Name      |Rows|Bytes|Cost(%CPU)| Time   |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT  |           |  3 |  51 |    3  (0)|00:00:01|
| 1 |  TABLE ACCESS FULL| EMPLEADOS |  3 |  51 |    3  (0)|00:00:01|
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

Hay varias configuraciones de AUTOTRACE que permiten controlar la salida del reporte:

  • SET AUTOTRACE OFF Desactiva la generacion del reporte. Es el valor por defecto.
  • SET AUTOTRACE ON EXPLAIN El reporte muestra solamente el plan de ejecucion.
  • SET AUTOTRACE ON STATISTICS El reporte muestra solo las estadisticas de la sentencia SQL ejecutada
  • SET AUTOTRACE ON El reporte muestra las estadisticas y el plan de ejecucion de la sentencia SQL ejecutada
  • SET AUTOTRACE TRACEONLY Es similar a SET AUTOTRACE ON pero se suprime la impresion de la salida del query. Si STATISTICS esta activado, la sentencia se ejecuta pero no se muestra el conjunto resultado.

Como ver el plan de ejecucion

Moulin Rouge, la Goulue - Henri de Toulouse-Lautrec

Moulin Rouge, la Goulue – Henri de Toulouse-Lautrec

Ahora puedes ver nuestros vídeos cortos acerca del lenguaje SQL

Como vimos en el articulo “El comando EXPLAIN PLAN“, dicho comando inserta el plan de ejecucion de una sentencia SQL en la tabla PLAN_TABLE. Oracle provee ademas un conjunto de herramientas para visualizar el plan de ejecucion en un formato que resulte practico a quien deba interpretarlo.

Una vez ejecutado el comando EXPLAIN PLAN se puede utilizar alguno de los siguientes scripts o package para visualizar el plan de ejecucion:

  • UTLXPLS.SQL este script reside en el servidor de base de datos y se puede utilizar para visualizar la PLAN_TABLE cuando se ejecutan sentencias en forma serializada.
  • UTLXPLP.SQL reside en el servidor de base de datos y se puede utilizar para ver el plan de ejecucion cuando se ejecutan sentencias que se procesan en paralelo.
  • DBMS_XPLAN.DISPLAY esta funcion del package DBMS_XPLAN acepta diversas opciones de visualizacion de la PLAN_TABLE.

Ejemplo de visualizacion del plan de ejecucion utilizando UTLXPLS.SQL

SQL> explain plan for select EMP_APELLIDO from EMPLEADOS;
Explained.
SQL>  @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3220727562
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLEADOS |     1 |    17 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
12 rows selected.

Ejemplo de visualizacion del plan de ejecucion utilizando UTLXPLP.SQL

SQL> @$ORACLE_HOME/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3220727562
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLEADOS |     1 |    17 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
12 rows selected.

Ejemplo de visualizacion del plan de ejecucion utilizando DBMS_XPLAN.SQL

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3220727562
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLEADOS |     1 |    17 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
12 rows selected.

Como leer el plan de ejecucion

Para comprender como se debe leer un plan de ejecucion comencemos con un ejemplo muy simple. Veamos a continuacion un plan de ejecucion sencillo:

Query Plan
--------------------------------------------------
SELECT STATEMENT     [CHOOSE] Cost=1234
  TABLE ACCESS FULL EMPLEADOS [:Q65001] [ANALYZED]

Cuando analizamos un plan de ejecucion, el paso que esta indentado mas a la derecha es la primer operacion que se realiza para la ejecucion de la sentencia (mas adelante veremos como se hace para determinar el orden de los pasos cuandoleemos un plan de ejecucion). Por lo tanto, en nuestro ejemplo, la primer operacion es TABLE ACCESS FULL EMPLEADOS. Este paso indica que estamos haciendo un barrido o recorrido completo de la tabla EMPLEADOS. Una vez finalizado dicho recorrido, el conjunto de filas resultante sera pasado al nivel superior para que siga procesando el query. Es decir que los datos seran pasados a la operacion SELECT que es la ultima etapa de la sentencia de nuestro ejemplo.

[CHOOSE] indica cual es el valor de optimizer_goal para el query. Esto no necesariamente indica que el plan haya utilizado dicho objetivo. La unica forma de confirmarlo es mirando la variable  COST= que tambien es parte del plan de ejecucion. En el ejemplo el costo tiene valores, indicando que se utilizo CBO (optimizador basado en costos).

SELECT STATEMENT     [CHOOSE] Cost=1234

Si el plan de ejecucion hubiera dicho

SELECT STATEMENT     [CHOOSE] Cost=

entonces el optimizador utilizado seria RBO (optimizador basado en reglas).

Es posts posteriores veremos en detalle que es el optimizador y que significa CBO o RBO. El objetivo de este articulo es aprender a leer un plan de ejecucion, no a interpretarlo. Aprender a interpretarlo es un poco mas complejo pues requiere de muchos otros conceptos previos. Obviamente, todos estos conceptos seran tratados oportunamente en SqlEficiente. El numero que acompaña al costo es un valor que utiliza Oracle internamente a fines comparativos para determinar cual es el plan que tiene el mejor costo. Los valores de los costos no sirven para comparar sentencias diferentes.

[:Q65001] indica que esta operacion particular del query se ejecuta en paralelo. Es decir que sera ejecutada por procesos esclavos en paralelo en vez de ser ejecutada por un solo proceso en forma serializada.

[ANALYZED] indica que el objeto en cuestion (en nuestro caso, la tabla EMPLEADOS) ha sido analizado y hay estadisticas disponibles sobre dicho objeto para que las puede utilizar el optimizador basado en costos.

Para determinar el orden en que se ejecutan cada unos de los pasos del plan de ejecucion de la sentencia es necesario entender las siguientes relaciones PADRE-HIJO:

PADRE
  PRIMER HIJO
  SEGUNDO HIJO

En este ejemplo, la sentencia PRIMER HIJO se ejecuta primero, luego se ejecuta la sentecia SEGUNDO HIJO y por ultimo PADRE procesa la salida de ambos hijos.

Veamos a continuacion un caso mas complejo:

PADRE
  PRIMER HIJO
    PRIMER NIETO
  SEGUNDO HIJO

Aplicando los mismo principios, PRIMER NIETO es la primer operacion, en segundo lugar se ejecuta la operacion PRIMER HIJO seguida por SEGUNDO HIJO. Por ultimo, la operacion PADRE.

Estos mismos principios pueden utilizarse para aplicarlos a las operaciones reales de un plan de ejecucion. Veamos algunos ejemplos concretos.

Ejemplo 1

set autotrace traceonly explain
select ename,dname
  from empleados, deptos
 where empleados.deptno=deptos.deptno
   and dept.dname in ('TESORERIA','COMPRAS','VENTAS','OPERACIONES');
15 rows selected.

Este query produce el siguiente plan de ejecucion:

Execution Plan
---------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
   1    0   HASH JOIN (Cost=3 Card=8 Bytes=248)
   2    1     TABLE ACCESS (FULL) OF 'DEPTOS' (Cost=1 Card=3 Bytes=36)
   3    1     TABLE ACCESS (FULL) OF 'EMPLEADOS' (Cost=1 Card=16 Bytes=304)

En el plan de ejecucion vemos dos columnas de numeros que preceden al texto de cada fila. El primer numero es el identificador de sentencia (tambien llamado ID). El segundo numero es el identificador del padre (tambien llamado parent ID) de la operacion en cuestion. La primer operacion no tiene identificador de  padre ya que es la primera de todas. El ID y el PARENT ID son utilizados por el generador de planes del optimizador para construir el plan de ejecucion. Los pasos del plan de ejecucion son indentados para indicar la jerarquia y dependencia de las operaciones.

En nuestro ejemplo, el plan de ejecucion comienza con ID=0

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)

ID=0 no tiene padre, pero tiene un hijo.
ID=0 es padre de ID=1. Ademas ID=0 depende de las filas que devuelva ID=1
Por lo tanto ID=1 debe ejecutarse si o si antes que ID=0

Pasemos a ID=1:

   1    0   HASH JOIN (Cost=3 Card=8 Bytes=248)

ID=1 es hijo de ID=0
ID=1 is padre de ID=2 y de ID=3 y depende de las filas que le devuelvan ambos hijos.
Por lo tanto ID=2 y ID=3 deben ejecutarse si o si antes que ID=1

Pasemos a ID=2:

   2    1     TABLE ACCESS (FULL) OF 'DEPTOS' (Cost=1 Card=3 Bytes=36)

ID=2 es el primer hijo de ID=1
ID=2 no tiene hijos.
Este es el primer paso en la ejecucion de este query.
Las filas retornadas por esta operacion seran provistas a ID=1.

ID=1 tambien depende de ID=3:

   3    1     TABLE ACCESS (FULL) OF 'EMPLEADOS' (Cost=1 Card=16 Bytes=304)

ID=3 es el segundo hijo de ID=1
ID=3 no tiene hijos.
Esta es la seguna operacion que se ejecuta en este query.
Las filas retornadas seran provistas a ID=1.

ID=1 procesa las filas quer recibe de las operaciones de las cuales depende (ID=2 y ID=3) y retorna las filas a su padre
ID=0.
ID=0 retorna las filas al usuario.

En resumen:

La ejecucion comienza con ID=0: SELECT STATEMENT que depende de sus hijos para resolver el query por lo tanto ejecuta a su primer hijo ID=1 PID=0 HASH JOIN que a su vez depende de otros objetos hijo y por lo tanto ejecuta a su primer hijo ID=2 PID=1   TABLE ACCESS (FULL) OF ‘DEPTOS’  y luego a su segundo hijo ID=3 PID=2   TABLE ACCESS (FULL) OF ‘EMPLEADOS’
Las filas van siendo retonadas a los padres hasta llegar al final.

Creacion de la PLAN_TABLE

Tres musicos - Pablo Picasso

Tres musicos - Pablo Picasso

La PLAN_TABLE es creada automaticamente para todos los usuarios a fin de almacenar el output de la sentencia EXPLAIN PLAN. La PLAN_TABLE es la tabla de salida por defecto en la cual la sentencia EXPLAIN PLAN inserta las filas que describen el plan de ejecucion seleccionado por el generador de planes del optimizador de una base de datos Oracle.

Si bien la tabla PLAN_TABLE es creada automaticamente para cada usuario, es posible utilizar el script utlxplan.sql para crear manualmente una tabla local dentro de un esquema determinado. El nombre y ubicacion exacta del script utlxplan.sql depende de cada sistema operativo. En Unix, esta ubicado en $ORACLE_HOME/rdbms/admin. En general la ejecucion de utlxplan.sql es llevada adelante por el DBA ya que dicho script esta ubicado en un directorio local al servidor de la base de datos.

Por ejemplo, para ejecutar el script desde SQL*Plus:

CONNECT usuario/password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Si por algun motivo se desea crear la tabla con otro nombre, es posible ejecutar utlxplan.sql y luego renombrar la tabla con la sentencia RENAME:

RENAME PLAN_TABLE TO mi_plan_table;