Como aplicar hints en sentencias sin necesidad de modificarlas desde la aplicación usando SQL-BASELINES (por Alejandro Aguirrez)

           Durante mi experiencia como DBA me he encontrado con incontables problemas de performace sobre sentencias que eran ejecutadas desde  aplicaciones donde el código de las sentencias no puede ser  modificado. 

           En versiones anteriores a 11g debias generar outlines y editarlas para que la sentencia elija el PATH optimo para resolver la consulta en el menor tiempo . En 11g disponemos de una nueva feature que nos permite manejar la estabilidad de los planes de ejecucion SQL BASELINES.

            En el siguiente documento se detallará el procedimiento para cargar una baseline generada a partir de la sentencia original (generada por la aplicación) con la adición de un hint/s que produce la obtención del plan optimo para esta sentencia .

Nomenclatura utilizada

ORIGINAL_SQL_ID = SQL_ID de la sentencia original sin HINTS a la cual le queremos fijar un plan optimo aplicando HINTS

1 - (Opcional si no fue realizado antes )

Capturar la sentencia de la sentencia original sin HINTS. El siguiente código solo cargara la baseline si la misma está actualmente en memoria.

BEGIN

 dbms_spm.load_plans_from_cursor_cache(sql_id => '&original_sql_id', plan_hash_value => &original_plan_hash_value );

END;

/

2 - Ejecutar el SQL con el HINT necesario para generar un plan de ejecución optimo

3 - Identificar el sql_id y plan de ejecuion  de la sentencia con HINTS de la vista V$SQL.

select * from table(dbms_xplan.display_cursor('&hinted_SQL_ID'));

4 - Verificar que la baseline generada en el paso 1 exista. Extraer el campo SQL_HANDLE

SELECT * FROM dba_sql_plan_baselines WHERE signature IN (SELECT force_matching_signature FROM dba_hist_sqlstat WHERE sql_id='&original_sql_id')

5 - Asociar el plan de ejecución de la sentencia con hints con la sentencia original

var res number

exec :res := dbms_spm.load_plans_from_cursor_cache( -

sql_id => '&hinted_SQL_ID', -

plan_hash_value => &hinted_plan_hash_value, -

sql_handle => '&sql_handle_for_original');

6 - Verificar que la baseline fue agregada

SELECT * FROM dba_sql_plan_baselines WHERE signature IN (SELECT force_matching_signature FROM dba_hist_sqlstat WHERE sql_id='&original_sql_id')

7 - Dropear o deshabilitar el plan de ejecución original de la sentencia sin hints

exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');

8 - (OPCIONAL) Sacar la sentencia de la shared pool solo en caso que siga tomando el plan original

DECLARE

  name varchar2(50);

  version varchar2(3);

BEGIN

  select regexp_replace(version,'\..*') into version from v$instance;

  if version = '10' then

    execute immediate

      q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport

  end if;

  select address||','||hash_value into name

  from v$sqlarea

  where sql_id like '&original_sql_id';

  sys.dbms_shared_pool.purge(name,'C',1);

END;

/

9 - Ejecutar la sentencia desde la aplicación

10 - verificar que tome la baseline

select  SQL_ID,SQL_PROFILE,SQL_PLAN_BASELINE,PLAN_HASH_VALUE,EXECUTIONS,ELAPSED_TIME,ELAPSED_TIME/1000000/EXECUTIONS,DISK_READS,OPTIMIZER_COST  from V$sql where sql_id='&original_sql_id';

11 - (OPCIONAL) Si se considera que el plan con hints es el plan mas optimo modificar el la baseline para que este FIXED (no seguira capturando baselines para esta sentencia particular, en oportunidades cierto tipo de sentencias capturan demasiados planes lo cual no es recomendado. Por esta razon en ocaciones he tenido que deshabilitar la captura de nuevos planes para el sql_handle asociados al sql_id original).

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => '&sql_handle_for_original'',

    plan_name       => '& hinted_plan_name'',

    attribute_name  => 'fixed',

    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

Referencias:

Loading Hinted Execution Plans into SQL Plan Baseline. (Doc ID 787692.1)

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

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

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

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

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

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

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

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

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

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

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

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

create table t as select * from dba_objects

select count(1) from t

begin

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

end;

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

NUM_ROWS : 88538

STALE_STATS: NO

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

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

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

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

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

no rows selected

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

begin

dbms_stats.flush_database_monitoring_info;

end;

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

INSERTS UPDATES DELETES

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

3000 3000 3000

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

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

NUM_ROWS : 88538

STALE_STATS: YES

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

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

begin

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

end;

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

update t set object_id = rownum

where rownum <= 3000

delete t

where rownum <= 3000

insert into t

select * from dba_objects

where rownum <= 3000

begin

dbms_stats.flush_database_monitoring_info;

end;

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

NUM_ROWS : 88538

STALE_STATS: NO

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

Row Prefetching en Oracle

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

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

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

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

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

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

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

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


SELECT *
FROM
T


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

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

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


SELECT *
FROM
T


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

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

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

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


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

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

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


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

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

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

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

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

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

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

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

Nueva funcionalidad para el SELECT FOR UPDATE (SKIP LOCKED)

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

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

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

Abajo les muestro un ejemplo:

Creo una tabla T y le inserto 100 registros:

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


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

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

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

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


select * from t where estado = 'P';         

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

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

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

Resultado: 1

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

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

Resultado: 27

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

Comparacion de Métodos y Tipos de joins en Oracle

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

1. Determinar el orden de evaluación de las tablas.
2. Determinar el método de join.
3. Determinar los tipos de accesos (access path, ej: full scan, rowid, index range, etc).
4. 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

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:

Como realizar update/delete masivos en forma efectiva

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.

Inserción Directa en Oracle (DIRECT INSERT)

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.

Optimizando la recolección estadística sobre tablas particionadas

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

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

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

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






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

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

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

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

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

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

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

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

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

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

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

Demoró casi 12 segundos.

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

FALSE

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

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

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

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

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

TRUE


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

Transcurrido: 00:00:04.71

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

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

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

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

(Ahora podes bajar la ultima versión del 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.