CUANDO LOS LOBS AFECTAN EL DESEMPEÑO DE LA BASE DE DATOS

Hola amigos !

El siguiente caso  (que no pretende ser una guía para la reubicación de LOBS)  fue originado por el llamado de un cliente reportando  problemas de lentitud de una base de datos Oracle 11.1.0.7 en configuración 'RAC' de cuatro nodos, con un aplicativo 'Oracle Business Process Management' operando en ambiente productivo. La primera actividad fue la obtención de un reporte de rendimiento por medio de la utilería 'AWR'  ejecutando el script 'awrrpti.sql' por cada nodo del cluster. 

Una vez generado el reporte, en la sección 'Top 5 Timed Foreground Events' del 'AWR' nos aparecio el evento 'enq HW -contention'  en todos los nodos del cluster, el cual no es muy común en este tipo de reportes: (solo muestro el top de un nodo)

Top 5 Timed Foreground Events

Event                                     Waits          Time(s)           Avg wait (ms)       %DB time    Wait Class

enq HW -contention           586,913         14,728                 25                        45.61       Configuration
db file sequential read      1,272,487          5,816                   5                        18.01        User I/O
DB CPU                                                         4,442                                            13.69
enq TX - row lock cont         3,973            1,310                330                         4.06        Application
direct path read                  948,724               943                    1                         2.92         User I/O

El evento 'enq HW -contention' nos hace referencia  a la 'marca de agua' o 'high water mark', la cual indica al manejador en que punto existen bloques libres y usados en un segmento de datos.  Que este evento aparezca en el 'Top 5'  indica que existe  un alto número de actualizaciones en algún segmento.  La nota Doc ID 9801919.8 de 'metalink' nos indica lo siguiente:

            "enq: HW - contention" may be seen for segments that have heavy  concurrency

              and often need to add an extent due to repeated SEG$ updates of dictionary data" 

Otras notas de 'metalink' nos sugieren que puede existir un problema con actualizaciones en campos tipo 'LOBs' (Large Objects) que contienen información no estructurada como archivos, imagenes o 'XMLs' entre otros.  Continuamos con  la búsqueda del ´culpable' de este evento en los 'tops' de las sentencias 'SQL'  en el reporte 'AWR'

En la sección 'sqls ordenados por tiempo de ejecución' nos aparece en primer lugar un 'UPDATE' sobre la tabla 'PPROCINSTANCE' el cual de forma notable ocupa el 75.91% del tiempo de la base de datos (por cuestiones de formato no incluí todas las columnas del 'AWR')

Elapsed Time(s)  CPU Time(s)   Executions   %Total DB Time     SQL text

  24,247                  1,259              84,911              75.91                        UPDATE PPROCINSTANCE SET .....
    1,832                     109              12,881                 5.74                        INSERT INTO PPROCINSTANCE ....

Continuamos en la sección 'sql ordenados por tiempo de CPU requerido'  y nuevamente aparece la sentencia 'UPDATE'  sobre la tabla 'PPROCINSTANCE':

CPU Time(s)   Elapsed Time(s)    %Total   %Total DB Time         SQL text

   1,259                 24,247                  34.20             75.91                       UPDATE PPROCINSTANCE SET ..
      174                      624                     4.73                1.95                      SELECT PPROCESSID, INSTID.... 

En la sección 'sql  ordenados por accesos a memoria' el 'UPDATE' de la tabla 'PPROCINSTANCE' aparece nuevamente

Buffer Gets      Gets per Exec   %Total     CPU Time (s)         SQL text

37,273,368          3,727.34             31.60        113.22                    DELETE FROM PCORRELATION ...
35,175,421             414.26             29.82      1259.44                    UPDATE PPROCINSTANCE SET ....

La sentencia tiene condiciones para los campos 'processid', 'instid' y 'threadid'. Sospechamos que puede existir un 'full scan'. Sin embargo, al revisar el 'explain plan'  se valida que el acceso es vía índice (aqui les debo el explain plan :-( )

     UPDATE pprocinstance SET (varios campos)..WHERE processid= ? AND instid= ? AND threadid=?

Para obtener el 'explain plan' sin necesidad de ejecutar nuevamente la sentencia, es muy útil el 'package' 'dbms_xplan.display_awr'.  Con el  'id query' obtenido en el reporte  'AWR'  se agrega como parámetro (en este caso el id 'g1jx24n27sykx' ) en el llamado del ´package'.

     SELECT plan_table_output FROM table (dbms_xplan.display_awr('g1jx24n27sykx'));

El siguiente paso es verificar si existe un 'LOB' en la tabla PPROCINSTANCE por medio de una consulta a la tabla  'DBA_LOBS'.   Identificamos al 'LOB'  'SYS_LOB0000108088C00003$$'  

     SELECT SUBSTR(table_name,1,40),  SUBSTR(segment_name, 1,30) ,  SUBSTR(tablespace_name,  1,30) 
           FROM dba_lobs 
       WHERE table_name = 'PPROCINSTANCE'
  
     SUBSTR(TABLE_NAME,1,40)                SUBSTR(SEGMENT_NAME,1,30)     SUBSTR(TABLESPACE_NAME,1,30)
       -------------------------------------------------       ------------------------------------------------    ---------------------------------------------------

          PPROCINSTANCE                                  SYS_LOB0000108088C00003$$                      USERS

Ahora que conocemos el segmento tipo 'LOB',  vamos a buscar en el reporte 'AWR' para validar si realmente tiene un impacto en el desempeño de la base de datos. En la sección de 'segmentos con más lecturas a disco' aparece el 'LOB'  de la tabla  PPROCINSTANCE con el 78% del total !

             Object Name                                               Obj. Type    Physical Writes    %Total DB Time   

           SYS_LOB0000108088C00003$$            LOB               993, 397                    73.40 
           SYS_LOB0000108088C00008$$            LOB                  31,622                      2.34 

Verificamos que índices tiene la tabla 'PPROCINSTANCE'  con la siguiente consulta. La tabla tiene tres índices que se encuentran en el tablespace 'USERS' que es el mismo que el utilizado por el 'LOB'.

            SELECT SUBSTR(index_name, 1,20), substr(tablespace_name,1,30) FROM dba_indexes
               WHERE table_name = 'PPROCINSTANCE';

             SUBSTR(INDEX_NAME,1,          SUBSTR(TABLESPACE_NAME,1,30)
            -------------------------------------          ------------------------------------------------
                   INSTPART                                                   USERS
                   ACT                                                               USERS
                   ASSIGPART                                                USERS

Ahora validamos el tablespace por default para la tabla PPROCINSTANCE  y no resulta una sorpresa que también sea USERS. Por alguna razón,  se generaron los datos, índices y lobs de la tabla maestra PPROCINSTANCE en el mismo tablespace, lo que genera problemas de contención.

          SELECT tablespace_name FROM dba_tables WHERE table_name = 'PPROCINSTANCE';

                  TABLESPACE_NAME
                  ------------------------------
                          USERS

La guía de Oracle 'Performance Tuning for Oracle Business Process Management Suite 10g' nos recomienda utilizar tablespaces independientes para los segmentos 'LOBS':

                     ' Use Separate Tablespaces for BLOBs If you are storing large

                        BLOBs in the database, you should consider a separate

                        tablespaces for the BLOBs'

Platicando con el cliente, le comentamos la necesidad de particionar la tabla y distribuirla en diferentes tablespaces. El regenerar la tabla PPROCINSTANCE impacta al resto del modelo de datos, implicando la recompilación de todos los objetos relacionados como stored procedures y packages entre otros. Por ser ambiente productivo, el cliente no desea que la tabla se vuelva a generar. Nos da como opción redistribuirla en diferentes tablespaces, pero sin regenerar ningún objeto. 

Para redistribuir la tabla PPROCINSTANCE se van a a generar tres tablespaces: para datos, índices y uno dedicado al segmento 'LOB'

                   CREATE TABLESPACE TBS_DATOS_PROC DATAFILE  '+BDBPMDATA' size 2048M;  

                   CREATE TABLESPACE TBS_IND_PROC DATAFILE   '+BDBPMINDEX' size 2048M;       

                   CREATE TABLESPACE TBS_LOBS_PROC DATAFILE  '+BDBPMLOG' size 10,000M   

Previo al proceso de reubicación es recomendable un 'full backup' de la base de datos y generar un listado que contenga el estatus de todos los objetos de la base de datos con la siguiente sentencia:

              SELECT object_name, object_status FROM dba_objects;

Una vez generados los tablespaces dedicados a la tabla PPROCINSTANCE es necesario reubicar los datos con la  siguiente secuencia :

1. Reubicar los datos de la tabla al tablespace 'TBS_DATOS_PROC'
2. Reubicar los índices de la tabla TBS_IND_PROC  al tablespace TBS_IND_PROC'
3. Reubicar el 'LOB'  ubicado en el campo INSTANCEDATA al tablespace TBS_LOBS_PROC
4. Reconstruir los índices inválidos

Ejecutamos el primer paso al reubicar los datos  de la tabla al tablespace 'TBS_DATOS_PROC'. Este paso va a invalidar todos los índices asociados a la tabla.

           ALTER TABLE OBPMENGORDERS.PPROCINSTANCE MOVE TABLESPACE TBS_DATOS_PROC;

 Continuamos con el segundo paso que es reubicar los índices al nuevo tablespace 'TBS_IND_PROC':

           ALTER INDEX OBPMENGORDERS.INSTPART REBUILD TABLESPACE TBS_IND_PROC;

              ALTER INDEX OBPMENGORDERS.ACT REBUILD TABLESPACE TBS_IND_PROC;

              ALTER INDEX OBPMENGORDERS.ASSIGPART REBUILD TABLESPACE TBS_IND_PRO;

Realizamos la etapa de reubicación del lob del campo INSTANCEDATA.  Por ser únicamente un campo el reubicado, no podemos utilizar la opción  'MOVE TABLESPACE'. Utilizaremos la opción 'MOVE LOB' indicando el campo a mover. Esta opción es la más lenta de todas, por lo que si se desea realizar una reubicación de 'LOBS', es recomendable realizar una prueba en ambiente pre-productivo para calcular ventanas de tiempo para el mantenimiento.

   ALTER TABLE OBPMENGORDERS.PPROCINSTANCE                                                                                                                                               MOVE LOB(INSTANCEDATA) STORE AS (TABLESPACE TBS_LOBS_PROC);

En caso de que el campo 'LOB'  a reubicar sea del tipo 'XML Type',  se puede utilizar el siguiente comando
donde se incluye la sentencia 'XMLDATA'

 ALTER TABLE xxxxxx move lob (CAMPO.XMLDATA) store as (TABLESPACE tablespace_destino);

Procedemos a  reconstruir los índices inválidados de la tabla PPROCINSTANCE

           ALTER INDEX OBPMENGORDERS.INSTPART REBUILD;

               ALTER INDEX OBPMENGORDERS.ACT REBUILD;

               ALTER INDEX OBPMENGORDERS.ASSIGPART REBUILD;

Por último volvemos a ejecutar el query para validar los objetos de la base de datos, asi como su estatus, que debe coincidir con el conteo previo a la reubicación.

              SELECT object_name, object_status FROM dba_objects;

Una vez que el conteo  es el correcto, finalizamos la reubicación.

Conclusiones

En el caso de tablas que contenga campos tipo 'LOB' y observan un elevado número de actualizaciones, es recomendable generar 'tablespaces' en discos independientes para reducir la contención y de ser posible generar la tabla particionada.  Se puede programar el uso de la sentencia 'alter table xxx shrink space compact' para reclamar el espacio eliminado.  Si se genero la tabla y ya cuenta con información, se puede realizar la reubicación por medio de la opción 'MOVE LOB', sin embargo hay que tener en cuenta los tiempos requeridos con esta opción.

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México