Compresión avanzada de Tablas en 11g (OLTP Compression)

En esta nota la idea es mostrarles algunas de las primeras pruebas que realicé sobre compresión de tablas. Uno de los features mas fervientemente presentados por Oracle en su última versión (11g) es justamente la compresión avanzada. Desde 9i se puede comprimir tablas pero con ciertas restricciones. En 9i y 10g la compresión de tablas la podriamos llamar básica ya que tiene varias limitaciones y la compresión solo aplica para un restringido set de operaciones (por ejemplo para el insert directo) lo que lo hace bastante util para sistemas DW pero no tanto para las bases OLTP.

Oracle 11g introdujo la compresión avanzada con el fin de reducir la utilización de recursos y la manipulación de grandes volúmenes de datos. Permite una sensible reducción del storage requerido para datos relacionales o estructurados (tablas), datos no estructurados (archivos) o datos de respaldo (backups).

El nuevo feature OLTP Table Compression usa un algoritmo diseñado para trabajar con aplicaciones OLTP. Dicho algoritmo trabaja eliminando valores duplicados a nivel de bloque. El ratio de compresión esperado es de 2 a 3 usando OLTP Compression. Lo más novedoso es que con este feature se puede leer la información comprimida sin necesidad de descomprimirla por lo tanto no existe degradación de rendimiento y hasta puede mejorar la performance debido a una reducción de I/O ya que se necesitará acceder menos bloques para obtener las mismas filas sumado con la consiguiente reducción de buffer cache requerido. De todas formas a mi me gusta ver para creer y por lo tanto les voy a mostrar mis pruebas para que uds saquen sus propias conclusiones, además de incentivarlos a tomar como práctica habitual testear siempre antes de implementar.

No tengo a mano en estos momentos una base R2 de 11g asi que mis pruebas se van a basar en R1. En R2 cambio la sintaxis pero la semántica de las operaciones es la misma que en R1. Por si alguno quisiera probar mi test en R2, y tiene pereza de consultar el manual SQL Reference, les paso las diferencias:

COMPRESS FOR ALL OPERATIONS         (11gR1) = COMPRESS FOR OLTP (11gR2)
COMPRESS FOR DIRECT_LOAD OPERATIONS (11gR1) = COMPRESS BASIC (11gR2)  (*)default

Para la prueba voy a crear 3 tablas:

T       : Sin Compresión
T_C_DSS : Compresión DSS o compresión básica (el mismo tipo de compresión de 
          versiones anteriores)
T_C_OLTP: Compresión avanzada (11g+)

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

rop@ROP11G> create table t (x int,y char(30),z date);

Tabla creada.

rop@ROP11G> create table t_c_dss (x int,y char(30),z date) compress;

Tabla creada.

rop@ROP11G> create table t_c_oltp (x int,y char(30),z date) compress for all operations;

Tabla creada.

rop@ROP11G> select table_name,pct_free,compression,compress_for from user_tables
  2  where table_name in ('T','T_C_DSS','T_C_OLTP');

TABLE_NAME                       PCT_FREE COMPRESS COMPRESS_FOR
------------------------------ ---------- -------- ------------------
T                                      10 DISABLED
T_C_DSS                                 0 ENABLED  DIRECT LOAD ONLY
T_C_OLTP                               10 ENABLED  FOR ALL OPERATIONS

Consultando en el catálogo se puede ver el tipo de partición. Observar que el pctfree en dss es 0 ya que no se esperan cambios.
Ahora voy a insertarles 5M de filas en forma aleatoria pero buscando un forma de que se repitan muchas veces valores en las columnas para que se aproveche la compresión
La columna x insertará valores unicos, la columna y insertará letras A y B y la columna z insertará fechas de hasta 10 dias posteriores al test.

rop@ROP11G> set timing on
rop@ROP11G> set autotr on
rop@ROP11G> insert into t
  2  select rownum ,
  3         chr(64+trunc(dbms_random.value(1,3))),
  4         trunc(sysdate)+trunc(dbms_random.value(1,10))
  5  from dual
  6  connect by rownum <= 5000000;

5000000 filas creadas.

Transcurrido: 00:02:57.65

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739

-------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |      |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | T    |       |            |          |
|   2 |   COUNT                        |      |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   3 - filter(ROWNUM<=5000000)


Estadísticas
----------------------------------------------------------
       4918  recursive calls
     304059  db block gets
      67349  consistent gets
          5  physical reads
  292595868  redo size
        875  bytes sent via SQL*Net to client
        690  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

rop@ROP11G> commit;

Confirmación terminada.

Transcurrido: 00:00:00.06
rop@ROP11G> insert into t_c_dss
  2  select rownum ,
  3         chr(64+trunc(dbms_random.value(1,3))),
  4         trunc(sysdate)+trunc(dbms_random.value(1,10))
  5  from dual
  6  connect by rownum <= 5000000;

5000000 filas creadas.

Transcurrido: 00:02:48.43

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739

----------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |         |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | T_C_DSS |       |            |          |
|   2 |   COUNT                        |         |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|         |       |            |          |
|   4 |     FAST DUAL                  |         |     1 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - filter(ROWNUM<=5000000)


Estadísticas
----------------------------------------------------------
       4644  recursive calls
     276921  db block gets
      60593  consistent gets
          0  physical reads
  290874948  redo size
        875  bytes sent via SQL*Net to client
        696  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

rop@ROP11G> commit;

Confirmación terminada.

Transcurrido: 00:00:00.04

rop@ROP11G> insert into t_c_oltp
  2  select rownum ,
  3         chr(64+trunc(dbms_random.value(1,3))),
  4         trunc(sysdate)+trunc(dbms_random.value(1,10))
  5  from dual
  6  connect by rownum <= 5000000;

5000000 filas creadas.

Transcurrido: 00:08:58.00

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1350848739

-----------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |          |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | T_C_OLTP |       |            |          |
|   2 |   COUNT                        |          |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|          |       |            |          |
|   4 |     FAST DUAL                  |          |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   3 - filter(ROWNUM<=5000000)


Estadísticas
----------------------------------------------------------
       7402  recursive calls
     450484  db block gets
      93862  consistent gets
          3  physical reads
  945961224  redo size
        877  bytes sent via SQL*Net to client
        697  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

Los tiempos de inserción convencional para cada tablas fueron:

T        = 2m 57s
T_C_DSS  = 2m 48s
T_C_OLTP = 8m 54s

El tiempo de inserción sobre la tabla oltp fue 3 veces mayor al de la tabla dss y la tabla común, pero lo mas llamativo es la cantidad de redo usado:

Redo Insert en T        =  292595868 ~=  279Mb  
Redo Insert en T_C_DSS  =  290874948 ~=  277Mb
Redo Insert en T_C_OLTP =  945961224 ~=  902Mb

También se observa que la cantidad de redo es de mas de 3 veces.
Busqué en metalink y la nota "COMPRESS FOR ALL OPERATIONS generates lot of redo [ID 829068.1]" declara que es esperable bastante más consumo de redo en las operaciones dml sobre una tabla con compresión avanzada (CA).

De todas formas, tarda mas pero veamos si comprimió y cuanto:

rop@ROP11G> select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments
  2  where segment_name in ('T','T_C_DSS','T_C_OLTP');

SEGMENT_NAME                       BLOCKS         MB
------------------------------ ---------- ----------
T                                   35456        277
T_C_DSS                             31744        248
T_C_OLTP                            11264         88

El factor de compresión sobre la table T_C_OLTP fue importante comparado con las otras dos tablas. Tambien observamos que la relación de 3 se sigue manteniendo ya que la tabla oltp aloca 3 veces menos espacio que las tablas con compresión dss y sin compresión que alocaron espacio similar. Parece que sacrificando mayor consumo de redo y más tiempo de procesamiento del insert tuvo sus resultados, no?.

La próxima prueba será analizar los inserts directos. Recordemos que este tipo de insert es muy usado para carga masiva , en especial en sistemas Datawarehouse (ETL).
Voy a testear la inserción de 1M de filas:

rop@ROP11G> insert /*+ APPEND */ into t
  2  select rownum ,
  3         chr(64+trunc(dbms_random.value(1,3))),
  4         trunc(sysdate)+trunc(dbms_random.value(1,10))
  5  from dual
  6  connect by rownum <= 1000000;

1000000 filas creadas.

Transcurrido: 00:00:29.37

  1  insert /*+ APPEND */ into t_c_dss
  2  select rownum ,
  3         chr(64+trunc(dbms_random.value(1,3))),
  4         trunc(sysdate)+trunc(dbms_random.value(1,10))
  5  from dual
  6* connect by rownum <= 1000000
rop@ROP11G> /

1000000 filas creadas.

Transcurrido: 00:00:32.01
rop@ROP11G> commit;

Confirmación terminada.

Transcurrido: 00:00:00.07
rop@ROP11G> ed
Escrito file afiedt.buf

  1  insert /*+ APPEND */ into t_c_oltp
  2  select rownum ,
  3         chr(64+trunc(dbms_random.value(1,3))),
  4         trunc(sysdate)+trunc(dbms_random.value(1,10))
  5  from dual
  6* connect by rownum <= 1000000
rop@ROP11G> /

1000000 filas creadas.

Transcurrido: 00:00:30.01
rop@ROP11G> commit;

Confirmación terminada.

Transcurrido: 00:00:00.01

Con el insert directo los tiempos fueron similares:

T        = 29s
T_C_DSS  = 32s
T_C_OLTP = 30s

y el espacio alocado:

rop@ROP11G> ed
Escrito file afiedt.buf

  1  select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments
  2* where segment_name in ('T','T_C_DSS','T_C_OLTP')
rop@ROP11G> /

SEGMENT_NAME                       BLOCKS         MB
------------------------------ ---------- ----------
T                                   48768        381
T_C_DSS                             33792        264
T_C_OLTP                            13312        104

De los resultados de arriba vemos que el insert directo incremento el espacio alocado de cada segmento de la siguiente manera:

SEGMENT_NAME                   deltha en Mb
------------------------------ -------
T                              104 (381-277)
T_C_DSS                        16  (264-248)
T_C_OLTP                       16  (104-88)

De estos resultados se deduce que la compresión avanzada y la basica funcionaron igual para los insert directos, ambas solo tuvieron que alocar 16Mb adicionales para acomodar 1M de filas nuevas. Sin embargo la tabla si compresión tuvo que alocar un 25% extra.

Otro punto que me interesa testear son los update's y ver como se comporta este tipo de operación en cada caso. Para probar esto voy a cambiar la columna en 50000 filas.

rop@DESA11G> update t set y='C' where y = 'B' and rownum <= 50000;

50000 filas actualizadas.

Transcurrido: 00:00:01.96

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3603919313

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      | 50000 |  1562K| 11537   (3)| 00:02:19 |
|   1 |  UPDATE             | T    |       |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T    |  2443K|    74M| 11537   (3)| 00:02:19 |
----------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=50000)
   3 - filter("Y"='B')

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


Estadísticas
----------------------------------------------------------
        258  recursive calls
       1667  db block gets
       1436  consistent gets
       1028  physical reads
    7656052  redo size
        847  bytes sent via SQL*Net to client
        574  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
      50000  rows processed

rop@DESA11G> commit;

Confirmación terminada.

rop@DESA11G> ed
Escrito file afiedt.buf

  1* update t_c_dss set y='C' where y = 'B' and rownum <= 50000
rop@DESA11G> /

50000 filas actualizadas.

Transcurrido: 00:00:01.84

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 2179303604

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |         | 50000 |  1562K|  9285   (3)| 00:01:52 |
|   1 |  UPDATE             | T_C_DSS |       |       |            |          |
|*  2 |   COUNT STOPKEY     |         |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T_C_DSS |  2512K|    76M|  9285   (3)| 00:01:52 |
-------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=50000)
   3 - filter("Y"='B')

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


Estadísticas
----------------------------------------------------------
        257  recursive calls
      51518  db block gets
        827  consistent gets
        890  physical reads
   15677976  redo size
        867  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
      50000  rows processed

rop@DESA11G> commit;

Confirmación terminada.

Transcurrido: 00:00:00.01
rop@DESA11G> ed
Escrito file afiedt.buf

  1* update t_c_oltp set y='C' where y = 'B' and rownum <= 50000
rop@DESA11G> /

50000 filas actualizadas.

Transcurrido: 00:01:09.89

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 2618608701

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |          | 50000 |  1562K|  3791   (8)| 00:00:46 |
|   1 |  UPDATE             | T_C_OLTP |       |       |            |          |
|*  2 |   COUNT STOPKEY     |          |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T_C_OLTP |  2814K|    85M|  3791   (8)| 00:00:46 |
--------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=50000)
   3 - filter("Y"='B')

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


Estadísticas
----------------------------------------------------------
        411  recursive calls
    6153098  db block gets
    4894807  consistent gets
       1567  physical reads
   40901448  redo size
        868  bytes sent via SQL*Net to client
        581  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
      50000  rows processed

rop@DESA11G> commit;

Confirmación terminada.

Transcurrido: 00:00:00.01

Los tiempos del update de la columna y en cada caso fueron:

           Tiempo   Redo   Db block gets
           ------   ----   -------------
T          1s        7Mb   1667
T_C_DSS    1s       15Mb   51518
T_C_OLTP   1m 9s    39Mb   6153098

Los tiempos en la tabla oltp para el update masivo son muy superiores con respecto a los tiempos de la misma operacion sobre las otras tablas. Intenté probar de realizar el mismo update pero con 1M de filas y si bien para las tablas t y dss los tiempos fueron de menos de 2 minutos para el caso de la tabla oltp no terminó y pasadas 2 horas tuve que matar la sesión. Lo probé 3 veces en dias distintos y en los tres casos tuve que suspender la ejecución.

En el ultimo test voy a ver como se comportan los select's. Para ver detalle voy a activar el trace 10046. La sentencia que armé recorre toda la tabla:

rop@DESA11G>  ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Sesión modificada.

Transcurrido: 00:00:00.07
rop@DESA11G> ed
Escrito file afiedt.buf

  1  select z,count(*),max(z)
  2  from t
  3* group by z
rop@DESA11G> /

Z           COUNT(*) MAX(Z)
--------- ---------- ---------
18-FEB-10     666293 18-FEB-10
17-FEB-10     665982 17-FEB-10
20-FEB-10     667869 20-FEB-10
16-FEB-10     666656 16-FEB-10
23-FEB-10     665526 23-FEB-10
19-FEB-10     666827 19-FEB-10
22-FEB-10     665960 22-FEB-10
21-FEB-10     666494 21-FEB-10
24-FEB-10     668393 24-FEB-10

9 filas seleccionadas.

Transcurrido: 00:00:09.89
rop@DESA11G> ed
Escrito file afiedt.buf

********************************************************************************

select z,count(*),max(z)
from t
group by z

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     10.33       9.55      41379      42190          0           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     10.35       9.57      41381      42192          0           9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82

Rows     Row Source Operation
-------  ---------------------------------------------------
      9  HASH GROUP BY (cr=42190 pr=41379 pw=0 time=0 us cost=11949 size=44228052 card=4914228)
6000000   TABLE ACCESS FULL T (cr=42190 pr=41379 pw=0 time=29090 us cost=11414 size=44228052 card=4914228)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                              334        0.00          0.02
  SQL*Net message from client                     2        0.02          0.02
********************************************************************************


select z,count(*),max(z)
from t_c_dss
group by z

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     10.71      10.02      33071      33680          0           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     10.72      10.04      33073      33682          0           9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82

Rows     Row Source Operation
-------  ---------------------------------------------------
      9  HASH GROUP BY (cr=33680 pr=33071 pw=0 time=0 us cost=9723 size=46852029 card=5205781)
6000000   TABLE ACCESS FULL T_C_DSS (cr=33680 pr=33071 pw=0 time=0 us cost=9155 size=46852029 card=5205781)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                              270        0.01          0.02
  SQL*Net message from client                     2        0.02          0.02
********************************************************************************

select z,count(*),max(z)
from t_c_oltp
group by z

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      9.82       9.42      12852      13635          0           9
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      9.83       9.42      12854      13637          0           9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82

Rows     Row Source Operation
-------  ---------------------------------------------------
      9  HASH GROUP BY (cr=13635 pr=12852 pw=0 time=0 us cost=4271 size=50894532 card=5654948)
6000000   TABLE ACCESS FULL T_C_OLTP (cr=13635 pr=12852 pw=0 time=0 us cost=3651 size=50894532 card=5654948)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                              110        0.00          0.00
  SQL*Net message from client                     2        0.02          0.02

Los tiempos en resolver la consulta fueron:

T        = 9.57s
T_C_DSS  = 10.04s
T_C_OLTP = 9.42s

El select sobre la tabla oltp fue el que menos tiempo arrojó, demostrando que no hubo overhead por el tema de la compresión.

Como conclusión general se ve que para operaciones masivas los tiempos y el consumo de redo sobre la tabla oltp son importantes y hay que tenerlos en cuenta. Tambien consideremos que este mecanismo, tal cual su nombre lo infiere, esta pensado para bases oltp en donde no es común el cambio masivo, aunque tampoco es tan infrecuente en ciertos casos, ya que muchas bases hibridas se comportan como oltp (por ejemplo durante el dia) y por la noche se usan como DSS con importante procesamiento batch que generalmente produce cambios masivos de datos.

En el paper oficial: "Advanced Compression with Oracle 11g R2" dice que las escrituras puntuales no se comprimen en cada operación y que la compresión de todo el bloque se realiza en forma batch cuando se alcanza un umbral en el bloque. Seguramente por ese motivo en mi test los tiempos fueron excesivos ya que al insertar o modificar muchas filas se alcanzó el umbral en varios bloques disparando la compresión en vivo y sumando tiempo al procesamiento de la sentencia.

A mi siempre me gusta testear a fondo cada nuevo feature para ver si lo puedo recomendar a mis clientes ya que no todo lo que brilla es oro y a veces existen restricciones que lo hacen inaplicable en ciertos negocios. Además hay que considerar el tema economico, la compresión avanzada, a diferencia de la compresión básica que viene habilitada para usar con la licencia de Enterprise Edition, se paga como un opcional aparte y por lo tanto es un factor no menor que merece se analizado antes de implementar este nuevo feature.

Verificando el uso de "Buenas Practicas" en código PL/SQL

Para verificar si se aplican "Buenas Practicas" de programación y para contribuir a realizar código mas robusto y menos propenso a que se generen errores en tiempo de ejecución, a partir de 10g R1, se introdujo un nuevo mecanismo que permite advertir en tiempo de compilación sobre potenciales problemas (WARNINGS), que si bien dejan compilada la unidad de código, pueden darnos dolores de cabeza y conducir a que las aplicaciones que utilizan dicho código generen errores imprevistos o peor aún, que no se obtengan los datos correctos alterando la semántica pretendida y siendo, en muchas ocasiones, muy complicados de detectar.

Existe 4 categorias de WARNINGS:

SEVERE : Pueden causar acciones inesperadas, errores que hagan
cancelar una operatoria o resultados erroneos.

PERFORMANCE : Pueden causar problemas de rendimiento

INFORMATIONAL: No afectan el rendimiento ni altera los resultados pero
advierte sobre complicaciones en el mantenimiento del
codigo a futuro.

ALL : Contempla todos los casos anteriores.


Para activar los mensajes de warning se puede usar el parametro PLSQL_WARNINGS a nivel sesion o a nivel instancia (cosa que no recomiendo), tambien se puede usar el paquete DBMS_WARNING para setear el nivel de warning deseado a nivel de código PL en procedures, packages, triggers, etc. Consultando la vista [USER | ALL | DBA]_WARNING_SETTINGS se puede saber que objetos tienen activado el warning y consultando la vista [USER | ALL | DBA]_ERRORS, filtrando por el campo ATTRIBUTE='WARNING' se ven todos los warnings generados.

Ahora que ya hice una introduccion rapida al tema, vayamos a los ejemplos:

Habilito para detectar todas las categorias:

rop@DESA10G> alter session set plsql_warnings='ENABLE:ALL';

Sesión modificada.

Creo una tabla sencila

rop@DESA10G> create table t (x int,y varchar(5));

Tabla creada.

rop@DESA10G> insert into t
  2          select rownum,
  3                 trunc(dbms_random.value(1,99999))
  4          from dual
  5          connect by rownum <= 100000;

100000 filas creadas.

rop@DESA10G> commit;

Confirmación terminada.

Voy a crear un procedimiento P_PRUEBA1 de forma tal de que se detecte un warning:

rop@DESA10G>ed
  1  create or replace procedure p_prueba1 (p_val int)
  2          is
  3             l_cnt int;
  4          begin
  5              select count(1) into l_cnt
  6              from t
  7              where y = p_val;
  8              if (l_cnt > 0) then
  9                   dbms_output.put_line ('El valor existe en la tabla');
 10              else
 11                   dbms_output.put_line ('El valor NO existe en la tabla');
 12              end if;
 13*         end;
rop@DESA10G> /

SP2-0804: Procedimiento creado con advertencias de compilación

rop@DESA10G> select text from user_errors where name = 'P_PRUEBA1';

TEXT
----------------------------------------------------------------------------------------------------
PLW-07204: puede que la conversión que no sea de tipo de columna dé como resultado un plan de consulta subóptimo

En el caso de arriba detectó un potencial problema de performance, ya que al comparar la columna "y" de tipo varchar2 con el parametro "p_val" de tipo number se hace una conversión implicita TO_NUMBER() de la columna "y". Oracle siempre pasa a number cuando se comparan los tipos number y char/varchar2.
Veamos otros ejemplos:

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create or replace procedure p_prueba2 (p_val int)
  2          is
  3             l_cnt int;
  4          begin
  5              select count(1) into l_cnt
  6              from t
  7              where y = to_char(p_val);
  8              if ( 0 = 0) then
  9                 if (l_cnt > 0) then
 10                      dbms_output.put_line ('El valor existe en la tabla');
 11                 else
 12                      dbms_output.put_line ('El valor NO existe en la tabla');
 13                 end if;
 14              else
 15                 null;
 16              end if;
 17*         end;
rop@DESA10G> /

SP2-0804: Procedimiento creado con advertencias de compilación

rop@DESA10G> select text from user_errors where name = 'P_PRUEBA2';

TEXT
----------------------------------------------------------------------------------------------------
PLW-06002: Código inaccesible

Este es una advertencia informativa. Ahora voy a crear una función:

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create or replace function f_prueba1
  2          return int
  3  is
  4    l_val int;
  5    begin
  6        l_val := dbms_random.value(1,10);
  7*   end;
rop@DESA10G> /

SP2-0806: Función creada con advertencias de compilación

rop@DESA10G> select text from user_errors where name = 'F_PRUEBA1';

TEXT
----------------------------------------------------------------------------------------------------
PLW-05005: la función F_PRUEBA1 se devuelve sin valor en la línea 7

al no retornar valor se podria generar un problema mas grave

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create or replace procedure p_prueba3 (p_val varchar2)
  2          is
  3          begin
  4              insert into t (x) values (p_val);
  5*         end;
rop@DESA10G> /

SP2-0804: Procedimiento creado con advertencias de compilación

rop@DESA10G>  select text from user_errors where name = 'P_PRUEBA3';

TEXT
----------------------------------------------------------------------------------------------------
PLW-07202: el tipo de enlace daría como resultado una conversión lejos del tipo de columna

rop@DESA10G>

El warning para el procedimiento P_PRUEBA3, aunque la traducción al español no es muy clara, da un posible problema en la conversión de tipos. Asi podriamos seguir probando otros tantos casos.
La idea fue mostrarles que con esta herramienta se puede mejorar la calidad del software pl/sql y detectar en forma automatica y proactiva posibles problemas en tiempo de ejecución.

Es realmente necesario reconstruir los índices periodicamente?

Hoy me gustaria comentar algo respecto al mantenimiento de índices, en particular los de tipo mas común (B*TREE). Existe un gran debate en los foros sobre si es necesario o no realizar reconstrucciones (rebuild) de indice cada cierto tiempo. Principalmente existen dos corrientes: a) la pragmatica, encabezada por Burleson (ver: Burleson: Index Rebuild Approach)que sin probar nada y exclamando tener basta experiencia práctica sobre sistemas reales promueve la necesidad de detección y reconstrucción periodica de indices desbalanceados o con alocación redundante y b) la cientifica con sus dos grandes exponentes: Tom Kyte y sobre todo Jonathan Lewis (ver: Lewis: Index Rebuild Approach)quienes siempre prueban y demuestran con una formalidad cuasi-matematica cada situación y que dicen que por definicón los indices son balanceados y solo en raras ocasiones se justifica un rebuild. Cualquiera que haya visto alguno de mis otras notas se dará cuenta que mi intención es probar tal cual lo hacen los dos gurues del caso b, obviamente ellos estan en un nivel superlativo y yo humildemente trato de imitar sus metodos de prueba.
El enfoque pragmatico (llamese Burleson y sus amigos) dicta que un índice con blevel (la cantidad de bloques del arbol b*tree hasta llegar a la hoja) de 4 o mayor (valga la aclaración que un índice con un blevel mayor a 3 es muy raro) o un indice cuya tabla tenga un ratio de cambios alto (sobre todo inserts y deletes) provoca que la alocación en las hojas sea muy ineficiente por lo cual una reconstrucción haría que los datos se compacten, ocupen menos hojas y por lo tanto aloquen menos espacio. Esto ultimo no es tan infrecuente como el caso del blevel mayor a 4 pero si pensamos un segundo, al reconstruir compactamos pero cto tardará en llenarse nuevamente?, si la operatoria es insert y luego delete tendría que pensar en un proceso diario o al menos semanal que llevará su tiempo y espacio adicional ya que cuando se reconstruye internamente (ya sea online o no) se va creando una copia, luego se renombra y se borra el viejo. Si no tenemos ventana de mantemiento tenemos que tener en cuenta que habrá que hacerlo online (desde 9i esto es posible).
El enfoque científico prueba que reconstruir en forma periodica los indices solo servirá para tener una posible ganancia (o tal vez perdida) en performance y para ganar espacio temporalmente dado que el clustering_factor (el dato mas importante para un indice, para que el optimizador sepa cuando es realmente necesario usarlo para armar el plan de acceso) no cambia con la reconstrucción.
Al margen de esta discusión, suena lógico que luego de 30 años desde la primera version de Oracle (Oracle 2) sea necesario reconstruir en forma sistematica y periodica los indices "ineficientes"?, algunos habrán notado que desde 10g existen tareas de mantenimiento automatizadas de segmentos por default que dan recomendaciones para liberar de ciertas tareas a los dba's (SEGMENT ADVISOR) y que hay casos donde una de las recomendaciones es reconstruir indices con el fin de recuperar espacio (shrink). Si bien esta sugerencia es licita, es realmente necesario?. Tal vez podría servirnos si una cierta tabla tuvo alguna carga y luego un borrado masivo y que no se prevean nuevas operaciones masivas. En ese caso se liberaría espacio.
Podría pensarse que al reconstruir se compactarán los hojas ,y en ciertos casos bajará el blevel, y por lo tanto un "range scan" recorrerá menos bloques hoja, pero si pensamos en que la tabla asociada se llenará masivamente en un corto plazo tendremos la contra de que el indice se tendrá que ir balanceando nuevamente y que esa tarea demandará split de bloques y contención dml. La ganancia es tan importante para tener que reconstruir todo el tiempo los indices sobre tablas con alto ratio de inserts/deletes?.
En conclusión, yo creo que la reconstrucción es necesaria en los siguientes casos: 1. cuando una tarea admistrativa deje en estado unusable a los indices (ej: operaciones sobre particiones sin actualizar los indices globales, move de tablas, sqlldr directo, etc), 2. cuando por un tema de espacio sea necesario realocar el indice en otro tablespace ó 3. cdo sea realmente justificado (y no por la dudas) como explique mas arriba.

A continuación voy a mostrar los efectos de la reconstrucción y que datos estadisticos cambian:

Primero voy a llenar una tabla con 1,5M de filas, uso la vista all_objects de base

rop@TEST10G> create table t as select * from all_objects;

Tabla creada.

rop@TEST10G> insert into t select * from t;

94773 filas creadas.

rop@TEST10G> /

189546 filas creadas.

rop@TEST10G> /

379092 filas creadas.

rop@TEST10G> /

758184 filas creadas.

rop@TEST10G> commit;

Confirmación terminada.

rop@TEST10G> select count(1) from t;

  COUNT(1)
----------
   1516368

Ahora voy a crear un indice por las columnas object_id y object_name

rop@TEST10G> create index t_idx on t(object_id,object_name);

Índice creado.

Voy a analizar la tabla y su indice con el viejo ANALYZE, pero para esta prueba sirve:

rop@TEST10G> analyze table t compute statistics for all indexes;

Tabla analizada.

Vamos a ver solo las estadisticas que me interesan mostrar en esta prueba:

rop@TEST10G> select blevel,leaf_blocks,clustering_factor from user_ind_statistics
  2  where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         2        8620           1516368

El blevel o cantidad de bloques hasta llegar a las hojas es 2, la cantidad de hojas es 8620 y el clustering_factor es 1516368, es decir igual a la cantidad de filas (ver clustering factor)

En este punto voy a eliminar masivamente casi todas las filas de la tabla, analizarla nuevamente y ver como cambiaron las estadisticas que me interesan del índice

rop@TEST10G> delete from t where rownum <= 1500000;

1500000 filas suprimidas.

rop@TEST10G> commit;

Confirmación terminada.

rop@TEST10G> analyze table t compute statistics for all indexes;

Tabla analizada.

rop@TEST10G> 
rop@TEST10G>   select blevel,leaf_blocks,clustering_factor from user_ind_statistics
  2   where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         2        8620              2142

Observamos qeu la cantidad de hojas y el blevel quedaron iguales aunque ahora la tabla tiene muy pocas filas. El clustering factor (cf) cambió ya que se adecúo a los nuevos datos (recordemos que el cf es una relación entre datos de la tabla y el indice)
Haciendo un rebuild del indice t_idx veremos como cambiaron las estadísticas

rop@TEST10G> alter index t_idx rebuild;

Índice modificado.

rop@TEST10G>  select blevel,leaf_blocks,clustering_factor from user_ind_statistics
  2  where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         1          99              2142

rop@TEST10G> 

Como vemos se redujeron notablemente la cantidad de hojas y tambien bajó el blevel, pero el cf quedó exactamente igual luego del rebuild.

Como manejar de forma efectiva la recolección de estadísticas de segmentos de la base de datos

Las estadísticas son una colección de datos que describe en detalle a la base de datos y a cada uno de sus objetos. Son utilizadas por el optimizador para escoger el mejor plan de ejecución posible para cada sentencia. Estas estadísticas se usan para propósitos de optimización de consultas y no tienen relación con las estadísticas de performance visibles desde las vistas V$. Se almacenan en el catalogo de la base y entre la información que describen esta la siguiente:

Estadísticas de Tabla
Numero de Filas
Numero de Bloques
Largo Promedio de Fila
Estadísticas de Columna
Numero de valores distintos de la columna (selectividad)
Numero de nulos en la columna
Distribución de los datos (histogramas)
Estadísticas de Índice
Numero de bloques hoja
Niveles
Factor de Agrupamiento (Clustering Factor)
Estadísticas de Sistema
Utilización y Rendimiento de I/O
Utilización y Rendimiento de CPU's

Debido a que los objetos de la base de datos van cambiando las estadísticas deben ser mantenidas regularmente para así evitar el uso de planes erróneos basados en información desactualizada.

Recolección de Estadísticas Automática

Oracle Corporation recomienda que la recolección de estadísticas sea automática (default desde 10g) para así evitar que queden objetos sin estadísticas o con estadísticas desactualizadas. Este tipo de tareas automáticas, entre otras, se ejecutan dentro de las ventanas de mantenimiento predefinidas inicialmente. Si es necesario recolectar estadísticas en forma manual se debera invocar el paquete DBMS_STATS.
La job GATHER_STATS_JOB es creado automaticamente cuando se crea la base de datos y es manejado por el Scheduler. El Scheduler corre el job cuando la ventana de mantenimiento esta abierta (10pm a 6am de lunes a viernes y durante todo el dia el fin de semana).
El job GATHER_STATS_JOB llama al paquete interno DBMS_STATS.GATHER_DATABASE_STATS_JOBS_PROC que recolecta estadísticas de todos los objetos que no tuvieran ninguna estadística anteriormente y de aquellos objetos que hayan sido modificados significativamente (mas del 10% de las filas). La recolección de estadísticas se realiza primero sobre los objetos que mas cambiaron, fijando asi un esquema de prioridades que garantiza que los objetos con mas necesidad de renovación de estadísticas se actualicen dentro de la ventana de mantenimiento. Con la configuración default, de ser necesario, se continua analizando objetos luego de cerrada la ventana de mantenimiento.

Como verificar si se están recolectando las estadísticas en forma automática

Para asegurarse que se estén recolectando las estadísticas automáticamente chequear:

Que el job que corre las estadisticas este activo:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

Que el monitor de modificaciones este activo:

Verificar que el parámetro STATISTICS_LEVEL se igual a TYPICAL ó ALL
Consideraciones para la recolección de estadísticas

Cuando utilizar estadísticas manuales

La recolección automática deberia ser adecuada para la mayoria de los objetos con un ratio de modificacion moderado. Debido a que el analisis de los objetos se realiza una vez por dia durante la ventana de mantenimiento, que generalmente es por la noche, a veces esta frecuencia no es suficiente para los objetos que sufren muchos cambios en el mismo dia ya que tienen que esperar que se abra la ventana de mantenimiento y por ende los objetos quedan desactualizados rapidamente. Existen dos tipos de objetos con dichas caracteristicas:

• Tablas volatiles que se borran o se truncan y que se vuelven a llenar durante el dia.
• Objetos que sufren cargas masivas que superan el 10% del tamaño total del objeto.

Enfoque de solución para tablas volátiles


• Se puede setear las estadísticas en null. Cuando el optimizador se encuentra con una tabla sin estadísticas dinámicamente obtiene las estadísticas necesarias como parte de la optimización. Este muestreo dinámico es gobernado por el parámetro OPTIMIZER_DYNAMIC_SAMPLING. El valor por default es 2 y no debería ser menor al default.
Para dejar las estadísticas nulas en una tabla hay que borrarlas y luego bloquearlas.
Por ejemplo, si quisieramos que al tabla ROP.T no tuviera estadísticas nunca hacemos:

begin
dbms_stats.delete_table_stats('ROP','T');
dbms_stats.lock_table_stats('ROP',''T');
end;

• Las estadísticas se podrían setear a valores que representen el estado típico de la tabla. Para implementarlo se deberá recolectar estadísticas en el momento mas adecuado y luego bloquear las estadísticas de la tabla en cuestión.


Enfoque de solución para objetos con carga masiva


• Para tablas con carga masiva las estadísticas deberán recolectarse inmediatamente luego de la carga. Es recomendable que la sentencia de actualización de estadísticas sean parte del script o proceso de carga.

• Los procesos automáticos de recolección de estadísticas no consideran las tablas externas. Para analizar este tipo de tablas habrá que hacerlo manualmente y cada vez que hay cambios sustanciales en el archivo que define la tabla externa.

• Las estadísticas de sistema no son analizadas automáticamente y deben ser analizadas manualmente. Se recomienda recolectar estadísticas de sistema para así darle mas información al optimizador.


Recolección manual de Estadísticas


Si por algún motivo se deshabilita la recolección automática se requerirá la recolección manual usando el paquete predefinido DBMS_STATS. Este paquete también permite modificar,ver, exportar, importar, setear y borrar estadísticas.
DBMS_STATS puede recolectar estadísticas de tablas, índices, columnas individuales y particiones. Cuando se generan estadísticas para una tabla, columna o índice y el diccionario de datos ya posee estadísticas para el objeto en cuestión Oracle modifica los valores existentes pero permite, de ser necesario en el futuro, los valores anteriores.
Cuando se actualizan las estadísticas para un objeto dado, Oracle invalida cualquier sentencia que estuvieran parseada y que referencie al objeto analizado. La próxima vez que se ejecute la sentencia ser va a reparsear. El optimizador podría elegir un nuevo plan basado en las nuevas estadísticas recolectadas. Las sentencias distribuidas que referencien objetos remotos con recolección de estadísticas reciente no se invalidaran. Las nuevas estadísticas no tomaran efecto hasta que la sentencia se vuelva a parsear.
Los procedimientos de DBMS_STATS para recolectar estadísticas son:


GATHER_INDEX_STATS : Estadísticas para índice
GATHER_TABLE_STATS : Estadísticas para tabla, índice y columna.
GATHER_SCHEMA_STATS : Estadísticas para todos los objetos del esquema.
GATHER_DICTIONARY_STATS : Estadísticas para todos los objetos del diccionario.
GATHER_DATABASE_STATS : Estadísticas para todos los objetos de la base de datos.


Recolección de estadísticas utilizando el paquete DBMS_STATS


• Recolección de estadísticas utilizando muestreo (sampling)
• Recolección de estadísticas en paralelo.
• Estadísticas sobre objetos particionados.
• Estadísticas de columnas e histogramas
• Como determinar estadísticas desactualizadas (stale)


Recolección de estadísticas utilizando muestreo (sampling)

Las operaciones de recolección de estadísticas pueden utilizar muestreo para estimar las estadísticas y de esta forma minimizar los recursos y el tiempo necesarios para el análisis ya que sin utilizar muestreo se debe hacer full scan y ordenamiento de las tablas enteras. El muestreo se especifica usando el argumento ESTIMATE_PERCENT en la invocación de paquete DBMS_STATS.
Oracle recomienda utilizar DBMS_STATS.AUTO_SAMPLE_SIZE para maximizar el rendimiento y al mismo tiempo obtener la precisión estadística mas adecuada. De esta forma se delega al motor de base de datos el calculo del porcentaje de filas a evaluar (muestreo) basado en las propiedades de cada objeto.
Cuando el parámetro ESTIMATE_PERCENT es especificado manualmente, los procedimientos de recolección de DBMS_STATS pueden incrementar el porcentaje de muestreo si el valor especificado no produjera un muestreo lo suficientemente grande.

Recolección de estadísticas en paralelo

Las operaciones de recolección de estadísticas pueden corren en serie o en paralelo. Le grado de paralelismo es expresado definiendo el parámetro DEGREE del paquete DBMS_STATS. Es recomendable utilizar la función DBMS_STATS.AUTO_DEGREE. De esta forma es Oracle el que elige el grado de paralelismo más apropiado basandose en el tamaño del objeto a analizar y en los parámetros de paralelismo definidos.


Estadísticas sobre objetos particionados

DBMS_STATS puede recolectar estadísticas en forma separada para subparticiones, particiones o estadísticas globales para una tabla o índice completos. El tipo de estadísticas para tablas particionadas se especifica por medio del parámetro GRANULARITY
Dependiendo del tipo de sentencia, el optimizador puede optar por usar estadísticas a nivel partición (o subpartición) o estadísticas de la tabla o índice completos. Es recomendable dejar el parámetro GRANULARITY seteado en AUTO que es el valor default ya que de esta forma Oracle determina la granularidad mas adecuada dependiendo del tipo de partición.

Estadísticas de columnas e histogramas

Cuando se recolectan estadísticas sobre una tabla se obtiene información de la distribución de datos de las columnas. Como información básica de la distribución se obtiene el valor mínimo y máximo pero esto no es suficiente si los datos en la columna son muy sesgados. Para valores no uniformes se necesitan histogramas que describen la distribución de los datos para una columna dada. Los histogramas se generan seteando el parámetro METHOD_OPT en los procedimientos de recolección (gather_xxx_stats) del paquete DBMS_STATS. Oracle recomienda setear el parámetro METHOD_OPT a "for all columns size auto" con lo cual se determina automáticamente que columna necesita histograma y se define el tamaño de "bucket".


Como determinar si las estadísticas están desactualizadas (stale)

Para determinar si un objeto esta necesitando nuevas estadísticas, Oracle provee un mecanismo de monitoreo que es habilitado por default cuando el parámetro STATISTICS_LEVEL esta configurado en TYPICAL o ALL. La información de los cambios (INSERT/UPDATE/DELETE) sobre las tablas se almacena en la vista USER_TAB_MODIFICATIONS.
Si las tablas monitoreadas fueron modificadas en mas del 10% del total de sus filas entonces sus estadísticas son consideradas STALE y serán analizadas la próxima vez que se ejecuten los procedimientos de DBMS_STATS: GATHER_DATABASE_STATS o GATHER_SCHEMA_STATS que definan el parámetro options como GATHER STALE o GATHER AUTO.

Estadísticas de Sistema

Las estadísticas de sistema describen características de hardware tales como rendimiento y utilización de I/O y CPU. Esta información es analizada por el optimizador en la etapa de parsing de las sentencias. El optimizador analiza costos de i/o y cpu para cada sentencia y los utiliza como información adicional para elegir un mejor plan.
Hay dos opciones para recolectar estadísticas de sistema: 1) se analiza la actividad del sistema en un periodo de tiempo especifico (workload statistics) o 2) se simula carga de trabajo (noworkload statistics). El procedimiento utilizado para recolectar estadísticas de sistema es DBMS_SPACE.GATHER_SYSTEM_STATS y se necesitan privilegios de DBA para ejecutarlo.
A diferencia de las estadísticas de tablas, índices o columnas, Oracle no invalida las sentencias que ya están parseadas cuando se actualizan las estadísticas de sistema. Las nuevas estadísticas si serán consideradas por las nuevas sentencias parseadas.
Oracle ofrece dos opciones para recolectar estadísticas de sistema:

• Workload Statistics
• NoWorkload Statistics

Workload Statistics

Las estadisticas de carga (workload statistics) fueron introducidas en 9i y recolectan: single read time (sreadtim) y multiblock read time (mreadtim), mbcr, CPU speed, maximum system throughput y average slave throughput. Los valores de sreadtim, mreadtim y mbcr son obtenidos comparando el número de lecturas físicas secuenciales y random entre dos puntos en el tiempo comprendidos entre el principio y el final del intervalo de workload. Las estadísticas de carga dependerán de la actividad que tuvo el sistema durante el periodo de muestra. Si ,por ejemplo, en la muestra se detecta un bajo rendimiento de i/o, se reflejará en las estadísticas y se promoverán planes de ejecución que contemplen menos i/o.
Para recolectar las estadísticas de sistema usar:

Para comenzar a medir ejecutar: Dbms_stats.gather_system_stats(‘start’)
Para finalizar de medir ejecutar : Dbms_stats.gather_system_stats(‘stop’)

ó

Correr dbms_stats.gather_system_stats(‘interval’,interval=>N), donde N es el número de minutos del muestreo.

Para eliminar las estadísticas correr: dbms_stats.delete_system_stats(). Esto borrará las estadísticas de carga y volverá a las estadísticas default (noworkload).

NoWorkload Statistics

La principal diferencia entre workload statistics y noworkload statistics reside en el método utilizado para la obtención de las estadísticas. Este tipo de estadísticas se toma generando lecturas random sobre todos los datafiles al contrario de la toma de estadísticas workload que utilizan contadores que se van actualizando con actividad real de la base de datos. La estadísticas noworkload consisten de i/o transfer speed, i/o seek time, y cpu speed. Oracle utiliza por default valores conservadores para setear la velocidad de i/o. Los variables y sus valores configurados en el primer startup de la base son:

ioseektim = 10ms
iotrfspeed = 4096 bytes/ms
cpuspeednw =

Para recolectar las estadísticas noworkload en forma manual hay que ejecutar: dbms_stats.gather_system_stats(), sin ningún argumento. La recolección puede variar en tiempo de acuerdo a la i/o del sistema y al tamaño de la base de datos. Si se recolectan estadísticas workload las estadísticas noworkload serán ignoradas y no se utilizaran en el futuro.

Vistas donde se almacenan estadísticas

Las estadísticas se guardan en el catalogo de la base y pueden ser consultadas desde las siguientes vistas:

[USER | ALL | DBA]_TABLES
[USER | ALL | DBA]_OBJECT_TABLES
[USER | ALL | DBA]_TAB_STATISTICS
[USER | ALL | DBA]_TAB_COL_STATISTICS
[USER | ALL | DBA]_TAB_HISTOGRAMS
[USER | ALL | DBA]_INDEXES
[USER | ALL | DBA]_IND_STATISTICS
[USER | ALL | DBA]_CLUSTERS
[USER | ALL | DBA]_TAB_PARTITIONS
[USER | ALL | DBA]_TAB_SUBPARTITIONS
[USER | ALL | DBA]_PART_COL_STATISTICS
[USER | ALL | DBA]_PART_HISTOGRAMS
[USER | ALL | DBA]_SUBPART_COL_STATISTICS
[USER | ALL | DBA]_SUBPART_HISTOGRAMS

Migrar la programación de rutinas de DBMS_JOB a DBMS_SCHEDULER

La idea de hoy es mostrarles las ventajas que tiene usar el nuevo paquete DBMS_SCHEDULER y asi propiciar a que comiencen a migrar las rutinas programadas en la base usando DBMS_JOB. Este paquete esta actualmente en estado “Deprecated” y solo existe por cuestiones de compatibilidad hacia atrás. Oracle Corporation recomienda fuertemente la migración de toda la programación desde DBMS_JOB hacia DBMS_SCHEDULER.
Para comenzar a migrar se va a explicar un método simple para migrar bloques anónimos y procedimientos. Si bien la forma mas prolija y estructurada es armar entidades PROGRAMS y SCHEDULER y luego asociarlas por medio de un JOB, vamos enfocarnos en una solución más cercana a la forma de programación antigua (usando dbms_job) para que el impacto de cambio sea menor y así fomentar el uso de dbms_scheduler.

Comparación de programación con DBMS_JOB y DBMS_SCHEDULER
Para comparar los dos paquetes para programación de tareas vamos a usar dos ejemplos de uso común. Se va a programar un bloque anónimo y luego se mostrará como programar la ejecución de código almacenado en la base de datos.

Usando DBMS_JOB

Ejecución de Bloque Anónimo

DECLARE
    l_job int;
BEGIN
  DBMS_JOB.submit (
    job       => l_job,
    what      => '',
    next_date => trunc(SYSDATE)+22/24,
    interval  => 'trunc(SYSDATE+1) + 22/24');   

  COMMIT;
END;

Ejecución de procedimientos

DECLARE
    l_job int;
BEGIN
  DBMS_JOB.submit (
    job       => l_job,
    what      => 'procedimiento>',
    next_date => trunc(SYSDATE)+22/24,
    interval  => 'trunc(SYSDATE+1) + 22/24');   

  COMMIT;
END;

Usando DBMS_SCHEDULER
Ejecución de Bloque Anónimo

Opción 1: Intervalo definido como se define con DBMS_JOB (modalidad vieja)

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_1',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '',
    start_date      => trunc(SYSTIMESTAMP) + 22/24,
    repeat_interval => 'trunc(SYSTIMESTAMP+1) + 22/24',
    enabled => true);
END;

Opción 2: Intervalo definido de forma mas simple y comprensible (modalidad nueva)

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_1',
    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN foo; END;',
    start_date      => trunc(SYSTIMESTAMP) + 22/24,
    repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
    enabled => true);
END;

Como se ve en el ejemplo es muy sencillo definir los intervalos. Incluso se pueden definir días de la semana, días del mes, meses del año, etc. Para mayor detalle ver Calendaring Syntax en el manual “PL/SQL Packages and Types Reference 10g”

Ejecución de procedimientos

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_1',
    job_type        => 'STORED_PROCEDURE',
    job_action      => '',
    start_date      => trunc(SYSTIMESTAMP) + 22/24,
    repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
    enabled => true);
END;

Ventajas de usar DBMS_SCHEDULER

· Permite definir intervalos en forma mas expresiva y simple (Calendaring Sintax).

· Permite darle un nombre significativo al job. Con dbms_job se le asignaba un número interno del sistema.

· Se le puede definir prioridades de ejecución. Se pueden asociar ventanas de ejecución con planes de Resource Manager.

· Guarda registros historicos de detalle de ejecuciones, cantidad de corridas, errores, detalle de los errores, etc.

· Es mucho más sencillo matar un job programado desde dbms_scheduler que un job programado con dbms_job.


Consultas comunes para obtener información de programación DBMS_SCHEDULER

Para mostrar detalle de las corridas de los jobs:

select log_date,
       job_name,
       status,
       req_start_date,
       actual_start_date,
       run_duration
from   dba_scheduler_job_run_details

Para ver los jobs que están corriendo:

select job_name,
       session_id,
       running_instance,
       elapsed_time,
       cpu_used
from dba_scheduler_running_jobs;

Para ver detalle de como están definidos los jobs:

select job_name,
       job_creator,
       job_type,
       job_action,
       start_date,
       repeat_interval,
       next_run_date
       enabled,
       run_count,
       failure_count
from user_scheduler_jobs

Como monitorear el espacio temporal consumido

Es común que DBAs que recien se inician se preocupen cuando en un reporte de estado de tablespaces se muestra al tablespace temporal al 100% de utilización, es decir sin espacio disponible. En ese punto es importante aclarar que la alocación y dealocación de espacio temporal es distinta para los tablespace temporales respecto a los tablespaces de datos y por lo tanto si bien el reporte muestra un tablespace lleno en realidad no implica que no pueda utilizarse. Para obtener el espacio libre real armé la siguiente consulta:

select t2."TempTotal" "TempTotal (Mb)",
       t1."TempUsed" "TempUsed (Mb)",
       t2."TempTotal" - t1."TempUsed" "TempFree (Mb)"
  from (select nvl(round(sum(tu.blocks * tf.block_size) / 1024 / 1024, 2), 0) "TempUsed"
          from v$tempseg_usage tu, dba_tablespaces tf
         where tu.TABLESPACE = tf.tablespace_name) t1,
       (select round(sum(bytes) / 1024 / 1024, 2) "TempTotal"
          from dba_temp_files) t2

Con esta sentencia se puede ir monitoreando el espacio disponible. Puede resultar conveniente su utilización para hacer seguimiento de procesos batch que hayan cancelado por falta de espacio temporal. Es bastante frecuente que un proceso se quede sin espacio cuando las sentencias que lo componen generan un plan que requiere demasiado agrupamiento, agregacion, hashing, producto cartesiano, etc. Si faltaran indices o estadisticas en los segmentos involucrados se podría armar un plan erroneo que requiera mas espacio temporal que el necesario. Si las sentencias estuvieran bien escritas, los objetos con estadisticas actualizadas y con el esquema de indexacion adecuado podria estar subestimado el espacio temporal y se necesite un redimensionamiento.