Performace Tuning en E-Business Suite (segunda parte)

Esta entrada va muy relacionada con el post anterior, ya que explicaré los diferentes estados por los que puede pasar un concurrente y qué se puede hacer para disminuir los tiempos de espera.

Primero que nada, recordemos que los administradores de concurrentes son unas colas con reglas definidas para ejecuciones de programas concurrentes.

Existen diferentes tipos de concurrentes y esta nota sólo explicará los 3 más comunes.

Internal Manager

Éste manager es quizás el más importante de todos, ya que funciona como el orquestador de todos los demás managers. Dentro de sus actividades más importantes están:

Interactuar con el administrador (subir y bajar concurrentes).
Subir y bajar el resto de administradores concurrentes.
Reiniciar procesos fallidos de administradores concurrentes.
Monitoreo básico de estado.

Algo interesante es que, si este proceso concurrente llegara a caerse, los demás administradores de concurrentes seguirían procesando requests, sólo requests relacionados a los queues dejarán de funcionar (stop, verify, etc...)

Conflict Resolution Manager

Debido a que los programas concurrentes se pueden definir con ciertas incompatibilidades, este concurrente es el que se encarga de tratar todas estas reglas de incompatibilidad y de liberar a los administradores de concurrentes los programas concurrentes que hayan pasado las reglas de incompatibilidad. Un ejemplo claro es el siguiente:

Si tuviéramos un concurrente que insertara datos en una tabla y otro concurrente que borrara datos de la misma, quisiéramos una incompatibilidad para que no corrieran al mismo tiempo para evitar una pérdida de registros (un borrado mientras insertamos).

Este manager, recibe todos los concurrentes con incompatibilidad (STATUS_CODE=Q) y evita que los administradores de concurrentes estándar los ejecuten, al revisar que un concurrente puede correr libremente, cambia el estado del request a pendiente (STATUS_CODE=I), es hasta este momento que un administrador estándar lo puede procesar.


Standard Manager
Los administradores de concurrentes estándar son los que realmente procesan los programas concurrentes solicitados. Leen de la tabla FND_CONCURRENT_REQUESTS todo lo que esté pendiente de procesar STATUS_CODE=I.



Es importante notar algunas de las propiedades de los administradores de concurrentes antes de seguir adelante.

Processes: Es el número de procesos que tendrá el administrador de concurrente. Entre más procesos se tengan definidos, más concurrentes se podrán ejecutar al mismo tiempo. Es importante recordar que cada proceso es una sesión a la base de dtaos, y que en caso de estar ejecutando un programa concurrente, es una sesión activa.

Sleep Time: El administrador de concurrente, al ser una cola, deberá de tener un tiempo de poleo para saber qué es lo que tiene por procesar, este parámetro es el tiempo en segundos que realiza el poleo. Un sleep time pequeño, representa esperas pequeñas, sin embargo, puede tener un overhead muy grande por queries muy constantes a la tabla de FND_CONCURRENT_REQUETS.

Cache Size: Este parámetro es el que nos indica la cantidad de requests por procesar que una cola puede guardar en memoria sin tener que ir a buscar nuevamente a la tabla. Es decir, cuántos programas es capaz de procesar un concurrente sin incurrir nuevamente en un sleep time.


Todo lo anterior nos sirve para poder diagnosticar bien un problema de desempeño en los programas concurrentes.

Usando el query en la nota anterior, nos da un resultado similar al siguiente:

Aplicación: GL
Programa: Journal Import
Ejecutable: GLLEZL
Número de ejecuciones: 8770
Tiempo Tanscurrido: 05:57:11
Tiempo Promedio: 00:00:02
Tiempo Maximo: 00:00:10
Tiempo Mínimo: 00:00:00
Esperas: 20d 07:09:45
Espera Promedio: 00:05:33

A simple vista, podemos ver lo siguiente:

El concurrente cuando se ejecuta, corre muy rápido, un tiempo máximo de 10 segundos y en promedio 2 segundos, por lo cual, no existe un problema de desempeño.

En este caso en particular vemos que se tienen más de 20 días de espera para el procesamiento de los cocnurrentes. Recordemos que este tiempo es el sumarizado de todos los concurrentes esperando submitidos, es decir, si se submiten 101, 1 está corriendo, y los otros 100 cada segundo suman 100 segundos de espera.

¿Qué ocasiona esta espera tan elevada? En este caso, es la incompatibilidad del Journal import. ¿Por qué?

Al tener una incompatibilidad (incompatible con él mismo), el programa concurrente se calndariza con estado incompatible. En este punto, y en el peor de los casos, debemos de esperar el sleep time del conflict resolution manager para que lo evalúe y lo pase a un estado de pending normal. Una vez que está en ese estado, en el peor de los casos, debemos de esperar al sleep time del standard manager para que lo tome y lo procese. En este ejemplo, si los sleep times de los managers están a 60 segundos, el tiempo de ejecución es de 2 segundos, sin embargo el tiempo de procesamiento + esperas es de 2 minutos y 2 segundos.

Aquí lo que vale la pena es identificar las esperas más fuertes y ver de qué forma podemos atacar el problema real. Una probable solución en este caso, sería la de quitar la incompatibilidad consigo mismo. y crear una cola especializada que sólo procese journal imports y que tenga sólo 1 proceso, de esta forma se evita que corran dos a la vez. Al hacer esta cola especializada y eliminar la incompatibilidad, nos evitamos el sleep time del conflict resolution manager. Adicionalmente, podemos poner un sleep time de nuestra nueva cola en 10 segundos, y un caché de por lo menos 20.

Haciendo esto, el tiempo de espera para el procesamiento, se puede reducir en un 80%. En este caso es un Journal Import, pero imaginemos que fuera un posteo, o simplemente un concurrente que tiene a un usuario esperando a que termine, los beneficios pueden ser muy grandes.

Algo más que se pudiera revisar, es si realmente se necesitan más de 8,000 ejecuciones del concurrente, esto, aunque es un tema más funcional, muchas veces vale la pena cuestionarlo, hay ocasiones en que de 35,000 ejecuciones diarias de un concurrente, he logrado que se disminuya a 10 ejecuciones al día por tan sólo cuestionar e ir un poco más a fondo en la lógica del negocio.

Los concurrentes que tengan pocas ejecuciones y un alto consumo de tiempo de ejecución se les debe de habilitar un trace y hacer un tuning de manera ordinaria, si es un estándar de Oracle, segúramente ya existe un parche.

En general, es una forma muy sencilla de empezar con un tuning a los concurrentes de e-business, ya que nos muestra de forma clara la pérdida de tiempo en la ejecución de concurrentes.


En el siguiente post hablaré un poco sobre los traces a reportes.

Performace Tuning en E-Business Suite (primera parte)

Un comentario en Perfiles de Desempeños preguntaba sobre el performance Tuning de E-Business Suite y cómo se puede realizar ya que es una aplicación muy cerrada; así que decidí escribir esta entrada como un primer intento en acercarnos a la mejora del desempeño de un E-business suite.

Me ha tocado trabajar con versiones de Oracle Apps desde 10.4, pasando por 10.7, 11.0.3, 11i y recientemente con R12. Y lo que me ha tocado observar es que Oracle siempre nos ha facilitado la vida en cada nuevo release.

Primero que nada hay que empezar por entender la arquitectura, la cual (en su mínimo) está formada por:

Servidor de Base de datos
Servidor de Formas
Servidor de Reportes
Servidor Apache

Hay que tener en cuenta que, en cada uno de estos servicios o capas, pudiéramos encontrar una posibilidad de mejora, pero generalmente, el punto más crítico es la relación de Reportes - Base de Datos o Formas - Base de datos.

Oracle suele entregar un producto probado, y generalmente sin problemas fuertes de desempeño, pero esto sólo sucede en un ambiente "Fresh Vanilla", sin tener en cuenta todas las interfaces y/o desarrollos que se deben de hacer al sistema. Creo yo que en mi experiencia, el 95% de los problemas de desempeño en ambientes de E-Business suite, suelen ser causados por desarrollos, o aplicaciones que acceden al mismo servidor de base de datos.

Ahora, ya que tenemos muy customizado nuestro ambiente de E-business suite, ¿qué podemos hacer para ver qué ocasiona el problema de desempeño?

Hace aproximadamente 10 años, encontre en internet un white paper en www.oncalldba.com que me abrió la mente sobre el performance de concurrentes en e-business, desgraciadamente el white paper se convirtió después en libro, y no me ha sido posible conseguirle.

Desde entonces, tengo un query (que he modificado varias veces) que me da un punto de entrada para revisar que es lo que realmente sucede.

concurrentes.sql

SELECT --q.concurrent_queue_name cola,
 f.application_short_name app                                 ,
 SUBSTR(tl.user_concurrent_program_name, 1, 40) descripcion   ,
 SUBSTR(p.concurrent_program_name, 1, 20) programa_concurrente,
 COUNT(*) ejecuciones                                         ,
 CASE
  WHEN SUM(actual_completion_date       -actual_start_date) > 1
  THEN TRUNC(SUM(actual_completion_date -actual_start_date))
   || 'd '
   || TO_CHAR(to_date(MOD(ROUND(SUM(actual_completion_date -actual_start_date) *24 *60 *60), 86400), 'sssss'), 'hh24:mi:ss')
  ELSE TO_CHAR(to_date(ROUND(SUM(actual_completion_date    -actual_start_date) *24 *60 *60), 'sssss'), 'hh24:mi:ss')
 END Transcurrido ,
 CASE
  WHEN AVG(actual_completion_date       -actual_start_date) > 1
  THEN TRUNC(AVG(actual_completion_date -actual_start_date))
   || 'd '
   || TO_CHAR(to_date(MOD(ROUND(AVG(actual_completion_date -actual_start_date) *24 *60 *60),86400), 'sssss'), 'hh24:mi:ss')
  ELSE TO_CHAR(to_date(ROUND(AVG(actual_completion_date    -actual_start_date) *24 *60 *60), 'sssss'), 'hh24:mi:ss')
 END promedio ,
 CASE
  WHEN MAX(actual_completion_date       -actual_start_date) >1
  THEN TRUNC(MAX(actual_completion_date -actual_start_date))
   ||'d '
   || TO_CHAR(to_date(mod(ROUND(MAX(actual_completion_date -actual_start_date) *24 *60 *60),86400), 'sssss'), 'hh24:mi:ss')
  ELSE TO_CHAR(to_date(ROUND(MAX(actual_completion_date    -actual_start_date) *24 *60 *60), 'sssss'), 'hh24:mi:ss')
 END MAX ,
 CASE
  WHEN MIN(actual_completion_date       -actual_start_date) >1
  THEN TRUNC(MIN(actual_completion_date -actual_start_date))
   ||'d '
   || TO_CHAR(to_date(mod(ROUND(MIN(actual_completion_date -actual_start_date) *24 *60 *60),86400), 'sssss'), 'hh24:mi:ss')
  ELSE TO_CHAR(to_date(ROUND(MIN(actual_completion_date    -actual_start_date) *24 *60 *60), 'sssss'), 'hh24:mi:ss')
 END MIN,
 CASE
  WHEN SUM(actual_start_date       -requested_start_date) >1
  THEN TRUNC(SUM(actual_start_date -requested_start_date))
   ||'d '
   || TO_CHAR(to_date(MOD(ROUND(SUM(actual_start_date -requested_start_date) *24 *60 *60), 86400), 'sssss'), 'hh24:mi:ss')
  ELSE TO_CHAR(to_date(ROUND(SUM(actual_start_date    -requested_start_date) *24 *60 *60), 'sssss'), 'hh24:mi:ss')
 END esperas ,
 CASE
  WHEN AVG(actual_start_date       -requested_start_date) > 1
  THEN TRUNC(AVG(actual_start_date -requested_start_date))
   ||'d '
   || TO_CHAR(to_date(mod(ROUND(AVG(actual_start_date -requested_start_date) *24 *60 *60),86400), 'sssss'), 'hh24:mi:ss')
  ELSE TO_CHAR(to_date(ROUND(AVG(actual_start_date    -requested_start_date) *24 *60 *60), 'sssss'), 'hh24:mi:ss')
 END prom_espera
FROM fnd_application f                ,
 applsys.fnd_concurrent_requests r    ,
 applsys.fnd_concurrent_queues q      ,
 applsys.fnd_concurrent_processes PROC,
 apps.fnd_concurrent_programs p       ,
 apps.fnd_concurrent_programs_tl tl
WHERE r.program_application_id = p.application_id
AND tl.application_id          = f.application_id
AND tl.concurrent_program_id   = p.concurrent_program_id
AND r.concurrent_program_id    = p.concurrent_program_id
AND tl.LANGUAGE                = 'US'
AND r.status_code                        IN('C', 'G', 'E')
AND actual_completion_date BETWEEN trunc(sysdate) AND sysdate
 -- AND actual_completion_date BETWEEN to_date('17-08-2008 09:00:00',   'dd-mm-yyyy hh24:mi:ss')
 --AND to_date('18-08-2008 08:00:00',   'dd-mm-yyyy hh24:mi:ss')
AND p.application_id         = f.application_id
AND r.program_application_id = f.application_id
AND
 (
  proc.concurrent_queue_id = q.concurrent_queue_id
 AND queue_application_id  = q.application_id
 )
AND r.controlling_manager = proc.concurrent_process_id
GROUP BY
 --q.concurrent_queue_name,
 f.application_short_name ,
 p.concurrent_program_name,
 tl.user_concurrent_program_name
ORDER BY SUM(actual_completion_date -actual_start_date) DESC;

Este query nos arroja una salida similar a la siguiente:

APP    DESCRIPCION                               PROGRAMA_CONCURRENTE  EJECUCIONES  TRANSCURRIDO  PROMEDIO  MAX      MIN       ESPERAS      PROM_ESPERA 
------ ----------------------------------------- --------------------- ------------ ------------- --------- -------- --------- ------------ ------------
AR     Autoinvoice Import Program                RAXTRX                592          10:29:47      00:01:04  00:03:04 00:00:27  02:16:50     00:00:14    
WSH    Pick Selection List Generation - SRS      WSHPSRS               17951        09:56:04      00:00:02  00:00:30 00:00:00  4d 15:58:23  00:00:22    
BOM    Copy Item Costs Across Organizations      CMCCOC                81           06:54:56      00:05:07  00:06:33 00:03:00  00:32:21     00:00:24    
ALR    Check Event Alert                         ALECTC                13770        05:57:11      00:00:02  00:00:10 00:00:00  5d 07:09:45  00:00:33    
ONT    Order Import                              OEOIMP                1469         04:17:02      00:00:10  00:05:35 00:00:00  16:57:16     00:00:42    
CMX    Custom Pick Slip Report                   XXCMX_WSHRDPIK        948          02:17:22      00:00:09  00:00:21 00:00:07  00:46:31     00:00:03    
FND    Workflow Background Process               FNDWFBG               1214         02:09:42      00:00:06  00:06:28 00:00:00  00:40:21     00:00:02    
FND    Purge Concurrent Request and/or Manager   FNDCPPUR              5            01:31:51      00:18:22  00:27:28 00:06:57  00:12:58     00:02:36    

Con esta información podemos saber qué es lo que realmente está sucediendo en cuanto programas concurrentes se refiere.

En un siguiente Post analizaré a detalle lo que cada uno de estos campos significan y la forma de atacarlos.

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".