Como cambiar el umbral de tolerancia de cambios para recolección estadística en 11g (STALE_TOLERANCE)

En varios articulos escribí sobre las estadisticas y su importancia para el correcto funcionamiento del optimizador por costos (CBO). Mantener las estadisticas al dia es a veces una tarea bastante compleja y tediosa, en especial en entornos con gran volumen de datos y alta tasa de cambios. Asegurar que en cada ejecución de sentencias se cuente con estadisticas "frescas" es todo un desafio para los arquitectos y dba's.

A partir de 10g se automatizó bastante dicha tarea, ya que uno de los procesos que corren durante la ventana de mantenimiento, es justamente la recolección estadistica. Para optimizar la recolección solo se actualizan las tablas cuya tasa de cambio sea mayor al 10%. Se puede consultar que tablas estan desactualizadas consultando la vista de catálogo DBA_TAB_STATISTICS, en donde hay un campo llamado STALE_STATS que puede tomar dos valores YES (la tabla necesita nuevas estadisticas) o NO (la tabla no necesita nuevas estadisticas). El umbral es fijo en 10g y no puede modificarse. Ya que la ventana de mantenimiento esta configurada para activarse durante la noche por default, si por ejemplo, un proceso de cambio masivo sobre una tabla genera cambios por mas del 10% no tendremos estadisticas frescas hasta el otro dia. En esos casos se recomienda recolectar estadisticas manualmente inmediatamente despues de la operatoria de cambio sobre las tablas involucradas.

En 11g se puede cambiar el umbral a nivel de tabla, esquema o de la base completa, con lo cual se puede hacer tan sensible la toma de estadisticas como se requiera. En la práctica he usado dicho feature solo con granularidad de tabla en casos donde se detectaron cambios de planes de sentencias que referencian ciertas tablas con cambios menores al 10%. A continuación voy a mostrar como usar el nuevo procedure SET_TABLE_PREFS del paquete DBMS_STATS para cambiar el umbral.

Repasando, en 11g se agregaron los siguiente procedimientos al paquete DBMS_STATS

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

Con los sp's listados arriba se puede realizar las siguientes 3 nuevas configuraciones:

STALE_PERCENT: Para cambiar el umbral que determina cuando una tabla no tiene sus estadisticas al dia.

INCREMENTAL: Para optimizar la recolección sobre tablas particionadas (ver articulo xxx)

PUBLISH: Para testea un nuevo set de estadisticas antes de publicarlas

Voy a mostrar un ejemplo para cambiar el STALE_PERCENT de una tabla, consultando sobre el catalogo para que se vea como se van registrando los cambios:

Primero voy a crear una tabla y luego tomo le tomo las estadisticas manualmente.

create table t as select * from dba_objects

select count(1) from t

begin

dbms_stats.gather_table_stats(ownname = user; tabname = 'T');

end;

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: NO

La columna STALE_STATS nos permite determinar si las estadisticas estan frescas o no. Una práctica común que he visto muchas veces, es mirar la columna LAST_ANALYZED de la vista USER_TABLES. Claramente este valor puede ser engañoso, ya que se tiende a inferir que cuanto mas vieja haya sido la ultima toma mas desactualizada estará la tabla, pero... si la tabla no tuvo cambios importantes desde la ultima recolección?, en ese caso el campo STALE_STATS estará en NO y el LAST_ANALYZED podría tener varios dias o incluso meses. Esto ultimo no implica en absoluto que las stats de la tabla estén desactualizadas. Como regla, siempre recomiendo mirar la columna STALE_STATS para determinar si una tabla tiene las estadisticas correctas, y solo ver el LAST_ANALYZED como un dato adicional.

Ahora voy a generar cambios de tipos diversos a la tabla, de forma tal de generar mas del 10% de cambios, recordar que es el umbral de tolerancia default (STALE_TOLERANCE)

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

Voy a usar la vista USER_TAB_MODIFICATIONS que muestra la cantidad de DML´s por cada tabla desde la ultima toma de estadisticas. Pueden usar la info de dicha tabla, para conocer la tasa de cambios y el tipo de operaciones, lo cual resulta de mucha utilidad para conocer mas acerca de la operatoria en la base de datos.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'T';

no rows selected

No hay registros para la tabla, que raro, no?, si recien habia realizado cambios importantes. En realidad no es raro, el tema es que los cambios primero se almacenan en memoria y son "flusheados" a disco cada 30'. Para forzar el flush hacemos:

begin

dbms_stats.flush_database_monitoring_info;

end;

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'T';

INSERTS UPDATES DELETES

---------- ---------- ----------

3000 3000 3000

Ahora si aparecen los cambios, tal cual se esperaba. Chequeamos si las estadisticas se marcan como "viejas":

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: YES

Justamente, una vez impactados los cambios en el catalogo tambien se actualizó la columna STALE_STATS y pasó de NO a YES.

Con la intro que realicé mas arriba, ahora puedo mostrarles como cambiar el umbral para la tabla T, para que ahora en lugar de tomar el umbral global default, utilice un umbral mayor:

begin

dbms_stats.set_table_prefs(user,'T','STALE_PERCENT','15');

end;

Vuelvo a realizar los inserts, updates y deletes anteriores, realizo flush de cache para actualizar el catálogo y reviso si las estadísticas de la tabla están marcadas como STALE:

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

begin

dbms_stats.flush_database_monitoring_info;

end;

select num_rows,stale_stats from user_tab_statistics where table_name = 'T'

NUM_ROWS : 88538

STALE_STATS: NO

Como es observa, ahora las estadísticas no estan desactualizadas para Oracle y por lo tanto no se recolectarán las estadisticas para la tabla en la próxima ventana de mantenimiento. Este nuevo feature permite mayor granularidad para determinar cuando una tabla necesita estadisticas y cuando no se requieren, con lo cual se minimizan los tiempos de recolección, adecuando con mayor precisión dicho proceso a las necesidades particulares de cada tabla, esquema o base de datos.

Row Prefetching en Oracle

Cada vez que una aplicación necesita obtener datos desde la base de datos, se lo solicita al driver y este ejecuta una cierta sentencia que retorna el resultado fila por fila, o mejor aún, retorna un conjunto de filas que son almacenadas del lado del cliente (caching de aplicación) y procesadas posteriormente.

El mecánismo de retornar un conjunto de filas a vez se denomina "row prefetching" y sirve principalmente para minimizar las idas y vueltas a la base (round trips); los datos serán almacenados en la memoria y consumidos desde allí por la aplicación con la consiguiente mejora de rendimiento ya que se minimiza la comunicación con la base de datos. En esta nota tambien voy a mostrar ejemplos usando PL/SQL, Java y C#.

Voy a realizar una comparativa usando bloques PL/SQL para implementar cursores para procesar los datos de una tabla T con 100,000 registros y creada de la siguiente manera:

create table t (id int, pad varchar2(200));

insert into t
select rownum,dbms_random.string('a',200)
from dual
connect by rownum <= 100000; 

Con la tabla creada, ahora voy a ejecutar el bloque que obtiene los datos a procesar con un cursor explicito, y voy a activar el trace para ver la cantidad de fetches que se requieren:

declare
cursor cur1 is select * from t;
l_rec t%ROWTYPE;
begin
open cur1;

loop
 fetch cur1 into l_rec;
 exit when cur1%notfound;
 null;
end loop;
close cur1;
end;


SELECT *
FROM
T


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   100001      0.99       0.76          0     100016          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100003      0.99       0.76          0     100017          0      100000

Se observa desde la salida del trace (previamente procesada con tkprof) que la cantidad de fetches es igual a la cantidad de registros,es decir, se realizó un fetch por cada fila. Probemos realizar la misma operatoria pero ahora usando cursores implicitos:

begin
for i in (select * from t)
loop
null;
end loop;
end;


SELECT *
FROM
T


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1001      0.23       0.22          0       3899          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.23       0.22          0       3899          0      100000

Con cursores implicitos se necesitaron 1001, por lo cual haciendo una simple cuenta podemos afirmar que el prefetch fue de 100 filas, valor fijo y siempre y cuando el parametro plsql_optimize_level sea 2 (default a partir de 10g R2). Además notar que las lecturas lógicas y el tiempo de procesamiento total es menor cuando se usa prefetch.

Esta podría ser una de las tantas justificaciones para tentarlos a usar cursores implicitos, no?. En general yo suelo usar cursores implicitos (CI), ya que escribo menos, y además he probado que son mas eficientes que los cursores explicitos (CE). Claro, que en casos particulares no nos queda otra opción que usar CE cuando queremos tener mas control de todos las etapas (declare,open,fetch y close). Sin embargo con la introducción de BULK COLLECT podemos mejorar la performance con CE de forma tal de paralelizar. Tambien con bulk collect podemos definir facilmente el tamaño del fetch. Ahi va un ejemplo:

declare
cursor cur1 is select * from t;
type t_type is table of t%ROWTYPE;
l_t t_type;
begin
open cur1;
loop
 fetch cur1 BULK COLLECT into l_t LIMIT 100;
 exit when cur1%notfound;
 for i in l_t.first..l_t.last
 loop
    null;
 end loop;
end loop;
close cur1;
end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1001      0.25       0.24          0       3899          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.25       0.24          0       3899          0      100000

En el ejemplo anterior definí un tamaño de prefetch de 100, tal cual se puede verificar en la salida del trace. Definamos ahora un tambaño de fetch mayor (1000):

declare
cursor cur1 is select * from t;
type t_type is table of t%ROWTYPE;
l_t t_type;
begin
open cur1;
loop
 fetch cur1 BULK COLLECT into l_t LIMIT 1000;
 exit when cur1%notfound;
 for i in l_t.first..l_t.last
 loop
    null;
 end loop;
end loop;
close cur1;
end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.24       0.24          0       3052          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.24       0.24          0       3052          0      100000

Hicieron falta 101 llamadas a la base, cada una retornó 1000 filas al cliente. Dichas filas quedan almacenadas en la memoria del cliente para su posterior utilización.

Por ultimo, voy a mostrar como se define el tamaño del fetch en java y C#:

Porción de Código java para usar prefetch:

try
{
sql = "select id, pad from t";
statement = connection.prepareStatement(sql);
statement.setFetchSize(100);
      resultset = statement.executeQuery();
while (resultset.next())
{
id = resultset.getLong("id");
pad = resultset.getString("pad");
// Implementación de la lógica en el cuerpo del bucle
}
resultset.close();
statement.close();
}
catch (SQLException e)
{
throw new Exception("Error : " + e.getMessage());
}

Porción de Código C# (.NET) para usar prefetch:

sql = "select id, pad from t";
command = new OracleCommand(sql, connection);
command.AddToStatementCache = false;
reader = command.ExecuteReader();
   reader.FetchSize = command.RowSize * 100;
while (reader.Read())
{
  id = reader.GetDecimal(0);
  pad = reader.GetString(1);
  // Implementación de la lógica en el cuerpo del buclejavascript:void(0)
}
reader.Close();

Para ver mas sobre este tema y sobre otros temas de performance, recomiendo la lectura del excelente libro de Christian Antognini: Troubleshooting Oracle Performance (by Christian-Antognini)

Nueva funcionalidad para el SELECT FOR UPDATE (SKIP LOCKED)

En muchas ocasiones tuve la oportunidad de revisar código pl/sql en donde se programa, entre otras cosas, la "marcación" de registros por medio de un flag. Estas marcas, en general se implementan modificando una columna (ej: estado) donde se denota en que etapa del procesamiento se encuentra la sesión y así evitar solapamientos con otras sesiones paralelas que esten haciendo lo mismo.

La forma mas común que yo he visto para realizar la operatoria descripta es usando "SELECT ... FOR UPDATE NOWAIT" del registro de la tabla maestra para asegurar que las demás sesiones no puedan procesar dicho registro. Una vez que el registro se procesó, las otras sesiones podrán tomar el siguiente registro disponible para procesar. Si no se requiere un orden para procesar, este enfoque atenta contra el paralelismo real. Esto se da porque mientras una sesión este procesando un registro las otras deberan esperar a que se commitee para poder procesar el siguiente registro. Esto sucede porque Oracle no lockea los selects, entonces aunque el proceso este procesando un registro dado, no se puede "saltear" y tomar el siguiente, sino que se devuelve un error de que el recurso esta siendo usado (ORA-00054 recurso ocupado y obtenido con NOWAIT).

A partir de 11g se documentó una opción (por lo que pude probar existe desde 9i pero no estaba documentada) muy interesante para lidiar con este tipo de procesamiento, que en realidad es una extensión de la sintaxis del for update para permitir, justamente, para poder procesar con mucho mayor grado de paralelismo y asi posibilitar que cada sesion "saltee" el registro en procesamiento y tome el próximo disponible para procesar. Esto mejora sensiblemente los tiempos de procesamiento general, ya que se podrán levantar n sesiones en paralelo minimizando la interdependencia entre ellas.

Abajo les muestro un ejemplo:

Creo una tabla T y le inserto 100 registros:

create table t (id int,
                estado char(1),
                fecha date,
                importe number(8,2))


insert into t
select rownum,
      'C',
      sysdate+dbms_random.value(-50,50),
      dbms_random.value(1,1000000)
from dual
connect by rownum <= 100  

Cambio el estado de 10 filas, elegidas al aleatoriamente. Dichas filas quedarán en estado 'P', suponiendo que el estado 'P' es disponibles para procesar.

create view t_v as 
select id
from t 
order by dbms_random.value  

update t
set estado = 'P'
where id in (select id from t_v) 
and rownum <= 10  


select * from t where estado = 'P';         

       ID E FECHA        IMPORTE
---------- - --------- ----------
        1 P 20-DIC-10  888292.36
       27 P 09-MAR-11  845864.47
       39 P 19-ENE-11  583901.49
       52 P 23-FEB-11  157817.12
       62 P 05-ENE-11   680744.2
       63 P 19-ENE-11  679375.69
       73 P 20-ENE-11   750069.3
       87 P 26-FEB-11  783555.02
       96 P 13-DIC-10  973668.87
      100 P 28-FEB-11  756671.07

En una consola ejecutamos el siguiente bloque pl, para tomar el siguiente registro a procesar (Sesion 1)

declare
   cursor l_cur is
   select  *
   from  t
   where estado = 'P'
   for update nowait skip locked;
   l_rec l_cur%rowtype;
begin
      open l_cur;
      fetch l_cur into l_rec;   
      -- 
      dbms_output.put_line (l_rec.id);
end;

Resultado: 1

En otra sesion (Sesion 2) ejecutamos el mismo bloque pl:

declare
   cursor l_cur is
   select  *
   from  t
   where estado = 'P'
   for update nowait skip locked;
   l_rec l_cur%rowtype;
begin
      open l_cur;
      fetch l_cur into l_rec;   
      -- 
      dbms_output.put_line (l_rec.id);
end;

Resultado: 27

La sesión 1 tomó el registro con id=1 y la sesión 2 tomó el registro con id=27, que son el primero y segundo respectivamente en el listado de mas arriba. Claramente no se commiteo nada y sin embargo la sesion 2 pudo tomar un nuevo registro para procesar mientras la sesión 1 estaba procesando. Con el select for update convencional la sesión 2 hubiese fallado y por código se deberia volver a intentar hasta que la sesión 1 libere el registro (commit/rollback) con id=1 y asi permitir pasar al siguiente.

Optimizando la recolección estadística sobre tablas particionadas

Oracle 10g utiliza un algoritmo de dos pasadas para recolectar estadisticas sobre tablas particionadas:

1. Una pasada por sobre toda la tabla para actualizar las estadisticas globales.
2. Una segunda pasada para recolectar estadisticas en cada una de las particiones.

Este enfoque tiene el inconveniente que si se realizan cambios en algunas pocas particiones que las hagan elegibles para la recolección automática en la ventana de mantenimiento, además de refrescar la información estadistica propia de las particiones en cuestión, se deberá realizar la actualización global de la tabla. Para esto ultimo se recorre toda la tabla, incluso las particiones que no tuvieron cambio alguno. Esto puede realizar muy pesado dependiendo del tamaño de la tabla.

A partir de Oracle 11g se adopta una algoritmo de una sola pasada, de manera de que en lugar de realizar una pasada por toda la tabla para actualizar la información global, se realiza una actualización incremental infiriendo los cambios desde las particiones modificadas. Algunas de las estadisticas pueden ser derivadas facilmente desde las estadisticas de las particiones (por ejemplo el número de filas), pero otras estadisticas, tal como el número de valores distintos de una columna no. Para resolver esto Oracle usa una nueva estructura llamada sinopsis para cada columna a nivel de la partición de forma tal que el numero de valores distintos (NDV) a nivel global pueda ser derivado haciendo merge de las sinopsis de las particiones analizadas.






Si bien esto es un feature de 11g R1, en Oracle 10g R2, mas precisamente en 10.2.0.4 existe una opción para simular la recolección incremental mediante un nuevo valor 'APPROX_GLOBAL AND PARTITION' para el parametro GRANULARITY en el procedimiento GATHER_TABLE_STATS. Su comportamiento es igual al de 11g excepto para
los NDV de las columna no particionadas y para el número de claves distintas del indice a nivel global.

El mantenimiento incremental esta deshabilitado por default y se puede habilitar a nivel tabla, esquema, incluso a nivel de la base de datos.

A continuación les paso el resultado de mis pruebas usando Oracle 11g R1 (11.1.0.7):

Voy a usar una tabla particionada por rango de fechas con 3 particiones. La tabla es pequeña (alrededor de 5M de filas) pero servirá para ejemplificar:

select partition_name,num_rows
from user_tab_partitions where table_name = 'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P0710                             1332466
P0810                             2583379
P0910                             1084155
PMAX                                    0

Voy a eliminar 100,000 registros de una de las particiones:

delete from t partition (p0910)
where rownum <= 100000;    100000 filas suprimidas.  

Actualizo las estadisticas, usando el default, es decir sin recolección incremental:

begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'T');
end;

Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:11.71

Demoró casi 12 segundos.

select dbms_stats.get_prefs('INCREMENTAL', tabname=>'T') from dual;

FALSE

Con la consulta de arriba verificamos que se hizo la recolección convencional

Ahora voy a activar la recolección sobre la tabla T y voy eliminar filas y voy a volver a recolectar las estadisticas:

begin
dbms_stats.set_table_prefs(ownname => user,tabname => 'T',
                           pname => 'INCREMENTAL',pvalue => 'TRUE');
end;

Verificamos que efectivamente esta activado el modo incremental sobre la tabla T:

select dbms_stats.get_prefs('INCREMENTAL', tabname=>'T') from dual;

TRUE


begin  
dbms_stats.gather_table_stats(ownname => user,tabname => 'T');
end;

Transcurrido: 00:00:04.71

Ahora demoró 4s. En lugar de recorrer toda la tabla solo analizó la partición que cambió y luego derivó las estadisticas globales en base a los cambios efectuados y usando la sinapsis de la partición.

Hay que tomar en cuenta que los histogramas globales no se preservan luego de ejecutar la recolección incremental (ver Bug 8686932 en Metalink).

Si bien este método usado por Oracle para hacer mas efectiva la recolección se viene estudiando en ambitos academicos y de laboratorio hace ya tiempo, es Oracle el primer motor de base relacional en implementarlo.

Benchmarking de performance de sentencias usando SQL Performance Analyzer (SPA)

En esta nota voy a mostrarles como usar SQL Performance Analyzer (SPA) que es parte del Suite Real Application Testing, y sirve para evaluar y predecir de que forma se afectaran los planes de las sentencias luego de cambios en el entorno tales como:

* Upgrade de BD, HW o SO.
* Cambios en la configuración de BD, HW o SO.
* Cambio de parametrizacion de BD
* Cambios en el esquema de datos (agregado de indices, vistas materializadas)
* Estado de las estadisticas de objetos y de sistema.

Este nuevo feature es muy util para analizar el impacto de cambio ya que permite "jugar" facilmente con el entorno y realizar reportes comparativos, pruebas de regresión, impacto de carga, etc.

Ahora voy a armar un escenario de prueba en 11g para comparar un simple count de la tabla T usando el optimizador por reglas (RBO) contra el mismo count usando el optimizador CBO. Es claro, que RBO esta desoportado desde 10g y que el ejemplo no representa un caso real, o tal vez si, pero va a servir para mostrar como funciona SPA y de paso mostrarles que tan "ciego" es RBO en ciertos casos que intuitivamente parecen triviales.

Voy a crear un tabla T con 1M de registros y con pctfree del 90% para consumir muchos bloques tal que la diferencia entre las ejecuciones que voy a comparar sea mas notoria. Luego voy a crear una PK por el campo id. Con RBO va a hacer un FULL SCAN sobre la tabla T, ya que no se da cuenta que tiene una PK y que podría hacer un full index scan que es mas rapido. Obviamente CBO se percata de esto ya que al ser justamente PK tiene la misma cantidad de registros que la tabla y por lo tanto sirve para responder a la pregunta de cuantos registros tiene la tabla T.

create table t (id int,val varchar2(10)) pctfree 90;

insert into t 
as select rownum ,dbms_random.string('a',10)
from dual
connect by rownum <= 1000000 ;


alter table t add primary key (id);

Ejecuto la sentencia de prueba, uso un hint para ubicarla mas facilmente en la vista dinamica y obtener su sqlid:

select count(1) /*+ Prueba SPA */ from t;


select sql_id from v$sql 
where sql_text like '%Prueba SPA%' and sql_text not like '%sql_text%'; 


5r2ufj2vqkk4p

Ya tengo el sqlid, asi que lo que voy a hacer es armar un SQL Tuning Set (STS) usando el paquete DBMS_SQLTUNE (esto existe desde 10g, asi que lo podria hacer en 10g y luego migrarlo a 11g, por ejemplo para evaluar un upgrade entre esas versiones)

begin
    dbms_sqltune.create_sqlset(sqlset_name => 'Prueba',description => 'STS de Prueba');
end;

El STS que cree se llaman Prueba, ahora le cargo la sentencia desde su cursor en memoria:

DECLARE
    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
      OPEN l_cursor FOR
      SELECT VALUE(p)
      FROM   TABLE (
             DBMS_SQLTUNE.select_cursor_cache (
                 'sql_id = ''5r2ufj2vqkk4p''', -- basic_filter
                 NULL, -- object_filter
                 NULL, -- ranking_measure1
                 NULL, -- ranking_measure2
                 NULL, -- ranking_measure3
                 NULL, -- result_percentage
                 1)    -- result_limit
           ) p;
     
      DBMS_SQLTUNE.load_sqlset (
           sqlset_name    => 'Prueba',
           populate_cursor => l_cursor);
END;

Verificamos que efectivamente se haya creado el STS y que contenga la sentencia:

select * from user_sqlset where name = 'Prueba';


NAME                                   ID
------------------------------ ----------
DESCRIPTION
------------------------------------------------------------------------------------
CREATED   LAST_MODI STATEMENT_COUNT
--------- --------- ---------------
Prueba                                 10
STS de Prueba
12-AGO-10 12-AGO-10               1

select sqlset_name,sql_id from user_sqlset_statements where sql_id = '5r2ufj2vqkk4p';

SQLSET_NAME                    SQL_ID
------------------------------ -------------
Prueba                         5r2ufj2vqkk4p

En este punto, y teniendo creado el STS, que contiene la sentencia mas la información de contexto para evaluarla, podemos comenzar a utilizar el paquete DBMS_SQLPA (existe a partir de 11g R1) para realizar la comparación. Para el ejemplo los paquetes DBMS_SQLTUNE y DBMS_SQLPA son complementarios. Con el primero armo el workload (que puede contener una o mas sentencias obtenidas desde el AWR, desde un cursor, desde otro STS e incluso desde un archivo de trace) y con el segundo realizo el analisis comparativo (benchmarking). A continuación veamos como realizar dicho análisis:

Primero creo una tarea de análisis:

declare
    l_out char(50);
begin
      l_out:= dbms_sqlpa.create_analysis_task(
                   sqlset_name => 'Prueba',
                   task_name   => 'Prueba_TSK');
end;

Luego, configuro el ambiente para simular "el antes". En nuestro ejemplo la idea es comparar un count con RBO y con CBO, asi que seteo a nivel sesión el optimizador para que use RBO y ejecuto el analisis con dicho entorno:

alter session set optimizer_mode = RULE;

begin
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
       task_name      => 'Prueba_TSK',
       execution_type => 'TEST EXECUTE',
       execution_name => 'Prueba_EXEC_antes');
end;

Hago lo mismo para comparar "el despues", seteando el optimizador a su valor default en 11g:

alter session set optimizer_mode = ALL_ROWS;

begin 
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
       task_name      => 'Prueba_TSK',
       execution_type => 'TEST EXECUTE',
       execution_name => 'Prueba_EXEC_despues');
end;

Para realizar la comparación, se puede configurar sobre que metrica focalizarse, si no se aclara nada, se usa como metrica de comparación: "elapsed_time". En este ejemplo preferí usar "buffer_gets", ya que esta metrica es una de las que mas cambia entre los dos casos a comparar y por lo tanto hace mas contundente el reporte final.

BEGIN
DBMS_SQLPA.set_analysis_task_parameter('Prueba_TSK',
                                     'comparison_metric', 
                                     'buffer_gets');
END;

Ejectuo el sp para realizar la comparación:

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name        => 'Prueba_TSK',
    execution_type   => 'compare performance', 
    execution_params => dbms_advisor.arglist(
                          'execution_name1', 
                          'Prueba_EXEC_antes', 
                          'execution_name2', 
                          'Prueba_EXEC_despues')
    );
END;

Una vez ejecutado el analisis vemos mediante un reporte un resumen de las diferencias:

SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SET LONGCHUNKSIZE 200
SET TRIMSPOOL ON

rop@DESA11G> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Prueba_TSK', 'TEXT', 'TYPICAL', 'SUMMARY') from
 dual;
General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : Prueba_TSK                      SQL Tuning Set Name        : Prueba
  Task Owner   : ROP                             SQL Tuning Set Owner       : ROP
  Description  :                                 Total SQL Statement Count  : 1

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : EXEC_7692              Started             : 08/12/2010 16:27:22
  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 08/12/2010 16:27:22
  Description     :                        Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Comparison Metric: BUFFER_GETS
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : Prueba_EXEC_antes        Execution Name      : Prueba_EXEC_despues
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 08/12/2010 16:26:29      Started             : 08/12/2010 16:27:04
  Last Updated        : 08/12/2010 16:27:13      Last Updated        : 08/12/2010 16:27:13
  Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                   Per-SQL Time Limit  : UNUSED
  Number of Errors    : 0                        Number of Errors    : 0

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  92.05%
 Improvement Impact  :  92.05%
 Regression Impact   :  0%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall               1                  1
 Improved              1                  1

Top SQL Statements Sorted by their Absolute Value of Change Impact on the Workload
--------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|           |               | Impact on | Metric | Metric | Impact | % Workload | % Workload | Plan   |
| object_id | sql_id        | Workload  | Before | After  | on SQL | Before     | After      | Change |
----------------------------------------------------------------------------------------------------
|         4 | 5r2ufj2vqkk4p |    92.05% |  26429 |   2102 | 92.05% |       100% |       100% | y      |
----------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------

Como se observa en el reporte, con CBO se tiene una mejora del 92%

Otro reporte con detalle de cada sentencia:

Transcurrido: 00:00:00.32
rop@DESA11G> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Prueba_TSK', 
            'TEXT', 'TYPICAL', 'FINDINGS') from dual;
General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : Prueba_TSK                      SQL Tuning Set Name        : Prueba
  Task Owner   : ROP                             SQL Tuning Set Owner       : ROP
  Description  :                                 Total SQL Statement Count  : 1

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : EXEC_7692              Started             : 08/12/2010 16:27:22
  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 08/12/2010 16:27:22
  Description     :                        Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Comparison Metric: BUFFER_GETS
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : Prueba_EXEC_antes        Execution Name      : Prueba_EXEC_despues
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 08/12/2010 16:26:29      Started             : 08/12/2010 16:27:04
  Last Updated        : 08/12/2010 16:27:13      Last Updated        : 08/12/2010 16:27:13
  Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                   Per-SQL Time Limit  : UNUSED
  Number of Errors    : 0                        Number of Errors    : 0

Report Details: Statements Sorted by their Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------

SQL Details:
-----------------------------
 Object ID            : 4
 Schema Name          : ROP
 SQL ID               : 5r2ufj2vqkk4p
 Execution Frequency  : 1
 SQL Text             : select count(1) /*+ Prueba SPA */ from t

Execution Statistics:
-----------------------------
------------------------------------------------------------------------------------------------
|                       | Impact on | Value     | Value    | Impact  | % Workload | % Workload |
| Stat Name             | Workload  | Before    | After    | on SQL  | Before     | After      |
------------------------------------------------------------------------------------------------
| elapsed_time          |    91.11% |    15.477 |    1.376 |  91.11% |       100% |       100% |
| parse_time            |    -1900% |         0 |     .019 |   -1.9% |         0% |       100% |
| cpu_time              |     82.2% |      1.18 |      .21 |   82.2% |       100% |       100% |
| buffer_gets           |    92.05% |     26429 |     2102 |  92.05% |       100% |       100% |
| cost                  |   -59500% |         0 |      595 | -59500% |         0% |       100% |
| reads                 |    92.08% |     26418 |     2092 |  92.08% |       100% |       100% |
| writes                |        0% |         0 |        0 |      0% |         0% |         0% |
| io_interconnect_bytes |    92.08% | 216416256 | 17137664 |  92.08% |       100% |       100% |
| rows                  |           |         1 |        1 |         |            |            |
------------------------------------------------------------------------------------------------

Findings (3):
-----------------------------
 1. Ha mejorado el rendimiento de este SQL.
 2. La estructura del plan de ejecución SQL ha cambiado.
 3. La estructura del plan de ejecución SQL de la versión anterior de la carga
    de trabajo es distinta del correspondiente plan almacenado en el juego de
    ajustes SQL.


Execution Plan Before Change:
-----------------------------
 Plan Id          : 10810
 Plan Hash Value  : 1842905362

-----------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------
|  0 | SELECT STATEMENT     |      |      |       |      |      |
|  1 |   SORT AGGREGATE     |      |      |       |      |      |
|  2 |    TABLE ACCESS FULL | T    |      |       |      |      |
-----------------------------------------------------------------

Execution Plan After Change:
-----------------------------
 Plan Id          : 10811
 Plan Hash Value  : 2499172778

----------------------------------------------------------------------------------
| Id | Operation               | Name         | Rows   | Bytes | Cost | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |      1 |       |  595 | 00:00:08 |
|  1 |   SORT AGGREGATE        |              |      1 |       |      |          |
|  2 |    INDEX FAST FULL SCAN | SYS_C0046154 | 908242 |       |  595 | 00:00:08 |
----------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------


Transcurrido: 00:00:00.59

El ejemplo realiza una comparación trivial del procedimiento de SPA usando solo sqlplus (se podria usar EM para una comparación mas visual), pero que sirve para graficar su utilidad. Usando un procedimiento similar se podria realizar un analisis pre-upgrade que sirva para garantizar la estabilidad de las sentencias criticas post-upgrade sobre una base 11g. Para ello habria que realizar los siguiente pasos previos al analisis con SPA:

1. Crear el STS en la base a upgradear

Si la base a upgradear es 10g se puede crear el STS desde AWR determinando un intervalo representativo de la carga. Si la base a upgradear es 9i se puede obtener el STS desde un trace previamente generado en 9i durante un intervalo de carga real.

A continuación, muestro un ejemplo, que esta en la documentación oficial 10g, para
crear un STS desde AWR, usando un baseline previamente creado correspondiente a un intervalo con carga maxima "peak baseline", y se filtra para que el STS solo incluya las sentencias que se ejecutaron mas de 10 veces y con un ratio entre lecturas de disco y buffer gets mayor al 50%. Tambien se especifica que se recolecten las 30 sentencias TOP ordenadas por disk_reads/buffer_gets:

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN baseline_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
                  'peak baseline',
                  'executions >= 10 AND disk_reads/buffer_gets >= 0.5',
                   NULL,
                   'disk_reads/buffer_gets',
                   NULL, NULL, NULL,
                   30)) p;

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'my_sql_tuning_set',
             populate_cursor => baseline_cursor);
END;

Si no se creo un baseline, tambien se puede parametrizar usando dos snapshosts id de AWR para especificar el intervalo a procesar.

2. Migrar el STS a la nueva base (11g)

-- Crea la tabla stage para almacenar el STS para luego transferirlo a la nueva base
begin
    DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'TBL_STG_STS',
                                      schema_name => user);
end;

-- Graba el STS en la tabla stage
begin
    DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'Prueba',
                                    staging_table_name => 'TBL_STG_STS'); 
end;

Una vez creada y cargada la tabla stage, resta pasarla a la nueva base. Aca se puede usar data pump o el exp/imp convencional.

--  Crea el STS generado en 10g desde la tabla stage en 11g
begin
    DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'Prueba',
                                      staging_table_name => 'TBL_STG_STS',
                                      replace => TRUE); 
end;

En resumen, podriamos usar este procedimiento para evaluar rapidamente el impacto de cambios sobre las sentencias, y por ende en los planes de ejecución, producto de realizar cambios en el entorno, como por ejemplo, cambiar de equipo, de discos, agregar cpu, cambios de version de base de datos, cambios en parametrizacion, etc.
Se puede "jugar" con distintos entornos y ver como se comporta las sentencias, realizar benchmarking y analisis con diferentes estrategias y parametrizaciones, etc y asi poder inferir el comportamiento previo al cambio y prevenir la inestabilidad de las aplicaciones cuando ya es demasiado tarde y la vuelta atras implica un alto costo.

Mejorando la performance con binding con los nuevos Cursores Adaptables (Adaptive Cursors)

Todo aquel que haya asistido a un curso de sql o haya leido libros o documentación relacionada con "binding de variables" ya sabrá que es un concepto muy vinculado con el rendimiento de aplicaciones. En especial en los sistemas OLTP se recomienda fuertemente utilizar variables bind en las sentencias para evitar el hard parsing y asi minimizar la utilización de shared pool y obtener una mejor performance al saltear la etapa de parseo en la ejecuciones sucesivas a la primera ejecución de una sentencia dada.

El hecho de "bindear" si bien esta circunscripto dentro de las buenas prácticas tambien tiene ciertos problemas ya que no se conoce que valores se usaran para instanciar las variables bind y no queda claro para el optimizador que plan armar. A partir de 9i existe un mecanismo denominado "bind peeking" que permite al optimizador conocer los valores de la primera instanciación y por lo tanto armar un plan concreto. Esta nueva caracteristica introdujo nuevos problemas. El binding y los histogramas no se llevan del todo bien. Recordemos que los histogramas ayudan al optimizador ya que le proveen de la distribución de los datos.

Como dije antes, al bindear el optimizador no conoce de antemano con que valor se instanciará cada variable bind y por lo tanto no permite adecuar el plan a los valores de entrada. Si la distribución de los datos es uniforme esto no es un problema, pero que pasa si la distribución es dispar?. Que sucede si en la primera instanciación se genera un plan para usar full scan debido a que el valor de entrada tiene baja selectividad y luego las siguientes instanciaciones de valores tienen alta selectividad?. Estos ultimos generalmente requieren acceso por indices, pero el plan quedó fijado con la primera instanciación y por lo tanto usará full scan cuando en realidad debió usar acceso por indice, imaginense lo complicado que puede resultar esto. Por ejemplo, una mañana un programador ejecuta una consulta que instancia con un valor de borde o poco común para hacer un reporte complejo que recorre un porcentaje alto de filas y queda armado un plan con acceso full scan sobre una tabla grande, luego, si el cursor sigue en memoria, las aplicaciones usarán el mismo cursor para busquedas puntuales y usaran el plan generado por la consulta extraña (que usó full scan), suena caótico, no?.

En 11g R1 se agregó una nueva caracteristicas llamada "adaptive cursors" que soluciona el problema de "bind peeking". A continuación les muestro unas pruebas que realicé:

Para el test voy a crear una tabla sencilla con dos columnas X e Y. La columna Y tiene 3 posibles valores (A,B y C). Donde A tiene muy baja selectividad, B tiene selectividad media y C tiene selectividad alta.

rop@DESA11G> create table t (x int, y char(1))
  2  pctfree 90;
Tabla creada.

Cree la tabla T con pctfree en 90% para que se generen muchos bloques con no tantas filas.

rop@DESA11G> insert into t 
  2  select t_seq.nextval,
  3         case when (rownum between 1 and 4000000) then 'A'
  4              when (rownum between 4000001 and 5000000) then 'B'
  5              when (rownum between 5000001 and 5000010) then 'C'
  6         end
  7  from dual
  8  connect by rownum <= 5000010;

5000010 filas creadas.

rop@DESA11G> select bytes,blocks from user_segments where segment_name = 'T';

     BYTES     BLOCKS
---------- ----------
 679477248      82944

Generé una tabla que pesa mas de 600Mb.
Ahora creo un indice, recolecto estaditicas

rop@DESA11G> create index t_idx on t(y);

Índice creado.

rop@DESA11G>   begin
  2        dbms_stats.gather_table_stats(ownname => user,
  3                                      tabname => 'T',
  4                                      method_opt => 'for all indexed columns',
  5                                      cascade => true) ;
  6    end;
  7  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> select y,count(1)
  2          from t
  3          group by y;

Y   COUNT(1)
- ----------
A    4000000
B    1000000
C         10

En la ultima consulta se ve la distribución de la columna Y en la tabla T.

Voy a ejecutar una consulta y la voy a instanciar la variable bind :v con el valor 'A' para que se arme un plan que utilice full_scan:

rop@DESA11G> variable v char(1);
rop@DESA11G> exec :v:= 'A';

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> set autotr on
rop@DESA11G> select avg(x) from t where y = :v;

    AVG(X)
----------
21640320.5


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 | 22533   (1)| 00:04:31 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1666K|    11M| 22533   (1)| 00:04:31 |
---------------------------------------------------------------------------

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

   2 - filter("Y"=:V)


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

El plan usó efectivamente full_scan. Notar la gran cantidad de lecturas fisicas.

Consultemos la vista V$SQL:

rop@DESA11G> select sql_id,child_number,is_bind_sensitive, is_bind_aware
  2  from v$sql
  3  where sql_text = 'select avg(x) from t where y = :v';

SQL_ID        CHILD_NUMBER IS_BIND_SENSITIVE    IS_BIND_AWARE
------------- ------------ -------------------- ---------------
d9p5ax32fmqdn            0 Y                    N

Como se observa, en 11g se agregaron nuevas columnas a la vista v$sql relativas a las variables bind.

Instanciemos Y := 'C', que tiene muy alta selectividad:

rop@DESA11G> exec :v:='C';

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> set autotr on
rop@DESA11G> select avg(x) from t where y = :v;

    AVG(X)
----------
24640325.5


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 | 22533   (1)| 00:04:31 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1666K|    11M| 22533   (1)| 00:04:31 |
---------------------------------------------------------------------------

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

   2 - filter("Y"=:V)


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

Usó full scan, para retornar solo 10 valores, observemos nuevamente las lecturas fisicas.

Voy a consultar nuevamente la vista v$sql:

rop@DESA11G> select sql_id,child_number,is_bind_sensitive, is_bind_aware
  2  from v$sql
  3  where sql_text = 'select avg(x) from t where y = :v';

SQL_ID        CHILD_NUMBER IS_BIND_SENSITIVE    IS_BIND_AWARE
------------- ------------ -------------------- ---------------
d9p5ax32fmqdn            0 Y                    N

Sigue igual que antes.
Vuelvo a repetir la consulta anterior:

rop@DESA11G> select avg(x) from t where y = :v;

    AVG(X)
----------
24640325.5


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 | 22533   (1)| 00:04:31 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1666K|    11M| 22533   (1)| 00:04:31 |
---------------------------------------------------------------------------

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

   2 - filter("Y"=:V)


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

El plan sigue marcando acceso full scan, pero observemos ahora las lecturas fisicas. Fueron solo 4!.

rop@DESA11G> set autotr off
rop@DESA11G> select sql_id,child_number,is_bind_sensitive, is_bind_aware
  2  from v$sql
  3  where sql_text = 'select avg(x) from t where y = :v';

SQL_ID        CHILD_NUMBER IS_BIND_SENSITIVE    IS_BIND_AWARE
------------- ------------ -------------------- ---------------
d9p5ax32fmqdn            0 Y                    N
d9p5ax32fmqdn            1 Y                    Y

La consulta ahora muestra otro sqlid que es hijo del original con la columna is_bind_aware en "Y". El último plan mostró full scan aunque no concuerda con las pocas lecturas físicas y lógicas, ya que el método que usé para obtener el plan (dbms_xplan.diplay no tiene la opción de indicar el child) no muestra el plan del child 1 sino solo el plan padre (child=0). Después voy a mostrar con un trace 10046 que efectivamente usó acceso por indice (tambien se puede hacer con dbms_xplan.display_cursor indicando el child), lo cual cierra con la poca cantidad de lecturas que necesitó.

Consultando las nuevas vistas de "adaptive cursors" se ve como Oracle lleva registro de las ejecuciones y se adapta automaticamente a los cambios abruptos de selectividad al instanciar las variables:

rop@DESA11G> select  child_number, 
  2  bind_set_hash_value, 
  3  peeked, 
  4  executions, 
  5  rows_processed, 
  6  buffer_gets, 
  7  cpu_time
  8  from v$sql_cs_statistics
  9  where sql_id ='d9p5ax32fmqdn';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
------------ ------------------- - ---------- -------------- ----------- ----------
           1          2477564004 Y          1             21           4          0
           0           816821622 Y          1        4000001       82086          0


rop@DESA11G> ed
Escrito file afiedt.buf

  1  select * from v$sql_cs_histogram
  2  where sql_id ='d9p5ax32fmqdn'
  3* order by child_number,bucket_id
rop@DESA11G> /

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
000000044B9DC7B8 3303659956 d9p5ax32fmqdn            0          0          1
000000044B9DC7B8 3303659956 d9p5ax32fmqdn            0          1          0
000000044B9DC7B8 3303659956 d9p5ax32fmqdn            0          2          1
000000044B9DC7B8 3303659956 d9p5ax32fmqdn            1          0          3
000000044B9DC7B8 3303659956 d9p5ax32fmqdn            1          1          0
000000044B9DC7B8 3303659956 d9p5ax32fmqdn            1          2          0

6 filas seleccionadas.

Registrando la actividad y midiendo internamente rapidamente se detectó que el plan no era adecuado y se cambió.

A continuación muestro el resultado de tracear con el evento 10046:

El cursor principal o padre:

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

SQL ID: d9p5ax32fmqdn
Plan Hash: 1842905362
select avg(x)
from
 t where y = :v


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6     12.58      11.26     246075     246111          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12     12.58      11.26     246075     246111          0           3

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=82037 pr=82025 pw=0 time=0 us)
4000000   TABLE ACCESS FULL T (cr=82037 pr=82025 pw=0 time=19822 us cost=22641 size=28010066 card=4001438)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  direct path read                             1956        0.23          1.58
  SQL*Net message from client                     6        0.00          0.04
********************************************************************************

El cursor hijo 1:

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

SQL ID: 804rjbx6snjv4
Plan Hash: 3178687684
select avg(x)
from
 t where y = :v


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

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=0 pw=0 time=0 us)
     10   TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=0 us cost=4 size=7 card=1)
     10    INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 83154)


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
  SQL*Net message from client                     2        0.00          0.01
********************************************************************************

Una vez mas vemos que versión tras versión se van agregando "correcciones" al optimizador por costos para minimizar el margen de error y estabilizar los sistemas.

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.