Costo super alto!

En Febrero del 2008, publiqué un post llamado "¿Tunear en base al COSTO del plan de ejecución?". En ese post, les decía que NO hay que tunear en base al costo del plan de ejecución, sino al trabajo que realiza la consulta en la base de datos.

Voy a mostrarles una consulta en el que el costo es super alto pero el trabajo que realiza en la base de datos no lo es:

SQL_10gR2> explain plan for
2  SELECT (t5.column_value).getstringval() t5
3  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
4       TABLE(xmlsequence(t1.column_value))t2,
5       TABLE(xmlsequence(t2.column_value))t3,
6       TABLE(xmlsequence(t3.column_value))t4,
7       TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |    18E|    15E|    98P  (2)|999:59:59 |
|   1 |  NESTED LOOPS                         |                        |    18E|    15E|    98P  (2)|999:59:59 |
|   2 |   NESTED LOOPS                        |                        |  4451T|    31P|    12T  (2)|999:59:59 |
|   3 |    NESTED LOOPS                       |                        |   544G|  3045G|  1481M  (2)|999:59:59 |
|   4 |     NESTED LOOPS                      |                        |    66M|   254M|   181K  (2)| 00:36:18 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   7 |     COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   8 |    COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   9 |   COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Wow!!! Y esos números??? Extremadamente altos no??? Bueno, según el plan de ejecución, esa consulta retorna 18 cuatrillones (18E) de registros, 15 exabytes (15E), tiene un costo de 1.8E19 (98P) y el tiempo de ejecución es de aproximadamente 1 mes (999:59:59)!!!

Veamos qué sucede cuando ejecutamos la consulta y obtenemos las estadísticas de la misma:

SQL_10gR2> SELECT (t5.column_value).getstringval() t5
2  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
3       TABLE(xmlsequence(t1.column_value))t2,
4       TABLE(xmlsequence(t2.column_value))t3,
5       TABLE(xmlsequence(t3.column_value))t4,
6       TABLE(xmlsequence(t4.column_value))t5;

T5
----------------------------------------------------------------
< x/ >

1 fila seleccionada.

Transcurrido: 00:00:00.04

Estadísticas
----------------------------------------------------------
    0  recursive calls
    0  db block gets
     3  consistent gets
    0  physical reads
    0  redo size
  425  bytes sent via SQL*Net to client
  381  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
    1  rows processed

Como podemos observar, la consulta retorna un sólo registro y se ejecuta en tan solo 4 milisegundos, consumiendo sólo 3 bloques de datos.

Este ejemplo es otra prueba de cómo el optimizador puede calcular valores totalmente erróneos en el plan de ejecución. Es por eso, que hay que tener especial cuidado al ver esos números cuando obtenemos un plan de ejecución y al tratar de optimizar la consulta en base a esos números.

Para ésta consulta en particular, el problema es que el optimizador desconoce las estadísticas de las tablas (que en éste caso no son tablas, son llamadas a funciones). Fácilmente, podemos "mejorar" el plan de ejecución, agregando el hint CARDINALITY para decirle al optimizador cuántos registros va a retornar esa función... que en éste caso, es un sólo registro:

SQL_10gR2> explain plan for
2  SELECT /*+ cardinality(t1 1) cardinality(t2 1) cardinality(t3 1) cardinality(t4 1) cardinality(t5 1) */
3        (t5.column_value).getstringval() t5
4  FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
5       TABLE(xmlsequence(t1.column_value))t2,
6       TABLE(xmlsequence(t2.column_value))t3,
7       TABLE(xmlsequence(t3.column_value))t4,
8       TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |     1 |    10 |   122   (2)| 00:00:02 |
|   1 |  NESTED LOOPS                         |                        |     1 |    10 |   122   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                        |                        |     1 |     8 |    97   (2)| 00:00:02 |
|   3 |    NESTED LOOPS                       |                        |     1 |     6 |    73   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                        |     1 |     4 |    49   (3)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   7 |     COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   8 |    COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
|   9 |   COLLECTION ITERATOR PICKLER FETCH   | XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Recuerden siempre el acrónimo GIGO: Garbage In ... Garbage Out :)

DML Error Logging

Alguna vez trataron de actualizar 10 millones de registros? Que sucedía cuando uno de los registros fallaba? Oracle realizaba un rollback automático de los cambios... y qué sucede si el registro que falló era uno de los últimos en actualizarse? Y bueno... seguramente desperdiciamos todos el tiempo de ejecución de ese proceso ya que los cambios se perdieron tan solo porque un registro falló! A muchos les habrá pasado lo mismo al ejecutar una sentencia del tipo INSERT AS SELECT cierto? En donde uno de los registros no pudo insertarse por X motivo y por consecuencia toda la sentencia falló!

Generalmente, sabemos que la manera más rápida de realizar un DML es en una sola sentencia. Cuando tenemos una sentencia en donde pueden haber registros que terminen en error, se solía armar un procedimiento que recorra los datos que se quieren insertar, updatear, eliminar, etc. e ir ejecutando la senetencia de a un/o varios registros a la vez, y los registros que fallaban, se insertaban en una tabla de errores. Este procedimiento suele ser muy lento, ya que no estamos realizando un DML en una sola sentencia y en una sola vez para todos los registros; sino que estamos recorriendo los datos proceduralmente y realizando el DML de a X cantidad de registros a la vez con el fin de loguear los registros que terminaron en error e ir comiteando, a medida que se va ejecutando la sentencia DML, los registros que terminan satisfactoriamente.

En Oracle 10g Release 2, existe una nueva funcionalidad llamada "DML Error Logging" que nos permite ejecutar una sentencia DML de "principio a fin"... y si en el transcurso de ejecución de esa sentencia, uno o más registros fallaran, esos registros se loguean en una tabla de errores para que luego podramos corregirlos, sin necesidad, de volver a insertar todos los registros nuevamente ya que sólo tendremos que volver a insertar los que terminaron en error.
Por ejemplo: Si intentamos insertar 1 millón de registros, y sólo un registro falla, se insertarán en la tabla final 999,999 de registros, y el registro que terminó en error, se loguea en otra tabla para que luego podamos corregirlo y volverlo a insertar. Acaso no es sensacional ésto???

Veamos un ejemplo muy simple:

Creamos una tabla llamada TEST, en donde vamos a insertar 1 millón de registros.

SQL_10gR2> CREATE TABLE test
2  (
3  ID     NUMBER,
4  NOMBRE VARCHAR2(7)
5  );

Tabla creada.

Creamos una tabla llamada ERROR_LOG_TEST que apunta a la tabla TEST. Esta tabla va a contener todos los registros que terminen en error cuando queramos realizar un DML en la tabla TEST.

SQL_10gR2> EXEC dbms_errlog.create_error_log('TEST','ERROR_LOG_TEST') ;

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> DESC error_log_test

Nombre                                                Nulo?    Tipo
----------------------------------------------------- -------- ------------------------------------
ORA_ERR_NUMBER$                                                NUMBER
ORA_ERR_MESG$                                                  VARCHAR2(2000)
ORA_ERR_ROWID$                                                 ROWID
ORA_ERR_OPTYP$                                                 VARCHAR2(2)
ORA_ERR_TAG$                                                   VARCHAR2(2000)
ID                                                             VARCHAR2(4000)
NOMBRE                                                         VARCHAR2(4000)

Bien, ahora vamos a insertar los registros en la tabla TEST.

SQL_10gR2> INSERT INTO test
2  SELECT level, 'nom_'||level
3  FROM dual
4  CONNECT BY level <= 1000000   
5  LOG ERRORS INTO ERROR_LOG_TEST REJECT LIMIT UNLIMITED;  

999 filas creadas.

WAW! De 1 millón de registros, sólo 999 registros fueron insertados satisfactoriamente, el resto de los registros se insertaron en la tabla de logueo de errores.

Como notarán, use la cláusula LOG ERRORS INTO ... REJECT LIMIT UNLIMITED. Esta cláusula nos permite decirle a Oracle, que queremos utilizar "DML Error Logging" para nuestra sentencia. Fijense que coloqué UNLIMITED como parámetro de REJECT LIMIT. Esto le dice a Oracle, que no se fije en la cantidad de registros que terminan en error, que simplemente me inserte todos esos registros en la tabla de logueo de errores y que continúe con la ejecución de la sentencia hasta que termine. En vez de UNLIMITED, podría haber puesto 100, 1000, etc... que denota el máximo número de registros que quiero que Oracle loguee. Si hay más registros que terminan en error, Oracle simplemente aborta la ejecución de la consulta y devuelve un error por pantalla.

SQL_10gR2> SELECT count(*) FROM error_log_test;

COUNT(*)
----------
999001

1 fila seleccionada.

Como verán, no perdimos ningún registros. Los registros que se insertaron satisfactoriamente están en la tabla final TEST y el resto en la tabla de logueo de errores. Tener los registros en la tabla de logueo, nos permite corregirlos y tratar de insertarlos nuevamente en la tabla TEST.

Veamos porqué falló la inserción de la mayoría de los registros...

SQL_10gR2> SELECT count(*), ora_err_mesg$ FROM error_log_test GROUP BY ora_err_mesg$;

COUNT(*) ORA_ERR_MESG$
---------- ----------------------------------------------------------------------------------------------------
90000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 9, máximo: 7)
 9000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 8, máximo: 7)
    1 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 11, máximo: 7)
900000 ORA-12899: el valor es demasiado grande para la columna "TEST"."TEST"."NOMBRE" (real: 10, máximo: 7)

4 filas seleccionadas.

Como podemos ver, los registros fallaron porque el campo NOMBRE tiene un tamaño de 7 caracteres, y nosotros estamos intentando insertar valores más grandes. Solucionar éste problema es muy sencillo, simplemente, tenemos que agrandar el campo NOMBRE.

SQL_10gR2> ALTER TABLE test MODIFY nombre VARCHAR2(100);

Tabla modificada.

Ahora vamos a tratar de insertar nuevamente los registros en la tabla TEST desde la tabla de logueo de errores.

SQL_10gR2> INSERT INTO test
2  SELECT id, nombre
3  FROM error_log_test;

999001 filas creadas.

SQL_10gR2> SELECT count(*) FROM test;

COUNT(*)
----------
1000000

1 fila seleccionada.

Excelenete!!! Ya tenemos todos los registros en la tabla TEST! Ahora sólo resta truncar la tabla de logueo de errores o simplemente borrarla en caso de que no la vayamos a utilizar nunca más.

SQL_10gR2> TRUNCATE TABLE error_log_test;

Tabla truncada.

CONCLUSIÓN:

Esta nueva funcionalidad está acotada para ser utilizada sólo para algunos casos en particulares, pero espero que en lo posible, todos puedan comenzar a hacer uso de ésta funcionalidad ya que es muy simple de utilizar y muy eficiente a la hora de realizar carga masiva de datos y de logueo de errores de manera simultánea en una sola sentencia DML sin necesidad de recurrir a procedimientos costosos en cuanto a performance, desarrollo, mantenimiento y debuging.

Oracle adquiere Sun Microsystems

En momentos de crisis... pueden pasar cosas increíbles...



unbreakable Linux... y ahora? unbreakable Solaris??? =)

Para más información... AQUI

Cuántos registros hay en cada bloque de mi tabla?

En el día de hoy, me llegó una e-mail de una persona preguntándome lo siguiente: "Me podrías decir cómo hago para saber cuántos registros hay en cada bloque de mi tabla?". Bueno, la verdad es que es muy fácil ver cuántos registros caben en cada bloque y también es muy fácil comprobarlo.

Veamos un ejemplo:

En la base de datos de prueba en la que estoy actualmente, tengo bloques de 8 KB.

SQL_10gR2> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

Vamos a crear una tabla llamada TEST con 1.000 registros.

SQL_10gR2> CREATE TABLE test AS
2  SELECT level id, 'nom_'||level nombre
3  FROM dual
4  CONNECT BY level <= 1000;      

Tabla creada. 

Para ver la cantidad de bloques que necesité para almacenar los 1.000 registros y la cantidad de registros que hay en cada uno de esos bloques, podemos ejecutar la siguiente consulta.

SQL_10gR2> SELECT dbms_rowid.rowid_block_number(rowid) "Número de Bloque", count(*)
2  FROM test
3  GROUP BY dbms_rowid.rowid_block_number(rowid)
4  ORDER BY dbms_rowid.rowid_block_number(rowid) ASC;

Número de Bloque   COUNT(*)
---------------- ----------
           46196        438
           46197        425
           46198        137

3 filas seleccionadas.

Por lo que podemos observar, en el bloque 46196 tengo 438 registros, en el bloque 46197 tengo 425 registros y en el bloque 46198 tengo 137 registros. Pero cómo hacemos para comprobar que realmente es cierto? Cómo hacemos para verificar que el resultado de la consulta es verdadero? Bueno, lo que vamos a hacer, es realizar un Dump de los 3 bloques y ver la información del Trace que se genera automáticamente. Para ejecutar un Dump, primero necesitamos obtener el número del DataFile donde se encuentra almacenada nuestra tabla (segmento). Para ésto, primero vamos a obtener ésta información y luego a realizar el Dump de los bloques. Veamos...

SQL_10gR2> SELECT header_file FROM dba_segments WHERE segment_name = 'TEST';

HEADER_FILE
-----------
          4

1 fila seleccionada.


SQL_10gR2>  alter system dump datafile 4 block min 46196 block max 46198;

Sistema modificado.

SQL_10gR2> select spid
2  from v$session s, v$process p
3  where p.addr = s.paddr
4  and s.audsid = sys_context('userenv','sessionid')
5  /

SPID
------------
4360

1 fila seleccionada.

Ya se generó el Trace en el directorio especificado en el parámetro user_dump_dest. El nombre con el que se generó es test_ora_4360.trc (el número es el SPID... "System Process Identifier" que obtuvimos). Veamos las partes que más nos interesan del archivo de Trace...

data_block_dump,data header at 0x4f06a7c
===============
tsiz: 0x1f80
hsiz: 0x37e
pbl: 0x04f06a7c
bdba: 0x0100b474
76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46196.
nrow=438  <-- número de registros contenidos dentro del bloque 46196. 
frre=-1
fsbo=0x37e  <-- comienzo del espacio libre del bloque 46196.
fseo=0x6a9  <-- fin del espacio libre del bloque 46196.
avsp=0x32b  <-- espacio disponible del bloque 46196.
tosp=0x32b  <-- espacio total del bloque 46196. 
0xe:pti[0]    nrow=438    offs=0  <-- hay 438 registros en el bloque 46196 comenzando desde el registro número 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
hsiz: 0x364
pbl: 0x0a206a7c
bdba: 0x0100b475
 76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46197.
nrow=425  <-- número de registros contenidos dentro del bloque 46197.
frre=-1
fsbo=0x364
fseo=0x69d
avsp=0x339
tosp=0x339
0xe:pti[0] nrow=425 offs=0  <-- hay 425 registros en el bloque 46197 comenzando desde el registro número 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
hsiz: 0x124
pbl: 0x0a206a7c
bdba: 0x0100b476
 76543210
flag=--------
ntab=1  <-- número de tablas en el bloque 46198. 
nrow=137  <-- número de registros contenidos dentro del bloque 46198. 
frre=-1
fsbo=0x124
fseo=0x177a
avsp=0x1656
tosp=0x1656
0xe:pti[0] nrow=137 offs=0  <-- hay 137 registros en el bloque 46198 comenzando desde el registro número 0.

Bien, con éste ejemplo pudimos comprobar y verificar el resultado de nuestra primer consulta.

Encriptando columnas con TDE (Transparent Data Encryption) en tablas con millones de registros

Para los que no conocen TDE, es una nueva funcionalidad de 10g R2 que permite proteger datos sensibles de las columnas de nuestras tablas encriptando los datos al almacenarlos en los respectivos Data Files en el sistema operativo. Para protegernos de personas malintencionadas que quieran desencriptar los datos sin autorización, guarda las claves de encriptamiento en un módulo seguro externo a la base de datos.

Mi intención en éste post no es mostrar el paso a paso de cómo implementar TDE, sino mostrarles cómo encriptar columnas con TDE en tablas con millones de datos de la manera más eficiente y con el menor impacto posible en cuanto a la performance.

En el momento en el que encriptamos columnas en una tabla, sólo podemos acceder a la tabla en modo lectura... NO están permitidas las operaciones DML hasta que el encriptamiento termine. En tablas chicas, con muy pocos datos, ésto no suele perjudicarnos demasiado. Pero qué sucede en tablas con millones de registros? En este caso, el encriptamiento puede durar varias horas!!!

La estrategia que vamos a utilizar para encriptar columnas en tablas con millones de registros, es utilizando el paquete DBMS_REDEFINITION.

Veamos un ejemplo:

Tengo una tabla llamada TEST_TDE con 1 millón de registros. Esta tabla tiene 3 columnas: ID (primary key), NOMBRE, NUM_TARJETA. La columna que vamos a encriptar es NUM_TARJETA ya que tiene los números de tarjetas, y como para mi es información sensible, quiero encriptarla y protegerla con TDE.

SQL_10gR2> SELECT count(*) FROM test_tde;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE

Nombre                                     Nulo?   Tipo
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER
NOMBRE                                             VARCHAR2(44)
NUM_TARJETA                                        NUMBER

SQL_10gR2> SELECT index_name, column_name FROM dba_ind_columns WHERE table_name = 'TEST_TDE';

INDEX_NAME                               COLUMN_NAME
---------------------------------------- ----------------------------------------
TEST_TDE_ID_PK                           ID

1 fila seleccionada.

SQL_10gR2> SELECT constraint_name, constraint_type FROM dba_constraints WHERE table_name = 'TEST_TDE';

CONSTRAINT_NAME                C
------------------------------ -
TEST_TDE_ID_PK                 P

1 fila seleccionada.

Bien, ahora vamos a encriptar la columna NUM_TARJETA de la manera tradicional y sin utilizar ninguna técnica. En la SESIÓN 1, voy a encriptar la columna, en la SESIÓN 2, voy a modificar el valor de uno de los registros de la tabla mientras se está realizando el procedimiento de encriptado... veamos qué sucede!

SESIÓN 1:

SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta ENCRYPT);

Tabla modificada.

Transcurrido: 00:01:20.11

SESIÓN 2:

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:01:17.64

Como podemos observar, el encriptamiento de 1 millón de registros demoró 1 minuto 20 segundos. Mientras que se encriptaban los datos, ejecuté un UPDATE en otra sesión y demoró 1 minuto 17 segundos. Esto es debido a que la tabla quedó loqueda en la SESIÓN 1 por el proceso de encriptamiento y la SESIÓN 2 tuvo que esperar que termine de encriptar los datos para poder modificarlos. Imaginen los problemas de performance que tendrían en sus aplicaciones si tienen varios usuarios concurrentes realizando operaciones DML sobre esa tabla mientras se está ejecutando el procedimiento de encriptación!!!

Si ejecutamos el mismo UPDATE luego de que los datos ya se encuentran encriptados, podemos observar que no demoró nada en ejecutarse la operación DML.

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:00:00.00

Ahora vamos a ver la técnica que les mencionaba con el paquete DBMS_REDEFINITION. Lo primero que vamos a hacer, es ejecutar el procedimiento CAN_REDEF_TABLE de éste paquete para corroborar que la tabla puede redefinirse online.

SQL_10gR2> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST', 'TEST_TDE', dbms_redefinition.cons_use_pk);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.31

Como el procedimiento terminó sin errores, quiere decir que la tabla puede ser redefinida online.
Ahora vamos a comenzar el proceso de redefinir la tabla. Para ésto, primero tenemos que crear una tabla intermedia (TEST_TDE_TEMP) con la estructura de la tabla TEST_TDE en donde Oracle va a colocar los datos de la tabla original de manera temporal.

SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta DECRYPT);

Tabla modificada.

Transcurrido: 00:01:04.53

SQL_10gR2> SET long 400000000

SQL_10gR2> SELECT dbms_metadata.get_ddl('TABLE', 'TEST_TDE', 'TEST') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TEST_TDE','TEST')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST_TDE"
(    "ID" NUMBER,
"NOMBRE" VARCHAR2(44),
"NUM_TARJETA" NUMBER,
CONSTRAINT "TEST_TDE_ID_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

1 fila seleccionada.

SQL_10gR2> CREATE TABLE TEST_TDE_TEMP
2  (
3  ID          NUMBER,
4  NOMBRE      VARCHAR2(44),
5  NUM_TARJETA NUMBER ENCRYPT,
6  CONSTRAINT TEST_TDE_TEMP_ID_PK PRIMARY KEY (ID)
7  );

Tabla creada.

Como pueden notar, lo primero que hice fue desencriptar la columna NUM_TARJETA para poder volver a encriptarla con ésta técnica. Luego utilicé el paquete DBMS_METADATA para ver la estructura de la tabla TEST_TDE y poder crear una similar con el nombre TEST_TDE_TEMP.

Ahora vamos a redefinir la tabla con el procedimiento START_REDEF_TABLE.

SQL_10gR2> exec dbms_redefinition.start_redef_table('TEST', 'TEST_TDE', 'TEST_TDE_TEMP', 'id, nombre, num_tarjeta', DBMS_REDEFINITION.CONS_USE_PK);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:41.18

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE_TEMP

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER ENCRYPT

SQL_10gR2> SELECT master, log_table FROM dba_mview_logs WHERE log_table LIKE '%TEST%';

MASTER                         LOG_TABLE
------------------------------ ------------------------------
TEST_TDE                       MLOG$_TEST_TDE

1 fila seleccionada.

Fijense, que al redefinir la tabla, Oracle copió los datos de la tabla TEST_TDE y los insertó en la tabla transitoria TEST_TDE_TEMP con la columna NUM_TARJETA encriptada. A su vez, Oracle creó una vista materializada para registrar cualquier cambio que se realice sobre la tabla TEST_TDE para luego, en el momento de hacer la sincronización de datos entre las 2 tablas, pueda impactar cualquier cambio realizado.

Antes de realizar el proceso de sincronización entre las 2 tablas, voy a agregar algunos registros a la tabla TEST_TDE para ver si luego de la sincronización, los últimos cambios que realicé fueron impactados. Recuerden, que todos éstos pasos los estamos haciendo online, y que por el momento, los usuarios siguen trabajando sobre la tabla TEST_TDE sin ningún tipo de impacto en cuanto a la performance.

SQL_10gR2> INSERT INTO test_tde
2  SELECT * FROM
3  (
4  SELECT level id, 'nom_'||level, round(dbms_random.value(100000000000,900000000000))
5  FROM dual
6  CONNECT BY level <= 1000100  
7  )  
8  WHERE id > 1000000;

100 filas creadas.

Transcurrido: 00:00:06.93

SQL_10gR2> COMMIT;

Confirmación terminada.

Ahora vamos a sincronizar con el procedimiento SYNC_INTERIM_TABLE, las 2 tablas para aplicar cualquier cambio realizado en la tabla TEST_TDE a la tabla TEST_TDE_TEMP.
En éste paso en adelante, necesitamos una ventana de tiempo muy chica para ejecutar los 2 procedimientos que restan. En esta ventana de tiempo, los usuarios no deben estar modificando la tabla TEST_TDE.

SQL_10gR2> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.29

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000100

1 fila seleccionada.

Transcurrido: 00:00:06.92

SQL_10gR2> desc TEST_TDE

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER

Los 100 registros que habiamos agregado a la tabla TEST_TDE fueron impactados con éxito en la tabla TEST_TDE_TEMP! Para finalizar, lo único que resta es ejecutar el procedimiento FINISH_REDEF_TABLE para aplicar en la tabla TEST_TDE todos los cambios de la tabla TEST_TDE_TEMP (incluyendo la columna encriptada).

SQL_10gR2> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.37

SQL_10gR2> desc TEST_TDE

Nombre                                                 Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL NUMBER
NOMBRE                                                         VARCHAR2(44)
NUM_TARJETA                                                    NUMBER ENCRYPT

SQL_10gR2> DROP TABLE test_tde_temp;

Tabla borrada.

Con el mas mínimo impacto en la performance, pudimos encriptar la columna NUM_TARJETA de la tabla TEST_TDE (que contiene 1 millón de registros) y no afectar las operaciones de los usuarios online sobre esa tabla! Sólo tuvimos que necesitar una ventana de tiempo de 1 minuto para colocar la tabla TEST_TDE nuevamente disponible para todos los usuarios.

En lo posible, evitá utilizar el comando "EXPLAIN PLAN FOR" !!!

"EXPLAIN PLAN FOR" tiene problemas...

• 1er. PROBLEMA: Trata todas las Bind Variables como VARCHAR2.

Veamos un ejemplo:

SQL_10gR2> DESC emp

Nombre                  Nulo?    Tipo
----------------------- -------- -------------
 EMPNO                   NOT NULL NUMBER(4)
ENAME                            VARCHAR2(10)
JOB                              VARCHAR2(9)
GENDER                           VARCHAR2(1)
MGR                              NUMBER(4)
HIREDATE                         DATE
SAL                              NUMBER(7,2)
COMM                             NUMBER(7,2)
DEPTNO                           NUMBER(2)

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 7900

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2  SELECT * FROM emp WHERE empno = :bind;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    39 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    39 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMPNO_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:BIND))

Como podemos ver, creé una variable del tipo NUMBER pero al ejecutar el comando "EXPLAIN PLAN FOR" utilizando esa variable, Oracle la convirtió en una variable del tipo VARCHAR2 para luego aplicarle la función de conversión TO_NUMBER y volver a convertirla en tipo NUMBER.

• 2do. PROBLEMA: Puede NO mostrarte el plan de ejecución real que será utilizado en el ejecución de tu consulta.

SQL_10gR2> DESC dept
Nombre                  Nulo?    Tipo
----------------------- -------- ----------------
 DEPTNO                  NOT NULL VARCHAR2(10)
DNAME                            VARCHAR2(14)
LOC                              VARCHAR2(13)

SQL_10gR2> SELECT * FROM dept;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

4 filas seleccionadas.

SQL_10gR2> VAR bind NUMBER;

SQL_10gR2> EXECUTE :bind := 20

Procedimiento PL/SQL terminado correctamente.

SQL_10gR2> explain plan for
2  SELECT * FROM dept WHERE deptno = :bind;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'typical'));

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT           |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_DEPTNO_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:BIND)

SQL_10gR2> SELECT * FROM dept WHERE deptno = :bind;

DEPTNO     DNAME          LOC
---------- -------------- -------------
20         RESEARCH       DALLAS

1 fila seleccionada.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DEPTNO")=:BIND)

Como pueden observar, la primera vez que obtuve el plan de ejecución de la consulta, lo hice con el comando "EXPLAIN PLAN FOR" y vemos que estamos utilizando un índice para acceder a los datos. La segunda vez, optamos por ejecutar la consulta y luego obtener el plan de ejecución REAL (utilizando dbms_xplan.display_cursor). Qué observamos? Oracle está realizando un acceso a datos através de un FULL SCAN de la tabla. Esto sucedió porque el tipo de dato VARCHAR2 es siempre elegido para ser convertido en una comparación de tipos de datos distintos. En este caso, como la columna DEPTNO es del tipo VARCHAR2, y como estoy comparando esa columna con una variable del tipo NUMBER, Oracle tuvo que aplicar la función TO_NUMBER a la columna DEPTNO y como ya sabemos, si aplicamos una función a una columna indexada, Oracle no puede utilizar el índice en esa columna ya que la función lo deshabilita. Cuando obtuve el plan de ejecución de la primer consulta no hubo ningún tipo de conversión de datos (es por eso que accedimos por índice) ya que estoy comparando una columna del tipo VARCHAR2 (deptno) con una variable NUMBER... pero que en realidad esa variable NUMBER es un VARCHAR2 (ya que como dijimos anteriormente, si ejecutamos el comando "EXPLAIN PLAN FOR", Oracle trata todas las Bind Variables como VARCHAR2).

Siempre es bueno tener en mente cuál es la diferencia entre lo IDEAL y lo REAL. Lo IDEAL para éste caso sería acceder por índice a los datos, lo REAL es que Oracle está realizando lo contrario.

Diferencias entre COUNT(1) y COUNT(*) - Parte 2

Hace unas semanas, se me acercaron y me hicieron la siguiente pregunta:

"Me dijeron que si en una consulta se coloca el COUNT(*), por cada registro que leamos en un acceso por índice, vamos a tener que acceder también a la tabla ya que el símbolo * significa que estoy colocando todas las columnas de la tabla en la consulta, y si no tengo todas las columnas de la tabla en el índice, entonces Oracle tiene que acceder a la tabla a buscar el resto de las columnas. Es cierto?".

Bueno, no... no es cierto. Para validar el porque digo ésto, primero veamos un ejemplo:

SQL_10gR2> CREATE TABLE test AS
2  SELECT level id, 'texto_'||level texto
3  FROM dual
4  CONNECT BY level <= 100000 ; 

Tabla creada. 

SQL_10gR2> CREATE UNIQUE INDEX test_id_uq ON test(id) ;

Índice creado.

SQL_10gR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE) ;

Procedimiento PL/SQL terminado correctamente.

Bien, veamos el plan de ejecución de la siguiente consulta:

SQL_10gR2> SELECT COUNT(*)
2  FROM test
3  WHERE id = 100;

COUNT(*)
----------
      1

1 fila seleccionada.

SQL_10gR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  d6urw3zfuxz32, child number 0
-------------------------------------
SELECT COUNT(*) FROM test WHERE id = 100

Plan hash value: 4041652814

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN| TEST_ID_UQ |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)

Como podemos observar, coloqué el COUNT con el símbolo * y accedí al índice con el ID 100; pero luego de acceder al índice NO accedí a la tabla, simplemente accedí al índice (ya que tiene la columna que estoy utilizando en el predicado). Como anteriormente dije en el post "Diferencias entre COUNT(1) y COUNT(*) - Parte 1", no existe ninguna diferencia entre el COUNT(1) y COUNT(*), pero si hay diferencia si ejecutamos COUNT(*) y COUNT(nonbre_de_columna) ya que si colocamos una columna de la tabla en el COUNT, Oracle hace un conteo sólo de los valores de esa columna que NO tengan valores nulos.

Explain Plan Vs. Bind Variables

Obtener el plan de ejecución de una consulta que contiene Bind Variables sin haberlas reemplazado??? NO!!! NO!!! NO!!!!!!!!!!

Ya hablamos en otras ocasiones del beneficio que obtenemos al utilizar Bind Variables y también explicamos qué son. Cuando ejecutamos una consulta con Bind Variables (sin haberlas reemplazado) para obtener el plan de ejecución, el optimizador de costos (CBO) no sabe el valor de la Bind Variable; y por lo tanto, calcula la selectividad del filtro utilizando reglas definidas por defecto. Que quiere decir ésto? Que el plan de ejecución que obtenemos puede ser MUY distinto al plan de ejecución real!!! Porqué muy distinto? Porque todo depende del valor con el que se reemplazará la Bind Variable y el tipo de dato de la misma.

Veamos un ejemplo:

SQL_10gR> CREATE TABLE test AS
  2 SELECT TO_CHAR(level) id, 'test'||level descripcion
  3 FROM dual
  4 CONNECT BY level <= 100000;

Table created.

SQL_10gR> DESC test

Name                    Null?    Type
----------------------- -------- ----------------
ID                               VARCHAR2(40)
DESCRIPCION                      VARCHAR2(44)

SQL_10gR> CREATE UNIQUE INDEX test_uq ON test(id, descripcion);

Index created.

SQL_10gR> EXEC dbms_stats.gather_table_stats(user, 'TEST', cascade=>true) ;

PL/SQL procedure successfully completed.

Supongamos que detectamos un problema grave en la performance de una de nuestras aplicaciones. Al identificar la consulta que nos está causando problemas, obtenemos el plan de ejecución de la misma para ver si está accediendo correctamente...

Ejecutamos la consulta con Bind Variable:

SQL_10gR> EXPLAIN PLAN FOR
  2 SELECT descripcion
  3 FROM test
  4 WHERE id = :b1;

Explained.

SQL_10gR> @explains
Plan hash value: 1087767317

----------------------------------------------------------------------------
| Id | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT |         |     1 |    15 |       2 (0)|  00:00:01 |
|* 1 | INDEX RANGE SCAN | TEST_UQ |     1 |    15 |       2 (0)|  00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - access("ID"=:B1)

Por lo que vemos en el plan de ejecución, si filtramos la columna ID con un valor del mismo tipo de dato, el optimizador eligirá acceder por índice en vez de realizar un full scan de la tabla. Esto suena lógico sabiendo que los valores de la columna ID son únicos y que por cada valor con el que filtremos, a lo sumo obtendremos una ocurrencia del mismo valor en la tabla.

Ejecutamos una consulta sin Bind Variable:

SQL_10gR> EXPLAIN PLAN FOR
  2 SELECT descripcion
  3 FROM test
  4 WHERE id = 10000;

Explained.

SQL_10gR> @explains
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    15 |      54 (4)|    00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST |    1 |    15 |      54 (4)|    00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
1 - filter(TO_NUMBER("ID")=10000)

Pero qué sucede si filtramos la columna ID con un valor de distinto tipo de dato? Como en éste caso estamos realizando una conversión implícita, el optimizador no puede utilizar el índice que tenemos creado en la tabla y por lo tanto se ve forzado a realizar un full scan de la misma.

Si en nuestra aplicación el problema es justamente éste (que estamos realizando una conversión implícita), si no reemplazamos las Bind Variables con valores reales, estaremos pensando que el optimizador está accediendo de la manera correcta... cuando en realidad ésto no es cierto.

Recuerden lo siguiente: Siempre que obtengan el plan de ejecución de una consulta... reemplacen las Bind Variables con valores reales!!! En caso contrario... no deberíamos fiarnos demasiado con el plan de ejecución obtenido.

¿Tunear en base al COSTO del plan de ejecución?

Todos los días observo que alguien está queriendo tunear una consulta en base al costo del plan de ejecución. Pero... ¿Qué es el COSTO? ¿Qué representa? La respuesta es simple: El costo representa unidades de trabajo o recursos utilizados. El optimizador usa I/O a disco, CPU y memoria como unidades de trabajo. Entonces, el costo para una determinada consulta representa una estimación de la cantidad de I/O a disco, de CPU y memoria que se utilizará para la ejecución de la consulta.

Bien, con ésto ya dicho, porqué hay personas que tratan de tunear una consulta en base al costo??? El costo es simplemente un número que le asigna el optimizador de costos (CBO) a la consulta para saber qué plan de ejecución elegir entre todos los planes que genera en el momento de la optimización (el plan de ejecución que se genera con el menor costo es el que Oracle utiliza para ejecutar nuestra consulta), pero no existe un "mejor número" que debemos tener en mente para deducir si una consulta es óptima o no.

NO debemos tunear en base al costo. SI debemos tunear en base a los I/O lógicos (LIO's).

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE test AS
2  SELECT level id, 'nombre_'||level nom
3  FROM dual
4  CONNECT BY level <= 100000 ; 

Table created. 

SQL_9iR2> EXEC dbms_stats.gather_table_stats(user,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT nom
2  FROM test
3  WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=1 Bytes=17)
1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=1 Bytes=17)

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

Como podemos observar, el costo de la consulta es de 49. Si yo tuneo en base al costo no puedo saber si el plan de ejecución que se está utilizando para ésta consulta es óptimo o no porque no sé que costo sería el ideal para saberlo. Es por eso que NO debemos tunear en base al costo. Por otro lado, podríamos tunear en base a la cantidad de I/O lógicos que se estén realizando. En éste ejemplo, se realizan 323 LIO's. Si observamos la consulta, estoy seleccionando la columna NOM que corresponde al ID 50000 . Como creé la tabla de forma tal que todos los ID's sean únicos, ésta consulta me debería traer un solo registro. Si nos ponemos a pensar, hacer 323 LIO's para traer sólo un registro es demasiado. En éste momento es en donde nos damos cuenta que tenemos un problema de performance porque estamos haciendo un Full Scan de una tabla de 100.000 registros para buscar solamente el ID 50000 que nos devuelve un sólo registro.

Veamos qué sucede si creamos un índice único por la columna ID,NOM...

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) ;

Index created.

SQL_9iR2> EXEC dbms_stats.gather_index_stats(user,'TEST_ID_NOM_UQ') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT nom
2  FROM test
3  WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1    0   INDEX (RANGE SCAN) OF 'TEST_ID_NOM_UQ' (UNIQUE) (Cost=2 Card=1 Bytes=17)

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

Luego de crear un índice único por la columna ID,NOM, ejecutando nuevamente la consulta, notamos que ahora sólo estamos realizando 3 LIO's y, por consiguiente, el costo se decrementó ya que estamos utilizando menos recursos que el caso anterior.

DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)

El parámetro DB_FILE_MULTIBLOCK_READ_COUNT especifica la cantidad de bloques que van a ser leídos en cada I/O a través de un Full Scan.

En Oracle 10g Release 2, el valor por default de éste parámetro, es el valor que corresponde a la cantidad máxima de I/O que se puede realizar de forma más eficiente.

En ambientes OLTP o Batch, éste parámetro suele setearse en valores entre 4 y 16 bloques. En ambientes DSS o Data Warehouse, éste parámetro suele setearse en un valor mayor.

Este parámetro afecta al Optimizador de Costos (CBO) ya que si seteamos un valor alto, el CBO puede ser influenciado en elegir realizar un Full Scan en vez de acceder por índice. Como en los ambientes DSS o Data Warehouse suelen utilizarse planes de ejecución que incluyen Full Scan, aumentar el valor de éste parámetro puede ser beneficioso.

El máximo valor que podemos setear depende del sistema operativo.
La fórmula que se utiliza para buscar el valor de éste parámetro es:

DB_FILE_MULTIBLOCK_READ_COUNT = ( (MAX I/O SIZE) / DB_BLOCK_SIZE )

Si elegimos un valor mayor al máximo soportado, Oracle simplemente elige el valor máximo que puede utilizar.

Veamos un ejemplo de cómo buscar el valor máximo del parámetro DB_FILE_MULTIBLOCK_READ_COUNT sin utilizar la fórmula:

Primero, veamos el valor actual del MBRC:

SQL_9iR2> show parameter multiblock

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_file_multiblock_read_count  integer     16

Observemos el Explain Plan de una de las tablas:

SQL_9iR2> explain plan for
  2  SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
  3  FROM test ;

Explained.

SQL_9iR2> @explains
Plan hash value: 3740828345

---------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 | 46709   (1)| 00:10:55 |
|   1 |  SORT AGGREGATE       |                       |     1 |            |          |
|   2 |   TEST ACCESS FULL    | TEST                  |    20M| 46709   (1)| 00:10:55 |
---------------------------------------------------------------------------------------

El costo de la consulta es 46709.
Veamos qué sucede si seteo el valor del parámetro muy alto:

SQL_9iR2> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 50000 ;

Session altered.

SQL_9iR2> show parameter multiblock

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_file_multiblock_read_count  integer     64

Bien, como podemos observar, seteamos el parámetro en 50.000 bloques; pero como excedemos el máximo permitido, Oracle setea el máximo valor que puede alcanzar.

Ahora veamos si Oracle realmente está utilizando el nuevo MBRC que acabamos de setear:

SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;

Session altered.

SQL_9iR2> SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
  2  FROM test ;

  COUNT(*)
----------
  52673028

1 row selected.

SQL_9iR2> ALTER SESSION SET EVENTS '10046 trace name context off' ;

Session altered.

SQL_9iR2> @trace_file_name

TRACE_FILE_NAME
-----------------------------
testdb_ora_11379.trc

1 row selected.

[test@linux_test udump]$ cat testdb_ora_11379.trc | grep "scattered read" | awk '{ split ($11,listado,"="); print listado[2];}' | sort -n | tail -1

64

[test@linux_test udump]$ more testdb_ora_11379.trc | grep scattered

...
...
WAIT #1: nam='db file scattered read' ela= 1411 file#=115 block#=241927 blocks=64 obj#=70390 tim=1164040832168738
WAIT #1: nam='db file scattered read' ela= 1394 file#=115 block#=241991 blocks=64 obj#=70390 tim=1164040832171544
WAIT #1: nam='db file scattered read' ela= 1426 file#=115 block#=242055 blocks=64 obj#=70390 tim=1164040832174390
WAIT #1: nam='db file scattered read' ela= 1399 file#=115 block#=242119 blocks=64 obj#=70390 tim=1164040832177182
WAIT #1: nam='db file scattered read' ela= 1391 file#=115 block#=242183 blocks=64 obj#=70390 tim=1164040832179966
...
...

Bien. Vemos que efectivamente estamos leyendo 64 bloques en cada I/O que realizamos.

Ahora ejecutamos nuevamente nuestra consulta y observamos el Explain Plan:

SQL_9iR2> explain plan for
  2  SELECT /*+ full(test) noparallel(test) nocache(test) */ count(*)
  3  FROM test ;

Explained.

SQL_9iR2> @explains
Plan hash value: 3740828345

---------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 | 41994   (1)| 00:09:49 |
|   1 |  SORT AGGREGATE       |                       |     1 |            |          |
|   2 |   TEST ACCESS FULL    | TEST                  |    20M| 41994   (1)| 00:09:49 |
---------------------------------------------------------------------------------------

Ahora el costo de la consulta pasa a ser 41994.

Hay que tener mucho cuidado si decidimos modificar éste parámetro ya que no siempre obtenemos un beneficio. Recuerden que modificando el MBRC por default puede producir que nuestro sistema funcione peor, igual o mejor. Por lo general, el MBRC por default suele ser el correcto.

Mi consejo es que no modifiquen el MBRC si no es necesario.

Problemas utilizando Analytic Functions junto con PL/SQL en 8i

La idea de éste post no es explicar el funcionamiento de las Analytic Functions, sino el error PLS-00103 al utilizar Analytic Functions.
La mejor manera de explicar éste problema, es realizando un ejemplo.

La tabla de prueba que vamos a utilizar en nuestro ejemplo, contiene los siguientes registros:

SQL> SELECT * FROM test ;

        ID      NIVEL    SALARIO
---------- ---------- ----------
        10          1       2500
        20          2       3000
        30          1       3500
        40          2       4000
        50          1       4500
        60          2       5000
        70          1       5500
        80          2       6000
        90          1       6500
       100          2       7000

10 rows selected.

Primero vamos a realizar el ejemplo en una base de datos 8.1.7.4.0

SQL_8i> SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank
  2      FROM test ;

        ID      NIVEL    SALARIO       RANK
---------- ---------- ---------- ----------
        90          1       6500          1
        70          1       5500          2
        50          1       4500          3
        30          1       3500          4
        10          1       2500          5
       100          2       7000          1
        80          2       6000          2
        60          2       5000          3
        40          2       4000          4
        20          2       3000          5

10 rows selected.

Como podemos observar, si ejecutamos esa consulta con Analytic Functions en SQL*Plus, funciona a la perfección. Veamos qué sucede si intentamos ejecutar la consulta dentro de PL/SQL:

SQL_8i> CREATE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
 nivel ORDER BY salario DESC) AS rank
  6                 FROM test ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Warning: Procedure created with compilation errors.

SQL_8i> show errors

Errors for PROCEDURE PR_TEST:

LINE/COL     ERROR
-----------  ------------------------------------------------------------------------
5/63         PLS-00103: Encountered the symbol "(" when expecting one of the following:
             , from

Este error se debe a que según la Nota 147808.1, no podemos utilizar Analytic Functions dentro de PL/SQL en versiones anteriores a 9i. Podemos utilizar éste tipo de funciones en SQL, pero no en PL/SQL. Desde las versiones de la 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

Hay 2 formas de solucionar éste problema:
- Crear vistas utilizando Analytic Function y luego hacer referencia a esas vistas dentro de PL/SQL.
- Usar Dynamic SQL.

Veamos la implementación de esas 2 soluciones:

Utilizando una vista...

SQL_8i> CREATE VIEW pr_test_view AS
  2  SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY s
alario DESC) AS rank
  3  FROM test ;

View created.

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, rank
  6                   FROM pr_test_view ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Utilizando Dynamic SQL...

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
  2  IS
  3  --
  4  TYPE   mi_cursor IS REF CURSOR ;
  5  cur    mi_cursor ;
  6  --
  7  l_consulta  VARCHAR2(1000) ;
  8  l_id        test.id%TYPE ;
  9  l_nivel     test.nivel%TYPE ;
 10  l_salario   test.salario%TYPE ;
 11  l_rank      NUMBER ;
 12  --
 13  BEGIN
 14      --
 15      l_consulta := 'SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION
BY nivel ORDER BY salario DESC) AS rank FROM test' ;
 16      --
 17      OPEN cur FOR l_consulta ;
 18      --
 19      LOOP
 20          --
 21          FETCH cur INTO l_id, l_nivel, l_salario, l_rank ;
 22          --
 23          EXIT WHEN cur%NOTFOUND ;
 24          --
 25          dbms_output.put_line('ID: '||l_id||
 26                               ' - NIVEL: '||l_nivel||
 27                               ' - SALARIO: '||l_salario||
 28                               ' - RANK: '||l_rank) ;
 29          --
 30      END LOOP ;
 31      --
 32      CLOSE cur ;
 33      --
 34  END pr_test ;
 35  /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Ahora vamos a realizar el ejemplo anterior pero en una base de datos 9.2.0.8.0

SQL_9i> CREATE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
 nivel ORDER BY salario DESC) AS rank
  6               FROM test ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Procedure created.

SQL_9i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Realizamos el ejemplo anterior en una base de datos 10.1.0.2.0, veremos el mismo resultado que en 9i...

SQL_10g> CREATE PROCEDURE pr_test
  2  IS
  3  BEGIN
  4      --
  5      FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
 nivel ORDER BY salario DESC) AS rank
  6                   FROM test ) LOOP
  7          --
  8          dbms_output.put_line('ID: '||cur.id||
  9                               ' - NIVEL: '||cur.nivel||
 10                               ' - SALARIO: '||cur.salario||
 11                               ' - RANK: '||cur.rank) ;
 12          --
 13      END LOOP ;
 14      --
 15  END pr_test ;
 16  /

Procedure created.

SQL_10g> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Como pudimos observar, de la versión 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

Problemas en Vistas Materializadas - Parte 2

Como pudimos ver en el post "Problemas en Vistas Materializadas - Parte 1", podemos identificar con el paquete DBMS_MVIEW (procedimiento EXPLAIN_MVIEW), los problemas que pueden haber en nuestra Vista Materializada e implementar las soluciones necesarias. La contra de ese procedimiento es que nos muestra los errores que tenemos en la Vista Materializada, pero no nos dice cómo solucionarlos.

En Oracle 10g se introduce el paquete DBMS_ADVISOR (procedimiento TUNE_MVIEW) que nos dice qué cambios debemos implementar en nuestra Vista Materializada para soportar las capacidades que necesitamos.

Veamos un ejemplo:

SQL_10gR2> CREATE TABLE test1 AS
  2  SELECT level id, level*level total
  3  FROM dual
  4  CONNECT BY level <= 10 ;

Table created.

SQL_10gR2> CREATE TABLE test2 AS
  2  SELECT level id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 20 ;

Table created.

SQL_10gR2> COMMIT ;

Commit complete.

SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;

Table altered.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;

PL/SQL procedure successfully completed.

SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT test2.id,
  2         test2.nom,
  3         SUM(test1.total) sum_total
  4  FROM test1,
  5       test2
  6  WHERE test1.id = test2.id(+)
  7  GROUP BY test2.id,
  8           test2.nom
  9  ORDER BY id ;

        ID NOM         SUM_TOTAL
---------- ---------- ----------
         1 nom_1               1
         2 nom_2               4
         3 nom_3               9
         4 nom_4              16
         5 nom_5              25
         6 nom_6              36
         7 nom_7              49
         8 nom_8              64
         9 nom_9              81
        10 nom_10            100

10 rows selected.

Bien, ya creé mi ambiente de prueba. Ahora voy a crear las tablas de Log necesarias para la capacidad de REFRESH FAST. Pero las voy a crear de forma errónea para demostrar el poder que nos brinda el paquete DBMS_ADVISOR.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
  2  WITH ROWID, SEQUENCE (id) ;

Materialized view log created.

SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
  2  WITH ROWID, SEQUENCE (id,nom) ;

Materialized view log created.

Procedemos a crear la Vista Materializada:

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;
FROM test1,
     *
ERROR at line 15:
ORA-32401: materialized view log on "CDW"."TEST2" does not have new values

Efectivamente como supusimos, tenemos errores en las tablas de Log que creamos.
Ahora vamos a ejecutar DBMS_ADVISOR.TUNE_MVIEW y ver los resultados en la tabla USER_TUNE_MVIEW...

SQL_10gR2> DECLARE
  2  l_nombre  VARCHAR2(100) := 'test_1' ;
  3  BEGIN
  4  DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
  5  NOCOMPRESS
  6  LOGGING
  7  BUILD IMMEDIATE
  8  USING INDEX
  9  REFRESH FAST ON DEMAND
 10  USING DEFAULT LOCAL ROLLBACK SEGMENT
 11  DISABLE QUERY REWRITE
 12  AS
 13  SELECT test2.id,
 14         test2.nom,
 15         SUM(test1.total) sum_total,
 16         COUNT(*) cnt,
 17         COUNT(test1.total) cnt_sum
 18  FROM test1,
 19       test2
 20  WHERE test1.id = test2.id(+)
 21  GROUP BY test2.id,
 22           test2.nom
 23  ORDER BY id') ;
 24  END ;
 25  /

PL/SQL procedure successfully completed.

SQL_10gR2> SELECT *
  2  FROM user_tune_mview
  3  WHERE task_name = 'test_1' ;

TASK_NAME        ACTION_ID SCRIPT_TYPE    STATEMENT
--------------- ---------- -------------- -----------------------------------
test_1                   1 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
                                          N "CDW"."TEST2" ADD ROWID, SEQUENCE
                                           ("ID","NOM")  INCLUDING NEW VALUES

test_1                   2 IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE O
                                          N "CDW"."TEST1" ADD ROWID, SEQUENCE
                                           ("ID","TOTAL")  INCLUDING NEW VALU
                                          ES

test_1                   3 IMPLEMENTATION CREATE MATERIALIZED VIEW CDW.TEST_M
                                          V NOCOMPRESS
                                          LOGGING
                                          BUILD IMMEDIATE
                                          USING INDEX
                                           REFRESH FAST WITH ROWID DISABLE QU
                                          ERY REWRITE AS SELECT test2.id,
                                                 test2.nom,
                                                 SUM(test1.total) sum_total,
                                                 COUNT(*) cnt,
                                                 COUNT(test1.total) cnt_sum
                                          FROM test1,
                                               test2
                                          WHERE test1.id = test2.id(+)
                                          GROUP BY test2.id,
                                                   test2.nom
                                          ORDER BY id

test_1                   4 UNDO           DROP MATERIALIZED VIEW CDW.TEST_MV

4 rows selected.

La columna SCRIPT_TYPE nos muestra el tipo de recomendación. Deberíamos ver las columnas con el valor 'IMPLEMENTED' (la recomendación ya está implementada); pero en cambio, estamos viendo que aparecen como 'IMPLEMENTATION' (la recomendación NO está implementada).
El próximo paso es ejecutar los script recomendados...

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST2"
  2  ADD ROWID, SEQUENCE("ID","NOM")  INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> ALTER MATERIALIZED VIEW LOG FORCE ON "CDW"."TEST1"
  2  ADD ROWID, SEQUENCE("ID","TOTAL")  INCLUDING NEW VALUES ;

Materialized view log altered.

SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
  2  NOCOMPRESS
  3  LOGGING
  4  BUILD IMMEDIATE
  5  USING INDEX
  6  REFRESH FAST ON DEMAND
  7  USING DEFAULT LOCAL ROLLBACK SEGMENT
  8  DISABLE QUERY REWRITE
  9  AS
 10  SELECT test2.id,
 11         test2.nom,
 12         SUM(test1.total) sum_total,
 13         COUNT(*) cnt,
 14         COUNT(test1.total) cnt_sum
 15  FROM test1,
 16       test2
 17  WHERE test1.id = test2.id(+)
 18  GROUP BY test2.id,
 19           test2.nom
 20  ORDER BY id ;

Materialized view created.

Ejecutando las recomendaciones de la tabla, pudimos solucionar rápidamente el problema que teníamos en la Vista Materializada.

Qué sucede si ejecutamos el DBMS_ADVISOR.TUNE_MVIEW nuevamente?

SQL_10gR2> DECLARE
  2  l_nombre  VARCHAR2(100) := 'test_1' ;
  3  BEGIN
  4  DBMS_ADVISOR.TUNE_MVIEW(l_nombre, 'CREATE MATERIALIZED VIEW TEST_MV
  5  NOCOMPRESS
  6  LOGGING
  7  BUILD IMMEDIATE
  8  USING INDEX
  9  REFRESH FAST ON DEMAND
 10  USING DEFAULT LOCAL ROLLBACK SEGMENT
 11  DISABLE QUERY REWRITE
 12  AS
 13  SELECT test2.id,
 14         test2.nom,
 15         SUM(test1.total) sum_total,
 16         COUNT(*) cnt,
 17         COUNT(test1.total) cnt_sum
 18  FROM test1,
 19       test2
 20  WHERE test1.id = test2.id(+)
 21  GROUP BY test2.id,
 22           test2.nom
 23  ORDER BY id') ;
 24  END ;
 25  /
DECLARE
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1042
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 4

[TEST@TEST_10GR2 ~]$ oerr qsm 03116
03116, 00000, "The materialized view is already optimal and cannot be tuned any further"
// *Cause:   The materialized view has the capabilities that are specified in
//           the statement.
// *Action:  none

Oracle nos está diciendo que el script de creación de la Vista Materializada que nosotros ejecutamos, ya cuenta con todas las capacidades necesarias para su creación, y por lo tanto, ya se encuentra óptima y no se puede seguir tuneando.

Exchange Partition

Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.
En resumen, lo que hace la sentencia Exchange Partition es modificar el diccionario de datos y simular que los datos que ya tenemos cargados en una tabla, corresponden a una partición determinada de otra tabla.

Veamos un ejemplo muy sencillo para entender mejor éste tema:

SQL_10gR2> CREATE TABLE datos_1 AS
  2  SELECT level id, timestamp'2000-11-02 09:00:00' fecha
  3  FROM dual
  4  CONNECT BY level <= 100000 ;

Table created.

Elapsed: 00:00:01.06

SQL_10gR2> CREATE TABLE datos_2 AS
  2  SELECT level id, timestamp'2001-09-10 13:00:00' fecha
  3  FROM dual
  4  CONNECT BY level <= 100000 ;

Table created.

Lo que hicimos fue crear 2 tablas con distintas fechas en cada una de ellas.

Ahora creamos solamente la estructura de la tabla particionada en donde vamos a cargar los datos:

SQL_10gR2> CREATE TABLE test
  2  ( id, fecha )
  3  PARTITION BY RANGE ( fecha )
  4  (
  5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
  6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
  7  )
  8  AS
  9  SELECT 1, timestamp'2000-11-02 09:00:00'
 10  FROM dual
 11  WHERE 1 = 0 ;

Vamos a realizar un alter para modificar el diccionario de datos y relacionar cada una de las 2 tablas que creamos con la respectiva partición de la tabla TEST...

SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2000
  3  WITH table datos_1
  4  WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.03

SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2001
  3  WITH table datos_2
  4  WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.02

SQL_10gR2> SELECT count(*)
  2  FROM test ;

  COUNT(*)
----------
    200000

1 row selected.

SQL_10gR2> SELECT count(*)
  2  FROM datos_1 ;

  COUNT(*)
----------
         0

1 row selected.

SQL_10gR2> SELECT count(*)
  2  FROM datos_2 ;

  COUNT(*)
----------
         0

1 row selected.

Como podemos ver, con el Exchange Partition no tardamos casi nada en cargar los datos en la tabla particionada ya que en realidad no estamos cargando los datos, simplemente se modifica el diccionario de datos.

Pueden notar que agregué la sentencia WITHOUT VALIDATION. Que es ésto? WITHOUT VALIDATION suele ser una operación rápida porque sólo realiza modificaciones en el diccionario de datos. Si la tabla o tabla particionada que colocamos en el Exchange Partition tiene una primary key o unique constraint habilitado, entonces el Exchange Partition se realiza como WITH VALIDATION para mantener la integridad de las constraints.

Vamos a ejecutar nuevamente los 2 alter anteriores sin la sentencia WITHOUT VALIDATION...

SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2000
  3  WITH table datos_1 ;

Table altered.

Elapsed: 00:00:01.00

SQL_10gR2> ALTER TABLE test
  2  EXCHANGE PARTITION year_2001
  3  WITH table datos_2 ;

Table altered.

Elapsed: 00:00:01.05

Si hubiera ejecutado esos alter con un Trace, el reporte del Trace me mostraría, entre otras sentencias, las siguientes...

select 1
from
 "DATOS_1" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          0           0
Fetch        1      0.04       0.04          0         65          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         66          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DATOS_1 (cr=65 pr=0 pw=0 time=44582 us)


select 1
from
 "DATOS_2" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          0           0
Fetch        1      0.04       0.04          0         65          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         66          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL DATOS_2 (cr=65 pr=0 pw=0 time=46957 us)

Antes que nada, notamos que los alter se ejecutaron en mayor tiempo, cierto? De las consultas que observamos del Trace, vemos que se está realizando un FULL SCAN de las tablas y que se está ejecutando una función en el WHERE de cada consulta. Imagínense si tenemos que realizar ésta clase de procesos en ambientes con gran volumen de datos y en donde el sistema se encuentra saturado por el I/O a disco.

Qué sucede si no tenemos los datos separados por año en distintas tablas, y en cambio, tenemos todos los datos en una misma tabla? Bueno, tomando como ejemplo la tabla TEST que acabamos de cargar, podríamos realizar lo siguiente...

SQL_10gR2> CREATE TABLE test_2
  2  ( id, fecha )
  3  PARTITION BY RANGE ( fecha )
  4  (
  5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
  6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
  7  )
  8  AS
  9  SELECT *
 10  FROM test ;

Table created.

Elapsed: 00:00:05.04

SQL_10gR2> DROP TABLE test ;

Table dropped.

SQL_10gR2> ALTER TABLE test_2 RENAME TO test ;

Table altered.

SQL_10gR2> SELECT count(*)
  2  FROM test ;

  COUNT(*)
----------
    200000

1 row selected.

Buscar valores NULL en forma eficiente

Un problema que veo seguidamente, se relaciona con la escritura de consultas que buscan valores NULL en una tabla. Este es un problema muy común.
A continuación vamos a ver algunas soluciones que podemos implementar para buscar los valores NULL en forma eficiente y evitar lecturas innecesarias de bloques de datos.

Veamos algunos ejemplos:

SQL_9iR2> CREATE TABLE test AS
  2  SELECT decode(mod(rownum,100),0,null,level) id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Veamos la cantidad de valores NULL que tiene la tabla que creamos:

SQL_9iR2> SELECT count(*)
  2  FROM test
  3  WHERE id IS NULL ;

  COUNT(*)
----------
     10000

1 row selected.

Veamos cómo se ejecuta internamente esa consulta:

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     7 |   304 |
|   1 |  SORT AGGREGATE      |             |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL  | TEST        | 10000 | 70000 |   304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST"."ID" IS NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.16       0.16       3144       3150          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.17       0.16       3144       3150          0           1

Observamos que obviamente está realizando un Full Scan. Qué sucede si creamos un índice B*Tree?

SQL_9iR2> CREATE INDEX test_id_idx ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     7 |   304 |
|   1 |  SORT AGGREGATE      |             |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL  | TEST        | 10000 | 70000 |   304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST"."ID" IS NULL)

Porqué nuestra consulta no está accediendo a través del índice? por la simple razón de que los índices B*Tree no indexan los valores NULL. Como estamos realizando un COUNT buscando la cantidad de valores NULL de la tabla, el CBO sabe que si accede por índice, la consulta nos va a retornar un resultado erróneo ya que no existen valores NULL en el índice. Es por eso, que en vez de acceder por índice, realiza un Full Scan de la tabla. Esto tiene como consecuencia una lectura innecesaria de bloques por no haber accedido a través de un índice.

Una solución para éste problema sería crear un FBI (Function Based-Index) para indexar sólo los valores NULL de la columna:

SQL_9iR2> CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo')) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE NVL(id,'nulo') = 'nulo' ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |     7 |    27 |
|   1 |  SORT AGGREGATE      |                   |     1 |     7 |       |
|*  2 |   INDEX RANGE SCAN   | TEST_ID_NULO_IDX  | 10001 | 70007 |    27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("TEST"."ID",'nulo')='nulo')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1         26          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1         26          0           1

Veamos qué sucede si creamos un índice Bitmap:

SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

SQL_9iR2> CREATE BITMAP INDEX test_id_bitmap ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------------
| Id  | Operation                   |  Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |     7 |    44 |
|   1 |  SORT AGGREGATE             |                  |     1 |     7 |       |
|   2 |   BITMAP CONVERSION COUNT   |                  |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE| TEST_ID_BITMAP   |       |       |       |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TEST"."ID" IS NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1          5          0           1

Observamos que también podemos utilizar índices Bitmap para buscar valores NULL ya que ésta clase de índices SI indexan éstos valores.
Lo importante a tener en cuenta si utilizamos éste índice es que cada modificación que se realiza sobre el índice, requiere un gran trabajo del sistema que si utilizamos índices B*tree. Por otro lado, si a ésto le sumamos las modificaciones concurrentes que se realizan sobre la columna indexada, puede llegar a ser mortal para el sistema.