OraMDQ

Consideraciones importantes antes de cambiar tablas a modo nologging

2011-07-31T06:11:00.000-07:00

Las tablas en una base Oracle por default registran en redo todos los cambios (transacciones) que se le fueron aplicando. Esto sirve para recuperar, ante una caida abrupta de la base de datos, aplicando un procedimiento denominado rolling forward, las transacciones confirmadas que no llegaron a impactarse en los datafiles. El grabado en redo es sincronico y muchas veces es causante de demoras generales en la base de datos, en especial en bases OLTP con alta concurrencia y alta tasa transaccionabilidad, uno de los eventos de espera mas comunes es el log file sync.

Cuando en un reporte AWR o statspack vemos entre los TOp 5 events este tipo de eventos y buscamos en manuales, metalink o en foros, las dos recomendaciones principales son: a) bajar la transaccionabilidad, esto es minimizar los commits, y/o rollbacks, todo lo que sea posible sin afectar las reglas de negocio modeladas ó b) optimizar la escritura en redo, usando por ejemplo discos mas rápidos, recordemos que la escritura en redos, al contrario de la escritura en datafiles, es escritura secuencial, ideal es poner los redos sobre discos de estado solido, por la muy baja latencia y tambien verificar que no esten alojados sobre raid 5, siempre se recomienda raid 1 para estos archivos.

Otra opción interesante es pensar en generar la minima cantidad de redo sin modificar ninguno de los dos puntos tratados en el parrafo anterior. Cambiar a un tabla a nologging permite minimizar el redo generado para ciertas operaciones. Un mito muy común es poner todas las tablas en nologging y pensar que esto impide la registración de cambios en redo, nada mas lejos. La anulación de redo es imposible, ya que es un mecanismo fundamental del funcionamiento de la base de datos, lo que si se logra es reducir bastante la cantidad de redo, con la consecuencia que esto implica, que es ni mas ni menos que ante una falla de la instancia la operacion realizada en modo nologging no se puede recuperar, la unica opción es reprocesar. Las siguientes operaciones son las unicas disponibles para usar nologging:

create table...as select
create index
direct load con SQL*Loader
direct load INSERT (usando el hint APPEND)
alter table...move partition
alter table...split partition
alter index...split partition
alter index...rebuild
alter index...rebuild partition

Ahora, como siempre, comprobemos las operaciones que registran, y las que no, los cambios sobre redo:

Voy a crear una tabla en modo logging (default) y luego voy a a ver el redo que consumió dicha operación.

SQLPLUS> create table t as select * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 1610k

La cantidad de redo fue de 1610k, veamos que sucede si creamos la tabla en modo nologging:

SQLPLUS> create table t nologging as select * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 100k

El redo usado no fue nulo pero si mucho menor, en el caso aproximadamente 16 veces inferior al de crear la tabla en modo logging.

En el siguiente ejemplo vamos a comparar el redo insumido en una operacion de insert convencional con un insert en modo directo, ambos sobre una tabla nologging:

SQLPLUS> create table t nologging as select * from dba_tables where 1=0

SQLPLUS> insert into t select * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 1497k

SQLPLUS> insert into t select /*+ APPEND */ * from dba_tables

SQLPLUS>@myredosize.sql

SQLPLUS> 34k

Lo que se puede observar es que en el primer caso, el consumo de redo fue similar al del create table del ejemplo anterior en modo logging. Esto muestra claramente que no todas las operaciones inhiben el redo, en el caso del insert convencional sobre una tabla en nologging no hay diferencia de hacerlo sobre una tabla logging. La diferencia se ve en el insert directo, que justamente es una de las pocas tipos de sentencias que aprovechan el nologging.

Como siempre, es recomendable leer la documentación detenidamente para ver si tal funcionalidad o tal caracteristica es beneficiosa para lo que queremos hacer. Si por el contrario no entendemos bien cierto funcionamiento, tal vez, como es en el caso descripto en esta nota, tendemos a setear todas las tablas que podamos en nologging sin reducir como esperabamos el redo. Además hay que saber evaluar las consecuencias negativas que podría ocasionar tener tablas en nologging y perder la información al no estar sustentada con los redo's.

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

2011-03-23T08:23:00.000-07:00

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.

Factorial de n usando una sentencia SQL simple.

2011-02-18T04:04:00.000-08:00

Quizás alguna vez se han preguntado porque no existe una función de agregación PROD, que obtenga el producto de una columna numerica o expresión de una tabla, me parece que no, verdad?. Muy probablemente a la mayoria, nunca se le ha generado esa inquietud, porque de hecho, no tiene, a priori, demasiada utilidad en los sistemas reales. De todas formas, la idea de la nota es mostrarles que tan simple y elegante puede ser una solución usando solo una simple sentencia SELECT, independientemente si le encuentran alguna utilidad o no.

Como una breve introducción, voy a repasar las funciones de agregación, que son variadas. Las mas conocidas y usadas mas frecuentemente son:

SUM: Retorna la suma de una columna o expresión
AVG: Retorna el promedio de una columa o expresión
MIN: Retorna el valor mínimo
MAX: Retorna el valor máximo

Otras funciones, son las estadisticas, que suelo usar cuando necesito analizar tendencias y patrones, y son:

STDDEV : Retorna la desviación standard de una columna o expresión
VARIANCE : Retorna la varianza de una columna o expresión
MEDIAN : Retorna la mediana de una columna o expresión

existen muchas mas funciones de agregación para usar, ver detalle en el manual oficial para la versión de BD que necesiten, por ejemplo para 11g R2: SQL Reference 11.2 R2 (Funciones de Agregación)

En esta oportunidad, voy a mostrar como simular la función PROD usando algunas reglas matematicas simples, y por ultimo, usar dicha función para obtener el factorial de un número dado:

Para comenzar, voy a definir las dos funciones que voy a utilizar:

ln(n) = Retorna el logarimo natural de n

y su función inversa:

exp(n) = Retorna e elevado a la n

Ahora, partiendo de la siguiente propiedad matematica de los logaritmos:

ln(a) + Ln(b) = ln(a * b)

Elevando e con cada lado de la ecuación anterior:

exp(ln(a)+ln(b)) = exp(ln(a*b))

Dado que exp y ln son funciones inversas, entonces se anulan y nos queda a multiplicado por b:

exp(ln(a*b)) = a*b

En este punto, ya tenemos definida y explicada la regla. Ahora voy a mostrar un ejemplo de uso, para obtener el factorial:

La sentencia SQL para obtener el factorial de N tiene la siguiente forma:

select exp(sum(ln(rownum))) from dual
connect by rownum <= N ;

Un ejemplo usando como consola sqlplus:


rop@DESA10G> variable n number
rop@DESA10G> exec :n := 3;

Procedimiento PL/SQL terminado correctamente.


rop@DESA10G> select exp(sum(ln(rownum))) from dual
2            connect by rownum <= :n ; 

EXP(SUM(LN(ROWNUM)))
--------------------                   
6 

rop@DESA10G> exec :n := 6;

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> select exp(sum(ln(rownum)))
             from dual
2            connect by rownum <= :n ; 

EXP(SUM(LN(ROWNUM)))
--------------------                 
720

Como se ve, se puede obtener el factorial de cualquier número con una simple sentencia. También se podria usar para multiplicar las columnas de una tabla, multiplicar expresiones, etc.

Row Prefetching en Oracle

2011-01-31T04:38:00.000-08:00

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)

2011-01-27T05:43:00.000-08:00

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.

Un ejemplo de como realizar calculos con fechas con sql

2011-01-17T04:15:00.000-08:00

Hace unos dias me llegó un mail de esos tipicos mails cadena que supuestamente traen suerte si se lo envias a 20 personas mas. Claramente estos mails no tienen sustento real en general y actuan por ingenieria social como mecanismo de generación de tráfico basura. Rara vez alcanzo a leer mas de 2 lineas antes de borrarlos, pero en este caso me llamó la atención y despertó mi curiosidad, ya que probar su veracidad con una consulta sql seria muy facil. Claramente esto no tiene ninguna utilidad mas que mostrarles como resolver cuestiones y relaciones de fechas usando, en este caso Oracle, como una
calculadora extremadamente costosa.

El mail decia algo asi: "Este año el mes de julio tiene 5 viernes, 5 sabados y 5 domingos, esto se da cada 823 años, esto se denomina saco de dinero, envia esto a 20 amigos... bla bla bla". Cuando lo leí, me dí cuenta que no podia ser real ya que tener esa periodicidad tan exacta y extensa, tratandose de fechas, no era posible. Para probarlo prolijamente, realicé una consulta de forma tal de generar fechas automaticamente, comenzando desde una fecha bien lejana (500000 años hacia atrás) y sumando cada vez que el mes fuera Julio (07) los dias viernes, sabado y domingo (6,7,1). Si la suma es 15 entonces en ese año se da que lo que reza el mail en cuestión. A continuación les muestro la consulta y el resultado:


select to_char(dt,'YYYY') dt,count(1)
from (select sysdate-500000+rownum dt
     from dual
     connect by rownum <= 500000)
where to_char(dt,'MM') = '07'  
 and to_char(dt,'d') in (6,7,1)
group by to_char(dt,'YYYY')
having count(1) > 14
order by to_number(dt) desc

 1    2005    15
 2    1994    15
 3    1988    15
 4    1983    15
 5    1977    15
 6    1966    15
 7    1960    15
 8    1955    15
 9    1949    15
10    1938    15
11    1932    15
12    1927    15
13    1921    15
14    1910    15
15    1904    15
16    1898    15
17    1892    15
18    1887    15
19    1881    15
20    1870    15

Como se ve, en el 2005 se dió por ultima vez la relación citada, pasaron solo 6 años para que se repita y no 823!!!.

Análisis de consumo de espacio REDO global, por cada sesión y por cada sentencia

2011-01-14T07:32:00.000-08:00

El consumo de espacio de redo (redo consumption) es algo inevitable, aunque es posible minimizarlo en ciertos casos y con ciertas operaciones, no se puede cancelar por completo. El redo es necesario para asegurar que ante una caida imprevista de la base, los datos en los bloques modificados, commiteados y todavia no persistidos en disco (dirty blocks), puedan recuperarse al levantar nuevamente la base con un proceso automatico denominado "rolling forward".

Si la base esta en modo archivelog, cada redo log se copia aparte para que no se sobreescriba y asi permitir, cuando se lo requiera, por ejemplo, poder realizar backup con la base online, ir a un estado anterior de la base, recuperar una base usando el ultimo backup full y aplicando los archives, etc.

Si el consumo de redo es importante, la I/O se va a ver comprometida y podría afectar el rendimiento general de la base. Recordar que la escritura en redo es secuencial, distinta a la escritura en datafiles. Siempre alojar los redo sobre raid 1 o similares y separados de los datafiles. Además, tambien tener en cuenta que con cada commit se debe escribir en redo en forma sincronica. Esto significa serializar, y por lo tanto debe ser lo mas eficiente posible.

Para que puedan medir el consumo de redo, y en consecuencia cantidad de archives generados, en sus bases, les paso una serie de metodos y consultas que uso habitualmente. Las consultas permiten obtener lo siguiente:

Consumo de Redo de la ultima hora
Consumo de Redo por sesión
Consumo de Redo por Consultas (*)

(*) El consumo por sentencia no se puede obtener en forma directa, entonces armé un procedimiento para ir guardando espacio por sqlid. Esta forma puede no ser muy precisa en ciertas ocasiones. Tiene que usarse teniendo ciertos requisitos y consideraciones. Puede ser muy util para testear el consumo de redo de una aplicación antes de la puesta en producción.

Para obtener consumo de redo global de la ultima hora (desde el ultimo snapshot de AWR) (redo consumption by DB)

Ejecutar la siguiente consulta que da el consumo de la base de datos desde el ultimo snapshot, es decir desde la ultima hora exacta. Es decir si lo ejecutamos a las 16:30, nos dará el consumo desde las 16hs para toda la base


select round((t1.value-t2.value)/1024/1024,2) "Consumo_Redo(MB)"
from
(select value from v$sysstat where name = 'redo size') t1,
(select value from dba_hist_sysstat
where stat_name = 'redo size'
and snap_id = (select max(snap_id) from dba_hist_sysstat)) t2

Para obtener consumo de redo por sesión (redo consumption by session)

Ejecutar la siguiente consulta, que da el consumo de redo por sesión. Considerar que el acumulado es desde que la sesión se abre


select se.INST_ID,
 se.SID,
 se.USERNAME,
 se.OSUSER,
 se.TERMINAL,
 se.PROGRAM,
 round(ss.value/1024/1024,2) "Redo Size(Mb)"
from gv$session se,
gv$sesstat ss,
v$statname sn
where se.SID = ss.SID
and ss.STATISTIC# = sn.STATISTIC#
and sn.NAME = 'redo size'
and username is not null
order by  "Redo Size(Mb)" desc

Para obtener consumo de redo por sentencia (redo consumption by sqlid/sentence)

Primero realizar el setup copiado a continuación (copiar el contenido en un archivo .sql y ejecutarlo desde sqlplus en el usuario elegido como repositorio) :


Rem
Rem  setup_redo_usage.sql (Redo Usage by Sentence/SQLID)
Rem
Rem  NOMBRE
Rem       setup_redo_usage.sql
Rem
Rem  DESCRIPCION
Rem       Configura la programacion de un job para recolectar         
Rem       información de uso de espacio de redo por sqlid.
Rem
Rem  NOTAS - REQUISITOS DE INSTALACION
Rem       El usuario que ejecute el script debera tener quota
Rem       suficiente sobre un tablespace auxiliar (ej:TOOLS)
Rem       para poder almacenar la informacion de monitoreo generada
Rem       cada 5".
Rem       Es necesario otorgar privilegios de SELECT sobre las
Rem       vistas dinamicas:
Rem
Rem       Conectado con sys hacer:
Rem
Rem       grant select on gv_$sesstat to ;
Rem       grant select on v_$statname to ;
Rem       grant select on gv_$session to ;
Rem       grant select on gv_$sqlarea to ;
Rem      
Rem
Rem  MODIFICADO (DD/MM/YY)
Rem
Rem
Rem      Pablo A. Rovedo  06/01/11  -- Creado     v 1.0
Rem

-- Borra la tabla TBL_REDO_USAGE si existe

drop table tmp$redo_usage
/

-- Crea la tabla de repositorio TBL_REDO_USAGE

create table tmp$redo_usage
(
USERNAME     VARCHAR2(30),
OSUSER       VARCHAR2(30),
TERMINAL     VARCHAR2(30),
PROGRAM      VARCHAR2(48),
SQL_ID       VARCHAR2(13),
SQL_FULLTEXT CLOB,
REDO_SIZE    NUMBER,
SNAP         DATE,
INST_ID      NUMBER(1)
)
pctfree 0
nologging
/


-- Crea el procedimiento que recolecta la información de Alocación de
-- espacio de redo

create or replace procedure p_get_redo_usage
is
begin
insert /*+ append */ into tmp$redo_usage
select s.USERNAME,
       s.OSUSER,
       s.terminal,
       s.PROGRAM,
       s.SQL_ID,
       sa.SQL_FULLTEXT,
       round(ss.VALUE/1024/1024) redo_size,
       sysdate,
       s.inst_id
from gv$sesstat ss,
     v$statname sn,
     gv$session s,
     gv$sqlarea sa
where s.sid = ss.sid
  and s.inst_id = ss.inst_id
  and sn.STATISTIC# = ss.STATISTIC#
  and s.sql_id = sa.SQL_ID
  and s.inst_id = sa.inst_id
  and sn.name = 'redo size'
  and s.username not in ('SYS','SYSTEM');
commit;
end;
/

-- Borra el job si ya existe

begin
dbms_scheduler.drop_job(job_name => 'J_SAVE_REDO_USAGE',
                     force => true);
end;
/

-- Crea un nuevo job

begin
dbms_scheduler.create_job(
job_name => 'J_SAVE_REDO_USAGE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin p_get_redo_usage; end;'
,start_date => sysdate
,end_date => sysdate+1/24  -- Recolecta durante 1 hora
,repeat_interval => 'FREQ=SECONDLY;BYSECOND=0,5,10,15,20,25,30,35,40,45,50,55'
,enabled => TRUE
,comments => 'Almacena Informacion de Alocación de espacio de redo');
end;
/

Luego de terminada la recolección (en el script se definió un intervalo
de monitoreo de 1 hora) se puede ejecutar la siguiente consulta para ver
los resultados:


select sql_id,sum(redo_size) "redo_size(Mb)"
from (select unique sql_id,
       redo_size-lead(redo_size) over (partition by sql_id order by snap desc) redo_size
from tmp$redo_usage)
where redo_size is not null
group by sql_id
order by "redo_size(Mb)" desc

Comparacion de Métodos y Tipos de joins en Oracle

2010-12-22T06:07:00.000-08:00

Para armar el plan de ejecución el optimizador debe realizar las siguientes acciones básicas:

Determinar el orden de evaluación de las tablas.
Determinar el método de join.
Determinar los tipos de accesos (access path, ej: full scan, rowid, index range, etc).
Determinar el orden de filtrado.

Los 3 primeros forman la estructura de árbol que da soporte al plan de ejecución. El 4to define el caudal de datos que "fluye" por el árbol. En esta oportunidad solo voy a concentrarme en el punto 2, dejando los otros para futuras notas.

Los joins se realizan siempre entre dos set de datos, si la sentencias tuviera mas de dos tablas se determinan la dos primeras tablas a joinear y el resultado se joinea con la siguiente tabla, ese resultado se joinea con la siguiente tabla y asi siguiendo.

Los métodos de join mas comunes son:

NESTED LOOP JOIN
SORT MERGE JOIN
HASH JOIN
CARTESIAN JOIN

Descripción de NESTED LOOP JOIN

Los dos set de datos procesados por nested loop (NL) se llaman outer loop e inner loop. El outer loop es ejecutado una sola vez y el inner loop una vez por cada registro retornado por el outer loop. Las principales caracteristicas de NL son:

Son la mejor opción cuando se requiere obtener la primera fila lo antes posible, de esta forma no es necesario tener que procesar todos los datos para comenzar a retornar resultados. Esto es muy performante, por ejemplo, para aplicaciones front-end que usan paginación.
Permiten aprovechar los filtros y condiciones de joins usando los indices disponibles.
Se pueden usar con cualquier tipo de joins.

Descripción de HASH JOIN

Los dos set de datos procesados por hash join (HJ) son build input y probe input. Con el build input se construye en memoria (o en tablespace temporal si no hubiera suficiente memoria fisica disponible) una tabla de hash. Una vez que se construyó la build input se comienza a procesar usando para cada registro de la probe input la tabla de hash de modo de comparar si se satisface o no la condición de join. Las principales caracteristicas de HJ son:

La tabla de hash usualmente es contruida usando el set de datos mas pequeño.
No todos los tipos de joins pueden usarse, por ejemplo los theta joins y cross joins no son soportados.
Para que se comiencen a retornar las filas la tabla de hash debe estar creada y procesada.
HJ no puede aplicar condiciones de joins usando indices.

Descripción de SORT MERGE JOIN

Los dos set de datos procesado por el merge join (MJ) son leidos y ordenados de acuerdo a las columnas referenciadas en la condición de join. Una vez que los dos set estan ordenados son mezclados (merge). El ordenamiento se realiza en memoria siempre y cuando la memoria fisica sea suficiente, sino alcanza la memoria (pga) se deberá usar espacio temporal como soporte lo cual, como es esperable, ralentizará las operaciones. Las principales caracteristicas de MJ son:

Ambos data set deben ser ordenados antes del merge
La primera fila del result set recien es retornada cuando comienza el merge.
Todos los tipos de joins son soportados.

Tipos de Joins

Existen dos sintaxis posibles para usar con joins:

SQL-ANSI-86
SQL-ANSI-92

La primera es la que uso en general, y es la mas común, la segunda es mas nueva y es standard para otros motores de base de datos, es mas común para la nuevas generaciones de desarrolladores y dbas o para los que vengan de usar sql server . Es, además mas clara porque separa los filtros de los joins, lo cual es mas sencillo para leer e interpretar. Ahora voy a hacer un breve repaso de los tipos de joins con ejemplos en la dos notaciones:

Cross Join

Tambien llamado producto cartesiano. En general se usa cuando no se especifican los joins para algunas tablas. Tambien lo he visto en ciertos planes particulares donde es la mejor opción , aunque es muy raro


select emp.ename,dept.dname
from emp, dept

select emp.ename,dept.dname
from emp CROSS JOIN dept

Theta Join

Tambien llamados inner join, y retorna solo las filas que satisfacen una condición de join


select emp.enam, salgrade.grade
from emp, salgrade
where emp.sal between salgrade.local and salgrade.hisal

select emp.ename, salgrade.grade
from emp INNER JOIN salgrade on emp.sal between salgrade.losal and salgrade.hisal

Equi Join

Tambien llamado natural join, es un caso especial de theta join donde solo se usan operadores
de igualdad para las condiciones de join


select emp.ename, dept.dname
from emp, dept
where emp.deptno = dept.deptno

select emp.ename, dept.dname
from emp NATURAL JOIN dept on emp.deptno = dept.deptno

Self Join

Son un caso especial de theta join donde la tabla joineada es la misma.


select emp.ename,mgr.ename
from emp, emp mgr
where emp.mgr = mgr.empno


select emp.ename, mgr.ename
from emp JOIN emp mgr on emp.mgr = mgr.empno

Outer Join

Los outer join extienden el result set de los theta joins. Con este tipo de join todas las filas de una de la tablas involucradas son retornadas aunque no matcheen con las columnas de join de la otra tabla, retornando NULL en las columnas de los registros de la tabla que no matchea. Oracle usa una sintaxis propia pero lo recomendable es usar la sintaxis ansi-92 ya que es portable a otros motores de base de datos.

Por ejemplo, para ver la cantidad de empleados por departamento, considerando tambien los departamentos que no tienen ningun empleado:


select dept.dname,count(emp.ename)
from  emp,  dept
where dept.deptno = emp.DEPTNO (+)
group by dept.dname

select dept.dname,count(emp.ename)
from dept LEFT OUTER JOIN emp on (dept.deptno = emp.DEPTNO)
group by dept.dname

Con la nueva sintaxis tambien se puede usar RIGHT OUTER JOIN y FULL OUTER JOIN.

A partir de Oracle 10g es posible usar un nuevo tipo de join ( o subtipo) llamado partitioned outer join. Este tipo de join a priori pareceria estar relacionado con tablas particionadas pero no, en este caso, el concepto de particionado es que los datos se dividen en subset durante la ejecución


select dept.dname, count(emp.empno)
from dept LEFT JOIN emp PARTITION BY (emp.job)  ON emp.deptno = dept.deptno
group by dept.dname

Semi Join

Este tipo de join entre dos tablas retorna solo las filas de una de las tablas cuyas columas de join existen en la otra tabla.

Por ejemplo para ver que empleados tienen bonus:


select *
from  scott.emp emp
where exists (select null from scott.bonus bon
            where emp.EMPNO = bon.ename)
         
select *
from  scott.emp emp
where empno in (select empno from scott.bonus bon)

Anti Join

Este tipo de join entre dos tablas retorna solo las filas de una de las tablas cuyas columnas de join NO existen en la otra tabla

Por ejemplo para consultar los empleados que no tienen bonus:


select *
from  scott.emp emp
where not exists (select null from scott.bonus bon
            where emp.EMPNO = bon.ename)
         
select *
from  scott.emp emp
where empno not in (select empno from scott.bonus bon)

Una vez repasados los tipos de joins retomemos los métodos de joins y veamos con algunos ejemplos como se arman los planes según cada método:

Como siempre voy a crear el entorno para poder probar y si alguien quiere testearlo en su propio ambiente puede hacerlo:


-- Creo tabla T1
create table t1
as
select rownum c1,
     trunc(dbms_random.value(1,100)) c2,
     dbms_random.string('a',100) c3
from dual
connect by rownum <= 1000000  -- Creo tabla T2 create table t2 as select rownum c1,        trunc(dbms_random.value(1,100000)) c2,        dbms_random.string('a',100) c3 from dual connect by rownum <= 2000000  -- Creo un indice para la tabla T2 create index t2_idx on t2(c2)  -- Recolecto estadisticas para los segmentos creados: begin     dbms_stats.gather_table_stats(ownname => user,tabname => 'T1',cascade => true);
  dbms_stats.gather_table_stats(ownname => user,tabname => 'T2',cascade => true);
end;

Ahora voy a mostrar cada método de join, obviamente lo voy a forzar con hints para hacerlo mas sencillo:

Forzamos para que se use NESTED LOOP JOIN:


select  /*+ leading(t1) use_nl(t2) index(t2)  */ count(1)
from t1, t2
where t1.c2 = t2.c2
and t1.c3 > 'zzz'

Plan hash value: 3705558160

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |   109 |  3602   (1)| 00:01:19 |
|   1 |  SORT AGGREGATE     |        |     1 |   109 |            |          |
|   2 |   NESTED LOOPS      |        |    21 |  2289 |  3602   (1)| 00:01:19 |
|*  3 |    TABLE ACCESS FULL| T1     |     1 |   104 |  3600   (1)| 00:01:19 |
|*  4 |    INDEX RANGE SCAN | T2_IDX |    21 |   105 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

 3 - filter("T1"."C3">'zzz')
 4 - access("T1"."C2"="T2"."C2")

Forzamos para que se use MERGE JOIN:



select /*+ ordered use_merge(t2) */ count(1)
from t1, t2
where t1.c2 = t2.c2
and t1.c3 > 'zzz'

Plan hash value: 1164406001

------------------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   109 |       | 10796   (2)| 00:03:55 |
|   1 |  SORT AGGREGATE         |        |     1 |   109 |       |            |          |
|   2 |   MERGE JOIN            |        |    21 |  2289 |       | 10796   (2)| 00:03:55 |
|   3 |    SORT JOIN            |        |     1 |   104 |       |  3601   (1)| 00:01:19 |
|*  4 |     TABLE ACCESS FULL   | T1     |     1 |   104 |       |  3600   (1)| 00:01:19 |
|*  5 |    SORT JOIN            |        |  1997K|  9754K|    45M|  7195   (2)| 00:02:37 |
|   6 |     INDEX FAST FULL SCAN| T2_IDX |  1997K|  9754K|       |  1007   (2)| 00:00:22 |
------------------------------------------------------------------------------------------

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

 4 - filter("T1"."C3">'zzz')
 5 - access("T1"."C2"="T2"."C2")
     filter("T1"."C2"="T2"."C2")

Forzamos para que se use HASH JOIN:



select /*+ leading(t1) use_hash(t2) */ t1.*
from t1, t2
where t1.c2 = t2.c2
and t1.c3 > 'zzz'


Plan hash value: 442409572

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |   109 |  4620   (2)| 00:01:41 |
|   1 |  SORT AGGREGATE        |        |     1 |   109 |            |          |
|*  2 |   HASH JOIN            |        |    21 |  2289 |  4620   (2)| 00:01:41 |
|*  3 |    TABLE ACCESS FULL   | T1     |     1 |   104 |  3600   (1)| 00:01:19 |
|   4 |    INDEX FAST FULL SCAN| T2_IDX |  1997K|  9754K|  1007   (2)| 00:00:22 |
---------------------------------------------------------------------------------

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

 2 - access("T1"."C2"="T2"."C2")
 3 - filter("T1"."C3">'zzz')

Comparando los 3 planes para cada método se ve que solo con NL usa index range en lugar de usar un full scan por el indice. Los tiempos de NL son los mejores según la estimación del plan. Ejecutando cada uno de los 3 queries se puede ver que dicha estimación coincide con la realidad y que NL es el mas rapido. Esto se da porque tanto con HJ como con MJ no se puede usar el indice para buscar las coincidencias sobre la tabla T2 basado en los valores retornados por la tabla T1. Con NL se aprovecha dicha información para acceder mas puntualmente, via el indice. Cuanto menor sea la selectividad (o mas fuerte) el método NL tendrá mayor ventaja sobre los otros dos.

Cuando una consulta utiliza un indice, pero no el mejor indice posible

2010-12-15T04:31:00.001-08:00

Muchas veces me preguntaron por que una consulta no responde en un tiempo adecuado cuando el plan de ejecución muestra que se esta utilizando un indice. La respuesta en ciertos casos es muy sencilla y se debea a que el indice no es lo mas selectivo posible, no filtra todo lo que podria filtrar. Decir que una consulta usa un plan que accede por un indice no es suficiente para asegurar que se haya encontrado el camino mas eficiente. Para mostrarles un poco de que estoy hablando voy a armar un caso, un tanto trivial pero no por eso menos ilustrativo, para que se entienda la idea.

Voy a crear una tabla T con 3 columnas. La columna COL1 va a tener 1000 valores distintos y la columna COL2 va a tener 10 valores posibles. Además agrego la columna COL3 de relleno


create table t
as 
select mod(rownum,1000) col1,
       mod(rownum,100000) col2,         
      dbms_random.string('a',50) col3
from dual
connect by rownum <= 1000000

Una vez creada la tabla voy a crear un indice por COL1 y recolecto las estadisticas para la tabla y para el indice:


create index t_idx on t (col1)

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

Ejecuto la siguiente consulta y extraigo el plan usando DBMS_XPLAN.DISPLAY_CURSOR para que el plan sea mas detallado (en una nota futura voy a usar el mismo método para mostrar como ver como se "confunde" el optimizador cuando no se dispone de las estadisticas adecuadas):


select /*+ gather_plan_statistics */ * from t
where col1 = 9
and col2 = 9

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  25p2md7bszhj6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where col1 = 9   and col2
= 9

Plan hash value: 1020776977

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |     10 |00:00:00.01 |    1006 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |      1 |     10 |00:00:00.01 |    1006 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |      1 |   1000 |   1000 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------

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

1 - filter("COL2"=9)
2 - access("COL1"=9)

En el paso 2 del plan se observa que la estimación fue buena (E-Rows=A-Rows) pero la cantidad filtrada fue 1ooo filas, cuando realmente se deberian haber filtrado 10, como se ve en el paso 1 (A-Rows)

(Aclaración: A-Rows es la cantidad de filas reales y E-Rows es la cantidad de filas estimada)

Ahora voy a eliminar el indice T_IDX y voy a crear otro con el mismo nombre pero indexando por COL1 y COL2. Veamos el nuevo plan:


drop index t_idx

create index t_idx on t (col1,col2)

select /*+ gather_plan_statistics 2 */ * from t
where col1 = 9
and col2 = 9

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  25p2md7bszhj6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where col1 = 9   and col2
= 9

Plan hash value: 1020776977

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |     10 |00:00:00.01 |      14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |      1 |     10 |     10 |00:00:00.01 |      14 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |      1 |     10 |     10 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------

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

2 - access("COL1"=9 AND "COL2"=9)

Ahora el paso 2 retorna al paso 1 solo 10 filas, que es la cantidad de filas total retornada por la consulta. Observar que la cantidad de buffers utilizado es bastante inferior al caso anterior.
Esto demuestra que el nuevo indice fue 100 veces mas selectivo y por lo tanto se necesitaron menos recursos, en consecuencia menos tiempo de procesamiento, para obtener la misma salida.

La moraleja es que nunca hay que conformarse con solo verificar que la sentencia accede por un indice y profundizar en el análisis sobre el esquema actual de indexación comprobando si es el mejor indice posible o si se puede buscar alguna otra combinación mas eficiente.

Como realizar update/delete masivos en forma efectiva

2010-11-24T09:53:00.000-08:00

En esta nota voy a mostrarles un método efectivo para modificar o eliminar una gran cantidad de filas sobre una tabla grande. En general las tablas voluminosas se encuentran particionadas para lograr escalar en forma natural. El particionamiento principalmente provee 3 tipos de beneficios: 1) mejora la performance, 2) facilita la administración y mantenimiento y 3) incrementa la disponibilidad de los datos. Resolver una consulta usando como tabla subyacente particionada puede verse de la misma forma que resolver un problema dividiendolo en partes. La conocida premisa: divide y conquistaras es el principal objetivo detrás de particionar.

Desde que se introdujo el feature de partitioning (Oracle 8) se ha ampliado notablemente el set de operaciones posibles sobre tablas e indices para dar soporte y manejar las tablas/indices particionados. Con cada nuevo release se fueron agregando distintas opciones, métodos de particionado y operaciones para manipulación de segmentos. Los distintos features introducidas en cada release son:

Oracle 8 (1997)

Partition Pruning (*)
Range Partitioning (incluye operaciones ADD, DROP, RENAME, TRUNCATE, MODIFY, MOVE, SPLIT y EXCHANGE)


Oracle 8i (1999)

Particionamiento Hash
Particionamiento compuesto: range/hash
Se agregó la operación MERGE

Oracle 9i R2 (2002)

List Partitioning
Particionamiento compuesto: Range/List
Cláusula UPDATE GLOBAL INDEXES


Oracle 10g R1 (2004)

Indices globales particionados por Hash y List

Oracle 10g R2 (2005)

Se incremento el limite de particiones/subparticiones de 65k a 4M

Oracle 11g R1 (2007)

Particionamiento compuesto: range-range, list-range, list-list y list-hash.
Se agregó particionamiento por intervalo, por referencia y de sistema.

Oracle 11g R2 (2009)

Columnas virtuales como primary key para tablas particionadas referenciadas.
Indices particionados por sistema para tablas particionadas por lista.

Como se puede ver, practicamente en cada nuevo release hubo algún agregado de nueva funcionalidad. Sin embargo, a mi entender, el principal feature existe desde el primer release con partitioning (1997). Me refiero al partition pruning o poda de partición, que posibilita que el optimizador (siempre hablando de CBO) elija en forma automática, precisa y transparente la partición o particiones donde se encuentra los datos requeridos. Esto permite segmentar los datos y solo procesar los que nos interesan, sin tener que agregar ninguna inteligencia adicional en el código de aplicación.

Con respecto a las operaciones, la gran mayoria existen desde Oracle 8, solo se agregó tiempo después el MERGE. Una operación muy interensante es EXCHANGE, con la cual se puede intercambiar una tabla sin particionar con una partición. Justamente es esta la operación que voy a usar para proponer una alternativa rapida para cambiar o borrar gran cantidad de filas sobre tablas particionadas. A continuación, somo suelo hacer, voy a mostrar los pasos en detalle y comparar los tiempos y uso de recursos:

Voy a crear una tabla T particionada por lista con 3 particiones


create table t(c1 int,c2 varchar2(10),
c3 date,
c4 char(1))
partition by list (c4)
(
partition t_a values ('A') ,
partition t_b values ('B') ,
partition t_c values ('C')
);

Ahora voy a insertar 10M de filas distribuidas en forma arbitraria sobre las particiones:


insert into t
select rownum,
dbms_random.string('a',10),
sysdate-dbms_random.value(-100,100),
chr(trunc(dbms_random.value(65,68)))
from dual
connect by rownum <= 10000000;

Inserto 5M de filas sobre la partición en la que voy a trabajar para tener mas filas:


insert into t
select rownum+10000000,          
dbms_random.string('a',10),
sysdate-dbms_random.value(-100,100),
'A'  from dual
connect by rownum <= 5000000;

Luego de cargados todos los valores se confirman (commit) y luego se recolectan estadisticas.
Veamos el plan para una consulta que cuenta filas sobre la partición 1 (t_a):


explain plan for
select count(1)
from t where c2 > 'R' and c4 = 'A';

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2901716037

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |  8455   (3)| 00:03:04 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |  5588K|    69M|  8455   (3)| 00:03:04 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL   | T    |  5588K|    69M|  8455   (3)| 00:03:04 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Claramente se observa que el optimizador solo accedió la partición 1. Ejecutando la consulta vemos que la estimación del optimizador fue buena:



select count(1)
from t
where c4 = 'A' and c2 > 'R';



COUNT(1)
----------
5610297

El total de filas de la partición es:


select count(1)
from t
where c4 = 'A' ;

COUNT(1)
----------
8333946

En este punto, ya tenemos una partición con mas de 8.3M de filas de las cuales vamos a modificar 5.6M, lo cual es mas del 67%.
Primero voy a testear un update normal sobre la tabla T para luego realizar la comparativa con la misma modificación pero usando otro enfoque mas eficiente.



update t
set c3 = c3+1
where c4 = 'A'
and c2 > 'R'

5610297 filas actualizadas.

Transcurrido: 00:04:45.37

La modificación demoró 4' 45". Pensemos que la base de datos debe mantener la consistencia para garantizar la lectura consistente (mediante el UNDO) y persistir los cambios para poder recuperarse si un evento de falla ocurre durante la modificación (REDO). Estos mecanismos provocan que los tiempos se incrementen y se genere información adicional.

Revisemos cuanto espacio de UNDO y REDO se necesitó para realizar el update:


select 'REDO_SIZE',
round(ms.value/1024/1024) value
from v$mystat ms,
v$statname sn
where ms.STATISTIC# = sn.STATISTIC#
and sn.NAME = 'redo size'
union all
SELECT 'UNDO_SIZE',
t.used_ublk*8/1024 value
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')

REDO_SIZE  2489 Mb
UNDO_SIZE   885 Mb

Para modificar 5.3M se necesitaron 2489Mb de redo y 885Mb de undo!!!. En el ejemplo, la tabla no tiene indices. Si tuviera indices y la columna modificada sea parte de las columnas de indexación se generaría mas redo y undo, y además la sentencia tendría que actualizar los indices por cada fila modificada lo cual provocaría que el update demore bastante mas. Si el procesamiento masivo fuera un delete en lugar de un update, se generará mas undo (el delete es la operación dml que mas undo genera) y se tendrá que mantener balanceados los indices, lo cual implica mas tiempo de procesamiento.

Existe una forma mas sencilla de realizar el update usando la operación estrella de partitioning: EXCHANGE. Antes de usar el exchange tenemos que crear una tabla auxiliar (T_A) y para acelerar la creación configuro la tabla como nologging e inserto en forma directa usando el hint APPEND.

create table t_a_aux nologging as
select /*+ APPEND */
c1,
c2,
case when (c2>'R') then c3+1
else c3 end c3,
c4
from t
where c4 = 'A'

Transcurrido: 00:00:22.04

Solo se necesitaron 22" para insertar la filas en la tabla auxiliar. Con la función DECODE o CASE realizo el cambio simulando el update. Ahora solo resta realizar el intercambio entre la tabla auxiliar y la partición t_a con la operación EXCHANGE:


ALTER TABLE t
EXCHANGE PARTITION t_a
WITH table t_a_aux ;

Transcurrido: 00:00:11.46

El exchange se realizó en casi 12". Sumando la creación de la tabla auxiliar y el exchange, todo demoró solo 44"!!!, es decir mas de 6 veces mas rapido que el update tradicional.
Ejecutando la consulta para obtener el espacio de redo y undo generado se obtiene:

REDO_SIZE     1 Mb
UNDO_SIZE     0 Mb

Practicamente no hubo alocación de undo/redo. Por lo cual, para ciertos casos resulta muy util usar este metodo para actualizar dado que los tiempos de procesamiento se reducen sensiblemente y ademas los requerimientos de undo y redo son minimizados casi por completo.

Para eliminar (delete) en forma masiva, la creación de la tabla auxiliar solo deberá llenarse con las filas que no se borran. Si se necesitara borrar muchas filas de una tabla no particionada se podrá utilizar el mismo enfoque, es decir reemplazar el delete por un insert en una tabla nueva, recrear los indices y renombrar.

Reportes de Métricas de Carga y Tiempos de Respuesta de la Base de Datos (10g+)

2010-11-17T10:42:00.000-08:00

A partir de 10g se agregaron vistas dinamicas e información historica para poder entender mejor y en forma mas rapida la actividad de la base de datos. Si bien los reportes de statspack y AWR tienen la información, estos se basan de los snapshots como referencia para analizar un intervalo. Generalmente los intervalos son de 1 hora (automatico y default en 10g+) y muchas veces hay que esperar al próximo snapshot para tener una idea de la actividad actual.

Con las nuevas vistas dinamicas se puede saber casi en tiempo real cual es la actividad de la base consultando las siguientes vistas dinamicas:

V$SYSMETRIC : Metricas mas recientes y menos recientes del ultimo minuto
(una muestra cada 15").
V$SYSMETRIC_HISTORY : Ultima hora de todas las muestras (elige una muestra por
minuto).
V$SYSMETRIC_SUMMARY : Resumen de la actividad de la ultima hora (maximos, minimos,
promedios y desviación standard).

y las vistas historias que persisten parte de la información de las vistas dinamicas
de la ultima hora (el proceso MMON se encarga de copiar parte de la información mas relevante de las vistas V$ a disco) y se externaliza el resultado con las siguientes vistas:

DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY

Con esta información a disposición se obtiene una idea muy detallada de la actividad y el perfil de carga. Veamos una query que usa la vista sumarizada y retorna entre otros, los mismos datos que encontramos en los reportes statspack/awr en la parte "Load Profile" en la columa tabulada por segundo:


select metric_name,
  case (metric_id)
  when 2016 then round(minval/1024/1024,2)
  when 2058 then round(minval/1024/1024,2)
  else round(minval,2) end Min,
  case (metric_id)
  when 2016 then round(maxval/1024/1024,2)
  when 2058 then round(maxval/1024/1024,2)
  else round(maxval,2) end Max,
  case (metric_id)
  when 2016 then round(average/1024/1024,2)
  when 2058 then round(average/1024/1024,2)
  else round(average,2) end Avg,
  case (metric_id)
  when 2016 then round(standard_deviation/1024/1024,2)
  when 2058 then round(standard_deviation/1024/1024,2)
  else round(standard_deviation,2) end STDDEV,
  case (metric_id)
  when 2016 then 'Mbytes Per Second'
  when 2058 then 'Mbytes Per Second'
  else metric_unit end metric_unit
from v$sysmetric_summary
where metric_id in (2003,2026.2004,2006,2016,2018,2030,
               2044,2046,2058,2071,2075,2081,2123)
order by metric_id


METRIC_NAME                                                             MIN        MAX        AVG     STDDEV METRIC_UNIT
---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------
User Transaction Per Sec                                                  0      28.75       24.4       1.64 Transactions Per Second
Physical Writes Per Sec                                                   0      29.63      21.91       2.25 Writes Per Second
Redo Generated Per Sec                                                    0        .06        .05          0 Mbytes Per Second
Logons Per Sec                                                            0       1.18        .93        .08 Logons Per Second
Logical Reads Per Sec                                                     0    1015.72     592.05      75.19 Reads Per Second
Total Parse Count Per Sec                                                 0      52.75      28.29       4.33 Parses Per Second
Hard Parse Count Per Sec                                                  0       7.24       1.29        .88 Parses Per Second
Network Traffic Volume Per Sec                                            0        .03        .02          0 Mbytes Per Second
DB Block Changes Per Sec                                                  0     339.75     287.39      19.15 Blocks Per Second
CPU Usage Per Sec                                                         0      11.27       9.88        .51 CentiSeconds Per Second
User Rollback UndoRec Applied Per Sec                                     0         .3        .03        .07 Records Per Second
Database Time Per Sec                                                     0      72.48      26.67        8.1 CentiSeconds Per Second

Los datos anteriores se pueden obtener por transacción si se llegara a necesitar.

Ahora voy a mostrar como obtener las metricas basadas en percentiles, con ratios y porcentajes de las ultimas dos muestras del ultimo minuto. La mas reciente es de a lo sumo 15 segundos y la mas antigua es de a lo sumo 60 segundos.


select metric_name,
  round(value,2) value,
  metric_unit
from v$sysmetric
where metric_name like '%\%%' escape '\'
or metric_name like '%Percent%'
or metric_name like '%Ratio%'


METRIC_NAME                                                           VALUE METRIC_UNIT
---------------------------------------------------------------- ---------- ----------------------------------------------------------------
Buffer Cache Hit Ratio                                                95.75 % (LogRead - PhyRead)/LogRead
Memory Sorts Ratio                                                      100 % MemSort/(MemSort + DiskSort)
Redo Allocation Hit Ratio                                               100 % (#Redo - RedoSpaceReq)/#Redo
User Commits Percentage                                                 100 % (UserCommit/TotalUserTxn)
User Rollbacks Percentage                                                 0 % (UserRollback/TotalUserTxn)
Cursor Cache Hit Ratio                                               232.71 % CursorCacheHit/SoftParse
Execute Without Parse Ratio                                           63.74 % (ExecWOParse/TotalExec)
Soft Parse Ratio                                                      96.05 % SoftParses/TotalParses
User Calls Ratio                                                      33.48 % UserCalls/AllCalls
Host CPU Utilization (%)                                               4.63 % Busy/(Idle+Busy)
PX downgraded 1 to 25% Per Sec                                            0 PX Operations Per Second
PX downgraded 25 to 50% Per Sec                                           0 PX Operations Per Second
PX downgraded 50 to 75% Per Sec                                           0 PX Operations Per Second
PX downgraded 75 to 99% Per Sec                                           0 PX Operations Per Second
User Limit %                                                              0 % Sessions/License_Limit
Database Wait Time Ratio                                              42.12 % Wait/DB_Time
Database CPU Time Ratio                                               57.88 % Cpu/DB_Time
Row Cache Hit Ratio                                                   99.75 % Hits/Gets
Row Cache Miss Ratio                                                    .25 % Misses/Gets
Library Cache Hit Ratio                                                98.1 % Hits/Pins
Library Cache Miss Ratio                                                1.9 % Misses/Gets
Shared Pool Free %                                                    91.27 % Free/Total
PGA Cache Hit %                                                       99.89 % Bytes/TotalBytes
Process Limit %                                                        24.7 % Processes/Limit
Session Limit %                                                       16.86 % Sessions/Limit
Streams Pool Usage Percentage                                             0 % Memory allocated / Size of Streams pool
Buffer Cache Hit Ratio                                                96.18 % (LogRead - PhyRead)/LogRead
Memory Sorts Ratio                                                      100 % MemSort/(MemSort + DiskSort)
Execute Without Parse Ratio                                           64.59 % (ExecWOParse/TotalExec)
Soft Parse Ratio                                                      95.72 % SoftParses/TotalParses
Host CPU Utilization (%)                                               4.39 % Busy/(Idle+Busy)
Database CPU Time Ratio                                                15.8 % Cpu/DB_Time
Library Cache Hit Ratio                                               97.64 % Hits/Pins
Shared Pool Free %                                                    91.28 % Free/Total

Otra consulta que suelo usar es mas simple y solo me retorna el tiempo de respuesta general y el tiempo de respuesta por transacción, ambos en segundos, y asi se puede analizar rapidamente y detectar si algo esta pasando con la base. Yo tengo idea de los tiempos razonables para cada base y si veo algo que se dispara me doy cuenta mirando solo esos dos valores. Abajo muestro como es la consulta que utilizo y la salida de la misma.

select end_time,
round(max(decode(metric_id,2106,value/100,null)),4) "SQLRTime",
round(max(decode(metric_id,2109,value/100,null)),4) "RTime/Trx"
from v$sysmetric_history
where metric_id in (2106,2109)
and end_time > sysdate-10/24/60
group by end_time
order by end_time desc


END_TIME                   SQLRTime RTime/Trx
18/11/2010 12:15:34 p.m.    0.0013    0.0172
18/11/2010 12:14:33 p.m.    0.0063    0.0843
18/11/2010 12:13:33 p.m.    0.0087    0.1101
18/11/2010 12:12:33 p.m.    0.0039    0.1147
18/11/2010 12:11:34 p.m.    0.009    0.1214
18/11/2010 12:10:34 p.m.    0.0062    0.1145
18/11/2010 12:09:34 p.m.    0.0079    0.1102
18/11/2010 12:08:34 p.m.    0.0081    0.1167
18/11/2010 12:07:34 p.m.    0.0085    0.1112
18/11/2010 12:06:34 p.m.    0.0078    0.1141

Si quisiera ver la historia mas antigua o necesito armar un reporte historico sumarizado y/o agrupado por hora, dia, semana o mes se puede usar una vista historica (DBA_HIST_xxx).

Aplicación de Parche 11.2.0.2 (no tan parche)

2010-11-17T04:35:00.000-08:00

La semana pasada tuve que aplicar el parche 11.2.0.2 sobre un equipo de desarrollo. Cuando entré a metalink y busqué el parche que aplicaba a mi SO (Solaris SPARC) me llamó la atención el tamaño del parche. Los ultimos parches que instalé recuerdo que no pesaban mucho mas de 1Gb. El parche de 11.2.0.2 sobre la plataforma que necesitaba pesa 5.1Gb!, y para AIX mas de 6Gb. Recien una vez que leí la documentación entendí el porque. El tema es que Oracle cambió la politica de aplicación de parches a partir de 11.2.0.2. Ahora no son mas incrementales, sino totales y ademas contienen todo el bundle, es decir el server, cliente, gateway, grid, etc.

Recuerdo que en 9i venia todo junto, si querias instalar solo un cliente necesitabas bajarte 3 archivos que contenian todo, lo cual resultaba engorroso. En 10g independizaron las instalaciones de cliente, server, grid, etc. Ahora parece que nuevamente hay que bajar todo y luego elegir en la instalación lo que necesitamos.
Es obligatorio usar un home separado para la instalación, al ser total no se puede parchear sobre el home actual. En mi caso ese nuevo requisito no me molesta ya que siempre considero como una buena practica instalar los parches sobre una copia en un home nuevo, para minimizar riesgos por si el parche falla a la mitad de la instalación y la vuelta atrás requiere respaldar los binarios anteriores. Si estan muy cortos de espacio, se complica un poco crear un home separado asi que en ese caso habrá que bajar las bases, respaldar los binarios, borrarlos e instalar el nuevo home.

La instalación sobre Solaris SPARC no tuvo contratiempos. Tuve que upgradear un Oracle 11g R1 (11.1.0.7) y un Oracle 11g R2 (11.2.0.1). Ambas actualizaciones se realizaron perfectamente y sin ningun contratiempo.

Inserción Directa en Oracle (DIRECT INSERT)

2010-10-08T11:37:00.001-07:00

La inserción directa de registros en tablas se realiza con las sentencias INSERT y MERGE (la parte de inserción) o desde una aplicación que utilice la interface directa de OCI (ej sqlloader). Cuando se necesita insertar un gran vólumen de filas en un tiempo óptimo, es necesario sacrificar cierta funcionalidad a expensas de velocidad. La mejora de rendimiento en la inserción no es gratis y hay ciertos requisitos que se deben cumplir y ciertas consecuencias a considerar antes de utilizarla. La inserción directa se activa de dos formas posibles:

- Agregando el hint /*+ APPEND */ en la sentencia INSERT INTO... SELECT ..
- Agregando el hint /*+ APPEND */ para INSERT INTO .. VALUES .. (en 11g R1)
- Agregando el hint /*+ APPEND_VALUES */ para INSERT INTO .. VALUES .. (en 11g R2)
- Ejecutando el insert en paralelo

En los siguientes casos no se puede utilizar la inserción directa

- La tabla a modificar tiene un trigger activo que se dispara con los inserts.
- La tabla a modificar tiene una foreign key habilitada.
- La tabla a modificar es una tabla indexada.
- La tabla a modificar esta almacenada en un cluster.
- La tabla a modificar contiene columna del tipo object type.

A continuación voy a comparar el insert normal con el directo poniendo foco en el espacio de redo y undo consumido en cada caso. Tambien voy a mostrar como el modo directo "saltea" el buffer cache. Justamente esto ultimo es la clave para acelerar los inserts, ya que se arman los bloques nuevos en memoria y se agregan a la tabla en forma directa sin necesidad de usar el cache. Durante la inserciòn no se incrementa el HWM y solo se actualiza al commitear la transacción. Por este motivo no se puede realizar ninguna operación adicional sobre la tabla modificada hasta tanto no se haya confirmado la transacciòn de insert directo. Si intentamos ejecutar cualquier sentencia que referencie a tabla luego de insertar en modo directo Oracle genera el error: "ORA-12838: No se puede leer/modificar un objeto despues de modificarlo en paralelo".

Para el ejemplo voy a crear una tabla T con dos columnas. Voy a usar una columna CHAR(500) para que se utilicen mas bloques sin tener que cargar tantas filas.


drop table t

create table t (x int,y char(500))

Ahora se insertaran 100000 registros en forma normal:


INSERT CONVENCIONAL
-------------------

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

El insert demoró: 10.8 segundos.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

La cantidad de redo usado fue de: 53Mb

  
SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

1104k

La cantidad de undo usado fue de: 1104Kb

Con la siguiente consulta chequeamos si se utilizo el buffer para el insert:


select count(1)
from v$bh bh,
     user_objects ob
where bh.OBJD = ob.data_object_id
  and ob.object_name = 'T'
  and bh.STATUS != 'free'
  and bh.CLASS# = 1

7174 bloques

Se cargaron todos los bloques en cache para la inserción.

Veamos que sucede con el insert en modo directo:


INSERT DIRECTO
--------------

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

10.3s

Demoró solo .5 segundos menos que el insert convencional. Esto puede llegar a desalentarnos de usar el modo directo, ya que no se ve tanta mejora, pero es importante aclarar que la diferencia de tiempos se va a notar mas cuando trabajemos con una cantidad de registros mas importante, yo diria del orden de los millones.


select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

56Gb

Ahora consumió un poco mas de redo, pero veamos el undo:


SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

8kb

Con el insert directo solo se consumieron 8k de undo, es decir 138 veces menos que con la el insert convencional.


select count(1)
from v$bh bh,
     user_objects ob
where bh.OBJD = ob.data_object_id
  and ob.object_name = 'T'
  and bh.STATUS != 'free'
  and bh.CLASS# = 1
  
0 Bloques

No se usaron bloques de cache, justamente esto era esperable ya que como comenté mas arriba el insert directo no utiliza memoria intermedia.
Como se vio con el insert directo se redujo notablemente el espacio de undo. Para reducir tambien el redo debemos realizar el insert sobre una tabla que tenga desactivado el logging, es decir que este en nologging (si la base esta en noarchivelog ya tiene desactivado el logging para la tablas cdo se inserta en modo directo).


INSERT DIRECTO CON NOLOGGING
---------------------------------  

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

9.6s

Se redujo el tiempo de inserción, ahora fue de 9.6s


select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

0

El consumo de redo fue nulo. Si bien esto parece muy bueno hay que tener en cuenta que deshabilitar el logging (en realidad se minimiza, ya que operaciones internas como correr el HWM o agregar extent generan redo y undo) provoca que ante un evento de falla no podamos realizar recovery de los datos recien insertados. Es recomendable realizar un backup lógico de las tablas o un backup incremental con RMAN inmediatamente luego de la carga directa. No es posible "bypassear" el redo para las tablas alojadas en tablespaces con force logging.


select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

8 kb

El undo se mantuvo exactamente igual que la prueba anterior.


select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

0 bloques

y tampoco se cargaron bloques en memoria.

Recordemos que para las 3 pruebas anteriores utilizamos una tabla sin indices. Veamos que pasa cuando se le agrega un indice a la tabla T


create index t_idx on t (x)

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

11.2.s

Claramente el insert demoró mas, ya que tuvo que mantener el indice actualizado durante las inserciones.


select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

7

El espacio consumido de redo ahora no fue 0, sigue siendo poco pero ahora es de 7kb, ya que tuvo que guardar información de redo para el indice.


SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

1600 kb

El consumo de undo tambien se incremento dado que se guardaron registros de undo para el nuevo indice

La siguiente tablita resume el resultado de las pruebas:

Como resumen, recomiendo utilizar siempre que sea posible el modo directo sobre todo cuando se necesita realizar una carga masiva (procesos ETL) y la performance en la carga es el principal objetivo. Tambien, en lo posible, y teniendo conciencia de lo que implica usar nologging, tambien recomiendo configurar la tabla receptoras en nologging. Por ultimo, es recomendable deshabilitar los indices (ponerlos en unusables) ya que como vimos en el ultimo caso, el mantenimiento de los indices durante la inserción genera mas undo/redo y ralentiza la carga general. Luego de la inserción habrá que reconstruir los indices inutilizados.

Como realizar una estimación de capacidad y escalabilidad de CPU y I/O (Capacity Planning)

2010-09-22T11:29:00.000-07:00

En esta nota les voy a mostrar como realizar un Capacity Planning (CP) de CPU y I/O en forma sencilla usando matemática simple. Para hacer un CP efectivo es importante determinar los subsistemas a modelar. Los subsistemas tipicos son CPU, I/O o discos, memoria y red. Existen productos comerciales que permiten realizar un CP global, sin tener que modelar por subsistema, pero yo prefiero analizar cada subsistema por separado ya que me parece que los resultados obtenidos serán mas precisos y claros. La CPU y I/O son claramente los mas importantes y son los subsistemas en los que vamos a ahondar en esta nota.

Seguramente alguna vez su gerente les ha consultado si la cantidad de procesadores o la velocidad de los discos es adecuada para garantizar que el sistema permanezca estable. Ante esta pregunta uno puede simplemente tener confianza y contestar que todo va a funcionar bien o uno puede ser mas profesional y realizar un CP y saber con exactitud en donde estará el punto de quiebre, es decir no solo contestar si el HW soporta la carga actual sino tambien, teniendo la proyección de crecimiento transacccional anual, saber hasta donde nos alcanza nuestro HW y preveer con tiempo la compra o upgrade de HW.

Ahora vayamos directamente a ver como se modela un subsistema de cpu y un subsistema de i/o. Para realizar CP mas sofisticado es necesario tener cierto conocimiento de Teoria de Colas (función Erlang C, Notación de Kendall, etc). En esta nota simplemente alcanza con un poco de criterio. En el gráfico A) se modela el subsistema de CPU. Este consta de una sola cola y N servers (cpu's). Esto quiere decir que cada petición puede ser atendida arbitrariamente por cualquier server o cpu y se van encolando cuando todas las cpu estan ocupadas (runqueue en terminos de SO). En el gráfico B) se modela el subsistema de I/O donde tenemos una cola por server o device. En este caso cada petición debe ser atendida por un device determinado.

Los dos modelos mencionados se representan infinidad de veces en la vida cotidiana, cuando vamos al banco, cuando estamos esperando en la estación de peaje, cuando esperamos para abordar un vuelo, cuando vamos a hacer nuestro pedido en un restaurant de comida rapida, etc.

Ahora vamos a definir las variables que necesitamos usar para realizar el CP:


Uc= User Calls
trx= transacción
λ= tasa de arribo  (ej: trx/ms ó Uc/ms)
St= Tiempo de Servicio  (ej: s/trx ó s/Uc)
Qt= Tiempo de Espera o Tiempo de espera  (ej: s/trx)
Rt= Tiempo de Respuesta  (ej: s/trx)
Q= Encolamiento   (ej: Nro de trx's)
U= porcentaje de carga (porcentaje de utlización del recurso)
M= nro de servers   (cantidad de CPU's o cantidad de IO devices)

y las formulas:

Rt = St + Qt

U = St * λ / M

Q = λ * Qt

Rt(cpu) = St / (1-U^M)

Rt(io)  = St / (1-U)

El objetivo es conseguir graficar el tiempo de respuesta vs tasa de arribo. El gráfico debería ser tipo exponencial con una curvatura o punto de quiebre que muestre a que tasa de arribo (eje X) se produce dicho quiebre.

Voy a explicar un poco mas en detalle que significa cada cosa:

1) λ (Tasa de Arribo): Esta se puede definir con métricas de Oracle tales como: Uc/s o Trs/s, o usar una métrica funcional, por ejemplo: carga de ordenes de compra por minuto o por hora.

2) M (Cantidad de CPU´s y I/O Devices): Es la cantidad de cpu's o I/O Devices. Para obtener este valor podemos usar utilitarios de SO y/o consultar vistas de Oracle.

3) U (Porcentaje de Carga): Es el porcentaje de carga de cpu o I/O. Esto se puede obtener usando utilitarios de SO como sar, iostat.

4) St (Tiempo de servicio): Es el tiempo en el que la petición esta siendo atendida por la cpu o por el dispositivo de i/o.

5) Qt (Tiempo de Espera en la Cola): Es el tiempo que una petición tiene que esperar en la cola porque los servers estan ocupados.

6) Q (Largo de la Cola): Es la cantidad de peticiones encoladas (esperando ser atendidas)

7) Rt (Tiempo de respuesta): El tiempo de respuesta es la suma de St (service time) y Qt (tiempo de espera o encolado). Una petición o esta esperando o esta siendo atendido.

En este punto, donde creo haber definido todo, les voy a mostrar un ejemplo:

Ejemplo (Capacity Planning de CPU)

Supongamos nos piden estimar el impacto que se producirá si se incrementan los usuarios de una aplicación X en un 20%. Lo que se quiere determinar es si se necesitará agregar mas procesadores o con lo que hay es suficiente para soportar la carga adicional.

Lo primero que tenemos que hacer es recolectar información en un periodo representativo. Cuanto mas datos recolectemos mas precisa será nuestro CP, luego debemos caracterizar la carga, esto significa definir si vamos a usar valores promedio, maximos, que vamos a usar como tasa de arribo, etc. En el ejemplo yo voy a tomar valores picos en un intervalo de maxima carga y como tasa de arribo usaré Trx/s.

λ= 20 trx/s (20 transacciones por segundo, se puede obtener desde statspack o AWR)
U= 0,40 (40% de utilización de cpu, esto se puede recolectar con sar -u)
M= 8 (hay 8 procedores, se obtiene desde el parametro de la base: cpu_count)

Ya tengo los 3 parametros escenciales, ahora solo tengo que aplicar las formulas y graficar:

U = St * λ / M, depejando para obtener St,

St = U * M / λ = 0.40 * 8 / 20 = 0,16 s/trx

Ahora que tenemos St podemos aplicar la formula para cpu:

Rt(cpu) = St / (1-U^M) = 0,16 / (1-0.40^8) = 0,16 s/trx

Como se observa el tiempo de respuesta es igual al tiempo de servicio. Esto se da porque el sistema esta holgado en cpu para la carga actual y no se producen encolamientos, es decir hay que esperar para ser atendido por las cpu's. Ahora lo que voy a hacer es una tabla en excel para proyectar el crecimiento y graficar la curva de manera de ver donde se produce el quiebre.

Vemos que el punto de quiebre esta alrededor del 30-35% (>), pensemos además que estamos partiendo de un workload pico y por lo tanto podemos pensar que la mayoria del tiempo va a estar bastante por debajo. De acuerdo al CP podemos asegurar que el sistema amortiguará bien la carga adicional del 20%, comenzando a degradarse rapidamente a partir del 25%.

Ejemplo 2 (Capacity Planning de I/O)

Como analizamos en el ejemplo 1, no tendriamos mayores problemas con la cpu al incrementar en un 30% la carga. Ahora veamos que ocurre con los discos.

λ= 5Mb/ms (5 Mb de transferencia por milisegundo, se puede obtener con sar -d o iostat)
U= 0,60 (60% de utilización de i/o)
M= 50 (hay 50 devices)

St = U * M / λ = 50 * 0.60 / 5 = 6 ms/Mb

Reemplazando en la formula de i/o:

Rt(io) = St / (1-U) = 6 / (1-0.60) = 15ms/Mb

Como se ve en el gráfico, el sistema podría soportar hasta un 50% (7.5Mb/ms) de crecimiento, una vez que llega al 60% de destabiliza.

Como resumen podemos inferir que nuestro Hardware esta bien a nivel cpu no tanto a nivel i/o ya que no escalará a largo plazo.

En esta nota mi idea fue mostrar como realizar un capacity planning de cpu y i/o en forma sencilla. Con este metodo tendremos una buena estimación de como escalará nuestro HW. De acuerdo a cada caso existen otros enfoque para modelar tales como el metodo basado en ratios, Teoria de Colas y metodo de regresión lineal.

Lo mas importante para obtener estimaciones precisas es obtener una buena muestra, que sea representativa sumado a una buena caracterización de la carga de trabajo a evaluar. Cuanto mas información hayamos recolectado mejor será nuestro pronóstico.

Usando esta metodologia se puede realizar simulaciones y proyectar a futuro, por ejemplo: que pasaría si agregamos o sacamos cpu's, como impacta el agregado de dispositivos de i/o mas rapidos, con mayor throughput y menor latencia, cuantos usuarios se podrian agregar para que operen con la aplicación sin poner en riesgo la estabilidad del sistema, etc.

Para los que le interesen temas de Capacity Planning en Oracle les recomiendo:
"Forecasting Oracle Performance". Este libro escrito por Craig Shallahamer, un verdadero gurú en el tema, me pareció excelente y lo usé como referencia para escribir la nota.

Optimizando la recolección estadística sobre tablas particionadas

2010-09-20T12:39:00.000-07:00

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.

Como obtener la edición del sofware instalado desde el inventory

2010-08-24T12:13:00.000-07:00

Para obtener la edición (Enterprise o Standard) del software Oracle (motor) instalado ,si todavia no se creo una base de datos (se puede sacar la edición desde el catalogo de la base) y si la persona que lo instaló no recuerda que opción eligió, se puede ejecutar el siguiente comando desde Unix/Linux que busca en el inventory:

$grep -w s_serverInstallType $ORACLE_HOME/inventory/Components21/oracle.server/*/context.xml |
tr ' ' '\n' |
grep VAL

VAL="EE"

En el ejemplo mostró "EE" = Enterprise Edition, si el software fuera Standard Edition retornará "SE".

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

2010-08-12T12:21:00.000-07:00

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.

Como realizar un Upgrade Manual desde versión 10g a 11g (Procedimiento Paso a Paso)

2010-07-30T11:59:00.000-07:00

Para actualizar (upgrade) una la versión de una base Oracle existen principalmente cuatro métodos:

Usar el Asistente Grafico (DBUA). Es el metodo sugerido en los manuales.
Usar scripting o forma manual (la que yo siempre tiendo a usar)
Export/Import
CTAS (para mi gusto la mas complicada, hay que crear dblink, armar los scripts para el traspaso de los objetos, etc)

En esta nota voy a escribir un procedimiento paso a paso para actualizar usando el método manual (el 2 en la lista de arriba):

Pre-Upgrade

1. Si el upgrade es sobre el mismo equipo habria que instalar el software (el motor) 11g en un nuevo home. Se puede usar el mismo user oracle de la instalación 10g corriente y setear las variables de entorno para 11g o se podria usar un nuevo usuario oracle (ej: oracle11g).

2. Una vez instalado el software conectarse con el usuario oracle de la instalación 11g y copiar el archivo $ORACLE_HOME/rdbms/admin/utlu112i.sql a un directorio compartido (ej: /tmp).

3. Conectarse con el usuario 10g (ej: oracle) o si se esta usando el mismo usuario setear las variables de ambiente para usar el motor 10g, para ejecutar el script copiado en el paso 2. Este script brinda información previa al upgrade que se usará para preparar la versión actual para que no haya problemas durante el upgrade

4. Conectarse con sqlplus:

$ sqlplus / as sysdba

5. Setear el spool para que no quede registro de la ejecución del script

sqlplus> spool upg_info.log

6. Ejecutar el script:

sqlplus> @/tmp/utlu112i.sql;

7. Desactivar el spool:

sqlplus> spool off

Ejemplo de Salida del Reporte de información pre-upgrade

A continuación se muestra una salida típica del script utlu112i.sql sobre una base llamada ROP10g sobre un equipo Solaris 10.

Oracle Database 11.2 Pre-Upgrade Information Tool 07-26-2010 15:14:36
**********************************************************************
Database:
**********************************************************************

--> name: ROP10G

--> version: 10.2.0.4.0

--> compatible: 10.2.0.1.0

--> blocksize: 8192

--> platform: Solaris[tm] OE (64-bit)

--> timezone file: V4

**********************************************************************
Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 313 MB

.... AUTOEXTEND additional space required: 83 MB

--> UNDO tablespace is adequate for the upgrade.

.... minimum required size: 121 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 73 MB

.... AUTOEXTEND additional space required: 23 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

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

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

WARNING: --> "sga_target" needs to be increased to at least 672 MB

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

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

--> "background_dump_dest" replaced by "diagnostic_dest"

--> "user_dump_dest" replaced by "diagnostic_dest"

--> "core_dump_dest" replaced by "diagnostic_dest"

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

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

Miscellaneous Warnings

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

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 11g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

WARNING: --> Database contains INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects was written to

.... registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... registry$nonsys_inv_objs.

.... Use utluiobj.sql after the upgrade to identify any new invalid

.... objects due to the upgrade.

.... USER PUBLIC has 1 INVALID objects.

.... USER SYS has 2 INVALID objects.
....
....
SQL>

Controles que se realizan con la herramienta de pre-upgrade

El script “utlu112i.sql” realiza el chequeo de los siguientes puntos:

Chequea si las estadísticas de diccionario están actualizadas.
Chequea si existen objetos invalidos.
Chequea si la configuración de SGA cumple con los requerimientos minimos en 11g.
Chequea si hay database links con passwords (11g encripta las passwords).
Se asegura que no haya archivos que necesiten recovery.
Se asegura que no haya archivos en modo backup.
Si el recyclebin esta activado chequea si esta vacio (si esta totalmente purgado).
Chequea si los archivos de timezone son de tipo 4 (los archivos que estan en $ORACLE_HOME/oracore/zoneinfo).
Revisa si hay refrescos de Vistas Materializadas pendientes.
Revisa si hay transacciones distribuidas pendientes.

Una vez que se corrigieron los warnings reportados por el script anterior se puede proceder a realizar el upgrade

Upgrade

1. Conectarse con el owner (usuario oracle) de la instancia 10g.

1.1 Verificar que no haya procesos oracle con el mismo nombre de la instancia

$ps -ef | grep -i ora | grep -v grep

1.2 Verificar que las variables de ambiente esten bien configuradas

$env | grep -i ora

2. Crear archivo pfile desde el spfile y copiarlo a $ORACLE_HOME/dbs en el nuevo equipo

3. Editar el pfile en el equipo nuevo y modificar los parametros que sea necesario (deprecated).

4. Bajar la base 10g en modo normal, si no hubiera conexiones sino se podria bajar en modo transactional, para que deje que termine las transacciones, no deje que se abran nuevas conexiones y baje la base en modo consistente y que no requiere revover. Como caso extremo tambien se podria considerar bajar la base en modo immediate.

sqlplus> shutdown transactional

5. Si la base 11g esta en otro equipo hay que transferir los archivos de base de datos (datafiles,redologs,controlfiles) al nuevo equipo en el mismo directorio y con los mismos permisos (usar comando unix scp, ftp, algun mecanismo de copiado de logical groups, etc). Si se realizará el upgrade sobre el mismo equipo y sobre los mismos archivos de base de datos no hace falta hacer nada en este punto.

6. Conectarse a la instancia 11g con sysdba

$ sqlplus / as sysdba

7. Levantar la base 11g en modo upgrade

SQPLUS> STARTUP UPGRADE

8. Setear el spool

SQLPLUS> spool upgrade11g.log

9. Ejecutar el script para obtener la información pre-upgrade

SQLPLUS> @?/rdbms/admin/catupgrd.sql;

10. Desactivar el spooling

SQLPLUS> spool off

11. Ejecutar el script utlu112s.sql para ver el resultado del upgrade.

SQLPLUS>@?/rdbms/admin/utlu112s.sql

12. Ejecutar el script utlrp.sql para recompilar stored procedures y clases java.

i) SQLPLUS>@?/rdbms/admin/utlrp.sql

ii) SQLPLUS>exec UTL_RECOMP.RECOMP_SERIAL ();

13. Verificar que todos las paquetes y clases java quedaron compiladas

SQLPLUS> select count(1) from dba_invalid_objects.

14. Crear spfile desde el pfile original.

SQLPLUS>create spfile from pfile;

15. Reiniciar la instancia en forma normal.

Ejemplo de Salida del Reporte de información post-upgrade

A continuación se muestra una salida típica del script utlu112i.sql sobre una base llamada ROP10g sobre un equipo Solaris 10

SQL> @?/rdbms/admin/utlu112s.sql;



Oracle Database 11.2 Post-Upgrade Status Tool           07-26-2010 14:32:12



Component                                Status         Version  HH:MM:SS



Oracle Server

                                  VALID      11.2.0.1.0  00:24:02



Gathering Statistics

.                                                                00:01:32

Total Upgrade Time: 00:25:36



PL/SQL procedure successfully completed.

Post-Upgade

1. Analizar password case-sensitive

SQLPLUS>alter system set sec_case_sensitive_logon = false scope=both;

Lo ideal seria dejar este parametro en true (default) , ya que fortalece la seguridad, pero habría que analizar como afecta algunas aplicaciones (por ejemplo algunas versiones de TOAD no se podrán conectar)

2. Setear el parametro COMPATIBLE a 11.2.0

SQLPLUS>alter system set compatible = '11.2.0' scope=spfile;

3. Habilitar los umbrales para alertas sobre tablespaces.

4. Reiniciar la instancia

5. Verificar conexión a través del listener

6. Dependiendo del tipo de backup y la herramienta que se use a veces es necesario cambiar la identificación interna de la base para que se tome como una nueva base

Como Cambiar el dbid:

SQLPLUS>shutdown immediate
SQLPLUS>startup mount
$ nid TARGET=SYS
SQLPLUS>startup mount
SQLPLUS>alter database open resetlogs

6 Backup full de la base de datos.

La importancia de ordenar adecuadamente las columnas cuando se define una tabla

2010-06-11T04:57:00.000-07:00

Dependiendo del caso, hay que prestar suficiente atención en el orden en el que se definen las columnas en la etapa de diseño fisico de las tablas. Para poder entender la situación que planteo, primero seria bueno que les muestre como almacena Oracle las filas en los bloques.

Una fila se almacena en un bloque de la siguiente forma:

Primero se define el Encabezado (H) que guarda propiedades acerca de la fila en si misma, tales como la cantidad de columnas que tiene y el flag que determina si esta lockeada. Luego vienen los datos en formato de duplas (largo de la columna,contenido de la columna). Como cada columna puede tener diferentes largos, cada una de ellas consta de dos partes: el largo Lx y los datos en si mismo Dx. Dado que el motor de base de datos no conoce el offset de las columnas en la fila, tiene que comenzar desde la primera columna, ver el largo, desplazarse hasta donde se encuentra el dato del largo del segunda columna y asi siguiendo hasta encontrar la columna buscada. Abajo, les muestro como se guarda la fila:

Como se habrán dado cuenta, si se necesita buscar una columna que esta al final, Oracle tardará mucho mas que para buscar una columna del principio, este overhead no es despreciable y podría afectar la performance, sobre todo para aplicaciones con requerimientos de tiempos de respuesta muy bajos, del orden de los milisegundos. Es por eso que en ciertos casos es recomendable definir al principio las columnas con mayor tasa de referencia y al final las que sean menos frecuentemente consultadas.
Para que puedan observar el grado de impacto de un orden de columnas no optimo, voy a armar un ejemplo sencillo que se pueda entender mejor:

Voy a crear una tabla T con 200 columnas de tipo INT, y luego las voy a insertar 5000 filas:

Creo la tabla T con la primera columna X1


create table t (x1 int);

Para no escribir la ddl con las 200 columnas lo voy a hacer dinamicamente, agregando las 199 columnas restantes:


begin
for i in 2..200
loop
  execute immediate 'alter table t add x'||i||' int';
end loop;
end;
/

Ahora voy a insertar las 5000 filas, de forma tal de llenar todas las columnas con el mismo valor por fila.


begin
for i in 1..5000
loop
  insert into t(x1) values (i);
  for j in 2..200
  loop
     execute immediate 'update t set x'||j||' = '||i||' where x1 = '||i;
  end loop;
  commit;
end loop;
end;
/

Una vez creada y populada la tabla T, voy a ejecutar un bloque anonimo que realiza 1000 veces la suma de todas las filas para cada columna Xn:


declare
l_cnt int;
l_foo int;
l_stime int;
begin
for i in 1..200
loop
   l_stime := dbms_utility.get_time();
   for j in 1..1000
   loop
      execute immediate 'select sum(x'||i||') from t' into l_foo;
   end loop;
   insert into t2 values (i,dbms_utility.get_time()-l_stime));
   commit;
end loop;
end;
/

Curva de Comparación

En la curva de arriba el eje X mide la posición de la columna en la fila y el eje Y el tiempo de procesamiento en segundos (usando el bloque pl de arriba) para operar con la columna. Como se puede apreciar el tiempo de procesamiento es directamente proporcional a la ubicación de la columna en la fila.

Herramienta para diagnosticar problemas de performance (Oracle Performance Viewer Freeware)

2010-04-30T07:36:00.000-07:00

(Ahora podes bajarte el utilitario desde http://www.oramdq.com/oracle-performance-viewer/)

Hace ya varios años que trabajo con bases de datos Oracle y en todo ese tiempo fui acumulando scripts que me facilitaron diversas tareas de administración, mantenimiento, monitoreo, etc. Ya que ultimamente me vengo dedicando a temas de performance, me di cuenta que en ese tipo de actividad es importante analizar la mayor cantidad de información en el menor tiempo posible, recordemos que Oracle es una base altamente instrumentada y que versión tras versión se van agregando mas métricas (Oracle Wait Interface). A partir de 10g tenemos el repositorio AWR que almacena historial detallado de la actividad de la base.

El AWR resulta de mucha utilidad como baseline o punto de comparación cuando nos encontramos con una base que esta con problemas de rendimiento, detectados o bien por la activación de alarmas o peor aún, cuando el usuario final persive la demora y realiza el reclamo. Cualquiera que trabaje como DBA de bases productivas, ni hablar si son muy criticas, tendrá que responder rapidamente a su superior evaluando el escenario, diagnosticando y proponiendo o activando cursos de acción en forma inmediata. A veces los problemas no se detectan con facilidad y empezar a correr scripts por separado puede resultar un tanto lento.

Para facilitarme la tarea, programé un pequeño utilitario en C# donde agregué varias de las consultas que utilizo diariamente, sumado a todo el poder gráfico que me permite entre otras cosas analizar la historia, filtrar convenientemente, exportar a excel la grilla, explotar la información con doble click sobre la celda, etc. Abajo copié algunas pantallas para mostrarles como esta pensada la aplicación y obviamente me interesa compartirla en forma gratuita con quien le interesa y asi poder mejorarla.

La pantalla principal es una MDI con 4 paneles, el de arriba a la izquierda tiene una estructura de árbol con todos los reportes disponibles hasta el momento, clasificados según cierto criterio. el panel de abajo a la izquierda tiene un resumen de la base de datos donde esta conectada la app. el panel de arriba a la derecha tiene la grilla con los resultados, y el panel de abajo a la derecha tiene la consulta que se ejecutó para llenar la grilla.

La pantalla 2 muestra los criterios de filtrado para llenar la grilla de acuerdo al tipo de reporte que se este ejecutando:

La pantalla 3 muestra la grilla resultado. En el ejemplo se ejecutó el reporte de historial de DB Time:

La pantalla 4 muestra la salida del reporte de historial de los 5 eventos de espera mas importantes:

La pantalla 5 muestra las sesiones actuales de la base (información actual)

Con doble click sobre una fila de la grilla se abre otro form con detalle de la sesion seleccionada. En el panel donde aparece el texto de la sentencia que esta ejecutando la sesión se puede clickear el boton derecho y ver y seleccionar "Detalle.." para ver el historial de ejecución de dicha sentencia.

La siguiente pantalla muestra todos los forms abiertos puestos en mosaico horizontal (menu Ventanas)

La pantalla 8 es el resultado de la ejecucion del reporte de Tablespaces

Con doble click sobre la fila de la grilla se abre un nuevo form con detalle del tablespace:

La pantalla 10 muestra los posibles filtros que se pueden realizar para analizar el historial de sentencias TOP.

La pantalla 11 muesta las sentencias TOP que cumplen los filtros definidos anteriormente:

Con un click sobre la celda que tiene el SQLID se copia el valor y luego presionando el boton de REFRESCAR se "pastea" el valor copiado para ver historial de la sentencia:

Todas las grillas de resultado se pueden exportar a excel, se pueden filtrar y todas las consultas se puede copiar (botón derecho sobre el panel donde esta el texto de la consulta y seleccionar COPIAR).

Mi idea fue mostrarles algunas pantallas para que vean la funcionalidad que intenté darle a mi aplicación. Obviamente hay varios reportes mas que no estan detallados en el blog, pero invito a quien quiera evaluar mi programita (se llama OraPerfViewer.exe y pesa alrededor de 150kb) que solo me escriba a: rovedop@gmail.com y les enviaré el ejecutable que solo requiere windows y un framework .NET instalado (creo que la mayoria de las windows lo traen instalado por default). Por el momento no es RAC-Aware, pero en el futuro lo será y corre sobre versiones 10g en adelante.

Es una primera versión, y seguramente tenga varios bugs y cosas que mejorar, pero estoy seguro que compartiendo y discutiendo ideas se mejoran las cosas.

Hard Parse vs Soft Parse vs Non Parse

2010-04-16T12:11:00.000-07:00

El impacto del parsing en una base de datos puede ser muy variable. En ciertos casos, afortunadamente la mayoria, no es notorio. En otros casos mas especificos puede ocasionar problemas mayores de rendimiento de la base de datos. En general los problemas de excesivo parsing se deben a una mala programación, es decir se deben analizar y solucionar desde el código de las aplicaciones que interactuan con la base. Es por eso que los desarrolladores deben estar concientes del impacto negativo que puede provocar una mala programación y considerar este tema como punto prioritario desde el inicio de la confección del código.

El parsing es el primer paso que se lleva a cabo para procesar una sentencia. En esta etapa se debe conocer de que tipo de sentencia se trata (DML, DDL o un select) para asi poder realizar los correspondientes chequeos. Los principales actividades son el chequeo sintáctico y el análisis semántico

Chequeo Sintáctico

Este chequeo verifica si la sentencia cumple con la gramática de la sentencia definida para la versión de la base.

Análisis Semántico

Se analiza si los objetos referenciados en la sentencia existen, si las columnas existen, si se tiene acceso a los segmentos y a las columnas (privilegios), etc.

Una vez que se pasan con éxito las dos etapas antes mencionadas, Oracle busca en la memoria (shared pool) para ver si ya fue ejecutada la misma sentencia por otra sesión. Si la encuentra, entonces se dice que se realizó un SOFT PARSE. Por otro lado, si no la encuentra, se realizan dos pasos adicionales, que son la optimización de la sentencia y la generación y carga del plan en la memoria (row source generation). La ejecución de todos los pasos se llama HARD PARSE. El hard parsing es cpu intensivo, y en el caso de que sea elevado puede compromenter seriamente la performance general dada la alta contención que se provoca. Para evitar el hard parsing hay que usar variables BIND en los statements (ej: usar preparedStatement). Si se trata de un código "enlatado" donde no se utilizan binds y no puede modificarse se puede utilizar en la base CURSOR_SHARING, cuyo default es EXACT y habría que cambiarlo a SIMILAR (existe a partir de 9i) o FORCE, aunque siempre recomiendo usar SIMILAR, porque es menos riesgoso.

El soft parse puede ser aún mas soft si se cachea el cursor en la sesión (session_cached_cursor) y asi se evita ir a la shared pool a buscarlo. Desde el código de la app se puede habilitar y definir el tamaño de cache mas adecuado (ej: ((oracle.jdbc.OracleConnection)connection).setStatementCacheSize(40)). Esto esta disponible en casi todas las interfaces (jdbc,.NET,PL/SQL,oci,etc).

Para evitar el reparseo en una sesión se debe mantener abierto el cursor. Algunas interfaces tales como PL/SQL, jdbc y la oci permiten realizar esto. La interfaz OLE DB, SQLJ u ODP, al menos hasta la ultima version que conozco, no lo permiten. A continuación voy a copiar 3 fragmentos de código Java para mostrar la diferencia entre parseo hard, soft y no parsear.

El primer fragmento de abajo muestra la NO utilización de binding, ya que se concatenan los literales y no se usa PreparedStatement:


TEST 1
-------

      sql = "SELECT X FROM t WHERE Y = ";
      for (int i=0 ; i<10000; i++)
      {
        statement = connection.createStatement();
        resultset = statement.executeQuery(sql + Integer.toString(i));
        if (resultset.next())
        {
          val = resultset.getString("X");       
        }
        resultset.close();
        statement.close();
      }

Este código, además de se muy pobre en performance, propicia el hacking por sql injection.

El segundo fragmento utiliza binding pero abre y cierra el cursor en cada ejecución por lo cual genera soft parse.


TEST 2
-------

      sql = "SELECT X FROM t WHERE Y = ?";
      for (int i=0 ; i<10000; i++)
      {
        statement = connection.prepareStatement(sql);
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if (resultset.next())
        {
          val = resultset.getString("X");          
        }
        resultset.close();
        statement.close();
      }

El último fragmento, que es el óptimo, reduce el parsing al mínimo (solo un soft parsing):


TEST 3
-------

      sql = "SELECT X FROM t WHERE Y = ?";
      statement = connection.prepareStatement(sql);
      for (int i=0 ; i<10000; i++)
      {
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if (resultset.next())
        {
          val = resultset.getString("X");          
        }
        resultset.close();
      } 
      statement.close();

En una prueba que realicé los tiempos de respuesta de cada test fueron los siguientes:

TEST1 --> 12.2"
TEST2 --> 6.4"
TEST2 (caching) --> 3.9"
TEST3 --> 3.7"
TEST3 (caching) --> 3.7"

Como se ve arriba, el TEST2, se puede mejorar usando caching, pero al usar caching en el TEST3 no se ven diferencias.

El parseo se puede ver como una mini compilación, podriamos comparar un código que ejecuta en un bucle un prepareStatement para cada sentencia con un código interpretado. Cualquier programador sabrá que la ejecución de un código compilado es mucho mas rápida que ejecutar un código que necesita interpretarse linea por línea.

Reporte de Perfil de Carga de Trabajo Historica en la Base (Historical Load Profile)

2010-03-26T07:11:00.000-07:00

La sentencia que copié más abajo permite realizar un reporte historico de la actividad general o carga de trabajo de una base de datos Oracle. Ya que la consulta obtiene información del AWR la cantidad de historia disponible dependerá de la retención definida en el repositorio (por default es de 7 dias aunque yo siempre aconsejo cambiarlo a 30 dias). La información proporcionada es lo que se muestra en un reporte AWR en la sección LOAD PROFILE, que esta al principio del reporte. Todos aquellos que hayan analizado performance mirando reportes de awr (ejecutando el script awrrpt.sql o bien usando una herramienta gráfica como el TOAD), sabrán que la sección de "Load Profile" junto con los 5 eventos tops son la primera "foto" del "estado de salud" general de la base. Sin embargo esa "foto" no sirve de mucho si no se conoce bien de antemano la actividad y tipo de carga o si no se ve alguna métrica con valores notoriamente grandes.

Para poder realizar un análisis efectivo hay que sacar varios reportes awr y compararlos para establecer diferencias. En esos casos, yo prefiero tener toda la historia disponible de un patallazo para así comparar mas facilmente, ver si alguna métrica esta en valores no habituales, poder exportar la salida del query a una planilla y realizar un gráfico historico para confeccionar un informe.

Antes de ejecutar el query es importante aclarar que los valores reportados están en unidades por segundo (primera columna del load profile de awr) y que funciona en bases 10g o superiores.


with intervals
as
  (select snap,
         extract(second from int)+
         extract(minute from int)*60+
         extract(hour from int)*60*60 int_sec         
  from
  (select end_interval_time snap,
          end_interval_time-lead(end_interval_time) 
  over (partition by startup_time order by snap_id desc) int
   from dba_hist_snapshot))
select to_char(snap_time,'YYYY/MM/DD HH24') snap_time,
       round((redo_size-lead(redo_size) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Redo Size",
       round((logical_reads-lead(logical_reads) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Logical Reads",
       round((db_block_changes-lead(db_block_changes) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Block Changes",
       round((physical_reads-lead(physical_reads) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Physical Reads",
       round((physical_writes-lead(physical_writes) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Physical Writes",
       round((user_calls-lead(user_calls) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "User Calls",
       round((parses-lead(parses) 
  over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Parses",
       round((parses_hard-lead(parses_hard) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Parses Hard",
       round((sorts-lead(sorts) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Sorts",
       round((logons-lead(logons) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Logons",
       round((executes-lead(executes) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Exectutes",
       round((user_rollbacks-lead(user_rollbacks) 
       over (partition by startup_time order by snap_time desc)+
       user_commits-lead(user_commits) 
       over (partition by startup_time order by snap_time desc))/
       int.int_sec,2) "Transactions"
from
(select s.end_interval_time snap_time,
        s.startup_time startup_time,
       max(decode(ss.stat_name,'redo size',value,null)) redo_size, 
       max(decode(ss.stat_name,'user rollbacks',value,null)) user_rollbacks,
       max(decode(ss.stat_name,'user commits',value,null)) user_commits,
       max(decode(ss.stat_name,'session logical reads',value,null)) logical_reads,
       max(decode(ss.stat_name,'db block changes',value,null)) db_block_changes,
       max(decode(ss.stat_name,'physical reads',value,null)) physical_reads,
       max(decode(ss.stat_name,'physical writes',value,null)) physical_writes,
       max(decode(ss.stat_name,'user calls',value,null)) user_calls,
       max(decode(ss.stat_name,'parse count (total)',value,null)) parses,
       max(decode(ss.stat_name,'parse count (hard)',value,null)) parses_hard,
       max(decode(ss.stat_name,'sorts (memory)',value,null)) sorts,
       max(decode(ss.stat_name,'logons cumulative',value,null)) logons,
       max(decode(ss.stat_name,'execute count',value,null)) executes
from dba_hist_sysstat ss,
     dba_hist_snapshot s
where s.snap_id = ss.snap_id
  and ss.stat_name in ('user rollbacks','user commits','session logical reads',
'db block changes','physical reads','physical writes','user calls',
'parse count (total)','parse count (hard)','sorts (memory)','logons cumulative',
'execute count','redo size')
group by s.end_interval_time,s.startup_time) t,
intervals int
where t.snap_time = int.snap
order by snap_time desc

Como solucionar errores de UNDO cuando se refrescan Vistas Materializadas

2010-03-08T12:04:00.001-08:00

La semana pasada estuve en una reunión para definir como solucionar un inconveniente en una de las bases de un cliente. El problema estaba relacionado con el refresco de dos vistas materializadas (las voy a llamar MV1 y MV2 para mantener la privacidad) y lo que ocurría era que en los ultimos dias no se habia podido refrescar las vistas porque se cancelaba el proceso por falta de espacio de UNDO. Las vistas se refrescan en modo COMPLETE cada 1 hora mediante un job en la base y mantienen un detalle diario. En general nunca superan los 100,000 registros, pero ahora tenian mas de 100 millones ya que se detectó que por un error de filtro en el where de la vista MV1 (la MV2 usa una sentencia que referencia a MV1) se tomo el detalle de mas de 2 años en lugar de lo del día.

El equipo de base de datos planteó recrear las vistas, lo cual es una solución valida y estuve de acuerdo en una primera instancia, pero tiene ciertas desventajas: 1) hay que ejecutar un drop e inmediatamente un create de cada vista lo cual puede ocasionar invalidaciones en cascada y por lo tanto debe hacerse en una ventana de mantenimiento y 2) hasta que no finalice la recreación de ambas vistas los objetos dependientes quedarán invalidos y es un tanto complicado estimar con certeza cuanto va a demorar este proceso, con el consiguiente riesgo de salirse de la ventana.

Como solución alternativa sugerí realizar un refresco de la siguiente forma (es importante notar que esto no requiere dropear ninguna mv):

sqlplus>exec dbms_refresh(list=>'MV1',atomic_refresh=>FALSE)

sqlplus>exec dbms_refresh(list=>'MV2',atomic_refresh=>FALSE)

A partitr de 10g el parámetro atomic_refresh por default es TRUE y para saber que significa voy a explicar brevemente como es el proceso de refresco intenamente:

Cada vez que se refresca una vista en modo FORCE se ejecutan dos pasos:

1) Se purga o se eliminan todas las filas actuales de la vista materializada
2) Se insertan las nuevas filas ejecutando el query definido en la MV.

El parámetro atomic_refresh define el método que se usará para realizar el paso 1. En 10g el paso 1 implica un DELETE de todas las filas, se dice que el proceso de refresco en 10g es atómico porque el delete e insert se hacen en una sola transacción (atomicamente). Antes de 10g el valor default del parámetro era FALSE lo cual implicaba que el paso 1 se hiciera con un TRUNCATE, que obviamente es mas rapido que el DELETE ya que no es transaccional. Justamente al no ser transaccional no consume espacio en UNDO, recordar que el DELETE es la operación DML que mas undo consume por lejos, ya que se debe guardar todas las columnas de cada fila por si es necesario una vuelta atrás.

Como conté mas arriba, en el caso particular del refresco de las dos MV's, ambas, por un errror de filtrado en la MV1, quedaron con millones de filas en lugar de con algunas pocas decenas de miles como debiera y dado que la base es 10g esta tomando el parametro default atomic_refesh = TRUE lo que dicta realizar un delete, en este caso será un delete de alrededor de 100M de filas en ambos casos y por lo tanto cancelaba siempre por espacio de UNDO, ya que no esta preparado ni cofigurado para soportar semejante borrado masivo. La sugerencia de cambiar el parametro default atomic_refresh= FALSE realizará un TRUNCATE y luego el insert refrescando las vistas en forma rapida sin necesidad de recrearlas.

Es común que una vez explicado el nuevo funcionamiento en 10g, que alguien se pregunte porque no se sigue truncando en lugar de hacer delete. La explicación es que en el caso que al realizarse el truncate y luego fallar el insert, la MV quedará vacia lo cual podría afectar el negocio ya que quedaran vacias hasta que el refresco se pueda completar con exito. En otro caso que tiene sentido el delete es cuando no pueden quedar nunca vacias las MV's porque se consultan mucho y si se hace truncate no se retornaran filas hasta que finalice el refresco. Generalmente los errores de refresco se produce cuando los datos se obtienen accediendo las tablas fuente por un dblink desde otra base. En el caso de la base en cuestión, este problema no existe ya que las MV's se refrescan con datos de tablas que estan en el mismo esquema.

Como ultima aclaración, es importante resaltar que no existe riesgo en la realización del refresco sugerido y se podrá realizar en cualquier momento del dia sin afectar el funcionamiento general. Una vez que este refrescado se podrán activar los jobs que disparan los refrescos normalmente.

A continuación voy a mostrarles un ejemplo para comparar tiempos, generando una tabla T y una vista materializada MV_T


rop@DESA10G> alter table t add primary key (x);

Tabla modificada.

rop@DESA10G> create materialized view mv_t
  2          refresh complete
  3          as
  4          select * from t;

Vista materializada creada.

rop@DESA10G> set timing on
rop@DESA10G> exec dbms_mview.refresh(list=>'MV_T',atomic_refresh=>TRUE)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:02:39.75
rop@DESA10G> exec dbms_mview.refresh(list=>'MV_T',atomic_refresh=>FALSE)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:15.54
rop@DESA10G>

En sintesis, es importante analizar los requerimientos de negocio, si estos requerimientos soportan la corta indisponibilidad que se provoca al refrescar no atomicamente (truncate) además de la posibilidad que quede vacia la MV, producto de un error o cancelación, hasta el próximo refresh, entonces es posible refrescar mas rapido y con muy poco consumo de UNDO seteando el parámetro atomic_refresh en FALSE.

Reporte para obtener métricas de SO desde AWR

2010-02-26T04:29:00.000-08:00

Como podemos hace para detectar problemas de cpu o i/o sin tener que pedir ayuda al grupo de Sistemas Operativos?, suena complicado, no?. Bueno... a partir de 10g una opción, por lo menos para ir teniendo un primer panorama, es ver si esta pasando algo con la caja donde reside nuestra base de datos consultando en el repositorio AWR. Para exteriorizar estas métricas existe la vista historica: DBA_HIST_OSSTAT, que en 10g contiene lo siguiente:

STAT_NAME
-------------------------------------------
BUSY_TIME
AVG_IDLE_TIME
NUM_CPUS
AVG_BUSY_TIME
OS_CPU_WAIT_TIME
VM_IN_BYTES
AVG_USER_TIME
AVG_SYS_TIME
LOAD
SYS_TIME
RSRC_MGR_CPU_WAIT_TIME
IDLE_TIME
USER_TIME
PHYSICAL_MEMORY_BYTES
IOWAIT_TIME
AVG_IOWAIT_TIME
VM_OUT_BYTES

y en 11g R1 se agregan las siguientes:

TCP_SEND_SIZE_DEFAULT
TCP_RECEIVE_SIZE_DEFAULT
TCP_RECEIVE_SIZE_MAX
NUM_CPU_SOCKETS
TCP_SEND_SIZE_MAX
NUM_CPU_CORES

Abajo, les copio un script que armé para ver algunas métricas importantes. El reporte toma como unico parametro la cantidad de horas hacia atras que quiero analizar.


set line 120
set pagesize 9999
set verify off
accept horas prompt "Ingrese cantidad de horas hacia atras que desea reportar: "

col snap format a20

select unique to_char(snap,'DD-MON-YYYY HH24') snap,
       avg_idle_time-lead(avg_idle_time) over (partition by st order by snap desc) avg_idle_time,
       avg_user_time-lead(avg_user_time) over (partition by st order by snap desc) avg_user_time,
       avg_sys_time-lead(avg_sys_time) over (partition by st order by snap desc) avg_sys_time,
       avg_iowait_time-lead(avg_iowait_time) over (partition by st order by snap desc) avg_iowait_time,
       os_cpu_wait_time-lead(os_cpu_wait_time) over (partition by st order by snap desc) os_cpu_wait_time
from
(select s.end_interval_time snap,
        s.startup_time st,
       max(decode(stat_name,'AVG_IDLE_TIME',value,null)) AVG_IDLE_TIME,
       max(decode(stat_name,'AVG_USER_TIME',value,null)) AVG_USER_TIME,
       max(decode(stat_name,'AVG_SYS_TIME',value,null)) AVG_SYS_TIME,
       max(decode(stat_name,'AVG_IOWAIT_TIME',value,null)) AVG_IOWAIT_TIME,
       max(decode(stat_name,'OS_CPU_WAIT_TIME',value,null)) OS_CPU_WAIT_TIME
from dba_hist_osstat os,
     dba_hist_snapshot s
where s.snap_id = os.snap_id
group by s.end_interval_time,s.startup_time)
where snap > sysdate-&horas/24
order by snap desc
/


SNAP         AVG_IDLE_TIME AVG_USER_TIME AVG_SYS_TIME AVG_IOWAIT_TIME OS_CPU_WAIT_TIME
25-FEB-10 09 211854       108399     40156  99439  2087400
25-FEB-10 08 271995       61131     27296  97923  1199800
25-FEB-10 07 236760       85951     31938  84592  1489500
25-FEB-10 06 180172       130112     50168  90475  2281500
25-FEB-10 05 182995       123247     54623  105254  2274500
25-FEB-10 04 193166       116204     51902  127028  2197000
25-FEB-10 03 195871       119849     45241  130806  2126400
25-FEB-10 02 236610       85916     37782  160617  1574500

Obviamente, para tener un diagnostico preciso de uso de recursos de SO lo ideal es pedir a los grupos encargados de la administración del SO, networking o storage reportes detallados historicos de actividad, pero si queremos tener una primera foto en forma rapida de lo que esta pasando con solo tener acceso al repositorio de la base, podemos usar la consulta de arriba o cualquier variación (se puede agregar columnas para reportar uso de memoria virtual, tráfico de red, etc) de esta para tener una primera impresión.

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

2010-02-19T08:14:00.001-08:00

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)

2010-02-12T11:01:00.000-08:00

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.

Evolución de los mecanismos para Estabilizar Planes (graciosa comparación)

2010-02-12T10:15:00.000-08:00

Mirando un ppt de una presentación de los nuevos features de 11g me gustó uno de los slides donde se hace una especie de parodia sobre la evolución de los mecanismos para estabilizar los planes de ejecución que se fueron agregando en las distintas versiones. Se refiere a Larry (Ellison) que es el fundador y dueño de Oracle comparandolo con Dios y la creación de la tierra. Me pareció muy gracioso, y seguramente van a entender la sutileza todos aquellos que estén en el mundillo Oracle, en especial los que nos dedicamos a temas de performance que muchas veces admiramos la forma en que trabaja el optimizador y otras, sinceramente no entendemos porque toma ciertas decisiones que lo llevan a armar un plan desastroso, en fin... ahi va la evolución de los mecanismos de estabilidad:

In the beginning was the RULE …
On the Second Day, Larry Created the CBO … (7)
On the Third Day, Larry Created the Hint … (7)
On the Fourth Day, Larry Created the Outline … (8)
… and Larry Saw That it Was Good
On the Fifth Day, Larry Created the Profile … (10g)
On the Sixth Day, Larry Created the Baseline (11g)

Colorear una sentencia sql (Colored SQL)

2010-02-05T07:05:00.000-08:00

Muchos se preguntaran que significa "colorear un sentencia sql", verdad?. Lo que implica colorear es ni mas ni menos que marcar una sentencia identificandola por su sqlid, que es la identificación unica de una sentencia en la base da datos, para que los snapshots de AWR la incluyan en el repositorio y luego ser analizada. El repositorio de AWR almacena, entre otras cosas, las sentencias TOP, es decir las que mas consumieron entre dos snapshots, por default los snapshots se sacan cada hora, por lo que permite analizar por periodos de una hora. Si yo quisiera ver como se fue comportando una sentencia a lo largo de cierto tiempo pero dicha sentencia no esta dentro de las mas consumidoras no quedará registro y por lo tanto no se podrá analizar su actividad a posteriori. Para asegurar que se le siga el rastro a las ejecuciones de un sentencia, sin importar cuanto consume, a partir de 11g se la puede "colorear", veamos como es esto:

Armo una consulta bien sencilla, que obviamente no consumirá mucho y no quedará registrada en una base con una minima actividad:


rop@DESA11G> select 'TEST COLORED SQL' from dual;

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

Buscamos el sqlid asociado:


rop@DESA11G> set line 120
rop@DESA11G>  select sql_text,sql_id from v$sqlstats where sql_text like '%TEST COLORED SQL%';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
SQL_ID
-------------
 select sql_text,sql_id from v$sqlstats where sql_text like '%TEST COLORED SQL%'
f2c39t3uct6vp

select 'TEST COLORED SQL' from dual
0fm46pj2s9vux

Una vez obtenido el sqlid voy a marcarla o colorearla de la siguiente forma:


rop@DESA11G> ed
Escrito file afiedt.buf

  1  begin
  2    dbms_workload_repository.add_colored_sql(sql_id => '0fm46pj2s9vux'
  3    );
  4* end;
rop@DESA11G> /

Procedimiento PL/SQL terminado correctamente.

Ahora tomo un snapshot y luego ejecuto 3 veces la sentencia coloreada:


rop@DESA11G> begin
  2      dbms_workload_repository.create_snapshot;
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> select 'TEST COLORED SQL' from dual;

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

rop@DESA11G> /

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

rop@DESA11G> /

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

Tomo otro snapshot luego de aproximadamente 15' y veo si aparece:


rop@DESA11G> begin
  2      dbms_workload_repository.create_snapshot;
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> select executions_delta,cpu_time_delta,elapsed_time_delta from dba_hist_sqlstat
  2  where snap_id = (select max(snap_id) from dba_hist_sqlstat)
  3    and sql_id = '0fm46pj2s9vux';

EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA
---------------- -------------- ------------------
               3              0                  0

La sentencia apareció y se ve que consumió tan poco que no se llegó a registrar tiempo
Para desmarcarla hago lo siguiente:


rop@DESA11G> ed
Escrito file afiedt.buf

  1  begin
  2    dbms_workload_repository.remove_colored_sql(sql_id => '0fm46pj2s9vux');
  3* end;
rop@DESA11G> /

Procedimiento PL/SQL terminado correctamente.

Repito el proceso pero sin la consulta "coloreada":


rop@DESA11G> ed
Escrito file afiedt.buf

  1  begin
  2      dbms_workload_repository.create_snapshot;
  3* end;
rop@DESA11G> /

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> select 'TEST COLORED SQL' from dual;

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

rop@DESA11G> /

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

rop@DESA11G> /

'TESTCOLOREDSQL'
----------------
TEST COLORED SQL

rop@DESA11G> begin
  2      dbms_workload_repository.create_snapshot;
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

rop@DESA11G> ed
Escrito file afiedt.buf

  1  select executions_delta,cpu_time_delta,elapsed_time_delta from dba_hist_sqlstat
  2  where snap_id = (select max(snap_id) from dba_hist_sqlstat)
  3*   and sql_id = '0fm46pj2s9vux'
rop@DESA11G> /

ninguna fila seleccionada

rop@DESA11G>

Como se observa ahora no se registró en AWR, ya que como se vió es una sentencia con consumo nulo y que obviamente no califica entre las Top para ser persistida en el repositorio.

Extendiendo la información estadistica en 11g (estadisticas multicolumna)

2010-02-02T06:56:00.000-08:00

Una vez mas escribo una nota relacionada con el optimizador de Oracle y en especial sobre las estadisticas, que como ya se sabe son el pilar fundamental para garantizar un plan optimo. Asi como un matematico se basa en axiomas o teoremas para demostrar otro teorema en base a inferencias logicas, el optimizador utiliza la información estadistica que tiene a su disposición para inferir el plan de ejecución mas conveniente, el que menos recurso insume. En 11g se pueden suministrar extensiones a las estadisticas habituales para "ayudar" en ciertos casos particulares. Uno de los problemas que se daban esta relacionado con la correlación entre la información estadisticas de multiples columnas que se referencian en un predicado. Para entender mejor voy a mostrar un ejemplo completo (en una nota de diciembre habia escrito sobre estaditicas multicolumnas para mostrar SQL Profiles, pero esta nueva nota ahonda en mas detalle, ver nota: SQL Profiles. Una ayuda adicional...)

Primero, como es habitual, voy a crear una tabla T en base a los registros de la tabla DBA_OBJECTS y voy a crear un indice por dos columnas elegidas arbitrariamente:


rop@DESA11G> create table t as select * from dba_objects;

Tabla creada.

rop@DESA11G> create index t_idx on t(owner,object_type);

Índice creado.

rop@DESA11G>

Ahora vamos a ver la distribución de las columnas OWNER y OBJECT_TYPE:


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

OWNER                            COUNT(1)
------------------------------ ----------
PUBLIC                              26723
SYSTEM                                518
XDB                                   811
OLAPSYS                               720
FLOWS_FILES                            12
SYS                                 30217
TSMSYS                                  3
MDSYS                                1303
SYSMAN                               3360
EXFSYS                                303
SI_INFORMTN_SCHEMA                      8
ORACLE_OCM                              8
WMSYS                                 315
ORDSYS                               2353
SCOTT                                   6
WK_TEST                                47
FLOWS_030000                         1526
CTXSYS                                372
ORDPLUGINS                             10
WKSYS                                 371
ROP                                   122
OUTLN                                   9
DBSNMP                                 55

23 filas seleccionadas.

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

OBJECT_TYPE           COUNT(1)
------------------- ----------
INDEX                     3228
JOB CLASS                   13
CONTEXT                      7
TABLE SUBPARTITION          40
TYPE BODY                  238
INDEXTYPE                   11
PROCEDURE                  135
RESOURCE PLAN                7
RULE                         1
JAVA CLASS               22205
TABLE PARTITION            289
SCHEDULE                     2
WINDOW                       9
WINDOW GROUP                 4
JAVA RESOURCE              835
TABLE                     2576
TYPE                      2643
VIEW                      4788
LIBRARY                    181
FUNCTION                   296
TRIGGER                    484
PROGRAM                     18
MATERIALIZED VIEW            1
JAVA SOURCE                  1
CLUSTER                     10
SYNONYM                  26795
PACKAGE BODY              1213
CONSUMER GROUP              14
EVALUATION CONTEXT          11
QUEUE                       35
RULE SET                    17
DIRECTORY                    3
EDITION                      1
OPERATOR                    57
UNDEFINED                    6
JAVA DATA                  325
SEQUENCE                   230
LOB                        768
PACKAGE                   1274
INDEX PARTITION            289
LOB PARTITION                7
JOB                         11
XML SCHEMA                  94

43 filas seleccionadas.

De la distribución mostrada podemos ver que tenemos 30210 filas cuyo owner es SYS y 26795 cuyo object_type es SYNONYM. Analizando por separadas ambas distribuciones vemos que son un porcentaje alto del total de cada agrupación y evaluadas por separado suena coherente el acceso full scan cuando se filtra por dichas columnas para los valores analizados.
Ahora veamos que pasa si en un mismo predicado filtramos por owner y object_type:


rop@DESA11G> select count(1) from t where owner = 'SYS' and object_type = 'SYNONYM';

COUNT(1)
----------
      9

Observamos que combinando las dos columnas solo cumplen dicho filtro 9 filas.
Voy a recolectar estadisticas y analizar el plan:


rop@DESA11G> begin
2             dbms_stats.gather_table_Stats(user,
3                              'T',
4                              method_opt => 'for all columns size skewonly');
5* end;
rop@DESA11G> /

rop@DESA11G> explain plan for
2          select *
3          from t
4          where owner = 'SYS' and object_type = 'SYNONYM';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11996 |  1183K|   288   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 11996 |  1183K|   288   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_TYPE"='SYNONYM' AND "OWNER"='SYS')

13 filas seleccionadas.

Estimó 11996 lo cual es muy impreciso, no?, deberia ser 9 o cercano para se mas real. Por que se confundió tanto el optimizador y eligió ir por full scan?. Miremos la salida del trace con el evento 10053, que nos muestra en detalle los pasos que sigue el optimizador para decidir que hacer. Abajo copio solo la parte del trace que nos interesa (el trace completo es muy extenso y lista parametrizaciones, transformaciones,orden de evaluación de predicados, etc):


***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5478, PopValCnt:16, NDV:23
Column (#6):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5475, PopValCnt:26, NDV:43
ColGroup (#1, Index) T_IDX
 Col#: 1 6    CorStregth: 3.96
ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
Table: T  Alias: T
 Card: Original: 69172.000000  Rounded: 11646  Computed: 11645.63  Non Adjusted: 11645.63
 Access Path: TableScan
 Cost:  288.43  Resp: 288.43  Degree: 0
   Cost_io: 285.00  Cost_cpu: 31622103
   Resp_io: 285.00  Resp_cpu: 31622103
ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
Access Path: index (AllEqRange)
 Index: T_IDX
 resc_io: 559.00  resc_cpu: 11318715
 ix_sel: 0.168358  ix_sel_with_filters: 0.168358
 Cost: 560.23  Resp: 560.23  Degree: 1
Best:: AccessPath: TableScan
      Cost: 288.43  Degree: 1  Resp: 288.43  Card: 11645.63  Bytes: 0

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

En el análisis el optimizador asigna un costo de 560 al plan que utiliza el indice y 288 al plan que utiliza el full scan, y como ya sabemos se queda con el que menor costo arroja, por lo tanto estima que es el mejor path.

Afortunadamente en 11g se pueden recolectar estadisticas a nivel multicoluma lo cual aporta información de correlación muy util, veamos como hacerlo:


rop@DESA11G> declare
2      out varchar2(30);
3  begin
4      out := dbms_stats.create_extended_stats(user,'T', '(owner,object_type)');
5  end;
6  /

rop@DESA11G> begin
2      dbms_stats.gather_table_stats(null,'T',
3      method_opt =>'for all columns size auto for columns (owner,object_type)');
4  end;
5  /

rop@DESA11G> explain plan for
2  select *
3  from t
4  where owner = 'SYS' and object_type = 'SYNONYM';

Explicado.

rop@DESA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1020776977

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     6 |   636 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     6 |   636 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='SYNONYM')

14 filas seleccionadas.

El plan ahora uso el indice y ademas observar que la estimación de filas a retornar es de 6, lo cual es bastante cercano a la realidad. Veamos el trace del evento 10053 para analizar que hizo ahora el optimizador:


***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#1):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5478, PopValCnt:16, NDV:23
Column (#6):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5475, PopValCnt:26, NDV:43
Column (#16):
 NewDensity:0.000091, OldDensity:0.000007 BktCnt:5480, PopBktCnt:5441, PopValCnt:104, NDV:250
ColGroup (#1, VC) SYS_STUXJ8K0YTS_5QD1O0PEA514IY
 Col#: 1 6    CorStregth: 3.96
ColGroup Usage:: PredCnt: 2  Matches Full:   Using density: 0.000091 of col #16 as selectivity of unpopular value pred
#1  Partial:  Sel: 0.0001
Table: T  Alias: T
 Card: Original: 69172.000000  Rounded: 6  Computed: 6.31  Non Adjusted: 6.31
Access Path: TableScan
 Cost:  288.20  Resp: 288.20  Degree: 0
   Cost_io: 285.00  Cost_cpu: 29526903
   Resp_io: 285.00  Resp_cpu: 29526903
ColGroup Usage:: PredCnt: 2  Matches Full:   Using density: 0.000091 of col #16 as selectivity of unpopular value pred
#1  Partial:  Sel: 0.0001
ColGroup Usage:: PredCnt: 2  Matches Full:   Using density: 0.000091 of col #16 as selectivity of unpopular value pred
#1  Partial:  Sel: 0.0001
Access Path: index (AllEqRange)
 Index: T_IDX
 resc_io: 2.00  resc_cpu: 19503
 ix_sel: 0.000091  ix_sel_with_filters: 0.000091
 Cost: 2.00  Resp: 2.00  Degree: 1
Best:: AccessPath: IndexRange
Index: T_IDX
      Cost: 2.00  Degree: 1  Resp: 2.00  Card: 6.31  Bytes: 0

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

El costo del full scan sigue siendo, obviamente, el mismo (288) pero ahora el acceso por indice es de tan solo 2 y por lo tanto es el path elegido.
Como les mostré en esta nota podemos garantizar un correcto funcionamiento para predicados con mas de un filtro recolectando las estadisticas extendidas, nuevas en 11g.

Enfoque para medir la disponibilidad efectiva de una base de datos Oracle

2010-01-29T03:31:00.000-08:00

La tarea de los dba's es poco conocida y a veces mal entendida por la mayoria de la gente que no esta especificamente en el tema de base de datos. Es por eso que muchas veces es dificil estimar o medir nuestra eficiencia si no se conoce nada o poco del tema. Dado que la gestión y mantenimiento de bases de datos es un tema muy técnico, resulta ser todo un misterio, incluso para algunos gerentes de sistemas con perfil más de gestión que técnico, que se conforman con no tener sobresaltos y que las bases productivas esten disponibles y con un tiempo de respuesta decente, obviamente esto es deseable en cualquier ambito, pero no permite medir eficiencia con detalle.

Una forma de medir nuestra eficiencia con una métrica entendible por el público en general, podría ser mostrar en numeros la disponibilidad de las bases de datos que gestionamos. Con disponibilidad (availability) me refiero al tiempo total que las bases están arriba, es decir que se pueden usar. Esto en alguna medida (al margen de temas de rendimiento) podría mostrar o cuantificar cuan bien administramos ya que si la base esta arriba las aplicaciones que usan dicha base están operativas. Quien no escucho alguna vez en la cola de un banco o realizando algún trámite municipal la frase: "el sistema esta caido, vuelva mas tarde..."?, en la gran mayoria de los casos es por algun problema en la base de datos o bien porque se tuvieron que realizar tareas de mantenimiento de urgencia, por mala administración, por errores de los operadores o dba's, etc. Es cierto que las bases de datos, y sobre todo Oracle, hoy en día son muy robustas y se han minimizado muchos de los potenciales problemas. Tambien el hardware es mas confiable y, si se tiene presupuesto, se puede redundar en componentes lo cual hace mucho mas improbable una indisponibilidad.

Para poder medir el tiempo de disponibilidad total de una base de datos armé un script que reporta la disponibilidad de los ultimos n dias. En el ejemplo lo hice para los ultimos 4 meses (120 dias). Hay que tomar en cuenta que el reporte se basa de ciertas premisas:

Debe estar disponible la data a procesar en el alert de la base a analizar. Si se realiza alguna tarea de depuracion diaria, semanal, etc, se deberá concatenar de antemano en un archivo toda la info grabada en un archivo de alert que contenga la totalidad de la información necesaria.
Si no existen registros con información de fecha y hora entre dos startups se toma como que la base estuvo arriba todo ese tiempo.
Cuando la base se baja abruptamente, ya sea por un shutdown abort, por un error grave de HW o bien porque se desconectó el cable de alimentación no se registra el shutdown en el alert por lo cual se pierde esa referencia y se usará el último registro disponible en el alert.

Para armar poder correr el reporte primero hay que generar una tabla externa (recordemos que esto existe a partir de 9i por lo cual este metodo no funcionara en versiones anteriores) que permite leer con la sentencia sql el archivo alert.

create directory BDUMP as '/u01/app/oracle/admin/ROP102/bdump'


CREATE TABLE "ALERT_LOG"
(  "TEXT" VARCHAR2(400)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "BDUMP"
ACCESS PARAMETERS
( records delimited by newline
nobadfile
nodiscardfile
nologfile
  )
LOCATION
( 'alert_ROP102.log'
)
)
REJECT LIMIT UNLIMITED;

Una vez creada la tabla externa ya puedo ejecutar la sentencia. La lógica que tomé se basa en buscar el string en el alert que registra el startup de la base y luego (usando funciones análiticas) obtener el registro próximo anterior. Con esto infiero que el tiempo entre el ultimo registro anterior al startup y el registro de startup es el tiempo en el que estuvo baja la base. Esto es una aproximación y puede tener cierta imprecisión pero creo que se acerca bastante a la realidad en la mayoria de los casos. Existen registros en el catalogo de Oracle de cuando una base levanta, pero no se tiene información de cuando se baja, incluso consideremos que si existiera esto, seria complicado registrar los shutdown abort. Por ese motivo recalco que el script que realicé es una aproximación, dado que no se puede saber con exactitud el momento preciso del en el que la base se bajó. Considerando todo lo expuesto anteriormente ahora si vemos como funciona el reporte:


select min_date,
max_date,
round((max_date-min_date)*24,2) total,
down,
round(100-((down*100)/((max_date-min_date)*24)),3) pct_up
from
(select min(to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english')) min_date,
max(to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english')) max_date,
sum(round((to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english')-
to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english'))*24,2)) down
from
(select start_time,
last_time
from
(select text,
lag(text,1) over (order by r) start_time,
lag(text,2) over (order by r) last_time
from ( select rownum r, text
from alert_log
where text like '___ ___ __ __:__:__ 20__'
  or text like 'Starting ORACLE instance (normal)'))
where text like 'Starting ORACLE instance (normal)'
and last_time like '___ ___ __ __:__:__ 20__'
and to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english') > sysdate-120))


MIN_DATE   MAX_DATE   TOTAL   DOWN  PCT_UP
--------   --------   ------  ----  ------
02/10/2009 29/01/2010 2858.22 22.45 99,214

Como se ve en la salida se muestra el intervalo de fechas analizadas, el tiempo total transcurrido entre las dos fechas (medido en horas), el tiempo que estuvo baja (medido en horas) y el porcentaje que estuvo arriba la base. Como vemos esta en valores esperados ya que la disponibilidad esta por arriba del 99%.

Concatenar valores de una columna en 11g R2 (usando la función LISTAGG)

2010-01-22T05:42:00.000-08:00

Varias veces me han preguntado como hacer para concatenar los valores de una columna en una sola fila agrupados por otra cierta columna. Para eso se necesita un operador de concatenación tal como existe el operador SUM() o el AVG() para sumar o sacar el promedio de un conjunto de columnas con cierto criterio de agrupamiento. Para solucionar esto lo que siempre sugeria era crear el operador STRAGG, que es una función que pueden encontrar en la pagina asktom.oracle.com. Abajo voy a crear la función para mostrarles como funciona:


rop@ROP92> create or replace type string_agg_type as object
2  (
3     total varchar2(4000),
4
5     static function
6          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7          return number,
8
9     member function
10          ODCIAggregateIterate(self IN OUT string_agg_type ,
11                               value IN varchar2 )
12          return number,
13
14     member function
15          ODCIAggregateTerminate(self IN string_agg_type,
16                                 returnValue OUT  varchar2,
17                                 flags IN number)
18          return number,
19
20     member function
21          ODCIAggregateMerge(self IN OUT string_agg_type,
22                             ctx2 IN string_agg_type)
23          return number
24  );
25  /

Type created.


rop@ROP92> create or replace type body string_agg_type
2  is
3
4  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5  return number
6  is
7  begin
8      sctx := string_agg_type( null );
9      return ODCIConst.Success;
10  end;
11
12  member function ODCIAggregateIterate(self IN OUT string_agg_type,
13                                       value IN varchar2 )
14  return number
15  is
16  begin
17      self.total := self.total || ',' || value;
18      return ODCIConst.Success;
19  end;
20
21  member function ODCIAggregateTerminate(self IN string_agg_type,
22                                         returnValue OUT varchar2,
23                                         flags IN number)
24  return number
25  is
26  begin
27      returnValue := ltrim(self.total,',');
28      return ODCIConst.Success;
29  end;
30
31  member function ODCIAggregateMerge(self IN OUT string_agg_type,
32                                     ctx2 IN string_agg_type)
33  return number
34  is
35  begin
36      self.total := self.total || ctx2.total;
37      return ODCIConst.Success;
38  end;
39  end;
40  /

Type body created.

rop@ROP92>
rop@ROP92> CREATE or replace
2  FUNCTION stragg(input varchar2 )
3  RETURN varchar2
4  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5  /

Function created.

rop@ROP92>
rop@ROP92> select deptno, stragg(ename)
2    from scott.emp
3   group by deptno
4  /

DEPTNO   STRAGG(ENAME)
-------------------------------
   10   CLARK,KING,MILLER
   20   SMITH,FORD,ADAMS,SCOTT,JONES
   30   ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

En el ejemplo concatené los empleados en una sola fila agrupados por el departamento al cual pertenecen. Si tuvieran que concatenar mas valores y no le alcanza con varchar2 tambien pueden encontrar en el sitio de Tom una variante para usar CLOB, aunque es bastante mas lenta.

Ahora bien, en 11g R2 hubo una actualizacion importante de la funciones analiticas, tambien llamadas Analytic Functions II, y una de estas nuevas funciones (LISTAGG)se puede utilizar para hacer lo mismo que stragg en forma nativa evitando tener que crear el tipo stragg y demás. Les muestro un ejemplito para listar los tipos de trabajo que hay en cada departamento:


rop@ROP112> select deptno, listagg(job,',') within group (order by job) jobs
2    from (select distinct deptno, job from scott.emp)
3   group by deptno
4   order by deptno
5  /

DEPTNO JOBS
---------- ------------------------------
   10 CLERK,MANAGER,PRESIDENT
   20 ANALYST,CLERK,MANAGER
   30 CLERK,MANAGER,SALESMAN

Como siempre digo, es importante leer los manuales cada vez que se libera una nueva versión, sobre todo el "Oracle New Features" que es un resumen de las nuevas caracteriticas. Me sucede a menudo que veo codigos o formas de administración antiguas que insumen muchas horas para hacer lo mismo que ya esta resuelto en forma nativa o mas simple a partir de una nueva versión.

Como encontrar los "agujeros" (gaps) en las columnas que se llenan con valores de secuencias

2009-12-15T10:57:00.001-08:00

Voy a mostrar un ejemplo de uso de las funciones analiticas "Analytical Functions" para mostrarles de que forma sencilla y elegante se pueden encontrar los gaps de valores en las columnas alimentadas por secuencias. Es sabido que el objeto secuencia no es transaccional, por lo tanto si una transaccion se descarta (rollback) el próximo valor de secuencia obtenido para la transacción se pierde. Tambien es común que las secuencias manejen un cache y por lo tanto ante una bajada de la base de datos tambien se pierden los valores cacheados que no habian sido consumidos. A veces se necesita saber cuales son los intervalos no cosecutivos por algún tema de negocio. Para hacer eso se podría usar un bloque procedureal con pl/sql, pero con funciones analiticas lo resolvemos de una manera mas simple y performante, veamos con un ejemplo desde cero como hacerlo:


rop@DESA11G> create table t (x int);

Tabla creada.

rop@DESA11G> create sequence t_seq;

Secuencia creada.

Una vez creada la tabla y la secuencia voy a llenar la tabla T con 1000 valores consecutivos:


rop@DESA11G> insert into t
  2  select t_seq.nextval
  3  from dual
  4  connect by rownum <= 1000;   
1000 filas creadas.

Ahora voy a eliminar filas para generar gaps y poder mostrar como funciona la senntencia para listar agujeros.


rop@DESA11G> delete from t
  2  where (x between 100 and 150) or (x between 900 and 910);
62 filas suprimidas.

Ya tengo armado el ambiente asi que ahora simplemente ejecuto la sentencia
con FA para detectar los gaps:



rop@DESA11G> select *
  2  from (select unique x,
  3               lead(x) over (order by x) x_next
  4        from t)
  5  where x+1 != x_next;

         X     X_NEXT
---------- ----------
        99        151
       899        911

Se puede observar que se listaron los intervalos no cosecutivos que corresponden a las filas que se eliminaron en el ejemplo.

Reporte para analizar planes que referencien a una tabla dada

2009-12-11T09:57:00.000-08:00

Cada tanto me consultan desde el area de desarrollo sobre si agregando tal o cual indice a una tabla mejoraria el rendimiento de la aplicación. Como en muchas ocasiones yo no conozco el negocio me resulta dificil saber como se usa la tabla, es decir, que sentencias la referencian, si se hacen updates, deletes, inserts o solo se consulta. Para poder analizar esto a veces uso el script que copio abajo, que da el plan de ejecución y la ultima vez que se ejecutaron las sentencias que referencian a una cierta tabla. A esto se le puede sumar la generación de sugerencias usando advisor tales como SQL Tuning y SQL Access Advisors.


set serverout on
set line 120
set pagesize 9999
set verify off
set feed off

ACCEPT tabla PROMPT "Ingrese Tabla a Analizar: "
PROMPT
PROMPT

begin
    for i in (select sp.sql_id,max(sh.begin_interval_time) ufecha
              from dba_hist_sql_plan sp,
                   dba_hist_sqlstat ss,
                   dba_hist_snapshot sh
              where sp.sql_id = ss.sql_id
                and ss.snap_id = sh.snap_id
                and sp.object_name = upper('&tabla')
              group by sp.sql_id)
    loop
       dbms_output.put_line('Ultima Ejecución: '||to_char(i.ufecha,'DD/MM/YYYY HH24:MI'));
       for j in (select * from table(dbms_xplan.display_awr(i.sql_id)))
       loop
           dbms_output.put_line (j.plan_table_output);
       end loop;
       dbms_output.put_line(chr(10)||rpad('*',100,'*')||chr(10));
    end loop;
end;
/

set verify on
set feed on

SQL Profiles. Una ayuda adicional para que el optimizador elija el plan correcto.

2009-12-09T06:20:00.001-08:00

Se puede recolectar estadísticas sobre tablas (nro de filas,tamaño promedio de fila,bloques, etc), sobre indíces (clustering factor, cantidad de hojas, altura,etc), sobre columnas (histogramas, densidad, valores distintos,etc) sobre el sistema (velocidad de cpu,tasa de transferencia a disco,etc) y tambien sobre la tablas de catálogo. Toda esta información permite generar el acceso a los datos mas adecuado (armar el plan correcto).De todas formas hay ciertos casos donde no es suficiente toda la estadistica recolectada como cuando se invocan funciones en la sentencias sql o cuando existe una fuerte correlación entre columnas que se filtran en un predicado.

A partir de 10g existen los SQL Profiles que corrigen estos problemas aportandole al optimizador mayor información, hay que pensarlo como estadisticas particulares sobre las sentencias. En notas anteriores mostré las caracteristicas de STORED_OUTLINES(8i) y de las SQL BASELINES (11g). En esta nota con SQL PROFILES(10g) , en orden no cronólogico, se completa la trilogia de mecanismos de corrección de planes que se han ido agregando versión tras versión. Este conjunto de herramientas se podrían pensar como mecanismos de hinteo encubierto que han ido evolucionando y sofisticandose con cada nuevo realease de base.

A diferencia de los STORED_OUTLINES que fijan un determinado plan a una sentencia, los SQL PROFILES son un set de hints que corrigen ciertos calculos internos del optimizador para que no se desvien los planes.
Para obtener los profiles se puede usar el paquete DBMS_SQLTUNE que crea un tarea y realiza una pseudo ejecución tomando métricas que le permiten conocer mayor detalle y analizar las correlaciones entre las columnas referenciadas en los predicados de la sentencia.

En el ejemplo que armé, cree una tabla TBL_AUTOS que podría representar datos de un concesionario o fabrica automotriz, con 3 campos: un identificación unica del auto (ID), una categoria o segmento: (A) Segmento Bajo, (B) Segmento Medio, (C)) Segmento Alto y (D) Gama Premium y como tercer campo el precio o valor del vehiculo en dolares, es una aproximación de los valores aqui en Argentina. Ahora voy a crear la tabla tratando de generar en forma ficticia una distribución cercana a una distribución real.


rop@ROP102> create table tbl_autos (id int,categoria char(1),valor int);

Tabla creada.

rop@ROP102> create sequence seq_autos;

Secuencia creada.

rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'A',
4 trunc(dbms_random.value(8000,12000))
5 from dual
6 connect by rownum <= 60000;

60000 filas creadas.

rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'B',
4 trunc(dbms_random.value(12000,15000))
5 from dual
6 connect by rownum <= 25000;

25000 filas creadas.

rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'C',
4 trunc(dbms_random.value(15000,30000))
5 from dual
6 connect by rownum <= 14500;

14500 filas creadas.

rop@ROP102> insert into tbl_autos
2 select seq_autos.nextval,
3 'D',
4 trunc(dbms_random.value(40000,80000))
5 from dual
6 connect by rownum <= 500 ;

500 filas creadas.

rop@ROP102> create index autos_idx on tbl_autos (categoria,valor);

Índice creado.

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

Procedimiento PL/SQL terminado correctamente.

Con los pasos listado arriba tengo una tabla TBL_AUTOS, indexada por (categoria,valor) y con las estadísticas recolectadas.

Los vehiculos de categoria 'A' nunca superan los 12000 dólares entonces es de esperar que si realizo una consulta filtrando por categoria='A' y valor > 20000, el resultado debería ser 0, verdad?. Veamos que pasa.


rop@ROP102> explain plan for  select min(id)
  2    from tbl_autos
  3    where categoria = 'A'
  4      and valor > 20000;

Explicado.

rop@ROP102> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 649483413

--------------------------------------------------------------------------------
 Id   Operation           Name       Rows   Bytes  Cost (%CPU) Time    
--------------------------------------------------------------------------------
   0  SELECT STATEMENT                   1     11     73   (7) 00:00:01
   1   SORT AGGREGATE                    1     11                      
*  2    TABLE ACCESS FULL TBL_AUTOS  20832    223K    73   (7) 00:00:01
--------------------------------------------------------------------------------

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

   2 - filter("VALOR">20000 AND "CATEGORIA"='A')

14 filas seleccionadas.

Fijense que se estimaron 20832 filas, lo cual dista muchisimo de la realidad. Al calcular tan mal la cardinalidad el optimizador optó por un plan inadecuado, dictando recorrer toda la tabla para obtener el resultado (FULL SCAN), suena ilógico, no?. Esto se da por lo que comenté antes respecto a que el optimizador no cuenta con información de distribución correlacionada o multi-columna.

Voy a ejecutar el optimizador automático o SQL TUNING ADVISOR para ver como puedo evitar que se infiera un plan sub-optimo.


rop@ROP102> declare
  2     l_task_id     varchar2(20);
  3  begin
  4      l_task_id := dbms_sqltune.create_tuning_task (
  5        sql_text  => 'select min(id)
  6                      from tbl_autos
  7                      where categoria = ''A''
  8                        and valor > 20000',
  9        user_name  => 'ROP',
 10        scope      => 'COMPREHENSIVE',
 11        time_limit => 120,
 12        task_name  => 'test'
 13     );
 14     dbms_sqltune.execute_tuning_task ('test');
 15  end;
 16  /

Procedimiento PL/SQL terminado correctamente.

rop@ROP102> set serveroutput on size 999999
rop@ROP102> set long 999999
rop@ROP102> select dbms_sqltune.report_tuning_task ('test') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test
Tuning Task Owner  : ROP
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 120
Completion Status  : COMPLETED
Started at         : 12/07/2009 14:41:01
Completed at       : 12/07/2009 14:41:01

-------------------------------------------------------------------------------
Schema Name: ROP
SQL ID     : 7b5twtc2yrgsy
SQL Text   : select min(id)
                                 from tbl_autos
                                 where categoria = 'A'
                                   and valor > 20000

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  Se ha encontrado un plan de ejecución potencialmente mejor para esta
  sentencia.

  Recommendation (estimated benefit: 99.19%)
  ------------------------------------------
  - Puede aceptar el perfil SQL recomendado.
    execute dbms_sqltune.accept_sql_profile(task_name => 'test', task_owner
            => 'ROP', replace => TRUE);

  Validation results
  ------------------
  Se ha probado SQL profile  ejecutando su plan y el plan original y midiendo
  sus respectivas estadísticas de ejecución. Puede que uno de los planes se
  haya ejecutado sólo parcialmente si el otro se ha ejecutado por completo en
  menos tiempo.r

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time(ms):                   19                 0        100%
  CPU Time(ms):                       10                 0        100%
  User I/O Time(ms):                   0                 0
  Buffer Gets:                       248                 2      99.19%
  Disk Reads:                          0                 0
  Direct Writes:                       0                 0
  Rows Processed:                      1                 1
  Fetches:                             1                 1
  Executions:                          1                 1

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 649483413

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

 Id   Operation           Name       Rows   Bytes  Cost (%CPU) Time    

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

   0  SELECT STATEMENT                   1     11     73   (7) 00:00:01

   1   SORT AGGREGATE                    1     11                      

*  2    TABLE ACCESS FULL TBL_AUTOS      1     11     73   (7) 00:00:01

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


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

   2 - filter("VALOR">20000 AND "CATEGORIA"='A')

2- Original With Adjusted Cost
------------------------------
Plan hash value: 649483413

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

 Id   Operation           Name       Rows   Bytes  Cost (%CPU) Time    

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

   0  SELECT STATEMENT                   1     11     73   (7) 00:00:01

   1   SORT AGGREGATE                    1     11                      

*  2    TABLE ACCESS FULL TBL_AUTOS      1     11     73   (7) 00:00:01

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


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

   2 - filter("VALOR">20000 AND "CATEGORIA"='A')

3- Using SQL Profile
--------------------
Plan hash value: 552074332

--------------------------------------------------------------------------------
----------
 Id   Operation                     Name       Rows   Bytes  Cost (%CPU)
Time    
--------------------------------------------------------------------------------
----------
   0  SELECT STATEMENT                             1     11      3   (0)
00:00:01
   1   SORT AGGREGATE                              1     11            
        
   2    TABLE ACCESS BY INDEX ROWID TBL_AUTOS      1     11      3   (0)
00:00:01
*  3     INDEX RANGE SCAN           AUTOS_IDX      1             2   (0)
00:00:01
--------------------------------------------------------------------------------
----------

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

   3 - access("CATEGORIA"='A' AND "VALOR">20000 AND "VALOR" IS NOT NULL)

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

El analizador automático sugiere aplicar un sql profile y estima una mejora de casi el 100%. Apliquemos el profile y veamos como resuelve la sentencia con esa información adicional.


  1  begin
  2     dbms_sqltune.accept_sql_profile(task_name => 'test',
  3                                     task_owner => 'ROP',
  4                                     replace => TRUE);
  5* end;
rop@ROP102> /

Procedimiento PL/SQL terminado correctamente.

rop@ROP102> explain plan for   select min(id)
  2    from tbl_autos
  3    where categoria = 'A'
  4      and valor > 20000;

Explicado.

rop@ROP102> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 552074332

------------------------------------------------------------------------------------------
 Id   Operation                     Name       Rows   Bytes  Cost (%CPU) Time    
------------------------------------------------------------------------------------------
   0  SELECT STATEMENT                             1     11      3   (0) 00:00:01
   1   SORT AGGREGATE                              1     11                      
   2    TABLE ACCESS BY INDEX ROWID TBL_AUTOS      1     11      3   (0) 00:00:01
*  3     INDEX RANGE SCAN           AUTOS_IDX      1             2   (0) 00:00:01
------------------------------------------------------------------------------------------

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

   3 - access("CATEGORIA"='A' AND "VALOR">20000 AND "VALOR" IS NOT NULL)

Note
-----
   - SQL profile "SYS_SQLPROF_01256a3d43600000" used for this statement

Se puede observar el el optimizador ahora utilizó el profile "SYS_SQLPROF_01256a3d43600000" estimando ahora una cardinalidad de 1 (debería ser 0 (cero) pero nunca se muestra 0 a menos que se trate de un predicado con una contradicción, por ejemplo: 1=2).

A partir de 11g existen las denominadas "extended statistics" que nos permiten recolectar estadísticas del tipo function based y multi-column y minimizar los problemas de estimación de cardinalidad con filtros de columna correlacionadas.

Una introducción a los histogramas. Para que se usan y como se interpretan

2009-12-04T05:18:00.001-08:00

Si alguna vez se pusieron a buscar información sobre que son, como funcionan y para que sirven realmente los histogramas en Oracle se habrán percatado que no existe demasiada data al respecto. La mayoria de la información es solo de referencia y no se explica claramente la verdadera esencia. El tema es bastante extenso, en esta primera parte, mi idea es introducir los conceptos principales, como se guarda la información de histogramas en el catálogo y como se interpreta dicho contenido. En futuras notas voy a mostrarles mas detalle de como se calcula la cardinalidad y el costo de los planes en base a la información estadística y de histogramas y tambien en que casos no sirven.

Oracle utiliza los histogramas para mejorar los calculos de cardinalidad y selectividad cuando la distribución de los datos no es uniforme. Hay que pensar a los histogramas como una "dibujo" de los datos que representa la distribución del contenido en las columnas. Existen dos tipos de histogramas: "Frecuency Histograms" y "Height Balanced Histrograms". Los primeros se usan cuando los valores distintos de la columna son pocos, y los segundos cuando la columna tiene gran cantidad de valores diferentes.

En los histogramas por frecuencia cada valor de la columna se corresponde con una entrada del histograma. Cada entrada contiene en número de concurrencia para un valor. En los histogramas balanceados la columna es dividida en buckets (barras), cada barra tiene el mismo número de filas y cada valor puede ser representado por uno o mas buckets, según su popularidad (cantidad de ocurrencias).

La principal tarea de los histogramas es ayudar a obtener la cardinalidad correcta, como ya comenté en otras notas, el calculo de la cardinalidad es fundamental para que Oracle arme el plan mas adecuado, lo que implica elegir el método y el orden de los joins y la elección de los indices correctos, y asi garantizar la mejor performance posible.

La forma de almacenamiento sigue conceptos estadísticos como percentiles y cuartiles y los valores se agrupan en buckets (si se grafica un histogramas, los buckets son como las barras en un gráfico de barras común y corriente).

Ahora vamos a hacer un par de pruebas y asi tratar de entender un poco mas el concepto detrás de los histogramas.

Primero voy a crear una tabla que tendrá 11110 filas, con cuatro valores posibles (1,2,3 y 4) de forma tal de que cada valor tenga una cantidad redonda de registros (potencia de 10).



rop@DESA10G> create table t
  2  as select case when (rownum between 1 and 10) then 1
  3                 when (rownum between 11 and 110) then 2
  4                 when (rownum between 111 and 1110) then 3
  5                 when (rownum between 1111 and 11110) then 4
  6            end x
  7  from dual
  8  connect by rownum <= 11110;

Tabla creada.

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

         X   COUNT(1)
---------- ----------
         1         10
         2        100
         3       1000
         4      10000

La tabla T nos quedó con la distribución que se muestra arriba. Voy a recolectar
estadísticas y ver en la tabla de catálogo USER_TAB_COL_STATISTICS los datos estadisticos de la columna X:


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

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 4
LOW_VALUE                     : C102
HIGH_VALUE                    : C105
DENSITY                       : .25
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 02-dic-2009 15:28:59
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE
-----------------

Procedimiento PL/SQL terminado correctamente.

De los datos mostrados arriba vemos que Oracle detectó 4 valores distintos, que no hay valores nulos, que la densidad es 0.25 (1/"cant. de valores distinto"=1/4),que hay un solo bucket y que no hay histogramas (NONE). Ya que en la recolección no explicitamos el parámetro method_opt, que da directivas de como armar los histogramas, se uso el default que es: "for all columns size auto". La unica información de distribución es la siguiente:


rop@DESA10G> select * from user_tab_histograms where table_name = 'T';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
T          X                       0              1
T          X                       1              4

Recolectando estadísticas diciendole a Oracle que use 4 buckets para representar la distribución obtenemos:


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

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 4
LOW_VALUE                     : C102
HIGH_VALUE                    : C105
DENSITY                       : .000045004500450045
NUM_NULLS                     : 0
NUM_BUCKETS                   : 4
LAST_ANALYZED                 : 02-dic-2009 16:12:21
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 3
HISTOGRAM                     : FREQUENCY
-----------------

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> select * from user_tab_histograms where table_name = 'T';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
T          X                       10              1
T          X                      110              2
T          X                     1110              3
T          X                    11110              4
rop@DESA10G>

Notemos que ahora se creo un "Frecuency Histrogram", que la densidad es mucho menor y viendo la tabla USER_TAB_HISTOGRAMS ahora se representa exactamente la distribución. Para interpretar esa tabla pensemos que la columna ENDPOINT_VALUE es cada valor posible de la columna y el ENDPOINT_NUMBER es la cantidad de registros iguales. Para 1 tenemos 10, para 2 tenemos 110-10=100, para 3 tenemos 1110-110=1000 y asi siguiendo.

Ahora voy a armar una distribución distinta, con muchos valores distintos para mostrarles el otro tipo de histograma:


rop@DESA10G>  drop table t;

Tabla borrada.

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create table t
  2  as select rownum x
  3  from dual
  4* connect by rownum <= 11110
rop@DESA10G> /

Tabla creada.

rop@DESA10G>  update t set x = 1 where rownum <= 5000;

5000 filas actualizadas.

rop@DESA10G> update t set x = 2 where rownum <= 3000 and x != 1;

3000 filas actualizadas.

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

Índice creado.

Se creó una tabla con la misma cantidad de filas que el ejemplo anterior pero ahora tenemos una distribución muy diferente. Para X=1 tenemos 5000 valores, para x=2 hay 3000 filas y para las demas solo 1 ocurrencia. Como ahora tengo muchos valores distintos si recolecto sin especificar me va a crear el histograma. Para evitar que lo cree en el parametro method_opt le especifiqué "for columns size 1" para que no asocie ningun histograma.


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

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');

Procedimiento PL/SQL terminado correctamente.

Como se ve, consultando la tabla USER_TAB_COL_STATISTICS no obtenemos ningua fila, es decir no tenemos información de histogramas.
Recordemos que para X=1 tenemos 5000 valores, es decir casi la mitad del total de filas, por lo tanto si filtramos en el predicado por dicho valor es de esperar que el optimizador use un full_scan, no?, veamos que pasa:


rop@DESA10G>  explain plan for select count(1) from t where x = 1;

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3482591947

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |   111 |   333 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("X"=1)

14 filas seleccionadas.

Notar que el optimizador estimó (columna ROWS) 111 filas, lo cual dista mucho de la realidad, recordemos que tenemos 5000 filas que cumplen con X=1. Si recolectamos usando el método default:


rop@DESA10G> ed
Escrito file afiedt.buf

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

Procedimiento PL/SQL terminado correctamente.

rop@DESA10G> exec print_table('select * from user_tab_col_statistics where table_name = ''T''');
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 3112
LOW_VALUE                     : C102
HIGH_VALUE                    : C3020C0B
DENSITY                       : .00009000900090009
NUM_NULLS                     : 0
NUM_BUCKETS                   : 254
LAST_ANALYZED                 : 04-dic-2009 10:02:53
SAMPLE_SIZE                   : 11110
GLOBAL_STATS                  : YES
USER_STATS                    : NO
AVG_COL_LEN                   : 4
HISTOGRAM                     : HEIGHT BALANCED
-----------------

Procedimiento PL/SQL terminado correctamente.

El tipo de histograma ahora es: "HEIGHT BALANCED", la cantidad de filas distintas es 3312 y el nro de buckets es de 254 (el máximo posible)

La representación de los datos en la tabla T ahora es la siguiente:


rop@DESA10G> select * from user_tab_histograms where table_name = 'T';


TABLE_NAME                     COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT
------------------------------ ---------- --------------- -------------- --------
T                              X                      113              1
T                              X                      181              2
T                              X                      182           8008
T                              X                      183           8052
T                              X                      184           8096
T                              X                      185           8140
T                              X                      186           8184
T                              X                      187           8228
T                              X                      188           8272
T                              X                      189           8315
T                              X                      190           8358
T                              X                      191           8401
T                              X                      192           8444
T                              X                      193           8487
T                              X                      194           8530
T                              X                      195           8573
T                              X                      196           8616
T                              X                      197           8659
T                              X                      198           8702
T                              X                      199           8745
T                              X                      200           8788
T                              X                      201           8831
T                              X                      202           8874
T                              X                      203           8917
T                              X                      204           8960
T                              X                      205           9003
T                              X                      206           9046
T                              X                      207           9089
T                              X                      208           9132
T                              X                      209           9175
T                              X                      210           9218
T                              X                      211           9261
T                              X                      212           9304
T                              X                      213           9347
T                              X                      214           9390
T                              X                      215           9433
T                              X                      216           9476
T                              X                      217           9519
T                              X                      218           9562
T                              X                      219           9605
T                              X                      220           9648
T                              X                      221           9691
T                              X                      222           9734
T                              X                      223           9777
T                              X                      224           9820
T                              X                      225           9863
T                              X                      226           9906
T                              X                      227           9949
T                              X                      228           9992
T                              X                      229          10035
T                              X                      230          10078
T                              X                      231          10121
T                              X                      232          10164
T                              X                      233          10207
T                              X                      234          10250
T                              X                      235          10293
T                              X                      236          10336
T                              X                      237          10379
T                              X                      238          10422
T                              X                      239          10465
T                              X                      240          10508
T                              X                      241          10551
T                              X                      242          10594
T                              X                      243          10637
T                              X                      244          10680
T                              X                      245          10723
T                              X                      246          10766
T                              X                      247          10809
T                              X                      248          10852
T                              X                      249          10895
T                              X                      250          10938
T                              X                      251          10981
T                              X                      252          11024
T                              X                      253          11067
T                              X                      254          11110

75 filas seleccionadas.

La tabla de arriba se lee diferente a la que info que guardaba el histograma del tipo "FRECUENCY" ya que hay muchos valores para representar Oracle uso el tipo de histograma "HEIGHT BALANCED". Analizando el histograma vemos que la columna ENDPOINT_VALUE tiene 254 valores (buckets). La primera fila:


TABLE_NAME                     COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT
------------------------------ ---------- --------------- -------------- --------
T                              X                      113              1

Tiene 113 buckets asignados. De donde sale es valor?. Pensemeos que tenemos 11110 filas, de las cuales tenemos 5000 con valor 1. Hagamos un poco de aritmetica:

Si tenemos 11100 filas representadas con 254 buckets y para el valor 1 se asignaron 113 tenemos:


rop@DESA10G> select (11110/254)*113 from dual;

(11110/254)*113
---------------
      4942.6378

Cercano a 5000, no?, veamos que pasa para el valor 2:


TABLE_NAME                     COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT
------------------------------ ---------- --------------- -------------- --------
T                              X                      113              1
T                              X                      181              2

Para sacar la cantidad de buckets del ENDPOINT_VALUE tenemos que hacer:


rop@DESA10G> select (11110/254)*(181-113) from dual;

(11110/254)*(181-113)
---------------------
           2974.33071

Como se ve esta muy cercano a 3000 que es la cantidad real del filas del tipo 2.
La diferencia se da por lo siguiente: Ya que tenemos 11110 filas representadas por 254 barras a buckets entonces tenemos: 11110/254= 43.74 filas por bucket. Los dos valores obtenidos arriba para X=1 y X=2 no son exactos porque se calcula discretizando por bucket.

Una vez obtenido el histograma veamos el plan que se obtiene para la consulta:


rop@DESA10G>  explain plan for select count(1) from t where x = 1;

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4943 | 14829 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("X"=1)

14 filas seleccionadas.

Esta vez el optimizador eligió ir por un FULL_SCAN que es el mejor plan ya que tiene que recorrer casi la mitad de la tabla.

Si buscamos un valor con una sola ocurrencia:


rop@DESA10G> explain plan for select count(1) from t where x = 999999;

Explicado.

rop@DESA10G>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3482591947

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

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

   2 - access("X"=999999)

14 filas seleccionadas.

rop@DESA10G>

Se observa que se usa el indice en el plan, que obviamente es el mejor acceso posible.
Por último, es importante prestar atención a la cardinalidad (columna ROWS del plan) para el caso de X=1 el calculo fue: 4943 que es el valor redondeado que obtuvimos mas arriba haciendo: (11110/254)*113. De esto podemos confirmar que el Optimizador consultó el histograma para obtener la cardinalidad mas cercana a la real y por lo tanto generó el plan mas adecuado.

Testeando el commit asincrónico (COMMIT_WRITE)

2009-11-29T07:02:00.000-08:00

En 10g R2 se introdujo el parámetro COMMIT_WRITE cuya finalidad es permitir el uso de commits asincronicos. Como es sabido cada vez que efectuamos un commit (o rollback) se persiste la informacion de la transacción en los redo logs en forma sincronica, esto significa que hasta que no se haga efectiva dicha escritura en disco, no se retorna el control a la aplicación o proceso que confirmó la transacción. Ahora bien, si pensamos en un commit asincronico estariamos violando la premisa de Durabilidad de una transacción, recordemos que en Oracle las transacciones son compatibles con el concepto ACID (Atomicity, Consistency, Isolation, Durability), y por lo tanto se podrian perder transacciones si se produjera un error severo de Hardware. Al no asegurar la durabilidad de las transacciones no podemos confiar que la base de datos maneje en forma totalmente confiable nuestras transaccciones lo cual resulta en sistemas inestables y propensos a errores e insonsistencias. Despues de todo lo que les comenté se preguntaran para que puede servir este nuevo parámetro, no?, bueno... yo diría que en ciertos casos muy particulares y acotados, donde se pueda tolerar perdida de información y donde se requiera muy alta transaccionabilidad, encontrariamos un beneficio en performance (por ejemplo se reducirian las esperas en el evento log file sync) ya que la ejecución de las sentencias COMMIT es instantanea lo cual genera mayor cantidad de ejecuciones.

Antes de ir a la prueba me gustaría aclarar que si se ven tentados a usar este parámetro para acelerar el procesamiento, primero chequeen el diseño de transaccionabilidad de su aplicación y vean si es posible una vuelta de tuerca desde el lado del código, ya sea viendo si es posible "commitear" menos frecuentemente o si se esta "commiteando" en forma redundante, antes de ir una solución (quick and dirty) como es tocar el parámetro COMMIT_WRITE.

Para probar este parámetro, armé un bloque PL/SQL anónimo donde voy a probar las cuatro conbinaciones posibles del COMMIT_WRITE y voy a almacenar los resultados del test en la tabla RES para luego realizar una consulta que me permita comparar en forma sencilla cada opción.


SQL> create table T (X NUMBER(38),
2                    Y VARCHAR2(10),
3                    Z DATE)
/
Table created.

SQL>

SQL> create table RES (NAME     VARCHAR2(200) not null,
2                      VALUE    NUMBER,
3                      NCASE    NUMBER(1) not null)
/
Table created.

rop@BPP3> begin
2   execute immediate 'truncate table res';
3
4   for i in 1..4 loop
5
6    case i when 1 then
7        execute immediate 'alter session set commit_write=''immediate,wait''';
8            when 2 then
9        execute immediate 'alter session set commit_write=''immediate,nowait''';
10           when 3 then
11        execute immediate 'alter session set commit_write=''batch,wait''';
12             when 4 then
13          execute immediate 'alter session set commit_write=''batch,nowait''';
14   end case;
15
16   insert into res
17   select 'STAT...' || a.name name,
18          b.value,
19          case i when 1 then 1
20                 when 2 then 2
21                 when 3 then 3
22                 when 4 then 4
23          end
24   from v$statname a, v$mystat b
25   where a.statistic# = b.statistic#
26   union all
27   select 'LATCH.' || name,
28          gets,
29          case i when 1 then 1
30                 when 2 then 2
31                 when 3 then 3
32                 when 4 then 4
33          end
34   from v$latch
35   union all
36   select 'STAT...Elapsed Time',
37          hsecs,
38          case i when 1 then 1
39                 when 2 then 2
40                 when 3 then 3
41                 when 4 then 4
42          end
43   from v$timer;
44
45   for j in (select trunc(dbms_random.value(1,10000)) x,
46                    dbms_random.string('a',10) y,
47                    sysdate+dbms_random.value(-300,300) z
48             from dual
49              connect by rownum <= 100000)  
50   loop  
51      insert into t values (j.x,j.y,j.z);  
52      commit; 
53   end loop; 
54
55   for k in (select 'STAT...' || a.name name, b.value  
56             from v$statname a, 
57                  v$mystat b  
58             where a.statistic# = b.statistic#  
59             union all  
60             select 'LATCH.' || name,  gets 
61             from v$latch  
62             union all  
63             select 'STAT...Elapsed Time',hsecs  
64             from v$timer)  
65   loop
66       update res 
67       set value = (k.value-value)
68       where res.name = k.name 
69         and res.ncase = i;  
70    end loop; 
71    commit; 
72  end loop;  
73 end; 
/  
PL/SQL procedure successfully completed.

Ahora voy a ejecutar una consulta sobre la tabla de resultados de forma tal de encolumnar cada caso y poder comparar mas facilmente.


rop@BPP3> select name,
            max(decode(ncase,1,value,null)) "IMMEDIATE,WAIT",
            max(decode(ncase,2,value,null)) "IMMEDIATE,NOWAIT",
            max(decode(ncase,3,value,null)) "BATCH,WAIT",
            max(decode(ncase,4,value,null)) "BATCH,NOWAIT"
     from res
     group by name

NAME                                                         IMMEDIATE,WAIT IMMEDIATE,NOWAIT BATCH,WAIT BATCH,NOWAIT

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

STAT...recursive cpu usage                                             1393              887       1293          849

STAT...messages sent                                                 100040            10449     100008           53

STAT...commit wait requested                                         100000                0     100000            0

STAT...SMON posted for undo segment shrink                                0                0          0            0

STAT...commit batch performed                                             0                0     100000       100000

STAT...commit immediate performed                                    100000           100000          0            0

STAT...commit nowait performed                                            0           100000          0       100000

LATCH.session timer                                                      19                3         34            3

STAT...redo synch time                                                 4042                0       8777            0

STAT...commit batch requested                                             0                0     100000       100000

STAT...commit wait/nowait requested                                  100000           100000     100000       100000

STAT...commit wait performed                                         100000                0     100000            0

LATCH.messages                                                       287070            23614     462948          447

STAT...Elapsed Time                                                    5543              965      10056          927

STAT...redo synch writes                                             100000                0     100000            0

STAT...commit batch/immediate requested                              100000           100000     100000       100000

STAT...commit immediate requested                                    100000           100000          0            0

LATCH.Consistent RBA                                                 100035            10447     100015           54

LATCH.cache buffers lru chain                                           652              600       4870         3282

LATCH.object queue header heap                                           93               16     216728          344

LATCH.redo allocation                                                587541           414896     504629       304247

LATCH.JS queue state obj latch                                          396               72        720           72

STAT...CPU used by this session                                        1493              956       1388          915

STAT...session pga memory max                                       4587520            65536          0            0

STAT...commit nowait requested                                            0           100000          0       100000

LATCH.client/application info                                             4                0          8            0

LATCH.OS process allocation                                              19                3         34            3

LATCH.active checkpoint queue latch                                      24                5      54186           89

LATCH.archive process latch                                              26                9         46            9

LATCH.post/wait queue                                                150161                5     150132            3

LATCH.parameter table allocation management                               2                0          4            0

LATCH.redo writing                                                   300168            31357     354299          258

El resultado de la consulta me arrojó mas de 700 estadisticas y latches. Solo copié
las filas donde noté diferencias importantes en los casos. En especial, la fila resaltada (Elapsed Time) muestra el tiempo total de procesamiento en cada caso en centisegundos. Como se observa para los casos donde se usa WAIT los tiempos son bastante mayores, lo cual comprueba la mejora en performance al usar asincronismo (NOWAIT).

Mientras escribia esta nota descubrí que en 11g el parametro commit_write esta "deprecated", parece que tuvo poca vida, no?. De todas formas la documentación aclara que fue reemplazado por los parametros COMMIT_WAIT y COMMIT_LOGGING.
Usando estos dos nuevos parametros en conjunto se obtendría el mismo funcionamiento que COMMIT_WRITE, aunque que el parametro COMMIT_WAIT agregó una nueva opción para forzar el wait (force_wait)

Variante para evitar la creación de sinonimos locales en usuarios de aplicación

2009-10-22T05:56:00.000-07:00

Es una práctica recomendada y en general impuesta por el area de seguridad de las empresas, crear un esquema de datos (owner) y un esquema de acceso (usuario de explotación o usuario de aplicación) cuando se realiza el setup o deploy de una nueva aplicación o modulo en un base de datos. Este esquema imposibilita a los usuarios (excepto obviamente al los usuarios administradores o dba) realizar cambios sobre los objetos del esquema propietario. Las pautas mas importantes a seguir en dicho esquema de layout son:

Los permisos sobre los objetos del owner se deben otorgar por medio de roles hacia los usuarios de explotación (nunca en forma directa).
Los usuarios de explotación solo deben tener otorgado el rol de conexión y el rol de definido para la aplicación. No tendrán quota en ningun tablespace ni podrán crear ningun objeto.
Los objetos otorgados desde el owner hacia el usuario de explotacion deberan ser referenciados mediante un sinonimo local en el usuario de explotación (no referenciar ningun objeto anteponiendo el esquema owner).
Los esquema owner y de explotación y los roles deberán ser creados por el area de base de datos. En el deploy de las app se deberá otorgar los grants al rol y crear los sinonimos locales.

Una variante interesante para evitar la gestión de sinonimos locales es alterar la sesion (podría ser mediante un on-logon trigger) de forma tal de cambiar el esquema corriente (de explotación a owner) y asi no tener la necesidad de calificar el objeto anteponiendo el esquema. Les voy a mostrar como implementar esta variante:

Lo primero es crear un on logon trigger. En el ejemplo mi usuario de explotación de llama exp_rop


create or replace trigger SYSTEM.onLogon_trg
after logon on database
Begin                         
  if ( user in ('EXP_ROP')) then
      execute immediate 'alter session set current_schema=ROP';
  end if;
end;

Creo un esquema de explotación y el rol de aplicación (rol_rop). El objeto usado para la prueba
es la tabla ROP.T.


rop@DESA10G> create user exp_rop identified by exp_rop;

Usuario creado.

rop@DESA10G> grant create session to exp_rop;

Concesión terminada correctamente.

rop@DESA10G> desc t
Nombre                                                ¿Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
C1                                                             VARCHAR2(40)
N1                                                             NUMBER
N2                                                             NUMBER

rop@DESA10G> create role rol_rop;

Rol creado.

rop@DESA10G> grant select on t to rol_rop;

Concesión terminada correctamente.

rop@DESA10G> grant rol_rop to exp_rop;

Concesión terminada correctamente.

Ahora pruebo de conectarme con el usuario de explotación para ver si se ejecuta el trigger y se altera la sesión:


rop@DESA10G> conn exp_rop/exp_rop@movi10d
Conectado.
exp_rop@DESA10G> desc t
Nombre                                                ¿Nulo?   Tipo
----------------------------------------------------- -------- ------------------------------------
C1                                                             VARCHAR2(40)
N1                                                             NUMBER
N2                                                             NUMBER

exp_rop@DESA10G> select count(1) from t;

COUNT(1)
----------
    5000

exp_rop@DESA10G>

Como se vió pude acceder la tabla T que esta en el esquema ROP sin la necesidad de utilizar un sinonimo local.

Reporte de Tablas sin estadisticas o con estadisticas viejas (stale) para una sesion dada

2009-09-24T10:59:00.000-07:00

Una gran parte de los problemas repentinos de performance se da porque el optimizador arma un plan ineficiente producto de que las estadisticas actuales de las tablas, particiones o subparticiones involucradas no reflejan la realidad. Esto se debe a que los segmentos sufrieron un cambio de datos mayor al 10% y no se actualizaron las estadisticas en el catalogo. Uno de los datos con el que comenzamos a analizar este tipo de problema es el sid asociado a la sesion que esta ejecutando con demoras. Teniendo el sid el siguiente paso es ver la sentencia en ejecución y chequear si los segmentos referenciados cuentan con estadisticas frescas. Si la sentencia en cuestion es compleja y referencia varios segmentos nos demorará un tiempo revisar cada uno de los segmentos. Por tal motivo pensé en armar un query que basado principalmente en la vista dinamica v$sql_plan, obtiene los segmentos usados en los paths del plan de ejecucion y luego verifica si estan STALE o si estan nulas usando la vista dba_tab_statistics.
El script de abajo permite determinar automaticamente que tablas, particiones y subparticiones tienen estadisticas desactualizadas para un sid determinado.


set line 120
set pagesize 999
set verify off

col owner format a15
col table_name format a30
col partition_name format a30
col subpartition_name format a30

PROMPT
PROMPT "---------------------------------------------"
PROMPT "Reporte de Tablas con estadisticas STALE     " 
PROMPT "o nulas para una sesion dada                 "
PROMPT "---------------------------------------------"
ACCEPT sid PROMPT "Ingrese SID a evaluar: "

select st.owner owner,
    st.table_name table_name,
    st.partition_name partition_name,
    st.subpartition_name subpartition_name
from v$session s,
  v$sql_plan p,
  dba_tab_statistics st
where s.sql_id = p.sql_id
and p.object_owner = st.owner
and p.object_name = st.table_name
and s.sid = &sid
and nvl(st.stale_stats,'YES') = 'YES'
and ((nvl(st.partition_position,1)
  between
 (case when (REGEXP_LIKE(nvl(p.partition_start,'a'),'[^[:digit:]]'))
  then 1
  else to_number(p.partition_start) end)
  and  (case when
(REGEXP_LIKE(nvl(p.partition_stop,'a'),'[^[:digit:]]'))
       then 10000
       else to_number(p.partition_stop) end))
    or
    (nvl(st.subpartition_position,1) between
  (case when (REGEXP_LIKE(nvl(p.partition_start,'a'),'[^[:digit:]]'))
       then 1
       else to_number(p.partition_start) end)
       and  (case when
(REGEXP_LIKE(nvl(p.partition_stop,'a'),'[^[:digit:]]')) then 10000
       else to_number(p.partition_stop) end))
   )
/

set verify on  


IMPORTANTE: Para asegurar que esten impactados los cambios mas recientes en la
vista dba_tab_statistics es recomendable flushear la memoria de la
siguiente forma: dbms_stats.flush_database_monitoring_info.

Reporte historico de tiempo de ejecucion máxima, mínima y promedio de sentencias SQL

2009-09-23T08:47:00.001-07:00

Cualquier DBA que haya trabajado un tiempo administrando bases de datos de producción, seguramente fue consultado, y a veces acusado, debido a demoras en los procesos o reportes. Ante ese tipo de cuestionamientos, lo primero que tenemos que asegurar es si realmente el proceso esta demorado o si se trata de la percepción o ansiedad del usuario u operador. La unica manera de saber eso, es analizando la historia de ejecución de las sentencias involucradas en las rutinas afectadas. Como es sabido, desde 10g contamos con un completo repositorio que se actualiza automaticamente, que entre otras estadisticas y metricas posee información sobre las sentencias ejecutadas. La vista DBA_HIST_SQLSTAT recolecta para cada sentencia, el tiempo de ejecucion general, el tiempo en cpu, el tiempo en i/o, cantidad de ejecuciones, etc. Utilizando dicha información armé un query que muestra la ejecucion mas larga, la ejecucion mas corta y un promedio para cada sentencia registrada.
El query de abajo realiza las agregaciones (max,min y avg) para todas las sentencias ejecutadas durante toda la historia almacenada en AWR (por default 7 dias). Tambien se podria reescribir levemente la query para que dado un sql_id retorne los resultados particulares, recordemos que sql_id es la identificación unica desde 10g para las sentencias sql (antes se usaba hash_value para identificar univocamente una sentencia).


select sql_id,
       to_char(trunc(max(max_ela_time)/60/60),'09')||
       to_char(trunc(mod(max(max_ela_time),3600)/60),'09')||
       to_char(mod(mod(max(max_ela_time),3600),60),'09') max_ela_time,
       to_char(max(max_ela_time_dt),'DD/MM/YYYY HH24:MI') max_ela_time_dt, 
       to_char(trunc(min(min_ela_time)/60/60),'09')||
       to_char(trunc(mod(min(min_ela_time),3600)/60),'09')||
       to_char(mod(mod(min(min_ela_time),3600),60),'09') min_ela_time,
       to_char(min(min_ela_time_dt),'DD/MM/YYYY HH24:MI') min_ela_time_dt, 
       to_char(trunc(avg(avg_ela_time)/60/60),'09')||
       to_char(trunc(mod(avg(avg_ela_time),3600)/60),'09')||
       to_char(mod(mod(avg(avg_ela_time),3600),60),'09') avg_ela_time
from
(select unique sql_id,
       round((first_value(elapsed_time) 
        over (partition by sql_id order by elapsed_time desc))/executions/1000000) max_ela_time,
       first_value(dt) over (partition by sql_id order by elapsed_time desc) max_ela_time_dt,
       round((first_value(elapsed_time) 
        over (partition by sql_id order by elapsed_time))/executions/1000000) min_ela_time,
       first_value(dt) 
        over (partition by sql_id order by elapsed_time) min_ela_time_dt,
       round((avg(elapsed_time) over (partition by sql_id))/executions/1000000) avg_ela_time
from (select unique 
          ss.sql_id,
          s.snap_id,
          lag (s.snap_id) over (partition by s.startup_time,ss.sql_id order by ss.snap_id desc) snap_id_n,
          ss.elapsed_time_total elapsed_time,
          s.begin_interval_time dt,
          lag (ss.elapsed_time_total) 
            over (partition by s.startup_time,ss.sql_id order by s.snap_id desc ) elapsed_time_n,
          lag (s.begin_interval_time) 
            over (partition by s.startup_time,ss.sql_id order by s.snap_id desc ) dt_n,
          executions_total executions,
          lag (ss.executions_total) 
            over (partition by s.startup_time,ss.sql_id order by s.snap_id desc ) executions_n
      from dba_hist_sqlstat ss,
           dba_hist_snapshot s
      where s.snap_id = ss.snap_id)
where elapsed_time > elapsed_time_n
  and executions != 0)
group by sql_id
order by avg_ela_time desc
/

SQL_ID        MAX_ELA_T MAX_ELA_TIME_DT  MIN_ELA_T MIN_ELA_TIME_DT  AVG_ELA_T
------------- --------- ---------------- --------- ---------------- ---------
89qyn4bbt03jq  00 05 24 22/09/2009 18:00  00 00 56 22/09/2009 06:00  00 03 06
gfjvxb25b773h  00 00 13 22/09/2009 17:48  00 00 13 22/09/2009 17:48  00 00 13
a1axyycsv1fb1  00 00 06 21/09/2009 18:00  00 00 06 21/09/2009 18:00  00 00 06
fqmpmkfr6pqyk  00 00 05 21/09/2009 12:00  00 00 05 21/09/2009 12:00  00 00 05
b7jn4mf49n569  00 00 05 21/09/2009 20:00  00 00 05 21/09/2009 20:00  00 00 05
4c1xvq9ufwcjc  00 00 03 22/09/2009 17:48  00 00 03 22/09/2009 17:48  00 00 03
06fhnfwzpzvug  00 00 03 22/09/2009 17:48  00 00 03 22/09/2009 17:48  00 00 03
ahtrk133zdqa5  00 00 02 21/09/2009 22:00  00 00 02 21/09/2009 22:00  00 00 02
bunssq950snhf  00 00 02 21/09/2009 19:00  00 00 02 21/09/2009 19:00  00 00 02
d92h3rjp0y217  00 00 01 23/09/2009 03:00  00 00 00 21/09/2009 19:00  00 00 01
abtp0uqvdb1d3  00 00 02 23/09/2009 08:00  00 00 00 22/09/2009 06:00  00 00 01
8bfst16kjukv6  00 00 01 22/09/2009 17:48  00 00 01 22/09/2009 17:48  00 00 01
6jgrbypm756nu  00 00 01 21/09/2009 11:00  00 00 01 21/09/2009 11:00  00 00 01

Como minimizar problemas luego de un upgrade de versión de Oracle (Caso2: Cambio del orden de evaluación de predicados)

2009-09-16T04:42:00.000-07:00

En esta nueva nota les voy a contar un problema con el que se pueden encontrar al migrar desde version 8i hacia 9i o superior. El principal tema radica en la evolución constante que va teniendo el optimizador para estimar el costo de acceso a los datos de las sentencias. En la versión 7, donde apareció por primera vez el optimizador por costos, el costo se calculaba simplemente ponderando por la cantidad de requerimientos de lectura a disco. Esto, como es sabido, provocó un rechazo a cambiar de RBO a CBO bastante generalizado en su momento, dado que los planes de ejecución comenzaban a hacer cosas extrañas, causando importantes problemas de rendimiento generalizado. Por tal motivo, la mayoria de las compañias continuaron usando el optimizador por reglas, ya que les garantizaba que no se alteraran los planes y que no se destabilizaran las aplicaciones. El principal problema de usar solo los read request para generar el costo en Oracle 7 fue no considerar el caching que existe en distintos niveles.

A partir de 8i, se comenzó a ponderar por tipo de lectura (single reads y multiblock reads) y por tamaño y tiempo de lectura. Esto mejoró bastante la calidad de los planes generados y dió mayor confianza a las empresas para animarse a cambiar a CBO, mas que nada porque Oracle Corporation comenzó a incentivar fuertemente a salir de RBO, discontinuado a partir de 7.x (año 1992) y desoportado desde 10g. Si bien el comportamiento en 8i fue mucho mas estable faltaba tomar en cuenta algo muy importante, el tiempo de cpu.

Recien a partir de 9i se incluyó en el calculo del costo el tiempo insumido en procesamiento de cpu, anteriormente la formula para calcular solo tomaba en cuenta caracteristicas de i/o. En consecuencia con esta nueva variable de ponderación el optimizador puede cambiar el orden de evaluación de los predicados si estima que con ese nuevo orden se minimiza el tiempo de cpu. Este reordenamiento puede causar que ciertas consultas fallen en 9i+ y no en 8i. Este fallo generalmente se debe a una inconsistencia de datos, que antes quedaba "tapada" y que ahora al evaluar en otro orden genera un error, por ejemplo con una conversión implicita.

Vayamos a los ejemplos para graficar mejor este tema:

Primero voy a crear la siguiente tabla:


rop@DESA10G> create table t as
 2  select to_char(mod(rownum,30)) c1,
 3         rownum n1,
 4         mod(rownum,30) n2
 5  from dual
 6  connect by rownum <= 5000;  Tabla creada.

La tabla T tiene 3 columnas:
C1: solo tendrá valores 30 posibles valores (de 0 a 29) y es de tipo varchar2.
N1: tiene valores distintos del 1 al 5000.
N2: tiene los mismos valores de C1 pero es de tipo number.

Para simular estar en 8i, voy a cambiar el parametro de session "optimizer_features_enable" para que el optimizador se comporte con un optimizador de 8i.


rop@DESA10G> alter session set optimizer_features_enable = '8.1.7';

Sesión modificada.

Ahora, ejecuto una sentencia que cuenta la cantidad de filas filtrando la tabla
por los tres campos.


rop@DESA10G> ed
Escrito file afiedt.buf

 1  explain plan for
 2  select count(1) from t
 3* where c1 = 1 and n1 = 1111 and n2 = 1
rop@DESA10G> /

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

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

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

  2 - filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)

Note
-----
  - cpu costing is off (consider enabling it)

18 filas seleccionadas.

Mirando la información predicados (Predicate Information) notamos dos cosas interesantes. Primero hay una nota que nos advierte que el costo por cpu esta en off. Lo cual es lógico, por lo que comenté mas arriba respecto a que 8i no ponderaba por cpu y si bien estamos en 10g, recordemos que cambié el comportamiento del optimizador a 8i. Segundo vemos que los predicados se evaluaron en orden y que hubo una conversión implicita (TO_NUMBER()).

Vuelvo a poner el optimizador a su valor default y me fijo evaluo el plan:


rop@DESA10G> alter session set optimizer_features_enable = '10.2.0.4';

Sesión modificada.

rop@DESA10G> explain plan for
 2  select count(1) from t
 3  where c1 = 1 and n1 = 1111 and n2 = 1;

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    48 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    48 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    48 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

  2 - filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)

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

18 filas seleccionadas.

El plan no cambió, se hizo sampleo dinamico ya que no habia recolectado estadisticas, pero lo mas importante a destacar es el cambio en la evaluación de los filtros. Notemos que ahora la conversión implicita se dejó para lo ultimo. Por que hizo eso el optimizador?, bien, pensemos que una conversión implica ciclos de cpu y entonces, porque no mejor evaluarlo al final cuando seguramente queden menos filas, ya que se van filtrando con los dos predicados o filtros anteriores, y asi minimizar la cantidad de conversiones, no?. Como ya dijimos el optimizador en versiones 9i+ se preocupa por el costo de procesamiento de cpu y por lo tanto puede realizar ciertos ajustes (reordenamiento de predicados, merge de subqueries, etc) si con eso se reduce la utilización de cpu.

Analicemos mas en detalle el ejemplo y como se evalua con cpu costing en ON y en OFF

Con CPU Costing en OFF (8i)

filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)

El primer predicado (TO_NUMBER("C1")=1) evalua 5000 filas y como resultado de ese filtro se queda con 167 filas.
El segundo predicado (N1=1111) evalua 167 filas y se queda con 1.
El tercer predicado (N2=1) evalua 1 y devuelve 1 (el resultado del count()).

Con CPU Costing en ON (9i+)

filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)

El primer predicado (N1=1111) evalua 5000 filas y solo devuelve 1 fila.
El segundo predicado (N2=1) compara la fila y se la pasa al siguiente predicado.
El tercer predicado (TO_NUMBER("C1")=1) evalua una sola fila y devuelve el resultado.

En base al ejemplo analizado se ve claramente que con cpu costing apagado se deben realizar 5000 operaciones implicitas contra 1 operacion implicita cuando se tiene activado el costeo por cpu. Con este ejemplo sencillo, pero representativo, se puede ver lo importante del ordenamiento de la evaluación para minimizar el uso de recurso de cpu y por ende mejorar el tiempo de respuesta general.

Ahora veamos como fijar el orden de evaluación sin cambiar el comportamiento general de la sesion usando el hint "ordered_predicates":


rop@DESA10G> ed
Escrito file afiedt.buf

 1  explain plan for
 2  select /*+ ordered_predicates */ count(1) from t
 3* where c1 = 1 and n1 = 1111 and n2 = 1
rop@DESA10G> /

Explicado.

rop@DESA10G>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    48 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    48 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    48 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

  2 - filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)

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

18 filas seleccionadas.

Con el hint la evaluación es ordenada, tal cual hubiese sido en 8i por default.
En el plan no se muestró el costo de cpu, pero consultando la tabla de soporte PLAN_TABLE podemos ver que costos fueron asignados de acuerdo al orden de evaluación.


rop@DESA10G> ed
Escrito file afiedt.buf

 1  select filter_predicates,cpu_cost from plan_table
 2* where filter_predicates is not null
rop@DESA10G> /

FILTER_PREDICATES                                    CPU_COST
-------------------------------------------------- ----------
"N1"=1000 AND "N2"=1 AND TO_NUMBER("C1")=1           1302275
TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1           1802225
rop@DESA10G>

El costo de cpu al evaluar en 10g fue de 1302275 (con reordenamiento) y el costo de cpu sin ordenamiento fue de 1802225 (8i)

Como demostré con el ejemplo, el optimizador evalua los predicados de forma tal de optimizar el uso de cpu. Este cambio podría hacer fallar ciertos codigos que antes funcionaban y que tenian inconsistencias de datos, como por ejemplo, tener valores no numericos sobre campos varchar que deben tener numeros, y entonces al convertir implicitamente se genere un error de invalid number (ORA-01722). Queda claro que si ocurre esto es debido a un mal diseño, ya que una columna que aloja solo números no debería ser de tipo varchar, ya que de esta forma se promueve, por un lado inconsistencias en los datos y por otro lado, se afecta el rendimiento debido a las conversiones implicitas.

Como minimizar problemas luego de un upgrade de versión de Oracle (Caso1: Ordenamiento implicito de GROUP BY en versiones anteriores a 10g R2)

2009-09-10T06:13:00.000-07:00

Esta nota pretende ser la primera de una serie de notas en las que voy a comentarles problemas que se pueden dar luego de un upgrade de versión de base de datos. Este año estuve involucrado en unos cuantos upgrades de versiones, siempre hacia version 10g R2 pero partiendo desde distintas versiones base (8i, 9i R2 y 10g R1). Aqui en Argentina todavia son muy pocos los que migraron a 11g ya que es una practica habitual, y en mi opinión muy acertada, esperar al segundo release para upgradear. Al momento de esta nota acaba de salir el 11g R2, pero solo para linux, asi que estimo que en poco tiempo tendremos disponible el nuevo release para las plataformas unix "grandes", tales como solaris, aix, hp-ux y tambien para la familia de SO's de windows.

Es sabido que cada nueva versión de Oracle introduce nuevos features, y en especial features que cambian el comportamiento del optimizador y que producen cambios en los paths de los planes de ejecución. Los cambios en el optimizador son generalmente para mejorar el acceso a los datos y por ende reducir el tiempo de respuesta. Esta mejora en la "inteligencia" del optimizador no debería ocacionar cambios de comportamiento, a menos que no se cumplan las Buenas Practicas de confección de sentencias sql. Una mala práctica, y por desgracia bastante común, es confiar en el ordenamiento implicito que se da, por ejemplo, al usar distint/unique o en el ordenamiento que se produce con el group by. Este último es a veces innecesario y agrega un path implicito para ordenar que suma un tiempo mas antes de retornar la respuesta.

A partir de 10g R2 cambió el path SORT GROUP BY por el path HASH GROUP BY mejorando el rendimiento dado que no se infiere la necesidad de retornar el resultado ordenado. Todas las unidades de código que "confiaban" en este ordenamiento implicito y que necesitan por negocio un cierto orden van a comenzar a devolver resultados erroneos al upgradear a 10g R2 o versión superior, recordemos que las "Best Practices" dictan usar siempre ORDER BY cuando debe haber un orden ya que el comportamiento no esta garantizado a futuro.

Ahora, como suelo hacer, les voy a mostrar el ejemplo del group by, en próximas notas le voy a mostrar otros "issues" que pueden causar fuertes dolores de cabeza cuando no se detectan a tiempo.

Voy a usar mi conocida, y nunca bien ponderada, tablita de ejemplo T



rop@TEST10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


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

Tabla creada.

rop@TEST10G> exec dbms_stats.gather_table_Stats(user,'T');

Procedimiento PL/SQL terminado correctamente.


rop@TEST10G> select object_type,count(1)
2         from t
3         group by object_type

OBJECT_TYPE           COUNT(1)
------------------- ----------
INDEX                    22538
JOB CLASS                    2
CONTEXT                      5
TABLE SUBPARTITION          18
TYPE BODY                  174
INDEXTYPE                   10
PROCEDURE                  252
RESOURCE PLAN                4
RULE                         4
JAVA CLASS               16417
SCHEDULE                     1
TABLE PARTITION            625
WINDOW                       2
WINDOW GROUP                 1
JAVA RESOURCE              770
TABLE                    22631
TYPE                      1941
VIEW                      3804
LIBRARY                    150
FUNCTION                   329
TRIGGER                    565
PROGRAM                     12
MATERIALIZED VIEW            3
DATABASE LINK                5
CLUSTER                     10
SYNONYM                  23307
PACKAGE BODY               807
QUEUE                       27
CONSUMER GROUP               6
EVALUATION CONTEXT          14
RULE SET                    19
DIRECTORY                   16
UNDEFINED                    6
OPERATOR                    57
JAVA DATA                  306
DIMENSION                    5
SEQUENCE                  2759
LOB                        713
PACKAGE                    866
JOB                         18
INDEX PARTITION            617
LOB PARTITION                1
XML SCHEMA                  26

43 filas seleccionadas.

El listado salio desordenado, veamos el plan que genera:


rop@TEST10G>  explain plan for
2   select object_type,count(1)
3   from t
4   group by object_type;

Explicado.

rop@TEST10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2963600285

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    26 |   208 |   339   (7)| 00:00:05 |
|   1 |  HASH GROUP BY     |      |    26 |   208 |   339   (7)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| T    | 99843 |   780K|   324   (2)| 00:00:04 |
---------------------------------------------------------------------------

9 filas seleccionadas.

El path es HASH GROUP BY en reemplazo de SORT HASH GROUP

Ahora, voy a usar, el truco mas rápido para salir del paso, tipo de solución "quick and dirty", pero solución al fin, que me ha salvado varias veces cuando se pasó por alto algún nuevo mecanismo y se comienzan a ver los problemas en plena hora pico o cuando cancelan procesos baths al dia siguiente del upgrade. Podemos setear a nivel sesion el parámetro "optimizer_features_enable", tambien se puede setear a nivel de hint con opt_param(param,valor), para hacer un flashback al comportamiento de un versión anterior. Como el caso de esta nota se da a partir de 10g R2, como estrategia siempre tomo la decisión de ir al upgrade anterior mas próximo donde funciona como antes, para asi estabilizar el comportamiento y no tener que retornar al release original. Como dije antes, en este caso la solución definitiva sera disparar un requerimiento de cambio de código y que el sector de desarrollo agregue el order by en las sentencias que necesitan del ordenamiento para funcionar correctamente.


rop@TEST10G> alter session set optimizer_features_enable = '10.1.0.5';

Sesión modificada.


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

OBJECT_TYPE           COUNT(1)
------------------- ----------
CLUSTER                     10
CONSUMER GROUP               6
CONTEXT                      5
DATABASE LINK                5
DIMENSION                    5
DIRECTORY                   16
EVALUATION CONTEXT          14
FUNCTION                   329
INDEX                    22538
INDEX PARTITION            617
INDEXTYPE                   10
JAVA CLASS               16417
JAVA DATA                  306
JAVA RESOURCE              770
JOB                         18
JOB CLASS                    2
LIBRARY                    150
LOB                        713
LOB PARTITION                1
MATERIALIZED VIEW            3
OPERATOR                    57
PACKAGE                    866
PACKAGE BODY               807
PROCEDURE                  252
PROGRAM                     12
QUEUE                       27
RESOURCE PLAN                4
RULE                         4
RULE SET                    19
SCHEDULE                     1
SEQUENCE                  2759
SYNONYM                  23307
TABLE                    22631
TABLE PARTITION            625
TABLE SUBPARTITION          18
TRIGGER                    565
TYPE                      1941
TYPE BODY                  174
UNDEFINED                    6
VIEW                      3804
WINDOW                       2
WINDOW GROUP                 1
XML SCHEMA                  26

43 filas seleccionadas.

Cambié la version del optimizador y el resultado fue el esperado, salió ordenado. Miremos el nuevo plan:


rop@TEST10G>  explain plan for
2  select object_type,count(1)
3   from t
4   group by object_type;

Explicado.

rop@TEST10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3156910365

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    26 |   208 |   337   (6)| 00:00:05 |
|   1 |  SORT GROUP BY     |      |    26 |   208 |   337   (6)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| T    | 99843 |   780K|   322   (2)| 00:00:04 |
---------------------------------------------------------------------------

9 filas seleccionadas.

Ahora uso en antiguo path SORT GROUP BY y en consecuencia la salida no altero el orden
Por ultimo, agregamos el order by y vemos como se agrega (ahora explicitamente) un path para ordenar el resultado antes de retornarlo.


rop@TEST10G> ed
Escrito file afiedt.buf

1   explain plan for
2  select object_type,count(1)
3   from t
4   group by object_type
5*  order by object_type
rop@TEST10G> /

Explicado.

rop@TEST10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3861070257

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    26 |   208 |   354  (11)| 00:00:05 |
|   1 |  SORT ORDER BY      |      |    26 |   208 |   354  (11)| 00:00:05 |
|   2 |   HASH GROUP BY     |      |    26 |   208 |   354  (11)| 00:00:05 |
|   3 |    TABLE ACCESS FULL| T    | 99843 |   780K|   324   (2)| 00:00:04 |
----------------------------------------------------------------------------

Este es un caso interesante de cambio de comportamiento que saca a la luz problemas de mala programación, que en versiones anteriores pasaban desapercibidas. Por tal motivo es muy importante tener código de calidad ,y que no solo funcione, para evitar sorpresas a futuro. En la próxima nota les voy a mostrar otro caso de cambio interesante, relacionado con cambios en el orden de evaluación de predicados.

Instalación, Configuración y uso del paquete DBMS_PROFILER

2009-08-21T12:27:00.000-07:00

En esta nota les voy a mostrar un poco acerca del paquete DBMS_PROFILER, que no es tan conocido, pero si se usa correctamente, puede ayudar a detectar en forma precisa en que lineas de código se encuentran las mayores demoras y asi focalizar la tarea de mejora de rendimiento de una aplicación o bloque de código PL/SQL.
El paquete DBMS_PROFILER fue introducido en 8i y permite al equipo de desarrollo obtener detalle y comportamiento del código PL/SQL. No existe por default y hay que correr dos scripts para instalarlo. Por medio de esta herramienta los desarrolladores podrán obtener con granularidad de línea de código el detalle de ejecuciones, tiempo total, tiempo máximo, tiempo mínimo, etc.

Instalación y Configuración

La instalación consta de 2 pasos: La etapa 1 debe ser ejecutada por un DBA conectado como sys. Esta etapa crea el paquete. La etapa 2 no requiere privilegios especiales y puede ser ejecutada por el dueño (owner) en donde esta definido el modulo de código a instrumentar.

Paso 1

Este paso crea el paquete DBMS_PROFILER en y realiza los grants y creación de sinónimo para poder ser utilizado desde cualquier usuario.


CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/profload.sql

Paso 2

Este paso crea las 3 tablas y la secuencia necesarias para persistir la información recolectada por el DBMS_PROFILER.


CONNECT profiler/profiler@service
@$ORACLE_HOME/rdbms/admin/proftab.sql
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Como Usar DBMS_PROFILER

Para activar el profiler se debe encerrar la porción de código a evaluar por las siguientes sentencias:


Begin
….
dbms_profiler.start_profiler();
…
dbms_profiler.stop_profiler;
…
End;

Una vez ejecutado el modulo a analizar se deberá consultar en las tablas PLSQL_xxx para obtener los resultados.

Ahora les voy a mostrar su uso con un ejemplo sencillo:

Creamos un procedimiento sencillo que recorre un cursor implícito y va contando las iteraciones.


create or replace procedure foo
is
l_cnt int := 0;
begin
dbms_profiler.start_profiler('Test');  -- Comienzo de traza
for i in (select * from all_objects)
loop
   l_cnt := l_cnt+1;
end loop;
dbms_profiler.stop_profiler;           -- Fin de traza
end;

Luego de ejecutarlo, verificamos los resultados con la siguiente consulta:


SELECT u.runid,
     u.unit_type,
     u.unit_name,
     d.line#,
     s.text,
     d.total_occur,
     d.total_time,
     d.min_time,
     d.max_time
FROM   plsql_profiler_units u,
     plsql_profiler_data d,
     user_source s
WHERE  u.runid = d.runid AND
     u.unit_number = d.unit_number AND
     u.unit_name = s.name AND
     d.line# = s.line AND
     u.runid = 2
ORDER BY u.unit_number, d.line#

La unidad de medida es en nanosegundos (10 elevado a la -9), para pasar a segundos hay que dividir por 1000 millones.
En base a los resultados obtenidos se ve que demoró casi 3s en obtener los datos de las filas de la tabla (linea 6) y 0,03s en ejecutar la asignación (linea 8).

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

2009-08-14T11:53:00.000-07:00

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

Existe 4 categorias de WARNINGS:

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

PERFORMANCE : Pueden causar problemas de rendimiento

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

ALL : Contempla todos los casos anteriores.

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

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

Habilito para detectar todas las categorias:


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

Sesión modificada.

Creo una tabla sencila


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

Tabla creada.

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

100000 filas creadas.

rop@DESA10G> commit;

Confirmación terminada.

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


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

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

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

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

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


rop@DESA10G> ed
Escrito file afiedt.buf

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

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

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

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

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


rop@DESA10G> ed
Escrito file afiedt.buf

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

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

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

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

al no retornar valor se podria generar un problema mas grave


rop@DESA10G> ed
Escrito file afiedt.buf

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

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

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

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

rop@DESA10G>

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