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

Lo nuevo en Particionamiento de tablas (Partitioning)

La version 11g ofrece nuevas funcionalidades para crear y mantener tablas particionadas. En esta primera nota les voy a mostrar como usar el particionamiento por intervalos, las columnas virtuales para particionar y el particionamiento de sistema.

Usando partionamiento por intervalos

Una de la tareas mas habituales cuando se trabaja con particionamiento es la creación de nuevas particiones, por ejemplo, en un tipico particionamiento por año y mes hay que ir creando de antemano las particiones con el transcurso de tiempo. Generalmente se crea una partición delimitada por MAXVALUE y todo lo que se pase de la ultima partición definida explicitamente va a parar a la partición definida con delimitador máximo, si se llegaran juntar mas de un mes se requerira realizar split para emplolijar la tabla. Si bien todas estas tareas se pueden automatizar de forma tal de programar la creación automatica de las particiones, resulta mas efectivo que directamente Oracle cree la particion adecuada solo cuando se necesite, bajo demanda. En 11g se puede definir un intervalo con lo cual se le define la directiva precisa de como realizar el partionamiento. Como siempre, todo se entiende mejor con un sencillo ejemplo de uso:

Primero voy a crear una tabla, tal cual la crearia en 10g (sin intervalos):

rop@ROP111> CREATE TABLE T_10G 
  2     (C1 NUMBER(38,0), 
  3      C2 VARCHAR2(10), 
  4      C3 DATE
  5     ) 
  6  PARTITION BY RANGE (C3)  
  7  (PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

Tabla creada.

Ahora voy a usar la nueva sintaxis y le voy a definir el intervalo usando la función NUMTOYMINTERVAL(1,'MONTH'), para que cree una particion por año,mes.

rop@ROP111> CREATE TABLE T_11G 
  2     (C1 NUMBER(38,0), 
  3      C2 VARCHAR2(10), 
  4      C3 DATE
  5     ) 
  6  PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
  7  (PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

Tabla creada.

rop@ROP111> insert into t_10g values (1,'A',to_date('2009-05-01','YYYY-MM-DD'));
insert into t_10g values (1,'A',to_date('2009-05-01','YYYY-MM-DD'))
            *
ERROR en línea 1:
ORA-14400: la clave de partición insertada no corresponde a ninguna partición

rop@ROP111> insert into t_11g values (1,'A',to_date('2009-05-01','YYYY-MM-DD'));

1 fila creada.

Vamos a consultar el diccionario para ver que efectivamente se haya agregado en forma automatica la nueva partición:

rop@ROP111> select partition_name,high_value from user_tab_partitions where table_name = 'T_11G';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------------------------------------
P0902                          TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

SYS_P44                        TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

Como se ve, se agregó la partición SYS_P44. El nombre que le asignó es un nombre generado por el sistema y no sigue el formato deseado asi que lo voy a renombrar.

rop@ROP111> alter table t_11g rename partition SYS_P44 to p0905;

Tabla modificada.

Ahora, voy a insertar una fila mas con fecha del 1.1.2010:

rop@ROP111> insert into t_11g values (2,'B',to_date('2010-01-01','YYYY-MM-DD'));

1 fila creada.

rop@ROP111>  select partition_name,high_value from user_tab_partitions where table_name = 'T_11G';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------------------------------------
P0902                          TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

P0905                          TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

SYS_P45                        TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

Como se ve, se agregó otra partición para "acomodar" la nueva fila a su correspondiente partición.

También podemos consultar la información de como se definió el intervalo usando la nueva columna INTERVAL:

  1* select table_name,partitioning_type,interval from user_part_tables
rop@ROP111> /
TABLE_NAME                     PARTITION INTERVAL
------------------------------ --------- --------------------------------------------------
T_10G                          RANGE
T_11G                          RANGE     NUMTOYMINTERVAL(1,'MONTH')

rop@ROP111> 

La posibilidad de definir un intevalo y que Oracle se encargue de crear las particiones a medida que se vayan necesitando resulta muy interesante para facilitar el mantenimiento y administración de particiones.

Usando columnas virtuales para particionar

Hace unos años estuve en un proyecto en una empresa de comunicaciones, donde se usaba una tabla que guardaba información volátil con una ventana semanal. El modelo ya estaba definido y se borraban diariamente las filas de forma tal de siempre mantener 6 dias hacia atrás. A medida que los datos insertados diariamente se incrementaban, la depuración (borrado físico) se fue haciendo mas complicada y demoraba cada vez mas tiempo (además de generase mas redo y undo). La tabla no estaba particionada y me pidieron de armar un particionamiento por dias de semana para asi truncar la particion del dia, en lugar de borrar los registros. El problema fue que la tabla tenia la fecha de insert del registro pero no tenia el dia de la semana. Como en ese momento la base era 10g la solución de menos impacto fue agregar una columna de tipo char(1) que se actualizaba con un trigger en cada insert. El trigger lo unico que hacia era obtener el dia de la semana del sysdate y lo guardaba en la nueva columna. En 11g se podría haber resuelto en forma mas transparente con solo agregar una columna virtual a la tabla. A continuación les muestro un ejemplo:

rop@ROP111> create table t (c1 int,
  2                  c2 varchar2(10),
  3                  c3 date,
  4                  c3_v char(1)
  5                  generated always as
  6                 (to_char(c3,'d')) virtual
  7                 )
  8  partition by list (c3_v)
  9  (partition p1 values ('1'),
 10   partition p2 values ('2'),
 11   partition p3 values ('3'),
 12   partition p4 values ('4'),
 13   partition p5 values ('5'),
 14   partition p6 values ('6'),
 15   partition p7 values ('7')
 16  );

Tabla creada.

Ahora obtengo el dia actual:

rop@ROP111> select sysdate from dual;
SYSDATE
---------
11-FEB-09

y la función a aplicar para particionar para el dia actual:

rop@ROP111> select to_char(sysdate,'d') from dual;
T
-
4

Voy a insertar 2 filas. Notar que no le paso nada a la columna virtual:

rop@ROP111> insert into t (c1,c2,c3) values (1,'A',sysdate);
1 fila creada.

rop@ROP111> ed
Escrito file afiedt.buf
  1* insert into t (c1,c2,c3) values (2,'B',sysdate+2)
rop@ROP111> /

1 fila creada.

rop@ROP111> commit;
Confirmación terminada.

Analizo la tabla para luego mostrar que las filas se insertaron en la partición correcta

rop@ROP111> analyze table t compute statistics;

Tabla analizada.

rop@ROP111> select partition_name,num_rows from user_tab_partitions
  2  where table_name = 'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                      0
P2                                      0
P3                                      0
P4                                      1
P5                                      0
P6                                      1
P7                                      0

7 filas seleccionadas.

Haciendo un describe, la columna virtual se ve como una columna mas:

rop@ROP111> desc t
 Nombre                                                            ¿Nulo?   Tipo
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         NUMBER(38)
 C2                                                                         VARCHAR2(10)
 C3                                                                         DATE
 C3_V                                                                       CHAR(1)

Para finalizar vamos a ver en el diccionario la función aplicada y la columna virtual agregada:

rop@ROP111> select column_name,data_type,data_default,virtual_column,internal_column_id,segment_column_id 
  2         from user_tab_cols where table_name = 'T';


COLUMN_NAME                    DATA_TYPE       DATA_DEFAULT         VIR INTERNAL_COLUMN_ID SEGMENT_COLUMN_ID
------------------------------ --------------- -------------------- --- ------------------ -----------------
C1                             NUMBER                               NO                   1                 1
C2                             VARCHAR2                             NO                   2                 2
C3                             DATE                                 NO                   3                 3
C3_V                           CHAR            TO_CHAR("C3",'d')    YES                  4

Usando particionamiento de Sistema

El ultimo "11g new feature" de partitioning es el denominado partitioning interno o de sistema. Con este tipo de particionamiento se delega a la capa de aplicación la determinación de a cual partición se inserta una fila.
Voy a crear una tabla particionada por sistema y voy a intentar insertar una fila sin definir a que partición tiene que ir.

rop@ROP111> create table t (c1 int,
  2                  c2 varchar2(10),
  3                  c3 date)
  4  partition by system
  5  (partition p1,
  6   partition p2,
  7   partition p3
  8  );

Tabla creada.

rop@ROP111> insert into t values (1,'A',sysdate);
insert into t values (1,'A',sysdate)
            *
ERROR en línea 1:
ORA-14701: Se debe utilizar el nombre de partición ampliada o la variable ligada para DML en tablas particionadas
mediante el método Sistema

Oracle no permite insertar una fila si no se explicita a que partición debe asociarse.
Ahora le voy a insertar 2 filas con la nueva sintaxis, la primera en la partición p3 y la segunda en la partición p1.

rop@ROP111> insert into t partition (p3) values (1,'A',sysdate);

1 fila creada.

rop@ROP111>  insert into t partition (p1)  values (2,'B',sysdate-2);

1 fila creada.

Ya que le aclaramos en el mismo insert en que partición grabar, no hubo problema.
Para finalizar voy a mostrar que las filas se insertaron correctamente:

rop@ROP111> analyze table t compute statistics;

Tabla analizada.

rop@ROP111> ed
Escrito file afiedt.buf

  1  select partition_name,num_rows from user_tab_partitions
  2* where table_name = 'T'
rop@ROP111> /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                      1
P2                                      0
P3                                      1

rop@ROP111> select table_name,partitioning_type from user_part_tables where table_name = 'T';

TABLE_NAME                     PARTITION
------------------------------ ---------
T                              SYSTEM

rop@ROP111>

Este tipo de particionamiento puede aplicarse a ciertos tipos de aplicaciones que requieran tener control total de donde insertar las filas y aprovechar las virtudes del particionamiento.

Nuevas Caracteristicas de los triggers en 11g

Hoy voy a comentarles respecto a los nuevos features de 11g relacionados con los triggers. Las 3 extensiones al funcionamiento de los triggers son:

1. Se pueden crear triggers compuestos, que son como varios triggers definidos en uno.
2. Se pueden crear los triggers inicialmente deshabilitados.
3. Se puede establecer un orden de ejecución de triggers sobre tablas con mas de un trigger definido.

En esta nota voy a mostrarles como crear y usar los triggers compuestos y además haré una comparativa de tiempos con respecto a los triggers tradicionales.

Para comenzar me gustaría mostrarles mediante un ejemplo usando dos tablas inventadas, de forma de simular una base de libros de una biblioteca. Los libros pueden estar en 3 estados: D:"Disponible", P:"Prestado" y R:"En restauracion". Se mantiene un registro de cada cambio de estado en la tabla libros_log que se llena mediante un trigger cada vez que se realiza un update del campo estado en la tabla libros.

rop@ROP111> create table libros (id_libro int primary key,
                                 autor varchar2(20),
                                 titulo varchar2(30),
                                 editorial varchar2(10),
                                 estado char(1) check (estado in ('P','D','R')))
/

Tabla creada.
Transcurrido: 00:00:00.15
rop@ROP111>
rop@ROP111> create table libros_log (id_operacion int,
                                     id_libro int references libros (id_libro),
                                     estado char(1) check (estado in ('P','D','R')),
                                     fecha_operacion date)
/

Tabla creada.

Transcurrido: 00:00:00.01

rop@ROP111> create sequence libros_log_seq;

Secuencia creada.

Una vez creadas las dos tablas y la secuencia voy a proceder a cargar la tabla de libros con 1M de registros generados aleatoriamente, de forma tal de acercarme a la realidad, aunque sean datos irreales.

rop@ROP111> insert into libros
            select rownum,
            dbms_random.string('a',trunc(dbms_random.value(1,20))),
            dbms_random.string('l',trunc(dbms_random.value(1,30))),
            'EDITORIAL'trunc(dbms_random.value(1,10)),
            'D'
            from dual
            connect by rownum <= 1000000  
/  
1000000 filas creadas.  
Transcurrido: 00:02:02.97   

rop@ROP111> commit;
Confirmación terminada.

Ahora voy a cambiar el estado a los libros registrados. La idea es cambiar el estado tratando de simular una distribución real. Se definirá un 40% del total de los libros como prestados (P) y el 2% en estado de restauración (R):

rop@ROP111> update libros
            set estado = 'P'
            where id_libro in (select trunc(dbms_random.value(1,200000))
                               from libros
                               where rownum <= 200000*0.4)  
/  
66163 filas actualizadas.   
Transcurrido: 00:00:03.71   
rop@ROP111>
rop@ROP111> update libros
            set estado = 'R'
            where id_libro in (select trunc(dbms_random.value(1,200000))
            from libros
            where rownum <= 200000*0.02)  
/  
3966 filas actualizadas.  
Transcurrido: 00:00:02.45  

Voy a crear un trigger simple, tal como lo creariamos en versiones 10g o inferiores:

rop@ROP111> create or replace trigger libros_trg_simple
2 after update of estado on libros
3 for each row
4 begin
5 insert into libros_log values (libros_log_seq.nextval,
6 :old.id_libro,
7 :new.estado,
8 sysdate);
9 end libros_trg_simple;
10 /
Disparador creado.

Ahora voy a crear el trigger compuesto introducido en 11g:

rop@ROP111> create or replace trigger libros_trg_compound
2  for update of estado
3  on libros
4  compound trigger
5    type libros_log_type is table of libros_log%rowtype index by pls_integer;
6    l_libros_log_array  libros_log_type;
7    l_pos pls_integer := 0;
8  before statement is
9  begin
10      null;
11  end before statement;
12  before each row is
13  begin
14      null;
15  end before each row;
16  after each row is
17  begin
18      l_pos := l_pos+1;
19      l_libros_log_array(l_pos).id_operacion := libros_log_seq.nextval;
20      l_libros_log_array(l_pos).id_libro := :old.id_libro;
21      l_libros_log_array(l_pos).estado := :new.estado;
22      l_libros_log_array(l_pos).fecha_operacion := sysdate;
23  end after each row;
24  after statement is
25  begin
26      forall i in 1..l_libros_log_array.count()
27            insert into libros_log
28            values l_libros_log_array(i);
29  end after statement;
30  end libros_trg_compound;
31  /

Disparador creado.

Transcurrido: 00:00:00.06
rop@ROP111>

Notar que se realiza un bulk insert lo cual deberia ser mas rapido que insertar por cada update. Tambien se puede ver que se obtiene el próximo valor de secuencia (linea 19) en forma directa desde PL/SQL sin necesidad de usar un select into (nuevo en 11g) .

Los triggers fueron creados habilitados (comportamiento default), pero podrian haberse creado inicialmente deshabilitados aprovechando que en 11g se puede.
Para realizar la primera prueba deshabilitamos el trigger compuesto y dejamos solo habilitado el trigger simple

rop@ROP111> alter trigger libros_trg_compound disable;
Disparador modificado.

Vamos a simular que se recibieron todos los libros que estaban para restaurar (estado=R) y entonces hay que cambiarles el estado en la base para que queden nuevamente disponibles (estado=D).

rop@ROP111> update libros
            set estado = 'D'
            where estado = 'R'
/
3960 filas actualizadas.

Transcurrido: 00:00:01.90

rop@ROP111> rollback;
Rollback terminado.

Una vez tomado el tiempo, deshacemos el update con rollback y habilitamos el trigger
compuesto y deshabilitamos el simple

rop@ROP111> alter trigger libros_trg_compound enable;
Disparador modificado.

Transcurrido: 00:00:00.01
rop@ROP111> alter trigger libros_trg_simple disable;
Disparador modificado.

rop@ROP111> update libros
            set estado = 'D'
            where estado = 'R';
3966 filas actualizadas.

Transcurrido: 00:00:01.22

Como observamos de la comparación, se nota una mejora en los tiempos al usar el trigger compuesto en relación con el trigger simple.

Para notar mas la diferencia voy a modificar todos los registros de forma tal de
setear todos los estados en disponible (estado=D). Primero pruebo con el trigger simple activo:

rop@ROP111> alter trigger libros_trg_simple enable;
Disparador modificado.

rop@ROP111> alter trigger libros_trg_compound disable;
Disparador modificado.

rop@ROP111> update libros
            set estado = 'D';
1000000 filas actualizadas.

Transcurrido: 00:02:57.83
rop@ROP111> rollback;

Rollback terminado.
Transcurrido: 00:01:20.89

Por ultimo comparamos con los tiempos usando el trigger compuesto:

rop@ROP111> alter trigger libros_trg_simple disable;
Disparador modificado.

rop@ROP111> alter trigger libros_trg_compound enable;
Disparador modificado.

rop@ROP111> update libros
            set estado = 'D';

1000000 filas actualizadas.
Transcurrido: 00:02:45.03

Como vemos en la ultima comparación, al modificar mayor cantidad de registros se ve mas claramente la diferencia entre procesar todo al final de la sentencia en forma bulk (trigger compuesto) que con el procesamiento tradicional donde el trigger se dispara por cada registro.

Por que el optimizador no usa mi índice cuando arma el plan de ejecución (Clustering Factor)

Es común que los desarrolladores se frusten al ver que el optimizador por costos (CBO) no usa el índice que recién crearon. No es casualidad que le haya llevado varios años a Oracle convencer a sus clientes que el optimizador por reglas (RBO) no servía mas (de hecho se dejó de actualizar en 1992). Es cierto que versión tras versión el CBO fue haciéndose mas sofisticado (de hecho se lo considera una de las piezas de software mas complejas que existe) y también es verdad que en sus primeras versiones tenia algunos problemas, pero eso fue cambiando. En mi opinión, el gran salto se dio a partir de 10g ya que despues de muchas promesas durante los años anteriores, fue recien en 10g donde se desoportó a RBO, aunque se podía seguir utilizando.
Uno de los motivos mas frecuentes de que el optimizador no escogiera el plan ideal era la falta o desactualizacion de estadísticas de los objetos involucrados en las sentencias. Para mi las dos principales innovaciones de 10g fueron: a) la automatización de la recolección de estadísticas mediante las ventanas de mantenimiento, que se activan por default, y b) el denominado dynamic sampling por el cual si se realiza una pseudo recolección de estadísticas "on the fly" (al vuelo) para los segmentos que aun no cuenten con información estadística. Sin embargo, hay casos donde las estadísticas son "frescas" pero de todas formas nos frustamos al ver que no se arma el plan que pretendemos, no se usa nuestro índice.
Cuando comencé con el tema de tuning sentí esa misma frustración muchas veces y con el tiempo me di cuenta que en la mayoría de las casos era por desconocimiento, no sabia que pasaba y entonces recurría a una técnica muy frecuente y efectiva para salir del paso, el uso de un hints. No voy a ahondar demasiado en el tema de hints, pero es sabido que excepto algunos tipos de hints en ciertos casos especificos, no es una práctica recomendada. Uno de esos desconocimientos fue no conocer que era el clustering_factor. El clustering_factor es un número (una parte de información recolectada en la toma de estadísticas de los índices) que compara el orden de un índice con el grado de desorden en la tabla que indexa. Ese número puede ser tan pequeño como la cantidad de bloques de la tabla indexada o tan grande como el número de filas indexadas. Si ese valor tiende a ser igual a la cantidad de bloques decimos que el clustering_factor es bajo y si el valor tiende a ser igual a la cantidad de filas decimos que el clustering_factor es alto. En este ultimo caso es cuando el optimizador tiende a evitar el uso del índice para consultas del tipo RANGE SCAN. Cuanto mas alto sea el clustering_factor menos probable será que el optimizador escoja el índice para generar su plan.Ahora voy a intentar ejemplificar como funciona el clustering_factor:

Con sqlplus creo una tabla T con datos en cierto orden para que al indexar por la columna X me queden ordenados (clustering factor bajo) y al indexar por la columna Y me queden desordenados (clustering factor alto)

rop@DESA10G> create table t
  2  as
  3  select trunc(rownum/10000) x,
  4         trunc(dbms_random.value(0,9999)) y,
  5         dbms_random.string('a',10) z
  6  from dual
  7  connect by rownum <= 1000000;

Tabla creada.



rop@DESA10G> create index t_x on t(x);

Índice creado.

rop@DESA10G> create index t_y on t(y);

Índice creado.

rop@DESA10G> begin
  2      dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

Vamos a ver la cantidad de filas y bloques estimadas para la tabla T:

rop@DESA10G> select num_rows,blocks
  2  from user_tab_statistics
  3  where table_name = 'T';

  NUM_ROWS     BLOCKS
---------- ----------
    997144       3243

rop@DESA10G> select index_name,clustering_factor,leaf_blocks,num_rows
  2  from user_ind_statistics
  3  where table_name = 'T';

INDEX_NAME                     CLUSTERING_FACTOR LEAF_BLOCKS   NUM_ROWS
------------------------------ ----------------- ----------- ----------
T_X                                         3183        1952    1000000
T_Y                                       984472        2090    1000000

Como resultado de la recolección estadistica se ve que el indice T_X tiene un clustering factor similar a la cantidad de bloques y que el indice T_Y tiene un clustering factor similar a la cantidad de filas.

Una vez armado el escenario, vamos a ver como cambia el plan en concordancia con el factor de aglutinamiento (clustering factor) de acuerdo a la columna que se usa para filtrar en el predicado

rop@DESA10G>  select avg(length(z)) from t where x between 90 and 100;

AVG(LENGTH(Z))
--------------
            10


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

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    14 |   523   (1)| 00:00:12 |
|   1 |  SORT AGGREGATE              |      |     1 |    14 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   100K|  1374K|   523   (1)| 00:00:12 |
|*  3 |    INDEX RANGE SCAN          | T_X  |   100K|       |   200   (1)| 00:00:05 |
-------------------------------------------------------------------------------------

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

   3 - access("X">=90 AND "X"<=100)


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

El optimizador eligió ir por el indice ya que es mas barato dado que tiene el dato de que el clustering factor es bajo.

Ahora probamos de filtrar por la columna Y, cuyo indexacion tiene clustering factor elevado:

rop@DESA10G>  select avg(length(z)) from t where y between 90 and 100;

AVG(LENGTH(Z))
--------------
            10


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    15 |   740   (3)| 00:00:17 |
|   1 |  SORT AGGREGATE    |      |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1197 | 17955 |   740   (3)| 00:00:17 |
---------------------------------------------------------------------------

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

   2 - filter("Y"<=100 AND "Y">=90)


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

rop@DESA10G>

Tal cual se esperaba el optimizador optó por hacer un full scan basandose en la dispersión de los datos en los bloques de la tabla.

Manejo de Segmentos y Reorganización de espacio en 10g

Para explicar un poco el manejo de espacio de datos en segmentos la idea es mostrar la funcionalidad introducida en 10g para compactar segmentos con espacio inutilizado en forma online (shrink). En versiones 9i se puede realocar espacio realizando un move online, pero para esto es necesario tener como minimo el espacio ocupado por el segmento a realocar. Voy a usar la función show_space.


Voy a crear una tabla T con 10000 registros. La idea es que la columna Y tenga valores entre 0 y 4 y que haya uno de estos valores por bloque. Hice unos calculos previos para hacer que entren justo 5 filas por bloque (mi base usa tamaño de bloque de 8k)

rop@DESA10G> create table t
2  pctfree 30
3  as
4  select rownum x,
5         mod(rownum,5) y,
6         dbms_random.string('a',1000) z
7  from dual
8  connect by rownum <= 10000;  Tabla creada.   rop@DESA10G>  exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           2,000
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.

Se ve que hay 2048 bloques en total y 2000 bloques sin espacio disponible (full).


Vamos a ver si efectivamente se almacenó un valor de Y distinto por bloque:

rop@DESA10G> select y,count(distinct dbms_rowid.rowid_block_number(rowid)) cnt_blk
2  from t
3  group by y;

   Y    CNT_BLK
---------- ----------
   0       2000
   1       2000
   2       2000
   3       2000
   4       2000

rop@DESA10G>

Ahora voy a eliminar los registros cuyo valor de Y sea 0,1 y 2, es decir el 60% de las filas y el 60% espacio ocupado en cada bloque

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................           2,000
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

Observamos que los 2000 bloques que estaban totalmente llenos ahora tienen entre el 50-75 espacio libre, mas precisamente con el 60% disponible.


Ahora, si borramos las filas con Y = 3, solo no quedará un 20% del bloque ocupado, ya que solo quedan las filas con valor Y = 4

rop@DESA10G> delete from t where y = 3;

2000 filas suprimidas.

rop@DESA10G> commit;

Confirmación terminada.

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................           2,000
Full Blocks        .....................               0
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

Podemos notar que ahora los 2000 bloques quedaron con espacio libre entre 75-100, mas precisamente con el 80% libre


En este punto podemos asegurar que tenemos una alocación efectiva del 20%.


Si la tabla T esta alojada en un tablespace con manejo de segmentos automatico (ASSM) se pueden compactar las filas en los bloques. Tenemos que habilitar primero la tabla en cuestión para mover las filas:

rop@DESA10G> alter table t enable row movement;

Tabla modificada.


Luego, ejecutamos el siguiente comando:

rop@DESA10G> alter table t shrink space compact;

Tabla modificada.

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................           1,714
Full Blocks        .....................             285
Total Blocks............................           2,048
Total Bytes.............................      16,777,216
Total MBytes............................              16
Unused Blocks...........................               8
Unused Bytes............................          65,536
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         113,545
Last Used Block.........................             120

Procedimiento PL/SQL terminado correctamente.


Vemos que ahora quedaron 285 bloques llenos, es decir, en lugar de dejar 1 fila por cada uno de los 2000 bloques quedaron 285 bloques.

Haciendo la siguiente consulta:

select y,count(distinct dbms_rowid.rowid_block_number(rowid)) cnt_blk
from t
group by y

   Y    CNT_BLK
---------- ----------
   4       287

Se ve que quedaron las filas compactadas en 287 bloques. Sin embargo la tabla sigue alocando el mismo espacio que al principio (16Mb).
Para poder dealocar el espacio usamos el siguiente comando:

rop@DESA10G> alter table t shrink space;

Tabla modificada.

rop@DESA10G> exec show_space('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             285
Total Blocks............................             304
Total Bytes.............................       2,490,368
Total MBytes............................               2
Unused Blocks...........................               4
Unused Bytes............................          32,768
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         111,241
Last Used Block.........................              44

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G>

Finalmente vemos que ahora quedaron alocados un total de 304 bloques, comparado con los 2048 que estaban alocados inicialmente.

Como mostré arriba ahora se puede dealocar el espacio no utilizado y lo mas importante que se puede hacer online, mas rapido y sin espacio extra.