Errores más frecuentes en Sistema Oracle

A continuación se indican los errores más comunes encontrados en Sistemas Oracle. Siguiendo el Método Oracle de Mejora del Rendimiento, se deberían evitar todos estos errores. Si se evidencian estos errores en el sistema y no pueden ser eliminados, entonces se debe hacer una reingeniería de la Aplicación en aquellos aspectos en los que el esfuerzo par mejorar el rendimiento sea elevado.

1. Gestión de Conexiones Errónea

La Aplicación se conecta y desconecta para cada interacción de Base de Datos. Este problema es común en componentes midleware “no oficiales” en Servidores de Aplicaciones. Tiene un impacto en el rendimiento de 2 órdenes de magnitud y es completamente no escalable.

2. Mala utilización de cursores y de la Shared Pool.

La no utilización de cursores genera parsers repetitivos. Si no se utilizan variables bind se realizará hard parser de todas las sentencias SQL. Tiene un impacto en el rendimiento de un orden de magnitud y es completamente no escalable. Se deben utilizar cursores con bind variables que permiten que el cursor se abra y ejecute muchas veces. Se debe sospechar de Aplicaciones que generan SQL dinámico.

3. Transferencia incorrecta de datos de la Base de Datos

En muchas ocasiones la base de datos esta mal distribuida entre los discos disponibles. En otras ocasiones el número de discos es no apropiado pues configuran los discos en función de maximizar la capacidad global en lugar de hacerlo maximizando el ancho de banda de las operaciones de entrada/salida.

4. Problemas de configuración de Redo Log

En muchas ocasiones se asignan muy pocos ficheros de Redo Log y además son de pequeño tamaño. Los ficheros de Redo Log pequeños hacen que las verificaciones del sistema generen una alta carga en la buffer cache y en el subsistema de entrada/salida. Si hay demasiado pocos ficheros de Redo Log, el proceso de archivado no puede realizarse y la base de datos hasta que el proceso de archivado se pueda realizar.

5. Serialización de acceso a bloques de datos.

El acceso en serie a bloques de datos en la buffer cache es debido a un insuficiente número de free lists, free list groups, entradas de transacciones (INITRANS) u ordenación de segmentos de rollback. Este punto es especialmente habitual en Aplicaciones que realizan muchos INSERT, en Aplicaciones que han aumentado el tamaño de bloque de base de datos a 8K o 16K o en Aplicaciones con un elevado número de usuarios activos y pocos segmentos de rollback.

6. Full Table Scans de mucha duración

La existencia de full table scans de larga duración en operaciones online interactivas puede indicar un diseño poco eficiente de las transacciones, carencia de índices o sentencias SQL poco optimizadas. Estas operaciones son, por su naturaleza, intensivas respecto a transferencias del subsistema de entrada/salida y no escalables.

7. Ordenaciones en disco

Las ordenaciones en disco para operaciones online podrían indicar un diseño de transacciones poco eficiente, carencia de índices o sentencias SQL poco optimizadas. Estas operaciones son, por su naturaleza, intensivas respecto a transferencias del subsistema de entrada/salida y no escalables.

8. Elevado número de SQL recursivo de SYS.

Un elevado número de sentencias SQL recursivas debidas al usuario SYS puede indicar actividades de gestión de espacio, como puede ser asignación de extensiones. Esta situación es no escalable y tiene impacto en el tiempo de respuesta del usuario. La ocurrencia de sentencias SQL recursivas ejecutas por otro usuario diferente a SYS son debidas probablemente a SQL y PL/SQL y no suponen un problema.

9. Errores de Schema y problemas del Optimizador

En muchos casos, una Aplicación utiliza demasiados recursos debido a que el schema propietario de las tablas no ha sido migrado con éxito desde el entorno de desarrollo o desde una implementación previa. Ejemplos de esto es pérdida de índices o estadísticas incorrectas. Estos errores pueden ocasionar la ejecución de planes no óptimos y generar un rendimiento pobre. Cuando se migran Aplicaciones de rendimientos conocidos, se deben exportar, mediante DBMS_STATS, las estadísticas del schema para mantener la estabilidad de los planes de ejecución. De igual modo, los parámetros relativos al optimizador pueden tener preferencia sobre planes de ejecución previamente eficientes. Por estas razones, los schemas, las estadísticas de los schemas y la configuración del optimizador se deben manejar juntas como un grupo para garantizar la consistencia del rendimiento.

10. Uso de parámetros de inicialización no estándar.

Esto suele ocurrir debido a un consejo no adecuado o a suposiciones incorrectas. En particular, los parámetros asociados con SPIN_COUNT sobre latches y características del optimizador no documentadas pueden generar una gran cantidad de problemas que requieren una importante investigación.

11. Pensar que solo existen los 10 errores anteriores

Uso de índices

He aquí la pregunta que muchos se hacen cuando se encuentran con problemas de rendimiento en sus tablas.... ¿he de crear un índice?. Veamos en que criterios podemos basarnos.

Como norma general, para cualquier tipo de índice, debe limitarse el número de estos y crear siempre los que exclusivamente sean necesarios, es decir, el menor número posible dentro de todos los que hagan falta.

La selectividad de un índice es la relación entre el número de valores distintos de una columna indexada y el número de registros de la tabla. Si una tabla tiene 1000 registros, y una columna indexada de la tabla tiene 950 valores diferentes, la selectividad del índice es 0.95 (950/1000). La mejor selectividad es 1. Los índices únicos sobre columnas no nulas y las claves primarias siempre tienen selectividad 1.

La selectividad de un índice nos da una medida de su utilidad para evitar I/O en la ejecución de sentencias contra la tabla. Si un índice sobre una tabla de 1000 registros tiene sólo 5 valores diferentes, entonces su selectividad es muy pobre (5/1000 = 0.005). Para cada posible valor de este índice, habrá un promedio teórico de 200 filas. En este caso, podría ser más interesante realizar un full table scan en vez de acceder vía índice a esta tabla, si bien se debe comprobar con datos reales de acceso a la misma.

Si se está usando CBO, el optimizador no realizará accesos a tablas a través de índices con una selectividad muy baja.

Puede sin embargo ser conveniente usar índices con baja selectividad, si se cumplen varias condiciones.

• Las sentencias empleadas no usan variables bind o se ha implementado algún mecanismo para distinguir aquellas consultas que deben emplear el índice, como por ejemplo, uso de literales, basándose en la distribución de datos.
• El índice está analizado con histogramas.
• El valor usado en las condiciones tiene muy poca cardinalidad.

Un saludo para todos.

Programación Orientada a Objetos en Oracle

- INTRODUCCIÓN:

Los type de Oracle son tipos de datos que declara el usuario, y que puede ser utilizado para la declaración de variables, tanto en procedimientos, como en funciones, o incluso en tablas.

Con el tiempo, los type de Oracle, han derivado en lo que en Java o C++ se denominan clases. Es decir que cada tipo esta formado por sus objetos y sus atributos:

· Los objetos son variables, que a su vez pueden pertenecer a otros tipos.

· Los atributos son procedimientos o funciones que pertenecen al type.

A parte, los type de Oracle tienen todas las características propias de la programación orientada a objetos:

· Encapsulación: El type contiene dentro todos los objetos y atributos correspondientes, siendo estos últimos transparentes para el usuario.

· Herencia: Un type puede heredar todos los objetos y atributos de otro.

· Polimorfismo: El type de oracle, una vez heredado puede añadir nuevos objetos y atributos.

- TYPE OBJECT:

Los tipos de objeto, crean tipos de variable, que puede contener, objetos de otro tipo y métodos. La sintaxis de creación es:

CREATE OR REPLACE TYPE NOMBRE IS OBJECT (

VARIABLE1 TIPO_DATO,

VARIABLE2 TIPO_DATO,

…

VARIABLEN TIPO DATO,

MEMBER PROCEDURE NOMBRE_PROC (VAR IN TIPO_DATO) ,

MEMBER FUNCTION NOMBRE_FUNC (VAR IN TIPO_DATO) RETURN TIPO_DATO

);

CREATE OR REPLACE TYPE BODY NOMBRE IS (

MEMBER PROCEDURE NOMBRE PROC (VAR) IS

VRIABLE1 TIPO_DATO,

VARIABLE2 TIPO_DATO,

…

VARIABLEN TIPO DATO

BEGIN

CODIGO;

END NOMBRPE PROC;

END;

Como podemos observar, los tipos tienen dos partes: el TYPE, que contiene la especificación de objetos y métodos, y el TYPE BODY, que contiene el código de los métodos del tipo. Evidentemente sólo se hará el TYPE BODY si existen métodos en el tipo.

Una vez creado el tipo, Oracle lo va a interpretar como un tipo de dato, y como tal podrá ser usado en cualquier parte. Así, al declarar una variable en un procedimiento o en una función, podremos usar el tipo como si se tratara de otro tipo de datos tales como varchar2 o number.

De hecho, también se puede utilizar como tipo de datos en tablas, declarandolo después como una neested table, de la siguiente manera:

CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;

Una vez creada la tabla, se pueden realizar operaciones de insert, update, delete o select como en cualquier otra tabla. Para más información visitar la página : http://www.psoug.org/reference/nested_tab.html .

- TYPE TABLE:

El tipo de tabla, crea una tabla física en base de datos, de un tipo de datos concreto (lo cual incluye cualquier otro type que haya creado en la base de datos) sobre la que, a lo demás, podremos ejecutar sentencias sql. Para poder ejecutar sentencias SQL sobre un tipo tabla, hay que basar este en un tipo de objeto (type object) en el cual cada objeto será el campo de la base de datos; así como crear el tipo tabla a nivel de base de datos. Si el tipo de tabla es creado a nivel de procedimiento o función no podrá ser usado para ejecutar sentencias SQL.

La sintaxis de creación es la siguiente:

CREATE OR REPLACE TYPE NOMBRE_TIPO IS TABLE OF TIPO_DATO;

Una vez creado el tipo, podremos usarlo dentro de cualquier procedimiento o función. Para ello tendremos que inicializarlo, con un constructor. Una vez hecho esto, se utilizará con la referencia al tipo de dato usado (en caso de que sea otro type). Por ejemplo:

Creamos el type que nos va a decir los campos:

CREATE OR REPLACE TYPE PERSON IS OBJECT(

NOMBRE VARCHAR2(10),

APELLIDOS VARCHAR2(15));

CREATE OR REPLACE TYPE PERSON_TABLE IS TABLE OF PERSON;

Para usarlo en un procedimiento, lo haríamos de la siguiente manera:

CREATE OR REPLACE PROCEDURE INTRO_PERSON IS

DECLARE

PERSON_DATES PERSON_TABLE;

NUM_PERSON NUMBER;

BEGIN

PERSON_DATES := PERSON_TABLE(); -- Constructor que inicializa la variable.

/* Introducimos los datos que queremos. En este caso, 3 nombres. Hacemos referencia,

como veis Al tipo inicial, y no al tipo de tabla, a la hora de rellenarlo. Esto es así

imprescindible. Con respecto al rellenado de las variables del tipo, se puede hacer referencia

por posición (escribiendo en la Posición adecuada el dato), o por referencia (tal y como está

relleno en el ejemplo)*/

PERSON_DATES.EXTEND;

PERSON_DATES(1) := PERSON(NOMBRE => ‘JUAN’, APELLIDOS => ‘RAMIREZ’);ç

PERSON_DATES.EXTEND;

PERSON_DATES(1) := PERSON(NOMBRE => ‘PEDRO’, APELLIDOS => ‘RAMIREZ’);

PERSON_DATES.EXTEND;

PERSON_DATES(1) := PERSON(NOMBRE => ‘FRANCISCO’, APELLIDOS => ‘PEREZ’);

/*Aplicamos ahora una sentencia SQL sobre la variable PERSON_DATES*/

SELECT COUNT(APELLIDOS)

INTO NUM_PERSON

FROM TABLE(CAST(PERSON_DATES AS PERSON_TABLE))

WHERE APELLIDOS LIKE ‘REMIREZ’;

DBMS_OUTPUT.PUT_LINE (‘Nº DE PERSONAS: ’ || NUM_PERSON) ;

END;

Podemos ver que al aplicar la sentecia SQL hay dos palabras clave importantes: TABLE y CAST.

La primera indica a Oracle que se trata de una tabla de memoria, y no de una tabla de base de datos.

La segunda hace un cast de conversión. Es necesario hacerlo, porque si no Oracle no reconoce el tipo.

- TYPE BODY:

Los procedimientos y funciones miembros de un tipo se programan igual que cualquier otro procedimiento o función. Lo único a tener en cuenta es que para hacer referencia a cualquiera de los atributos del tipo (cualquiera de las variables), se pondría la palabra self delante.

Ejemplo:

Supongamos que al typo PERSON creado anteriormente le añadimos como member functión la función validar:

MEMBER FUNCTION VALIDAR RETURN NUMBER IS

VALIDACION NUMBER;

BEGIN

IF SELF.NOMBRE = SELF.APELLIDOS THEN

VALIDACION := 1;

ELSE

VALIDACION = 0;

END IF;

RETURN VALIDACION;

END;

El procedimiento del ejemplo anterior podría usarse de la siguiente manera:

CREATE OR REPLACE PROCEDURE INTRO_PERSON IS

DECLARE

PERSON_DATES PERSON_TABLE;

NUM_PERSON NUMBER;

VALIDAR NUMBER;

BEGIN

PERSON_DATES := PERSON_TABLE(); -- Constructor que inicializa la variable.

/* Introducimos los datos que queremos. En este caso, 3 nombres. Hacemos referencia,

como veis Al tipo inicial, y no al tipo de tabla, a la hora de rellenarlo. Esto es así

imprescindible. Con respecto al rellenado de las variables del tipo, se puede hacer referencia

por posición (escribiendo en la Posición adecuada el dato), o por referencia (tal y como está

relleno en el ejemplo)*/

PERSON_DATES.EXTEND;

PERSON_DATES(1) := PERSON(NOMBRE => ‘JUAN’, APELLIDOS => ‘RAMIREZ’);

/*Validamos si el nombre no coincide con el apellido*/

VALIDAR:= PERSON.VALIDAR;

IF VALIDAR = 1 THEN

RAISE DUP_VAL_ON_INDEX;

END IF;

PERSON_DATES.EXTEND;

PERSON_DATES(1) := PERSON(NOMBRE => ‘PEDRO’, APELLIDOS => ‘RAMIREZ’);

/*Validamos si el nombre no coincide con el apellido*/

VALIDAR:= PERSON.VALIDAR;

IF VALIDAR = 1 THEN

RAISE DUP_VAL_ON_INDEX;

END IF;

PERSON_DATES.EXTEND;

PERSON_DATES(1) := PERSON(NOMBRE => ‘FRANCISCO’, APELLIDOS => ‘PEREZ’);

/*Validamos si el nombre no coincide con el apellido*/

VALIDAR:= PERSON.VALIDAR;

IF VALIDAR = 1 THEN

RAISE DUP_VAL_ON_INDEX;

END IF;

/*Aplicamos ahora una sentencia SQL sobre la variable PERSON_DATES*/

SELECT COUNT(APELLIDOS)

INTO NUM_PERSON

FROM TABLE(CAST(PERSON_DATES AS PERSON_TABLE))

WHERE APELLIDOS LIKE ‘REMIREZ’;

DBMS_OUTPUT.PUT_LINE (‘Nº DE PERSONAS: ’ || NUM_PERSON) ;

EXCEPTION

WHEN DUP_VAL_ON_INDEX

DBMS_OUTPUT.PUT_LINE (‘NO PUEDE COINCIDIR EL NOMBRE CON EL

APELLIDO’) ;

END;

- HERENCIA:

Para heredar se usa el comando UNDER. Si cogemos el tipo PERSON, del ejemplo anterior para crear un nuevo tipo, que a lo demás incluya el DNI, la sentencia sería:

CREATE OR REPLACE TYPE PERSON_COMPLEX UNDER PERSON (

DNI VARCHAR2(10)

);

Esto crearía un tipo llamado PERSON_COMPLEX, que tendría todos los atributos y métodos del tipo PERSON, más el atributo DNI.