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.