TKPROF y el parámetro SYS=Y

Conozco muchas personas que al ejecutar la herramienta TKPROF, lo hacen con el parámetros SYS=N para que no se incluyan en el archivo de salida las consultas recursivas que realiza internamente la base de datos. Si bien en algunos casos se hace ésto para que el archivo de salida no contenga información en exceso, muchas veces cuando buscamos la causa de un determinado problema, si colocamos SYS=N lo único que estaremos logrando será "ocultar" el causante de ese problema.

Hace unos días, investigando un problema de performance en un proceso de un cliente, ejecuté la herramienta TKPROF con el parámetro SYS=Y (que es la opción por defecto) y noté que había una consulta recursiva que estaba leyendo millones de bloques de datos!

La consulta es la siguiente:

select min(bitmapped)
from
 ts$ where dflmaxext =:1 and bitand(flags,1024) =1024

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     7915      0.08       0.07          0          0          0           0
Execute   7915      0.17       0.14          0          0          0           0
Fetch    15830      4.24       4.23          0    3245150          0        7915
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    31660      4.50       4.45          0    3245150          0        7915

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=410 pr=0 pw=0 time=691 us)
      2   TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=659 us)

Esta consulta es interna de la base de datos. Debido a que el proceso se ejecuta con el usuario APPS y debido a que a éste usuario durante su creación se lo asignó dentro de un grupo de tablespaces temporales (new feature en 10g) llamado TEMP (que contienen los tablespaces TEMP1 y TEMP2), Oracle necesita ejecutar ésta consulta para determinar cuál de los dos tablespaces es el mejor para realizar la operación de sort que está requiriendo el proceso. Como pueden ver, esta consulta es ALTAMENTE costosa y consume muchos recursos del sistema ya que la consulta lee 3,245,150 bloques (410 * 7915) y el proceso en total lee 4,452,813 bloques! Por lo cual, ésta consulta está leyendo más del 70% del total de todos los bloques de datos del proceso!!!

Para solucionar éste problema, lo que hice fue modificar el usuario APPS, sacarlo del grupo de tablespaces temporales TEMP y asignarle directamente el tablespace TEMP2 que tiene un tamaño de 39 GB a comparación del TEMP1 que tiene sólo 6 GB. Al hacer éste cambio, lo que logramos fue que Oracle deje de ejecutar esa consulta debido a que ya no tiene necesidad de determinar cual de los dos tablespaces es el mejor para ejecutar una determinada operación de sort.

Al ejecutar nuevamente el proceso, podemos ver el resultado de la solución implementada:

select min(bitmapped)
from
 ts$ where dflmaxext =:1 and bitand(flags,1024) =1024

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        410          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        410          0           1

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=410 pr=0 pw=0 time=3045 us)
      2   TABLE ACCESS FULL TS$ (cr=410 pr=0 pw=0 time=2952 us)

Luego que se realizó éste cambio, se notó una mejora muy importante en cuanto a la performance y se notó una disminución drástica de la utilización de los recursos del sistema.

Tengan siempre presente la importancia del parámetro SYS=Y al momento de ejecutar la herramienta TKPROF.

Por cierto, esta consulta es un Bug! Hay que aplicar el Parche 5455880 para solucionar éste grave problema de performance.