jueves, 15 de octubre de 2009

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.

2 comentarios:

Ivan (Ecuador) dijo...

Saludos Leonardo, he seguido tu blog casi en su totalidad, y es una herramienta llena de conocimientos muy buenos, yo trabajo con Oracle 10gR2 y quiera aprender a optimizar sentencias sql, por ejemplo tengo un tabla PRUEBA que tiene 3 campos: ID, NOMBRE, FECHA_INGRESO, en la cual se guarda en el campo FECHA_INGRESO la fecha de ingreso del registro con la fecha del sistema con el SYSTIMESTAMP. Ahora luego de cargar la informacion quiero ver los registro que se insertaron en una fecha especifica y ahi es que yo he visto normalmente la siguiente sql en la clausula WHERE:
WHERE TRUNC(FECHA_INGRESO) = TO_DATE('20-05-2009','DD-MM-YYYY') Asumiendo que el campo FECHA_INGRESO es parte de la clave primaria. Esta correcto eso?.
De igual manera veo bastantes WHERE con subconsultas o la clausula IN... Hay una forma de mejorar las consultas para evitar que se demoren tanto?.
Muchas gracias por la atencion a este mensaje.

Leonardo Horikian dijo...

Hola Ivan,

Para responder a ésta pregunta necesitaría mas información. Por ejemplo, si los 3 campos son parte de la clave primaria, tendria que saber cómo está compuesta (si con sólo estos 3 campos o si hay más) y el orden de los campos en la clave primaria. Tambien necesitaria ver la consulta completa que quieres ejecutar ya que ésto hará que se modifique el tipo de acceso a los datos.

Más alla de esto, y como regla general, no conviene utilizar funciones sobre columnas indexadas (a menos que sea completamente necesario) ya que deshabilitan el uso de algún índice que contenga a esa columna. En caso de que sea necesario el uso de funciones sobre columnas, podríamos optar por la creación de un índice de función para que podamos acceder por índice.

De todas maneras, me gustaría que me detallaras el escenario de pruebas que estas utilizando para poder decirte con más certeza lo que te convendría utilizar para tu caso en particular.

Saludos