Tablas con problemas de lentitud a la hora de ser consultadas

Comunidad Oracle Hispana

Comentario de Jorge Aquino
"Hola a todos.Mi siguiente pregunta es que tengo una tabla a la cual se le realizan muchas transacciones por lo que a veces las personas que solamente requieren realizar consultas se le alenta mucho, como puedo realizar para que se mejore el performance de dicha tabla.Ya le he hecho varios index pero de todas maneras sigue estando lenta.gracias por los comentarios."

Bueno dices en tu mensaje, que tienes una tabla que sufre muchas transacciones y que a la hora de consultar, se hace muy lenta la consulta en sí misma. Te digo, que podrías tal vez, tener muchos objetos con este mismo problema.

Veamos:

Partamos del hecho que sobre un objeto podemos tener consultas, deletes, udpates ó inserts de registros o tuplas, como lo quieran llamar.

La lentitud en la consulta, puede deberse a alguno de los siguientes factores:

1. Problemas de fragmentación en la tabla
2. Falta de índices que satisfagan la consulta
3. Problemas de disponibilidad de recursos de hardware ( CPU, memoria, I/O )
4. Problemas de concurrencia de datos
5. Problemas de programación

Analicemos las opciones:

Problemas de fragmentación: Si la tabla es pequeña ( menos de 500000 registros ) y hace poco fue creada y no tiene muchos borrados sobre ella, es posible que este no sea el problema. Si fuese lo contrario, sería recomendable, que recrearlas el objeto.

Falta de índices: Me dices que ya hiciste varios índices, pero esto no ayudo en nada. Importante: si tu base de datos es 10g ó superior, el optimizador de consultas trabaja basado en Costo y no en Regla. Podrías tener algunos índices superflúos. Con la generación del "explain plan" de la consulta, podrías verificar el parceo que esta realizando el optimizador de consultas. Si el resultado es un FTS ( Full Table Scan ), los índices no estan sirviendo de nada.

Problema de Recursos: Dependiendo de tu sistema operativo, tienes varias herramientas para verificar la utilización del CPU, memoria y discos duros. Es normal, que el comportamiento del CPU sea alto durante períodos cortos, pero no es normal que la constante sea siempre al 100% de utilización. Para las áreas de PGA de los usuarios, es necesario contar con memoria RAM disponible en el servidor, si el porcentaje de memoria es poca, podrías tener algunos problemas en este punto y el equipo podría estar constantemente haciendo cambios de contexto y generando mucho swaping, esta situación aumentaría considerable el I/O del equipo y el rendimiento del mismo se vendría al suelo.

Problemas de concurrencia de datos: Los objetos estan quedando bloqueados por las transacciones que se estan llevando sobre los registros de la tabla. Recuerda que Oracle, realiza los bloqueos en forma automática a nivel de bloque, por tanto, si tenemos mucha actividad sobre un objecto específico y la densidad de registros por bloque es muy alta, puede darse, que se generen constantes bloqueos a nivel de tabla.

Para evitar que se produzca esta situación, es necesario que manejes el tema de concurrencia.

En las tablas e índices de la base de datos, existe un parámetro a nivel de almacenamiento, con el nombre de "initrans". Este parámetro tiene como valor de facto 1 para tablas y 2 para los índices. Esto quiere decir, que si una sesión esta haciendo un borrado, actualización o inserción de un registro o parte del registro en un bloque de la base de datos, otro usuario no podrá hacer esta misma actividad, con otros registros que se encuentren en ese mismo bloque, aún cuando los registros no esten sufriendo modificaciones. Si un usuario realiza una consulta sobre todos o algunos de los campos de un (os) registro (s) de la tabla, podría adquirir un bloqueo compartido sobre el objeto, siempre y cuando la lectura del registro pueda hacerse de manera consistente.

Veamos un ejemplo, si el bloque de la base de datos es de 8K, y el registro de una tabla mide 128 bytes y los parámetros de "pctused" y "pctfree", no se modificaron, en el objeto creado, podríamos utilizar hasta aprx. el 90% de los 8K, cuando se inserten nuevos registros, siempre y cuando el porcentaje de utilización sea por debajo del 40% del bloque previo al inicio de la operación del insert. ( Los valores de estos parámetros son 40 y 10 respectivamente ). Si tengo un 90% de los 8K para utilizar y mi registros mide 128 bytes, quiere decir que en cada bloque puedo llegar a tener como máximo 60 registros. Si tengo mucha gente trabajando sobre la tabla, puede ser que muchos usuarios requieran registros que estan en la mismo bloque, ya que la densidad ( población de registros del bloque ) es muy alta. Esto podría provocar constantemente bloqueos exclusivos sobre el bloque, evitando así, que otros usuarios puedan consultar o realizar modificaciones o borrados sobre los registros restantes.

Para evitar este problema, es necesario cambiar el "initrans" de la tabla y de los índices asociadas a la misma.

Con las siguientes instrucciones:

• "alter table . initrans <> y
• "alter index . initrans <>,

puedes modificar el nivel de concurrencia sobre los objetos. Hay que tener mucho cuidado con los valores que se otorguen. Valores muy altos, pueden causar contención a nivel del bloque, dando como resultado, problemas de rendimiento.

Ejemplo: alter table scott.emp initrans 10;

Yo por lo general en tablas con alto nivel de concurrencia y mucha población, suelo otorgar un valor de "initrans" para la tabla de 10 y un valor de 20 en el "initrans" para los índices. Mi regla es, el índice con el doble del valor de la tabla - siguiendo el patrón del valor de facto-.

Esta solucción, me ha dado excelentes cuando la he aplicado a objetos de alto nivel transaccional, como tablas de autorizaciones para sistemas de tarjetas de crédito, encabezado y detalle de tablas para registro de transacciones en puntos de venta, inventarios, contabilidad, etc.

Cuando vayan a realizar el cambio de parámetro, es necesario verificar previamente, que nadie este en ese momento utilizando los objetos, ya que la base de datos, requiere realizar un bloqueo exclusivo sobre los objetos.

Problemas de programación: El último punto pero no menos importante, es la calidad del código fuente que estemos utilizando. Si existe en el código instrucciones tales como "select for update", puede provocar bloqueos excesivos sobre la tabla. Aquí es neceario verificar, si realmente es necesario bloquear los registros, a la hora de realizar la consulta.

Si es una pantalla en dónde se puede consultar y modificar datos, es probable que funcione de esa manera.

Aquí habría que cambiar los hábitos de como se usa la consulta en sí. Evitar consultas con patrones, sería sumamente necesario. Por ejemplo: %PAIS%. Lo otro, sería necesario, que el usuario no se quede mucho tiempo en la pantalla de consulta/modificación, para evitar el bloque por tiempo excesivo.

Si logras encontrar en el alert de la base de datos, el mensaje: ORA-00060: deadlock detected while waiting for resource, la causa de tus problemas podría ser cualquiera de las dos últimas opciones.