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.

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

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)

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)

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)

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

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

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)

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)

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

1. Usar el Asistente Grafico (DBUA). Es el metodo sugerido en los manuales.
2. Usar scripting o forma manual (la que yo siempre tiendo a usar)
3. Export/Import
4. 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.