Manejo Automático de Memoria

Con la llegada de 10g, el parámetro de inicialización SGA_TARGET fue introducido. La forma en que oracle presentaba este parámetro era...

"Put away your scripts and let Oracle automatically resize your memory pools."

"Haz a un lado tus scripts, y permite que Oracle reconfigure de forma automática los pools de memoria".

Anteriormente, en versiones menores a 9.2.0.X, un cambio en la estructura de memoria iba acompañado de un bounce de la base de datos.

Ya en 9iR2 lo que solíamos hacer era sobredimensionar el SGA_MAX_SIZE, y aumentar las estructuras de memoria como fueran necesarias. En forma personal, siempre veo esto como mala práctica; quizás se justifica para una base de datos completamente nueva, en la cual se desconoce la carga que vas a tener, y que difícilmente se permitirá hacer un bounce.

Siempre he tenido mis dudas sobre el manejo automático de lo que sea en Oracle (Quedé sorprendido con el buen funcionamiento del UNDO_MANAGEMENT), e incluso recuerdo, que cuando tuve la oportunidad de preguntarle a Ken Jacobs (Dr. Dba) sobre el manejo automático de memoria, no se animó a decir que funcionaba a la perfección, simplemente dijo que detrás de toda esta tecnología, había mucha experiencia de DBAs, y que Oracle estaba haciendo su mejor esfuerzo para facilitar la administración.

De alguna forma estoy de acuerdo, pero es un tanto difícil lograr que las cosas funcionen bien. Doy algunos ejemplos claros.

Si se tiene una aplicación mal diseñada, que no usa bind variables, el sistema estará pidiendo a gritos aumentar el tamaño del shared_pool a costa del buffer_cache.

Otro ejemplo que me ha tocado vivir, es: cuando llega una compañía tipo QUEST o BMC Software ofreciendo productos que ayudan a hacer "Tuning Mágico" eligiendo "n" posibilidades de reescritura de una sentencia SQL. Cada uno de los "n" planes de ejecución se van contra el shared_pool, y de esta forma volvemos a pegarle a los tamaños de las estructuras.

Una carga masiva de información, o recreación de objetos, pudiera incrementar el tamaño del buffer_cache y disminuir alguna otra estructura, etc...

Recientemente en un ambiente productivo, se reportó una lentitud generalizada del sistema, generamos un reporte de AWR en un lapso de una hora y nos encontramos con lo siguiente:

Top 5 Timed Events                                     
~~~~~~~~~~~~~~~~~~                                     
Event                                 Waits    Time (s)
------------------------------ ------------ -----------
CPU time                                        103,907
cursor: pin S wait on X           1,881,946      35,170
...

El problema generalizado de la base de datos era relacionado al CPU. A nivel sistema operativo, "sar" nos mostraba lo siguiente:

prod$ sar 1 10

%usr    %sys    %wio   %idle
  92       8       0       0
  95       5       0       0
  96       4       0       0
  96       4       0       0
  97       3       0       0
  92       8       0       0
  92       8       0       0
  94       6       0       0
  93       7       0       0
  96       4       0       0

  94       6       0       0

La cantidad de uso de CPU era muy alta, y a nivel base de datos las esperas más altas eran "cursor: pin S wait on X", ¿pero exáctamente a qué se debía?

Lo primero que se revisó fue Metalink, y se encontró la nota 731233.1 Revisando las operaciones de Resize que se estaban ejecutando, se observó que cada 30 segundos había operaciones similares a las siguientes:

 SELECT component,
  oper_type      ,
  final_size     ,
  TO_CHAR(start_time ,'mm/dd/yyyy') started
   FROM v$sga_resize_ops
  WHERE status ='COMPLETE'
ORDER BY started DESC,
  component;



COMPONENT             OPER_TYPE     FINAL_SIZE   STARTED    
--------------------- ------------- ------------ -----------
DEFAULT buffer cache  SHRINK        7482638336   11/18/2008 
shared pool           GROW          3070230528   11/18/2008 
DEFAULT buffer cache  GROW          7616856064   11/18/2008 
shared pool           SHRINK        2936012800   11/18/2008 
DEFAULT buffer cache  SHRINK        7482638336   11/18/2008 
shared pool           GROW          3070230528   11/18/2008 
DEFAULT buffer cache  GROW          7616856064   11/18/2008 
shared pool           SHRINK        2936012800   11/18/2008 
DEFAULT buffer cache  SHRINK        7482638336   11/18/2008 
shared pool           GROW          3070230528   11/18/2008 
DEFAULT buffer cache  GROW          7616856064   11/18/2008 
shared pool           SHRINK        2936012800   11/18/2008 
DEFAULT buffer cache  GROW          7482638336   11/18/2008 
shared pool           SHRINK        3070230528   11/18/2008 
DEFAULT buffer cache  SHRINK        7331643392   11/18/2008 
shared pool           GROW          3221225472   11/18/2008 
DEFAULT buffer cache  SHRINK        7482638336   11/18/2008 
shared pool           GROW          3070230528   11/18/2008 
DEFAULT buffer cache  GROW          7616856064   11/18/2008 
shared pool           SHRINK        2936012800   11/18/2008 

 

Así que ¿qué se puede hacer?, creo que 3 cosas.

1) Deshabilitar por completo el Automatic Memory Management (creo yo que es la mejor opción) SGA_TARGET=0.

2) Dejar valores por default más elevados, es decir, si sabemos que como mínimo requerimos el shared_pool de 900m, incluir en el archivo de inicialización shared_pool=900m

3) Aparentemente aplicar un par de parches (7189722 y 6528336) para evitar los crecimientos y decrementos de estructuras tan frecuentemente (no lo he probado).

El problema con el cliente, en esta ocasión se debió a un mantenimiento programado, la instancia usa un init y no un spfile, dentro del init, el shared_pool estaba definido muy pequeño, el db_cahce_size y otros pools no estaban definidos, y el sga_target en mas de 10GB.

Ahora, debido a que no suelo usar manejo automático de memoria, quizás lo que diga a continuación pudiera no ser válido (simplemente así creo que funcionaría).

Cuando uno tiene un spfile, y manejo automático de memoria, Oracle usa parámetros como:

*.__db_cache_size=58720256
*.__java_pool_size=8388608
*.__large_pool_size=4194304
*.__shared_pool_size=159383552
*.__streams_pool_size=50331648

Estos se pueden ver cuando creamos un pfile a partir de un spfile. Si se reiniciara oracle, por lo menos se tiene un registro de cómo se comportan de forma "optima" las estructuras de memoria en la instancia. Usando los valores con "__" como valores iniciales, y si existen valores predefinidos sin "__" se usan como cota inferior, o valor mínimo.

Debido a que se usa un archivo de init en esta instancia, al hacer el bounce de la base de datos, las estructuras vuelven a su tamaño original (no hay parámetros "__" en el init), y esto nos lleva a una gran cantidad de cambios en las estructuras de memoria.

Cuando el problema se presentó, el shared_pool estaba creciendo cerca de los 3gb, y el db_cache_size muy cerca de los 7gb.

Este tipo de problemas me ha tocado verlos sólo en instancias muy concurridas y con SGAs muy grandes. De manera personal creo que el activar el manejo automático no siempre es la mejor opción, con los advisors activados, podemos darnos cuenta de qué valores son los óptimos, y programar los cambios en estructuras durante periodos de mantenimiento.

Cómo evitar un Hard Parse pesado

Este último mes y medio he estado muy metido resolviendo problemas de desempeño con algunos clientes, y aquí expongo un caso con el cuál nunca me había enfrentado (o por lo menos eso creo)y pudiera ser de utilidad para alguien.

Dentro de un E-Business suite, un usuario se quejaba sobre el desempeño de una forma de Order Entry que al hacer un drilldown de GL a XLAIQDRL,
el sistema no respondía en mucho tiempo.

Lo que logramos identificar en este problema, fue un acceso muy pesado a la tabla MTL_TRANSACTION_ACCOUNTS.

Como primer intento de solución, encontramos una nota en metalink que pudiera ayudar a resolver el problema, la cual indica aplicar un
parche junto con la creación de un índice.

CREATE INDEX "INV"."MTL_TRANSACTION_ACCOUNTS_N79" ON 
             "INV"."MTL_TRANSACTION_ACCOUNTS" ("GL_BATCH_ID", "REFERENCE_ACCOUNT", "TRANSACTION_DATE") 
COMPUTE STATISTICS 
TABLESPACE "APPS_TS_TX_IDX";

Al hacer los cambios sugeridos, y al revisar el proceso nuevamente, nos encontramos con las siguientes estadísticas en el trace

call     count       cpu    elapsed       disk      query 
------- ------  -------- ---------- ---------- ---------- 
Parse        1      2.86       2.78          0          0 
Execute      1     28.87      28.43          0          0 
Fetch        1    114.17     111.90        120     756369 
------- ------  -------- ---------- ---------- ---------- 
total        3    145.90     143.12        120     756369 


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

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY (cr=756369 pr=120 pw=0 time=111904779 us)
  11176   UNION-ALL  (cr=756369 pr=120 pw=0 time=22934845 us)
      0    FILTER  (cr=0 pr=0 pw=0 time=7 us)

La cantidad de bloques leídos de memoria es mucha, pero quizás por el tipo de accesos que hace, pudiera considerarse normal, el plan de ejecución
consta de más de 590 líneas.

Al hacer un cambio en el índice de orden de columnas

CREATE INDEX "INV"."MTL_TRANSACTION_ACCOUNTS_N79" ON 
             "INV"."MTL_TRANSACTION_ACCOUNTS" ("GL_BATCH_ID", "REFERENCE_ACCOUNT", "TRANSACTION_DATE") 
COMPUTE STATISTICS 
TABLESPACE "APPS_TS_TX_IDX";

se logró lo siguiente:

call     count       cpu    elapsed       disk      query 
------- ------  -------- ---------- ---------- ---------- 
Parse        1      3.31       3.23          0          0 
Execute      1     34.25      33.44          0          0 
Fetch        1     27.81      27.15          0     331474 
------- ------  -------- ---------- ---------- ---------- 
total        3     65.37      63.83          0     331474 

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

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY (cr=331474 pr=0 pw=0 time=27155623 us)
  11176   UNION-ALL  (cr=331474 pr=0 pw=0 time=22074357 us)
      0    FILTER  (cr=0 pr=0 pw=0 time=8 us)
      0     FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)

Como se puede ver, el cambio del orden de las columnas, hizo que se leyeran menos de la mitad de los bloques de memoria, y por lo mismo, el query se ejecutó en menos de la mitad del tiempo que antes.

Pero hay un detalle importante a notar,

call     count       cpu    elapsed
------- ------  -------- ----------
Parse        1      3.31       3.23 
Execute      1     34.25      33.44
Fetch        1     27.81      27.15 

Misses in library cache during parse: 1

10  SORT ORDER BY (cr=331474 pr=0 pw=0 time=27155623 us)

Como se puede observar, la sentencia SQL no se encontraba en memoria, por lo cuál se obligó a un Hard Parse. La llamada execute, se dice que nos muestra
estadísticas en la fase de ejecución de un cursor "y... ¿qué diablos es eso?"... Pues bien, parece ser que para una sentencia select, el tiempo de esta llamada,
es el tiempo que Oracle gasta en generar el plan de ejecución.

En el trace, justo después de la llamada

PARSING IN CURSOR #47 len=7409 dep=0 uid=44 oct=3 lid=44 tim=7410852239759 hv=948570397 ad='bd8a6170'
SELECT TRX_CLASS_NAME,TRX_TYPE_NAME,TRX_NUMBER_DISPLAYED,TRX_DATE

 PARSING IN CURSOR #49 len=198 dep=2 uid=0 oct=3 lid=0 tim=7410852558503 hv=4125641360 ad='bebe5ac0'
 EXEC #49:c=0,e=931,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=7410852560307
 PARSING IN CURSOR #49 len=493 dep=2 uid=0 oct=3 lid=0 tim=7410852561080 hv=2584065658 ad='bdfbfd78'
 EXEC #49:c=0,e=514,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=7410852563193
 PARSING IN CURSOR #49 len=789 dep=2 uid=0 oct=3 lid=0 tim=7410852564507 hv=3159716790 ad='baf88850'
 ... (60 llamadas similares) ...
 EXEC #49:c=0,e=617,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7410880914683
 PARSING IN CURSOR #50 len=192 dep=1 uid=0 oct=3 lid=0 tim=7410880915705 hv=3136611056 ad='bea06ed8'
 EXEC #50:c=0,e=614,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7410880916713

EXEC #47:c=34380000,e=33582714,p=1,cr=244,cu=0,mis=0,r=0,dep=0,og=1,tim=7410885823104

Todas las llamadas de cursor #49 y #50 eran queries recursivos, muchos sobre la tabla association$. Y claro, si vas a generar un plan de ejecución de +550 líneas, pues imagínense las posibilidades. Por supuesto que esto no sucedía si la sentencia SQL ya estaba en memoria y sólo un "Soft Parse" sucedía, pero por la naturaleza de la aplicación, y de la lógica del negocio, no solía ser un query tan frecuente, y generalmente a los 10 minutos se encontraba fuera del shared_pool, obligando a un "hard parse" en la siguiente ejecución (34 segundos en el execute).

Así que opté por una solución alterna.

¿Cómo se puede precalcular el plan de ejecución de una sentencia SQL?

La respuesta es sencilla. OUTLINES.

Yo había usado con anterioridad Outlines para hacer cambios en los planes de ejecución en aplicaciones de código cerrado, y la lógica que usaba era:

Cuando Oracle reciba una sentencia SQL y se calcule su Hash_value (soft parse), al usar outlines, en lugar de generar un plan de ejecución, use este otro ya "PRECALCULADO". Así que, ¿Qué sucedería si genero un outline para que use su mismo plan de ejecución?, el resultado fue el siguiente:

Al yo conocer el Hash Value "hv=948570397", y sabiendo que en ese momento, la sentencia estaba en el shared_pool, ejecuté lo siguiente:

SQL> BEGIN
  2    DBMS_OUTLN.create_outline(
  3      hash_value    => 948570397,
  4      child_number  => 0,
  5      category      => 'RIT_OUTLINES');
  6  END;
  7  /

Procedimiento PL/SQL terminado correctamente.

Algunas veces marca un error de End Of Communication Chanel, pero sí genera el outline (me ha sucedido con 10.2.0.2 en linux suse y solaris).

SQL> SELECT name,
  2    category,
  3    used
  4 FROM dba_outlines;

NAME                           CATEGORY       USED
------------------------------ -------------- ------
SYS_OUTLINE_08110513401290701  RIT_OUTLINES   UNUSED

Ahora alteramos el sistema para que se use nuestro outline

SQL> ALTER system
  2  SET use_stored_outlines = rit_outlines;

Sistema modificado.

SQL> alter system flush shared_pool;

Sistema modificado.

De esta forma bajamos la sentencia SQL de memoria y obligamos a un "Hard Parse". Intentamos nuestra prueba nuevamente, y estos fueron los resultados:

call     count       cpu    elapsed       disk      query 
------- ------  -------- ---------- ---------- ---------- 
Parse        1      3.98       3.98          0        449 
Execute      1      2.70       2.62          0          0 
Fetch        1     28.92      28.24          0     331299 
------- ------  -------- ---------- ---------- ---------- 
total        3     35.60      34.85          0     331748 

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

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY (cr=331299 pr=0 pw=0 time=28242017 us)
  11176   UNION-ALL  (cr=331299 pr=0 pw=0 time=22968921 us)

Como se puede ver, se realizo un "hard parse", y el tiempo que se utilizó en la llamada Execute fue de menos de 3 segundos (comparado contra los 34 segundos).

Revisamos que nuestro outline haya sido utilizado:

SQL> SELECT name,
  2    category,
  3    used
  4 FROM dba_outlines;

NAME                           CATEGORY       USED
------------------------------ -------------- ------
SYS_OUTLINE_08110513401290701  RIT_OUTLINES   USED

Cabe decir que no se puede usar un parámetro de init para usar los outlines, por lo que se recomienda un trigger para dejarlo activarlo en cada startup (o bien a nivel sesión). Debe de estar habilitado el Query Rewrite y se debe de tener mucho cuidado al usarlos, ya que puede haber algunas implicaciones al hacer uso de outlines.

Para el usuario final, el tiempo bajó de 5 minutos a sólo 30 segundos, lo cual fue muy bueno. Así que ya saben, cuando su generación de un plan de ejecución sea muy tardado, siempre está la opción de precompilarlo en un outline.

Perfiles de desempeño

Recientemente he estado con clientes ayudando a resolver serios problemas de desempeño. Los ambientes, por diversos que sean, son muy similares, es decir, siempre se cuenta con una base de datos que corresponde a una aplicación, y esta aplicación tiene muchas "mini-aplicaciones" relacionadas.

Los clientes generalmente se dan cuenta de que sufren por problemas de desempeño cuando sus usuarios comienzan a quejarse... Y la pregunta es ¿Qué está causando el problema de desmpeño?

La gente suele enfocarse a ver el sistema como "Una Aplicación" e intenta optimizar todos los reportes que se generan dentro de la misma aplicación, pero la realidad, es que sólo se podría estar atacando un porcentaje muy bajo de los problemas en realidad.

Imaginemos que una aplicación es un "E-Business Suite" de oracle; de entrada tenemos carga generada por las sentencias SQL de reportes, SQLs de las formas, SQLs de sesiones en Framework, SQLs de Discoverer, SQLs de interfaces, etc... La probelmática puede venir de muchos lados a la vez.

Dada esta problemática, la forma más sencilla que he encontrado para hacer el monitoreo/análisis es crear un trigger de sesión que registre la actividad de cada sesión a una tabla de auditoria en la base de datos.

Aquí el script de creación de objetos:

trigger_auditoria.sql

/* se crea la tabla que mantendrá 
   los datos de auditoria.        */
CREATE TABLE "SYS"."AUDITORIA_HECG" 
   ( "ENTRADA" DATE, 
 "SALIDA" DATE, 
 "USUARIO_SISTEMA" VARCHAR2(30), 
 "USUARIO_BASE" VARCHAR2(30), 
 "EQUIPO" VARCHAR2(64), 
 "PROGRAMA" VARCHAR2(64), 
 "MODULO" VARCHAR2(48), 
 "ACCION" VARCHAR2(32), 
 "CPU" NUMBER, 
 "LECTURAS_LOGICAS" NUMBER, 
 "LECTURAS_FISICAS" NUMBER, 
 "LECTURAS_FISICAS_BYTES" NUMBER, 
 "ESCRITURAS_FISICAS" NUMBER, 
 "ESCRITURAS_FISICAS_BYTES" NUMBER,
 "PREV_SQL" NUMBER
   ) 
  TABLESPACE "SYSAUX" ;

/* ahora se crea el trigger 
   que llenará los datos de
   las sesiones            */

CREATE OR REPLACE TRIGGER "SYS"."AUD_LOGGOF" before logoff ON DATABASE
DECLARE mi_sesion NUMBER;
mi_cpu NUMBER;-- stat 12
mi_pr NUMBER;-- stat 54
mi_prb number;--stat 58
mi_pw NUMBER;-- stat 62
mi_pwb number; --stat 66
mi_lr NUMBER;-- stat 9

BEGIN
  FOR estadisticas IN
    (SELECT name,
      my.statistic#,
       VALUE,
       sid
     FROM v$mystat my,
       v$statname st
     WHERE my.statistic# IN(9,    12,    54,   58,    62,   66)
     AND my.statistic# = st.statistic#
    )
  LOOP
    CASE
      WHEN estadisticas.statistic# = 9 THEN
        mi_lr := estadisticas.VALUE;
      WHEN estadisticas.statistic# = 12 THEN
        mi_cpu := estadisticas.VALUE;
      WHEN estadisticas.statistic# = 54 THEN
        mi_pr := estadisticas.VALUE;
      WHEN estadisticas.statistic# = 58 THEN
        mi_prb := estadisticas.VALUE;
      WHEN estadisticas.statistic# = 62 THEN
        mi_pw := estadisticas.VALUE;
      WHEN estadisticas.statistic# = 66 THEN
        mi_pwb := estadisticas.VALUE;
    END CASE;
    mi_sesion := estadisticas.sid;
  END LOOP;
  INSERT INTO AUDITORIA_HECG
    SELECT logon_time,
      sysdate,
      osuser,
      username,
      machine,
      program,
      MODULE,
      ACTION,
      mi_cpu,
      mi_lr,
      mi_pr,
      mi_prb,
      mi_pw,
      mi_pwb,
      prev_hash_value
  FROM v$session
  WHERE sid = mi_sesion;
END;

Los campos que tiene la tabla de auditoría son los siguientes:

ENTRADA Se registra el logontime de la sesión a la base de datos.
SALIDA Es la hora en que la sesión se desconecta de la base de datos.
USUARIO_SISTEMA Usuario a nivel sistema operativo.
USUARIO_BASE Usuario que se utilizó para conectarse a la base de datos.
EQUIPO Máquina desde la cuál se realizó la conexión a oracle
PROGRAMA Programa utilizado para realizar la conexión (SQL Plus, TOAD, etc.).
MODULO Información adicional sobre la conexión.
ACCION Acción que se realiza dentro del módulo.
CPU Tiempo en centésimas de segundo de uso de CPU para la sesión.
LECTURAS_LOGICAS Cantidad de bloques lógicos leídos.
LECTURAS_FISICAS Cantidad de bloqes leídos físicos.
LECTURAS_FISICAS_BYTES Cantidad en bytes de lecturas físicas (en caso de que se tengan múltiples pools)
ESCRITURAS_FISICAS Bloques escritos hacia la base de datos.
ESCRITURAS_FISICAS_BYTES Cantidad en bytes de bloques escritos.
PREV_SQL Última sentencia SQL ejecutadapor la sesión.


Una vez creado el trigger, la información empezará a caer en la tabla a medida que las sesiones van terminando.

Los campos como módulo, acción, prev_sql, están en la tabla ya que me han ayudado a identificar sesiones y crear perfiles asociados con programas o código en específico.

Un pequeño ejemplo de la información:

  1  SELECT COUNT(1) Sesiones,
  2    programa,
  3    SUM(cpu) / 100 "CPU Segs.",
  4    SUM(lecturas_logicas) "Lecturas Logicas",
  5    SUM(lecturas_fisicas) "Lecturas Físicas"
  6  FROM auditoria_rit
  7  group by programa
  8* order by 5 desc

  SESIONES PROGRAMA            CPU Segs. Lecturas Logicas Lecturas Físicas
---------- ------------------ ---------- ---------------- ----------------
      5259 Monitor.exe            805.03         10181777           161536
     42216 SQL*Plus              1689.14         18391422           107703
        11 rman.exe                30.57           538894            16157
         4 ORACLE.EXE (J001)       34.66           553426            12075
        12 oradim.exe              16.35           139138            10178
        46 sqlplus.exe              74.2          2465261             6406
        26 ORACLE.EXE (J002)       32.91           476683             5634
         6 ORACLE.EXE (J003)       12.32           236175             4760
         6 SQL Developer          185.47          2059364             3986
        32 T.O.A.D.                11.54            30644             2522
         1 JDBC Thin Client         1.25            30364               47

11 filas seleccionadas.

Los límites para explotar la información son infinitos, se pueden agrupar por cierta fecha de inicio, por horarios durante el día, por usuarios, una vez que se sabe si el problema es CPU o I/O físico, puedes ir hacia los programas o módulos que más afectan a la base de datos.

Si llegaran a tener muchos valores en Nulo, es decir, un valor de usuario genérico, un application server como equipo, módulo y acción en nulo, prev_sql como "SELECT 1 FROM DUAL", creo que sería hora de pensar un poco en instrumentar el código de nuestra aplicación, por lo menos para añadir un módulo y/o acción con el paquete

DBMS_APPLICATION_INFO

SQL> exec dbms_application_info.set_module('CARGA','Auditoria');

Procedimiento PL/SQL terminado correctamente.

SQL> exit

c:\> sqlplus "/ as sysdba"

SQL>  SELECT entrada,
  2    usuario_sistema,
  3    usuario_base,
  4    programa,
  5    modulo,
  6    accion
  7 FROM auditoria_hecg;

ENTRADA   USUARIO_SISTEMA USUARIO_BA PROGRAMA    MODUL ACCION
--------- --------------- ---------- ----------- ----- ----------
21-OCT-08 HUGO-WIN\Hugo   SYS        sqlplus.exe CARGA Auditoria

De esta forma nos será más sencillo encontrar qué es lo que cada sesión realizó en el sistema.

Muchas veces no se mide el impacto que pudiera causar el monitoreo dentro de un sistema, o queries que se ejecutan relativamente rápido, pero que por la cantidad de veces que se ejecutan en el sistema, representan el 30% de las lecturas físicas en el sistema.

Este trigger no es muy intrusivo, no genera carga fuerte en el sistema, y la información que arroja es muy valiosa.

Hay que decir que para versión 9i, se deben de realizar cambios, agradezco a Issachar Zamora, los cambios que relizó en el trigger para que funcionará bien en 9i (las estadísticas de 9i no incluyen escrituras ni lecturas en bytes, además de que el stat_id cambia para algunas otras).

Una vez que tenga el trigger para 9i lo anexaré a este post.

Adicionalmente suelo poner un índice por (entrada, modulo), ya que los queries que suelo ejecutar van filtrados por fecha o por módulo en específico en un rango de fechas.

Si alguien tiene una idea de cómo mejorar, o qué parámetros cree conveniente añadir en el trigger, no duden en comentarlo.

Expansiones NVL

Muchas veces me he encontrado con queries relacionados a reportes de un ERP, en los cuales, debido a que los programadores no conocen en su totalidad los valores o número de parámetros a utilizar en cada ejecución, se opta por evaluar valores nulos y ejecutar una sola sentencia SQL, este tipo de condiciones las he visto de dos formas principalmente:

haciendo uso de un "OR"

(campo1 IS NULL OR campo1 = :b1)

o bien el uso de NVL o decode

campo1 = nvl(:b1, campo1)
campo1 = decode(:b1, null, campo1,:b1)

Pero ¿cuál de los dos pudiera ser la mejor opción?, a mi forma de ver, el NVL (o decode) es la mejor opción.

Para poder crear nuestro ejemplo, tenemos una tabla con 4 campos, el primero de baja cardinalidad, el segundo de muy alta cardinalidad y dos campos restantes con la misma distribución de datos. Crearemos unos índices y generaremos estadísticas.

SQL> CREATE TABLE prueba AS
  2  SELECT owner campo1,
  3    object_name campo2,
  4    created campo3,
  5    created campo4
  6  FROM dba_objects;

Tabla creada.

SQL> CREATE INDEX indice1 ON prueba(campo1,   campo2);

Índice creado.

SQL> CREATE INDEX indice3 ON prueba(campo3);

Índice creado.

SQL> CREATE INDEX indice4 ON prueba(campo4);

Índice creado.

SQL> BEGIN
  2    dbms_stats.gather_table_stats(ownname => 'SYS',   tabname => 'PRUEBA',   cascade => TRUE);
  3  END;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE(campo1 IS NULL OR campo1 = :b1)
  5   AND(campo2 IS NULL OR campo2 = :b2);

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|*  1 |  TABLE ACCESS FULL| PRUEBA |
------------------------------------

En este caso el optimizador de costos, decide simplemente hacer un full table scan a la tabla. e incluso si se usa el hint "USE_CONCAT", el plan de ejecución no cambia.

Para nuestro segundo ejemplo usamos el NVL

---------------------------------------
| Id  | Operation                     |
---------------------------------------
|   0 | SELECT STATEMENT              |
|   1 |  CONCATENATION                |
|*  2 |   FILTER                      |
|*  3 |    TABLE ACCESS FULL          |
|*  4 |   FILTER                      |
|   5 |    TABLE ACCESS BY INDEX ROWID|
|*  6 |     INDEX SKIP SCAN           |
---------------------------------------

  2 - filter(:B2 IS NULL)
  3 - filter("CAMPO1"=NVL(:B1,"CAMPO1") AND "CAMPO2" IS NOT NULL)
  4 - filter(:B2 IS NOT NULL)
  6 - access("CAMPO2"=:B2)
      filter("CAMPO2"=:B2 AND "CAMPO1"=NVL(:B1,"CAMPO1"))

En este caso podemos notar dos cosas interesantes; la primera va relacionada con la descomposición de un sólo query a dos sentencias SQL. La primera opción que muestra, es el "peor de los casos", y la segunda opción, es el caso que tiene la más grande cardinalidad y por lo tanto, nos regresaría menos registros, en este caso un index skip scan parece ser la mejor opción. Lo segundo a notar, es la operación "FILTER", que significa, que sólo si se cumple la condición de filtro, se ejecutá la parte del plan que depende de esa operación. Un ejemplo claro de la situación de filter es:

SQL> SELECT *
  2  FROM prueba
  3  WHERE 1=2;
----------------------------
| Id  | Operation          |
----------------------------
|   0 | SELECT STATEMENT   |
|*  1 |  FILTER            |
|   2 |   TABLE ACCESS FULL|
----------------------------

  1 - filter(NULL IS NOT NULL)

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

Se puede ver el "FILTER" que dice "null is not null" lo cual siempre evalúa a Falso y por consiguiente, el Full Table Scan nunca se ejecuta (0 gets).

Volviendo a nuestro ejemplo del NVL, oracle decide expandir el plan de ejecución, apostando a que pudiera tener suerte e ir por pocos datos.

No hay forma de controlar de forma sencilla el plan de ejecución, ya que oracle siempre intentará obtener la menor cantidad de registros basado en las estadísticas.

El parámetro "_or_expand_nvl_predicate" es el que regula esta expansión de plan de ejecución:

SQL> alter session set "_or_expand_nvl_predicate"=false;

Sesión modificada.

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo1 = nvl(:b1,   campo1)
  5   AND campo2 = nvl(:b2,   campo2);

Explicado.

---------------------------
| Id  | Operation         |
---------------------------
|   0 | SELECT STATEMENT  |
|*  1 |  TABLE ACCESS FULL|
---------------------------

Al ser un parámetro escondido, no recomiendo por ningún motivo cambiarlo de true a false. Si por alguna razón quieren deshacerse del plan extendido, se puede usar el hint "NO_EXPAND".

Ahora, ¿qué sucede cuando tienes dos campos con la misma cardinalidad, y oracle tiene que expandir el query?, ¿qué índice o plan de ejecución va a tomar?

En primera instancia, hubiera creído que tomaría el primer índice en orden alfabético (ya que el optimizador en muchos casos selecciona de esa forma), pero la realidad es que para la expansión de predicados, lo que importa es el orden inverso en el predicado del query:

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo3 = nvl(:b1,   campo3)
  5    AND campo4 = nvl(:b2,   campo4);

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|   1 |  CONCATENATION                |         |
|*  2 |   FILTER                      |         |
|*  3 |    TABLE ACCESS FULL          | PRUEBA  |
|*  4 |   FILTER                      |         |
|*  5 |    TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  6 |     INDEX RANGE SCAN          | INDICE4 |
-------------------------------------------------


SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo4 = nvl(:b1,   campo4)
  5    AND campo3 = nvl(:b2,   campo3);

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|   1 |  CONCATENATION                |         |
|*  2 |   FILTER                      |         |
|*  3 |    TABLE ACCESS FULL          | PRUEBA  |
|*  4 |   FILTER                      |         |
|*  5 |    TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  6 |     INDEX RANGE SCAN          | INDICE3 |
-------------------------------------------------

Hasta aquí, ya pudimos ver que de alguna forma podemos hacer que oracle tome un índice que nos interesa, pero en realidad, muchas veces el plan que oracle decide, no nos es suficiente, por ejemplo:

Imaginemos que tenemos un reporte que va hacia una tabla de facturación. El reporte permite la entrada de un rango de fecha y/o un rango de número de facturas. En este ejemplo, al usar NVL, oracle hará la expansión (seguramente) basado en el rango de número de facturas, ya que este campo suele tener mayor cardinalidad. Pero si nosotros sabemos de antemano, que el 90% de las ejecuciones son con un rango de fechas y no con un rango de número de facturas, ¿qué se puede hacer?

Podemos expandir manualmente el query.

Suponiendo que tenemos el siguiente query:

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo2 = nvl(:b1,   campo2)
  5    AND campo3 = nvl(:b2,   campo3);

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|   1 |  CONCATENATION                |         |
|*  2 |   FILTER                      |         |
|*  3 |    TABLE ACCESS FULL          | PRUEBA  |
|*  4 |   FILTER                      |         |
|*  5 |    TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  6 |     INDEX SKIP SCAN           | INDICE1 |
-------------------------------------------------

Podemos observar que se hace un skip scan del índice 1 o bien un FTS a la tabla, pero si sabemos de antemano que la mayoría de las veces la variable :b1 vendrá nula y no así :b2, lo mejor sería rehacer nuestro query para que al evaluar las variables, pueda tomar un mejor plan de ejecución.

Esto se puede lograr de la siguiente manera

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE :b1 is null
  5    AND campo3 = nvl(:b2,   campo3)
  6  union all
  7  SELECT *
  8  FROM prueba
  9  WHERE :b1 is not null
 10    AND campo2 = nvl(:b1,   campo2)
 11    AND campo3 = nvl(:b2,   campo3);

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  UNION-ALL                     |         |
|   2 |   CONCATENATION                |         |
|*  3 |    FILTER                      |         |
|*  4 |     TABLE ACCESS FULL          | PRUEBA  |
|*  5 |    FILTER                      |         |
|   6 |     TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  7 |      INDEX RANGE SCAN          | INDICE3 |
|   8 |   CONCATENATION                |         |
|*  9 |    FILTER                      |         |
|* 10 |     TABLE ACCESS FULL          | PRUEBA  |
|* 11 |    FILTER                      |         |
|* 12 |     TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|* 13 |      INDEX SKIP SCAN           | INDICE1 |
--------------------------------------------------


  3 - filter(:B1 IS NULL AND :B2 IS NULL)
  4 - filter("CAMPO3" IS NOT NULL)
  5 - filter(:B1 IS NULL AND :B2 IS NOT NULL)
  7 - access("CAMPO3"=:B2)
  9 - filter(:B1 IS NOT NULL AND :B1 IS NULL)
 10 - filter("CAMPO3"=NVL(:B2,"CAMPO3") AND "CAMPO2" IS NOT NULL)
 11 - filter(:B1 IS NOT NULL AND :B1 IS NOT NULL)
 12 - filter("CAMPO3"=NVL(:B2,"CAMPO3"))
 13 - access("CAMPO2"=:B1)
      filter("CAMPO2"=:B1)

Y de esta forma tenemos 4 filters. El primero, si :b1 y :b2 son nulos, entonces ejecuta un FTS. El segundo, si :b1 es nulo y :b2 no es nulo, utiliza el índice 3. El tercero, si :b1 es nulo y :b1 no es nulo, siempre se iguala a false por lo cual no se ejecuta. El cuarto evalúa que :b1 no sea nulo, siendo este, el mejor plan de ejecución y presenta el acceso a través del Index Skip Scan del índice 1.

Me ha tocado ver a diversos programadores que les es más fácil programar código dinámico y presentar las sentencias SQL con los predicados que no tienen un valor nulo. Así que si alguien tiene un código que maneje un cursor de referencia dinámico, o un armado de un query de forma dinámica y lo quiere compartir con nosotros, es más que bienvenido.

Funciones determinísticas y Group by vs. Distinct(2)

Recientemente un comentario sobre la entrada de Group by vs. Distinct, me hizo dudar un poco sobre lo que había escrito en relación a group by vs. distinct, pero a la vez me obligó a investigar un poco más, y estas son mis observaciones.

Intentando plasmar lo que se tiene en el comentario, tenemos lo siguiente:

Comenzamos creando una tabla

SQL> CREATE TABLE valores(valor NUMBER(1))
  2  TABLESPACE users;

Tabla creada.

Una vez que se tiene la tabla, insertamos 100 registros con sólo 10 valores distintos

SQL> INSERT
  2  INTO valores
  3  SELECT MOD(rownum,   10)
  4  FROM dba_objects
  5  WHERE rownum < 101;

100 filas creadas.

Cramos una función "tardada" para probar los ejemplos

CREATE OR REPLACE FUNCTION tardada(numero IN NUMBER) 
RETURN NUMBER IS salida NUMBER;
BEGIN
  SELECT COUNT(1)
  INTO salida
  FROM dba_tables
  WHERE MOD(rownum,   10) = numero;
  RETURN salida;
END;
/

Primero vamos a evaluar el tiempo y estadísticas sin agrupar la información

SQL> set autot traceonly stat
SQL> set timing on

SQL> SELECT tardada(valor)
  2  FROM valores;

100 filas seleccionadas.

Transcurrido: 00:00:05.67

Estadísticas
----------------------------------------------------------
        100  recursive calls
     246414  consistent gets
        100  sorts (memory)
        100  rows processed

Se procesó la información en más de 5 segundos consumiendo 246,000 bloques. Se procesan 100 registros, y se hacen 100 sorts

Si lo hacemos con un distinct, estas son las estadísticas

SQL> SELECT DISTINCT tardada(valor)
  2  FROM valores;

Transcurrido: 00:00:05.68

Estadísticas
----------------------------------------------------------
        100  recursive calls
     246407  consistent gets
        100  sorts (memory)
          2  rows processed

Como vemos, se usan prácticamente los mismos recursos, pero al final nos regresa sólamente 2 registros. La función se ejecuta 100 veces.

Ahora lo intentamos hacer con un group by:

SQL> SELECT tardada(valor)
  2  FROM valores
  3  GROUP BY tardada(valor);
  
Transcurrido: 00:00:05.76

Estadísticas
----------------------------------------------------------
        100  recursive calls
     246407  consistent gets
        100  sorts (memory)
          2  rows processed

En este caso son las mismas estadísticas que con el distinct. En el siguiente ejemplo, que fue el que me llegó a confundir, se tiene lo siguiente:

SQL> SELECT tardada(valor)
  2  FROM valores
  3  GROUP BY valor;

10 filas seleccionadas.

Transcurrido: 00:00:00.65

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
         10  rows processed

El tiempo de ejecución fue menor a un segundo, la lectura de bloques se redujo a un 10% (24,000) bloques. El resultado nos puede llevar a la conclusión de que de esta forma, sólo se evalúa la función 10 veces debido a que sólo hay 10 valores distintos y no las 100 veces que son los registros totales de la tabla. Esto es correcto de cierta forma, es decir, la función se evalúa 10 veces porque al agrupar por "valor", nos quedamos con 10 registros a evaluar en la función. Aparentemente no hay forma de reescribir de forma simple este ejemplo de group by con un distinct, pero creo que se podría hacer de la siguiente forma:

SQL> SELECT tardada(valor)
  2  FROM
  3    (SELECT DISTINCT valor valor
  4     FROM valores)
  5  ;

10 filas seleccionadas.

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
         10  rows processed

Como se puede ver, se tiene el mismo consumo de recursos que en la primera situación, sin embargo creo que son dos cosas distintas los primeros ejemplos a estos últimos, ya que los últimos ejemplos, primero agrupan los registros y luego evalúan la función, es por eso que regresan 10 registros; los primeros ejemplos, evalúan la función y después agrupan, por esto, sólo regresan 2 registros.

Para poder comprobar lo que digo, voy a generar una función que siempre regrese un valor distinto:

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> CREATE OR REPLACE FUNCTION rapida(numero IN NUMBER) RETURN NUMBER IS salida NUMBER;
  2  BEGIN
  3    SELECT secuencia.nextval
  4    INTO salida
  5    FROM dual;
  6    RETURN salida;
  7  END;
  8  /

Función creada.

Si nosotros ejecutamos los primeros ejemplos del distinct o del group by, las respuestas serán similares a la siguiente:

SQL> SELECT DISTINCT rapida(valor)
  2  FROM valores;

RAPIDA(VALOR)
-------------
            1
           22
           25
           30
           34
           42
           43
           51
           54
           57
          ...
           82
           92
           98

100 filas seleccionadas.

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
  2  FROM valores
  3  GROUP BY rapida(valor);

RAPIDA(VALOR)
-------------
            1
           22
           25
           30
           34
           42
           43
           51
           54
           57
          ...
           82
           92
           98

100 filas seleccionadas.

Con esto, vemos que se comportan igual el distinct y el group by de mis primeros ejemplos, e incluso podemos observar que la función hash que se utiliza para evaluar duplicados es la misma, ya que regresa en el mismo orden los registros.

Ahora vamos con el ejemplo que ejecuta solamente 10 veces la función:

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
  2  FROM valores
  3  GROUP BY valor;

RAPIDA(VALOR)
-------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10

10 filas seleccionadas.

Esto nos ayuda a entender que la agrupación se realiza antes de entrar a la función hash del group by o del distinct, y pudiera ser que no obtengamos el resultado deseado (aunque es un resultado muy válido, simplemente hay que cuidar el sentido).

Ahora, esto nos pone a pensar en que si tenemos una función que siempre que se introduzca un valor se obtendrá el mismo resultado, oracle debería de ser capaz de optimizar nuestro query para ejecutar menos veces la función.

Es aquí donde se tiene la función determinística:

SQL> CREATE OR REPLACE FUNCTION tardada2(numero IN NUMBER) RETURN NUMBER
  2  DETERMINISTIC
  3  IS salida NUMBER;
  4  BEGIN
  5    SELECT COUNT(1)
  6    INTO salida
  7    FROM dba_tables
  8    WHERE MOD(rownum,   10) = numero;
  9    RETURN salida;
 10  END;
 11  /

Función creada.

Y ahora probamos nuestros ejemplos iniciales

SQL> SELECT DISTINCT tardada2(valor)
  2  FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
          2  rows processed

SQL> SELECT tardada2(valor)
  2  FROM valores
  3  GROUP BY tardada2(valor);

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         10  recursive calls
      24647  consistent gets
         10  sorts (memory)
          2  rows processed

Como se puede observar, tanto en el distinct como en el group by, oracle puede hacer uso de un "cache" de resultados de funciones.

Ya como último ejemplo, si una función no es determinística, Oracle puede usar un caché. Es algo que alguna vez vi en un simposio de de Hotsos. Desconozco en su totalidad la forma de evaluar si usa o no un cache, pero el ejemplo es el siguiente:

SQL> SELECT DISTINCT
  2    (SELECT tardada(valor)
  3     FROM dual)
  4  FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
         58  recursive calls
      24680  consistent gets
         10  sorts (memory)
          2  rows processed

Como se puede ver, la llamada a la función se mete dentro de un query, y con eso, oracle puede hacer uso del cache. Hay un poco más de llamadas recursivas, quizá porque oracle debe determinar si puede o no hacer uso del cache de funciones.

Así que de todo esto, me quedo con la misma impresión que en el fondo, group by y distinct son prácticamente iguales, pero lo que sí es importante, es el definir correctamente nuestras funciones, si sabemos que es una función determinística, crearla como tal, en caso de que no, pues simplemente omitir "DETERMINISTIC".

Hints Globales (Global Hints)

Como se puede ver en mi entrada de Mi primer tuning, desde hace aproximadamente 10 años he usado hints en Oracle.

Para los que nunca han usado un hint, no son más que instrucciones adicionales al optimizador de costos, que permiten cambiar un plan de ejecución. En pocas palabras, los hints nos permiten decidir en lugar del optimizador.

Existen diferentes tipos de Hints, oracle los suele categorizar de la siguiente forma:

* Single Table (Index, full)
* Multi Table (use_nl, use_hash, leading)
* Query Block (unnest, nomerge)
* Statement (all_rows, first_rows(n))

Pero en este caso me voy a enfocar a una subcategoria, los "Global Hints".

En mi ejemplo de la vida real, me encontré con sentencias SQL similares a las siguientes

SELECT MIN(CLP.CAMPO_FECHA)   FROM
XX_VISTA_1 CIR, XX_TABLA_1 CLP  
WHERE CIR.CAMPO_TEXTO_2 = :b1 
AND  CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER  
AND CLP.CAMPO_FECHA IS NOT NULL 
AND  CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
 
SELECT MIN(CLP.CAMPO_FECHA_2)   
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP  
WHERE CIR.CAMPO_TEXTO_2 = :b1 
AND  CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER  
AND CLP.CAMPO_FECHA IS NOT NULL 
AND  CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);

Estas sentencias SQL tenían un pésimo plan de ejecución, ya que la vista "XX_VISTA_1", contenía un inline view, y oracle decidía combinar el inline view con el outer query en una simple sentencia SQL (esto no siempre es una buena idea).

Existían en realidad unas 6 sentencias SQL similares con el mismo problema.

Tras revisar el código de la vista, me di cuenta de que eso era lo que Oracle estaba realizando, y la solución era sencilla... Cambiar el código de la vista para que no hiciera el merge del inline view:

CREATE OR REPLACE FORCE VIEW "APPS"."XX_VISTA_1"
("ROW_ID", CAMPO_FECHA, ...) AS
   SELECT /*+ NO_MERGE(msi) */
        pdc.ROWID row_id,
        msi.FECHA campo_fecha,
 ...

La realidad es que esta es una de las peores soluciones y realmente no fue lo que recomendé, pero fue lo que el cliente decidió hacer como primer intento.

Internamente para ellos era más fácil modificar 1 vista que modificar 6 programas.

Lo que sucedió fue que a partir de la modificación de la vista, hubo sentencias SQL como la siguiente

SELECT DECODE (:a1,NULL, 'Normal',(
SELECT SECUENCIA_DSP FROM APPS.XX_VISTA_1
 WHERE CAMPO_NUMBER =
 (SELECT MAX (CAMPO_NUMBER)
  FROM APPS.XX_VISTA_1
  WHERE CAMPO_NUMBER_2 = :a2
  AND CAMPO_TEXTO = 'Active'
  AND CAMPO_TEXTO_2
  IN ('P', 'O', 'I', 'F')))
  ) FROM DUAL;

Que al cambiar el código en la vista empezó a tener un desempeño muy pobre, cambiando su plan de ejecución a varios Full Table Scans.

Entonces, si el código de la vista no debe de ser cambiado, ¿qué es lo que se puede usar?

Global Hints

Los hints que se suelen usar en select, updates y deletes, generalmente van relacionados a una tabla que existe dentro del mismo query block. Y no a tablas relacionadas con vistas internas.

La solución son los Global Hints, es decir, se puede usar un Global Hint para decirle al optimizador de costos cómo esolver una vista que existe en nuestro query block.

En mi ejemplo, podemos decirle a la vista que no haga el merge del inline view desde el query principal, y de esta forma evitar la modificación de la vista.

SELECT /*+ NO_MERGE(CIR.msi) */  MIN(CLP.CAMPO_FECHA)   FROM
XX_VISTA_1 CIR, XX_TABLA_1 CLP  
WHERE CIR.CAMPO_TEXTO_2 = :b1 
AND  CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER  
AND CLP.CAMPO_FECHA IS NOT NULL 
AND  CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
 
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA_2)   
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP  
WHERE CIR.CAMPO_TEXTO_2 = :b1 
AND  CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER  
AND CLP.CAMPO_FECHA IS NOT NULL 
AND  CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);

De esta forma se estabilizaron los planes de ejecución de 6 sentencias SQL y no se afectó el desempeño de las demás sentencias que hacían uso de la vista.


En el manual de Oracle de performance tuning, vienen dos ejemplos muy claros sobre el uso de global hints

Ejemplo 1

CREATE OR REPLACE VIEW v AS
SELECT
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
        ( SELECT *
          FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
     AND e1.employee_id = j.employee_id
     AND e1.hire_date = j.start_date
     AND e1.salary = ( SELECT
                       max(e2.salary)
                       FROM employees e2
                       WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * 
  FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * 
  FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * 
  FROM v;

Ejemplo 2

CREATE OR REPLACE VIEW v1 AS
  SELECT *
    FROM employees
    WHERE employee_id < 150;

CREATE OR REPLACE VIEW v2 AS
  SELECT v1.employee_id employee_id, departments.department_id department_id
    FROM v1, departments
    WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
                        FULL(v2.departments) */ *
  FROM v2
  WHERE department_id = 30;

Roles en Oracle

Generalmente cuando uno toma el curso de Workshop I de 10g, y llega a la parte de Roles (lección 6), se habla muy poco de cómo se pueden manejar los roles, e incluso no hay un ejemplo claro del mismo.

Las veces que me toca dar el curso a mi, me gusta explicar a fondo lo más que puedo acerca de roles.

Se nos dice que un rol, es asignado por default a un usuario. Un rol, puede ser asegurado de manera adicional, que por default, no trae seguridad alguna.

Entonces, partiendo de la teoría, un rol puede ser creado con la siguiente seguridad:

* Ninguna (Default)
* Password
* External
* Global

Por falta de hardware/software, no puedo demostrar la seguridad Global, pero las demás sí.

Empecemos por crear un usuario, solo para no perder la costumbre, crearé un usuario identificado a nivel sistema operativo.

C:\>sqlplus "/ AS SYSDBA"

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> CREATE USER "OPS$HUGO-WIN\HUGO" IDENTIFIED externally;

Usuario creado.

SQL> GRANT CONNECT TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente. 

Ahora nos conectamos como el usuario creado, identificado de manera externa, y validamos el primer rol asignado "CONNECT"

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

Aquí validamos dos cosas, la primera es que el rol de create session viene implícito en el connect, ya que de otra forma no hubieramos podido crear la sesión; y lo segundo es que el rol asignado, está activo por default.

Ahora crearemos un rol identificado por un password

SQL> conn / as sysdba
Conectado.

SQL> CREATE role dba_pass IDENTIFIED BY supersecreto;

Rol creado.

SQL> GRANT DBA TO dba_pass;

Concesión terminada correctamente.

SQL> GRANT dba_pass TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

Ya que se tiene asignado al usuario el nuevo rol, nos conectamos como el usuario

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
DBA_PASS
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

17 filas seleccionadas.

Como vemos, el rol está asignado por default, y no nos pide el password nunca, lo que debemos hacer si es que queremos que el usuario use el password, es quitarlo del default del usuario.

SQL> conn / as sysdba
Conectado.

SQL> ALTER USER "OPS$HUGO-WIN\HUGO"
  2  DEFAULT ROLE ALL EXCEPT dba_pass;

Usuario modificado.

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

Ahora intentamos usar el rol, validando que se requiere un password.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"

SQL> SET role dba_pass;
SET role dba_pass
*
ERROR en línea 1:
ORA-01979: falta la contraseña para el rol 'DBA_PASS' o no es válida


SQL> SET role dba_pass IDENTIFIED BY supersecreto;

Rol definido.

SQL> SELECT COUNT(1)
  2  FROM v$session;

  COUNT(1)
----------
        15

Ahora crearemos un rol identificado a través de un procedimiento.

Lo primero es crear el procedimiento.

SQL> conn / as sysdba
Conectado.

SQL> CREATE OR REPLACE PROCEDURE sec_roles
  2  authid CURRENT_USER  AS
  3  usuario VARCHAR2(50);
  4  BEGIN
  5    usuario := LOWER((sys_context('userenv',   'session_user')));
  6    DBMS_OUTPUT.PUT_LINE(usuario);
  7    IF UPPER(usuario) = 'OPS$HUGO-WIN\HUGO' THEN
  8      dbms_session.set_role('DBA_PROC');
  9    ELSE
 10      NULL;
 11    END IF;
 12  END;
 13  /

Procedimiento creado.

Es importante el punto de AUTHID, debe estar en current user si no, no funcionaría. Ya que se tiene el procedimiento creado, continuamos con el resto del rol y grants necesarios al usuario.

SQL> GRANT EXECUTE ON sec_roles TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

SQL> CREATE role dba_proc IDENTIFIED USING sys.sec_roles;

Rol creado.

SQL> GRANT DBA TO dba_proc;

Concesión terminada correctamente.

SQL> GRANT EXECUTE ON sys.dbms_session TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

Es necesario que el usario que queremos que use el rol, tenga un grant al procedimiento de sys.sec_roles, y tambien permisos de ejecución en sys.dbms_session.

SQL> conn /
Conectado.

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

SQL> exec sys.sec_roles;
ops$hugo-win\hugo

Procedimiento PL/SQL terminado correctamente.

SQL> select * from session_roles;

ROLE
------------------------------
DBA_PROC
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

16 filas seleccionadas.

SQL>

Hay que notar que al usuario "ops$hugo-win\hugo" jamás se le dio un grant sobre el ROL, el ROL lo obtiene con la ejecución del procedimiento.

¿Alguien tendrá un ejemplo de la autenticación global para compartir?

Operadores de Conjunto (caso práctico)

En la compañía, se está desarrollando una aplicación para un cliente. Ésta sirve para "clonar" esquemas dentro de una base de datos, con la flexibilidad de decidir los objetos, campos, constraints, etc. que quieres llevarte.

Una problemática que se presentó, fue la siguiente:

¿Cómo saber qué constraints se pueden replicar con una tabla que no se migra con todas las columnas?

Suponiendo que tenemos una tabla original con 3 campos, y la tabla destino, sólo contiene dos campos. Imaginemos un constraint de tipo "check" que valida que el (campo1 = 'true') or (campo2 = 'false') or (campo3 = 'lo que sea'). No podemos replicar ese constraint al nuevo esquema, ya que marcaría un error. Así que ¿cómo pudiéramos revisar, basado en nuestras columnas seleccionadas, los constraints que se pueden migrar sin problema?

La solución se puede lograr fácilmente con lógica de conjuntos

WITH registros AS
  (SELECT --+materialize
  constraint_name,
     column_name
   FROM dba_cons_columns
   WHERE TABLE_NAME = 'SDO_COORD_REF_SYS')
SELECT constraint_name,
       COUNT(1) numero
  FROM registros
 WHERE column_name IN('GEOG_CRS_DATUM_ID',
                      'SOURCE_GEOG_SRID')
 GROUP BY constraint_name 
INTERSECT
SELECT constraint_name,
       COUNT(1)
  FROM registros
 GROUP BY constraint_name;

Primero explicaré brevemente sobre la clausula WITH.

La clausula with, que nace con Oracle 9i Release 1, en lugar de repetir código complejo, o pesado, nos permite darle un nombre, y reusar la referencia a través del nombre múltiples veces dentro de una sentencia SQL. Forma parte del estándar SQL-99. Jonathan Lewis, adicionalmente incluye el hint de "materialize" en las cláusulas with, ya que de esta forma, se materializa el resultado en una tabla temporal y al seleccionarse en múltiples partes, el resultado materializado se accede de forma más rápida.

En este ejemplo, como sé que voy a trabajar con los constraints de una sola tabla, mi universo de datos son todos los registros contenidos en "dba_cons_columns" para la tabla en cuestión. De ahí el with

WITH registros AS
  (SELECT --+materialize
  constraint_name,
     column_name
   FROM dba_cons_columns
   WHERE TABLE_NAME = 'NOMBRE_TABLA')

De ahí, necesito obtener los constraints que incluyan mis columnas que voy a replicar, o por lo menos alguna de las columnas. Añado un "count" para saber cuántas de mis columnas son incluidas en el constraint.

 SELECT constraint_name,
       COUNT(1) numero
  FROM registros
 WHERE column_name IN('CAMPOS',
                      'A',
                      'BUSCAR')
 GROUP BY constraint_name 

Finalmente para terminar, necesito saber cuántas columnas en total tiene cada constraint.

SELECT constraint_name,
       COUNT(1)
  FROM registros
 GROUP BY constraint_name

Teniendo esos sets de datos, usamos lógica de conjuntos y hacemos una intersección con la palabra "INTERSECT"

SQL> WITH registros AS
  2    (SELECT --+materialize
  3    constraint_name,
  4       column_name
  5     FROM dba_cons_columns
  6     WHERE TABLE_NAME = 'SDO_COORD_REF_SYS')
  7  SELECT constraint_name,
  8         COUNT(1) numero
  9    FROM registros
 10   WHERE column_name IN('GEOG_CRS_DATUM_ID',
 11                        'SOURCE_GEOG_SRID')
 12   GROUP BY constraint_name
 13  INTERSECT
 14  SELECT constraint_name,
 15         COUNT(1)
 16    FROM registros
 17   GROUP BY constraint_name;

CONSTRAINT_NAME                    NUMERO
------------------------------ ----------
COORD_REF_SYS_FOREIGN_GEOG              1

SQL>

Esto nos da el resultado que si voy a incluir sólo las columnas 'GEOG_CRS_DATUM_ID', 'SOURCE_GEOG_SRID', sólo puedo llevarme el contraint COORD_REF_SYS_FOREIGN_GEOG.

Pasando ya de lleno a la lógica de conjuntos, Oracle cuenta con tres operadores de conjuntos

UNION [ALL]
MINUS
INTERSECT

Se pueden usar para unir varios queries y llevar a cabo la operación de conjunto deseada, llevan orden de precedencia a menos que vengan entre paréntesis.

SQL> SELECT *
  2  FROM
  3    (SELECT 1 FROM dual
  4     UNION ALL
  5     SELECT 1 FROM dual)
  6  INTERSECT
  7    (SELECT 1 FROM dual
  8     UNION ALL
  9     SELECT 2 FROM dual)
 10  ;

         1
----------
         1

Las expresiones correspondientes en las sentencias select, deben de coincidir en número y tipo, si no se sigue esto, oracle genera un error.

Hay algunas observaciones por considerar.

No se pueden usar operadores de conjuntos en columnas de tipo BLOB, CLOB, BFILE, VARRAY, o "nested tables".

No son válidos en columnas de tipo long.

Los operadores MINUS, UNION e INTERSECT terminan agrupando los resultados. Union All no agrupa y sólo junta los resultados de las dos fuentes de datos.

Y la más importante de todas las consideraciones es que Oracle para apegarse a un estándar, cambiará (no sé si el release 11g ya lo tenga) la precedencia del comando INTERSECT para que se evalue primero, por esta razón, Oracle recomienda siempre usar paréntesis cuando se use "INTERSECT".

Passwords en Oracle

Desde hace mucho tiempo, se conoce la forma en que el hash de oracle se calcula para las versiones menores a 11g. E incluso se conoce el algoritmo del cálculo del hash para 11g.

Lo que es importante notar es que con el poder de cómputo de estos días, y con la debilidad del password de oracle, si no usamos passwords difíciles de crackear, podemos tener un riesgo de seguridad muy grande.

A continuación expongo un script, sólo con fines ilustrativos, la forma en que oracle calcula el hash

hashoracle.pl

use warnings;
use strict;
use Crypt::CBC;
use Encode;
use Unicode::String qw(utf8 latin1 utf16);

my $llave = pack("H16", "0123456789ABCDEF");
my $usuario = 'Hugo';
my $password = 'abc123';

my $textoplano = uc($usuario.$password);
my $utf16 =utf8($textoplano);
my $octeto =  $utf16->utf16;
my $IV = pack("H16", "0000000000000000");
my $cifrado = Crypt::CBC->new(
   -key    => $llave,
   -cipher => 'DES',
   -iv => $IV,
   -header => 'none',
   -padding => 'null',
   -literal_key=>1,
);

my $textocifrado = $cifrado->encrypt($octeto);
my $cifrado1 = Crypt::CBC->new(
   -key    => substr($textocifrado, length($textocifrado)-8,8),
   -cipher => 'DES',
   -iv => $IV,
   -header => 'none',
   -padding => 'null',
   -literal_key=>1,
);

my $textocifrado1 = $cifrado1->encrypt($octeto);
my( $hex1 ) = unpack( 'H*', $textocifrado1 );
my $temp1 = uc(substr($hex1,length($hex1)-16,16));
print "\n$temp1\n";
__END__

Si este script se pusiera en un ciclo infinito a buscar passwords para hacer un match contra un hash preestablecido, podría buscar aproximadamente 800,000 hashes por segundo, si este mismo script se pasara a C, podría buscar aproximadamente 1,300,000 hashes por segundo. Alex Kornbust tiene un programa que incluso puede hacer uso de threads en los CPUs y genera hasta 2,400,000 por segundo.

Así que pensemos que, de acuerdo a estudios realizados, un password de 8 caracteres, puede ser descubierto en tan sólo 20 días, de 9 caracteres, ya puede pasar del año, pero teniendo 40 computadoras descifrando passwords, el tiempo vuelve a 20 días, así que ¿cuál es mi recomendación?

Seguir de lleno las recomendaciones de Oracle:

- Crea los passwords entre 8 y 30 caracteres (yo recomendaría de 10 a 30 caracteres).
- Utiliza símbolos del Character Set para definir los passwords (#$_).
- Además de incluir un dígito y un alfanumérico, también incluye un signo de puntuación.
- No inicies un password con un número.
- No utilices palabras reservadas de oracle.
- No uses palabras de diccionario.

Adicionalmente podemos crear passwords más complejos, pero esto también nos limita el uso un poco:

Empecemos con passwords entre comillas

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 11:56:39 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> alter user hugo identified by "de la sierra morena cielito";

Usuario modificado.

SQL> conn hugo/"de la sierra morena cielito";
Conectado.

En este punto tenemos un pequeño problema, o quizás sólo yo, pero no he podido pasar a nivel sistema operativo mi password, es decir, la conexión la debo de realizar con /nolog, o bien usando únicamente el usuario y esperar a que sqlplus nos pregunte el password.

C:\>sqlplus hugo/"de\ la\ sierra\ morena\ cielito"

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:00:23 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


C:\>sqlplus hugo

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:01:43 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL>


C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:02:43 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


SQL> conn hugo/"de la sierra morena cielito";
Conectado.

Algo de lo que podemos hacer uso, como bien nos recomienda oracle, es el uso de caracteres especiales, por ejemplo:

C:\>set NLS_LANG=spanish_america.we8pc850

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:05:04 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> alter user hugo identified by querétaro;

Usuario modificado.

Al hacer esto, nos aseguramos que los passwords sólo funcionan con nuestro caracter set o alguno muy similar:

C:\>set NLS_LANG=spanish_america.we8iso8859p1

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:08:18 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


SQL> conn hugo/querétaro
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> exit

C:\>set NLS_LANG=spanish_america.utf8

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:09:14 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


SQL> conn hugo/querétaro
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> exit

C:\>set NLS_LANG=spanish_america.we8pc850

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Jue Ago 28 12:09:37 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


SQL> conn hugo/querétaro
Conectado.

De esta forma podemos asegurar más nuestros passwords y asegurar de una mejor forma nuestra base de datos.

Rman y cómo catalogar Backups

Los esquemas de respaldo de todo mundo suelen ser muy variados, muy rara vez te encuentras con configuraciones iguales (BCVs, scripts, rman, veritas, HP, Tivoli, cartucheras virtuales, etc...). De manera personal, creo que lo más sencillo o fácil de llevar como DBA, es tener un software que administre la parte de backups y de restore de forma integrada con RMAN.

Suponiendo que tengamos Dataprotector, TSM, Netbackup, etc. integrado a RMAN, lo más sencillo es correr scripts de backup hacia canales de cinta de forma directa, mantener nuestras políticas de retención en RMAN y poder expirar y borrar de las cintas desde RMAN. Esto como DBAs nos da mucha visión de cómo se van llevando nuestros Backups, podemos generar los reportes necesarios con comandos sencillos de RMAN, etc...

Me he encontrado con algunos clientes, y recientemente a través de unos correos, con que mucha gente hace los respaldos a disco, una vez que el respaldo está en disco, se utiliza una cartuchera para llevarse el respaldo a cinta.

En este ejemplo en particular, imaginemos que el espacio en disco es reducido, y que sólo se mantiene el último backup en disco, y como se tiene respaldado en cinta la información, se pueden expirar y borrar los backups de disco anteriores.

Este es un ejemplo práctico de cómo llevar un backup, pero hay que tener varias consideraciones al hacerlos:

Debes de asegurarte de llevar un backup del controlfile. Recuerda que si tienes activado el backup automático de controlfile, y no incluyes el controlfile en tu backup, el resplado del controlfile pudier estar en otro PATH distinto a tus backupsets, considera añadir ese path a tu respaldo en cinta. Si ejecutas

RMAN> backup database include current controlfile;

El controlfile estará en el mismo path que tus backupsets.

Una vez expirado y eliminado el backup del día anterior, imaginemos que nos damos cuenta que un error de datos se tiene desde ayer y queremos recuperar la base de datos a un punto anterior (hace dos días), por lo cual el backup del día de hoy no nos sirve... ¿Qué se puede hacer para recuperar la información?, vamos a poner el ejemplo que se va a restaurar el respaldo de hace 2 días en un servidor nuevo para consultar la información sin afectar producción.

Como primer punto es, en un area de storage (en el server de desarrollo) hay que bajar el respaldo de cinta, eso lo puedes hacer con herramientas de sistema operativo o el software que usas.

Una vez que tienes tus backupsets en disco, existen 2 posibilidades, que hayas respaldado el controlfile o que no lo hayas respaldado.

En caso de que lo hayas respaldado, lo más sencillo es restaurarlo del backupset:

RMAN> SET DBID 524232147;

ejecutando el comando: SET DBID

RMAN> RUN
2> {
3>   RESTORE CONTROLFILE FROM 'D:\RESPALDO\TAG20080823T172009_48DQF74D_.BKP
4> } 

Este comando nos restaurará el controlfile contenido en el backupset (revisen sus logs de RMAN para poder saber cual es el backupset piece correcto), el destino del o de los controlfiles será el asignado en el parámetro control_files del archivo de inicialización (init o spfile).

Si no se respaldó el controlfile, se puede usar uno de producción, no importa que nuestro respaldo de hace 2 días no esté en el catálogo.

Una vez que ya tenemos un controlfile (el restaurado, o bien, el copiado de producción), tenemos 2 posibles escenarios:

El primero es que tengamos el controlfile del respaldo y que el path usado en el respaldo, sea el mismo usado para depositar los archivos en el server de desarrollo (en caso de Linux o Unix se puede usar un link simbólico). Si este es el caso, procedemos a montar la base de datos y empezar nuestro restore y recover:

RMAN> alter database mount;

base de datos montada
canal liberado: ORA_DISK_1

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS; 

Pero no siempre podemos tener la suerte de tener el mismo path, o tampoco contamos con un controlfile sin el registro de nuestro backup. ¿Qué podemos hacer en este caso?

Catalogar nuestro backup

No importa si se usa una base de datos de catálogo de rman o no, lo importante es que nuestro controlfile sepa que el respaldo de nuestra base de datos existe, la forma es la siguiente.

Reviso que no tenga un backup registrado, o por lo menos no uno que me sirva en mi controlfile:

RMAN> list backup of database;

RMAN>

Y ahora usando el controlfile actual de mi base de datos (restaurado o copiado), registro la copia del respaldo que tengo.

RMAN> CATALOG START WITH 'd:\RESPALDO';

buscando todos los archivos que coincidan con el patr¾n C:\oracle\product\flash_recovery_area\ORCL\BAC

Lista de Archivos Desconocidos para la Base de Datos
=====================================
Nombre de Archivo: d:\RESPALDO\respaldo.bkp

+Seguro que desea catalogar los archivos anteriores (introduzca SÝ o NO)? y
catalogando archivos...
catalogaci¾n realizada

Lista de Archivos Catalogados
=======================
Nombre de Archivo: d:\RESPALDO\respaldo.bkp

En este punto reviso que mi backup exista en la base de datos

RMAN> list backup of database;


Lista de Juegos de Copias de Seguridad
===================

Clave BS  Tipo LV Tama±o       Tipo de Dispositivo Tiempo Transcurrido Hora de Finalizaci¾n
------- ---- -- ---------- ----------- ------------ --------------------
56      Full    1.01G      DISK        00:02:17     23/08/08
        Clave BP: 58   Estado: AVAILABLE  Comprimido: NO  Etiqueta: TAG20080823T120433
        Nombre de Parte: d:\RESPALDO\RESPALDO.BKP
  Lista de Archivos de Datos en el juego de copias de seguridad 56
  Tipo de Archivo LV SCN Pto. Ctrl.    Hora de Punto de Control Nombre
  ---- -- ---- ---------- ------------------------ ----
  1       Full 4921504    23/08/08                 C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
  2       Full 4921504    23/08/08                 C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
  3       Full 4921504    23/08/08                 C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
  4       Full 4921504    23/08/08                 C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
  5       Full 4921504    23/08/08                 C:\ORACLE\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
  6       Full 4921504    23/08/08                 C:\CRYPTO\ORCL\DATAFILE\O1_MF_ORCL_42K6N37T_.DBF
  7       Full 4921504    23/08/08                 C:\PRUEBA01.DBF

Aquí puedo comenzar con mi restore desde mis backupsets recientemente registrados

RMAN> shutdown abort

RMAN> startup mount

conectado a la base de datos destino (no iniciada)
instancia Oracle iniciada
base de datos montada

Total del -rea Global del Sistema     314572800 bytes

Fixed Size                     1290328 bytes
Variable Size                142610344 bytes
Database Buffers             163577856 bytes
Redo Buffers                   7094272 bytes

RMAN> restore database;

Iniciando restore en 23/08/08
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=540 devtype=DISK

canal ORA_DISK_1: iniciando restauraci¾n del juego de copias de seguridad de archivos de datos
canal ORA_DISK_1: especificando archivo(s) de datos para restaurar del juego de copias de seguridad
restaurando el archivo de datos 00001 en C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
restaurando el archivo de datos 00002 en C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
restaurando el archivo de datos 00003 en C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
restaurando el archivo de datos 00004 en C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
restaurando el archivo de datos 00005 en C:\ORACLE\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
restaurando el archivo de datos 00006 en C:\CRYPTO\ORCL\DATAFILE\O1_MF_ORCL_42K6N37T_.DBF
restaurando el archivo de datos 00007 en C:\PRUEBA01.DBF
canal ORA_DISK_1: leyendo desde la parte de copia de seguridad d:\RESPALDO\RESPALDO.BKP

Consideración adicional, usen el comando set new name en caso de que deban restaurar a un path distinto.

Espero que les sea de utilidad, ya sea para clonar una base de datos o simplemente recuperar la información.

Query para ver lo que está sucediendo

Recientemente Tanel Poder en su blog publicó esta entrada. Vale mucho la pena el enfoque qué él le da.

Generalmente cuando me toca ir con algún cliente por algún tema de performance, siempre suele haber dos situaciones distintas:


1) Están por correr procesos pesados y se quiere una foto de todo lo que está sucediendo en el sistema.

2) El sistema da la apariencia de estar pasmado.

Para el primer caso, lo que puedo sugerir es la activación de statspack

SQL> @?/rdbms/admin/spcreate.sql

Creating Package STATSPACK...

Paquete creado.

No hay errores.
Creating Package Body STATSPACK...

Cuerpo del paquete creado.

No hay errores.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL> conn perfstat
Introduzca la contraseña:
Conectado.

SQL> exec statspack.snap(I_SNAP_LEVEL=>7);

A partir de este punto, dejo que corran todo lo que deben de correr, y después de unos 15 mins, o que los procesos han terminado, vuelvo a ejecutar

SQL> exec statspack.snap(I_SNAP_LEVEL=>7);

Genero los reportes diferenciales, y analizo la información

SQL> define begin_snap=1;
SQL> define end_snap=2;
SQL> @?/rdbms/admin/spreport.sql

Hay que recordar que toda la información es generalizada y agregada es decir, supongamos que tenemos lo siguiente:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
Event                                            Waits    Time (s)
----------------------------------------- ------------ -----------
CPU time                                                    15,000
db file scattered read                          21,156         830
control file sequential read                     1,261          10
....

Y esto en un periodo de 1 Hora entre snapshots, debemos de tomar en cuenta que la información es agregada.

En este ejemplo (totalmente ficticio), pareciera que la instancia tiene problemas de CPU, pero si pensáramos que el equipo cuenta con 32 procesadores, estaríamos viendo que en promedio cada CPU se ocupó 13.5% y el problema podría venir por otro lado.


Para las situaciones en donde el sistema aparenta estar pasmado, o se quiere saber lo que está sucediendo, suelo tener 3 sentencias SQL (la primera muy similar a la de Tanel)

SQL> select count(1), event
  2  from v$session_wait
  3  group by event
  4  order by 1 desc;

  COUNT(1) EVENT
---------- -------------------------------------
        19 enq: TM - contention
        12 rdbms ipc message
         1 jobq slave wait
         1 SQL*Net message to client
         1 smon timer
         1 pmon timer
         1 SQL*Net message from client

7 filas seleccionadas.

Esto nos dice que hay 19 sesiones sufriendo de encolamiento (pero lo mismo puede aplicar para cualquier wait != Idle)

SQL> select count(1), s.sql_hash_value, sw.event
  2  from v$session s, v$session_wait sw
  3  where s.sid=sw.sid
  4  group by s.sql_hash_value,sw.event
  5  order by 1 desc;

  COUNT(1) SQL_HASH_VALUE EVENT
---------- -------------- ----------------------------
        19     3864513861 enq: TM - contention
        11              0 rdbms ipc message
         1     2390948774 SQL*Net message to client
         1              0 smon timer
         1     3393152264 rdbms ipc message
         1              0 pmon timer
         1              0 jobq slave wait
         1              0 SQL*Net message from client

8 filas seleccionadas.

Y finalmente, podemos ir al Shared pool a ver el plan de ejecución

SQL>  select * from table(dbms_xplan.display_cursor(3864513861));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

HASH_VALUE  3864513861, child number 0
--------------------------------------
lock table hilos in exclusive mode

Plan hash value: 2876205575

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | DDL STATEMENT     |       |
|   1 |  TABLE ACCESS FULL| HILOS |
-----------------------------------

El último query sólo se puede hacer en instancias 10g+, en caso de nueve, se deberá buscar el plan de ejecución en V$SQL_PLAN.

Si se está usando 10g, tambien se puede evitar el acceso a la vista v$session_wait, ya que v$session contiene la información de eventos de espera.

SQL> select count(1) ,sql_hash_value, event
  2  from v$session
  3  group by sql_hash_value, event
  4  order by 1 desc;

  COUNT(1) SQL_HASH_VALUE EVENT
---------- -------------- -----------------------------
        19     3864513861 enq: TM - contention
        11              0 rdbms ipc message
         1              0 SQL*Net message from client
         1              0 smon timer
         1              0 pmon timer
         1     2593210877 SQL*Net message to client
         1              0 jobq slave wait
         1     3393152264 rdbms ipc message

8 filas seleccionadas.

Sesiones colgadas de Forms en E-business

Muy a menudo, me encuentro con clientes que quieren agregar muchos nodos aplicativos a un E-business suite porque tienen mucha carga en los nodos, y aparentemente no soportan más conexiones.

Sé que esto no va relacionado a base de datos completamente, pero para los Applications DBAs, suele ser un dolor de cabeza el estar revisando las cargas de CPU en los servidores de aplicaiones.

Me ha tocado administrar instancias de E-Business que tienen más de 2,000 usuarios concurrentes con sólo 2 servidores aplicativos, y el desempeño es excelente. Me ha tocado estar en ambientes donde la carga de un servidor de aplicación es altísima y sólo tiene 50 usuarios concurrentes.

La razón parece estar siempre ligada a procesos que se quedan en el limbo o en "stupid mode". Cada uno de estos procesos puede consumir hasta el 100% de un CPU, y si tenemos 10 de estos procesos, y sólo 6 procesadores, el servidor "estará corriendo como un perro con dos patas".



Hace un tiempo hice un script para facilitar la detección de estos procesos y finalmente eliminarlos con la tranquilidad de que no son procesos que existen en la aplicación.

sesiones_colgadas.sh

#!/usr/bin/ksh
##########################################
#   Script para eliminar las sesiones
#   colgadas de forms.
#
#   Valida que las sesiones de forms 
#   Consuman mucho CPU y lleven corriendo
#   mas de 5 minutos. Si cumplen estas 
#   caracteristicas, se evaluan a nivel 
#   base de datos, si no hay proceso
#   relacionado, el cliente se mata.
#   
#   
#   Elaborado por:  Hugo E. Contreras
#   Fecha:          Abril 10, 2008
#   
#   Resource IT
##########################################

#   Variables de ambiente
export ORACLE_HOME=/u01/oracle/PROD/apps/tech_st/10.1.2
export TWO_TASK=PROD
export USUARIO=monitor
export PASSWORD=******


for SerPid in `ps -eo pcpu,pid,etime,cmd|grep frmweb|sort -n|awk '$1 >10 && $3 > "05:00" {print $2}'`
do
ABC=`sqlplus -s $USUARIO/$PASSWORD << EOF
set head off
set pagesize 0
set feed off
  select count(1) from v\\$session where process ='$SerPid';
exit;
EOF`

echo "el Sid $SerPid tiene $ABC sesion(es) en la base de datos"

if [ $ABC -lt 1 ]
 then
  echo "Se procede a matar el proceso $SerPid"
  kill -9 $SerPid
fi

done

Hay muchas cosas a configurar en este script, y dependiendo del release del e-business, algo más puede cambiar, estas son algunas consideraciones que se deben de tener.

Si es 11i, el ORACLE_HOME a usar es el 8.0.6, en este caso el script está pensado para 12.0.3. En el caso de 11i, el proceso a buscar es f60webmx, para 12i es frmweb.

En caso de tener un RAC configurado como base de datos, lo primero es usar la vista GV$SESSION en lugar de V$SESSION. Algo adicional, es que se debe de evaluar el nombre del equipo, es decir

AND machine = `hostname`

Ya que pudiera ser que alguna instancia del RAC tuviera un proceso padre (gv$session.process) con el mismo número en una instancia distinta.

En caso de tener más de 1 application server, sea o no un RAC, también se debe poner la condición

AND machine = `hostname`

Esto es porque dos o más servidores de aplicación pudieran usar el mismo spid para un proceso de forms.

Alguna consideración en el caso de solaris; el comando

ps -eo pcpu,pid,etime,cmd

debe ser cambiado por

ps -eo pcpu,pid,etime,comm

Seguramente muchas otras cosas pueden cambiar entre HP-UX, Aix, Solaris o diferentes sabores de Linux, pero la idea es casi la misma:

Obtener la lista de procesos que contienen el ejecutable que nos interesa, buscar sólo aquellos que consumen más del 10% de 1 CPU y que lleven por lo menos 5 minutos activas.

Cuando todas estas condiciones se cumplen, se evalúa contra la base de datos para corroborar que el proceso hijo todavía existe, en caso de no existir, el proceso es eliminado a través de un kill -9.

El script se debe de ejecutar en el servidor de aplicación.

Conexiones en Windows vs. Linux

Recientemente queriendo hacer unas pruebas (sobre algo que no pude demostrar), me pregunté sobre las conexiones en windows vs. conexiones en Linux.

De forma arquitectónica ambas son diferentes, en windows, la arquitectura está basada en threads, los threads son objetos dentro de un proceso que ejecutan instrucciones, y permiten la ejecución "simultanea" de instrucciones dentro del proceso, para que código se ejecute en diversos procesadores. En teoría, las ventajas de este modelo son:

1.- Context Switches más rápidos.

2.- Implementación más sencilla de SGA (ya que no requiere un shared memory).

3.- Creación más rápida de conexiones (ya que crear un thread es más rápido que crear un proceso).

4.- Decremento en el uso de memoria. Esto es porque se pueden compartir más estructuras de memoria.

Estoy muy de acuerdo en la teoría con todo lo anterior, sin embargo, como diría Jonathan Lewis "Just because it's written, it doesn't mean it's true...".

Para hacer las pruebas, hice un pequeño código en Perl que abre "n" número de conexiones a la base de datos y mide el tiempo que tarda en realizar las conexiones.

el código es el siguiente:

conecta.pl

use DBI;
use Time::HiRes qw(gettimeofday tv_interval);
$cont = 100;
$t0=[gettimeofday];
for ($inicio=1;$inicio<=$cont;$inicio++){
    $dbh[$inicio] = DBI->connect( 'dbi:Oracle:HECG',
                            'system',
                            '******',
                          { RaiseError => 1, AutoCommit => 0 }
                          ) || die "No se pudo conectar a la base de datos: $DBI::errstr";
 #$sql = qq{select count(1) from v\$database};
 #$dbh[$inicio]->do($sql);
 }
$elapsed = tv_interval ($t0);
print "Las ".$cont." conexiones se realizaron en ";
print $elapsed ." segundos\n\n\n";
print "presiona cualquier tecla para terminar... ";
$dummy = < STDIN> ;
for ($fin=1;$fin<=$cont;$fin++){           
$dbh[$fin]->disconnect;
}

¿Por qué lo hice en Perl?, por dos sencillas razones:

1.- No soy programador, y decidí no complicarme la vida.
2.- Porque quería ejecutar el mismo código en Linux que en Windows.

Antes de ejecutar mi script, mi proceso Oracle se encontraba de esta forma:



Ejecuté el código para 100 conexiones e windows, y los resultados son los siguientes

C:\oracle\product\10.2.0\NETWORK\ADMIN>%PH%\perl.exe conecta.pl
Las 100 conexiones se realizaron en 4.53125 segundos

presiona cualquier tecla para terminar...

Vemos que el proceso incrementó el número de threads en casi 100 (quizá en el inicio se tenían algunos jobs corriendo, y por eso subió de 23 a 122).

El consumo de memoria fue de 70 mb. por las 100 sesiones, lo cuál significa que cada sesión inicialmente ocupa 700k.

El tiempo que tardaron las 100 conexiones fue de 4.5 segundos.

Ahora el código del script se lanzó en Linux, y el resultado fue el siguiente:

$ perl conecta.pl
Las 100 conexiones se realizaron en 3.14312 segundos

presiona cualquier tecla para terminar...

Las conexiones se ejecutaron mucho más rapidas en Linux que en Windows. Intentamos con 200 y 400 conexiones.

Para 200 el tiempo fue lineal:

Windows - 9.02403 Segundos
Linux   - 6.32876 Segundos

La sorpresa en 400 conexiones fue la siguiente:

Windows - 18.23311 Segundos
Linux   - 32.75434 Segundos

El tiempo fue lineal para windows, pero para linux no, la razón...

En linux cada sesión ocupaba 1.8 mb de memoria aproximadamente (720 mb sólo en sesiones), más el tamaño del SGA ~200Mb, más la memoria ocupada por el sistema operativo... nos daban un total mayor a la memoria física del equipo, y por esta razón el equipo empezó a hacer uso de Swap.

Lo cual nos lleva a concluir lo siguiente:

1.- Efectivamente la arquitectura de Windows utiliza mejor los recursos de memoria, ya que usa threads en lugar de procesos.

2.- La creación de threads NO ES MÁS RÁPIDA que la creación de procesos.

¿Qué arquitectura recomiendo yo?, definitivamente depende de muchos factores, pero se deben de tener en cuenta todos los factores antes de decidir la arquitectura correcta.

En un sistema que no cuenta con mucha memoria física el servidor de base de datos y que va a recibir muchas conexiones dedicadas, podría decir que un windows sería algo bueno, sin embargo, si las conexiones no van a ser persistentes, esta arquitectura podría traer problemas con memoria "non paged pool".

Por el otro lado, si se usa un equipo Linux, el equipo podría quedarse muy rápido sin memoría física, y el sistema operativo se degradaría en cuanto a desempeño.

Sé que hay muchas cosas más que probar, quizás el uso de Bequeath en lugar de SQL*Net, el comportamiento a través de Shared Servers, etc., pero eso se queda para otro post.