miércoles, 26 de diciembre de 2007

Cadena de conexión con jdbc thin

Error al testear la conexión JDBC en JDeveloper 10g ->

Test Failed: La dirección URL de Oracle especificada no es válida

Un método que puede solucionar este frustrante mensaje es usar la cadena de conexión que incluye la descripción del servicio (tal como la colocaríamos en el archivo tnsnames.ora)

jdbc:oracle:thin:@(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = puerto))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = base_de_datos)
)
)

Si todavía no funciona es recomendable probar la conexión a través de un cliente Oracle, configurando el archivo tnsnames.ora. Al momento que esta conexión logre conectarnos, solo resta reemplazar en la cadena de conexión la misma descripción del servicio del tnsnames (arriba en azul).


domingo, 11 de noviembre de 2007

Indices condicionales

Hay tablas para las cuales desearíamos hacer valer condiciones de unicidad pero dadas ciertas condiciones que dependan de alguna otra columna de control.

Un ejemplo típico se da cuando mantenemos registros en una tabla con borrado lógico, y queremos que exista un único registro activo (pudiendo aparecer el registro inactivo más de una vez). Digamos que tenemos la tabla t, con dos columnas fundamentales: un id y un indicador de registro activo Y/N. Queremos que el Id sea único solamente cuando Activo es 'Y'.

Muchas veces, mucha gente se enfrenta a este problema y lo resuelve creando una tabla auxiliar para hacer valer la condición de unicidad, almacenando en ella las tuplas que no desea repetir para lograr dicho control.
Todo el problema que implica crear (y mantener) estructuras adicionales y la complejidad de manipulación, puede evitarse utilizando lo que siempre estuvo al alcance: el índice de función y la sentencia CASE.

SQL> CREATE TABLE t (sec int primary key, id VARCHAR2(100), activo VARCHAR2(1));

Table created.

SQL> CREATE UNIQUE INDEX ix_t ON t
2 (CASE WHEN activo='Y' THEN id ELSE NULL END);

Index created.

SQL> insert into t values (1, 'COD1', 'Y');

1 row created.

SQL> insert into t values (2, 'COD1', 'N');

1 row created.

SQL> insert into t values (3, 'COD1', 'N');

1 row created.

SQL> insert into t values (4, 'COD1', 'Y');
insert into t values (4, 'COD1', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (LFER.IX_T) violated

Los índices condicionales no son un feature especial de Oracle, sino que se construyen utilizando los mismos índices de función que usualmente definimos para todas las filas de una tabla. En este caso, la función CASE permite que el índice único se construya sobre un subconjunto de registros, aquellos en el que el campo activo sea igual a 'Y'.

Ver también:
11g y sus índices invisibles
Mis índices no funcionan!
Indices de función para mejorar un LIKE

miércoles, 7 de noviembre de 2007

Variables en SQL*Plus (parte 3)

Anteriormente repasando variables en sqlplus, vimos las variables de sustitución y las de usuario. Finalmente llegamos al tercer tipo de variables, las variables bind (o de ligadura).

VARIABLES BIND

Estas variables tienen la particularidad de poder ser definidas en sqlplus, usadas y/o asignadas dentro de nuestros programas PL/SQL y luego leídas una vez terminado el programa. Son convenientes cuando queremos tener comunicación entre scripts de sqlplus y bloques de PL/SQL.

Las variables bind se definen así:
SQL> VARIABLE bvar NUMBER;
Pueden utilizarse también los tipos: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, BINARY_FLOAT y BINARY_DOUBLE

Si queremos asignar estas variables antes de llamar a nuestros bloques de PL/SQL, debemos hacerlo desde otro bloque BEGIN END o simplemente usando la sentencia EXEC:

SQL> exec :bvar := 1;

PL/SQL procedure successfully completed.

Las variables dentro de PL/SQL se referencian con dos puntos seguidos del nombre de la misma.
Ahora ya podemos utilizar las variables dentro de nuestro programa e imprimir el resultado:
SQL> BEGIN
2 IF :bvar = 1 THEN
3 :bvar := 0;
4 ELSE
5 :bvar := 1;
6 END IF;
7 END;
8 /
PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
0

SQL> /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
1

Y con esto culminamos el repaso de variables en sql*plus. Finalmente como complemento, veremos brevemente los cursores referenciales los cuales son también útiles para recuperar un conjunto de tuplas de nuestros SELECTs, desde los programas PL/SQL hacia fuera, y así tener más flexibilidad.

REFCURSORS

Se declaran como una variable bind, pero colocamos REFCURSOR en el tipo:

SQL> VARIABLE rcur REFCURSOR;

SQL> begin
2 OPEN :rcur FOR select segment_name, segment_type from user_segments order by segment_name;
3 end;
4 /

PL/SQL procedure successfully completed.

A diferencia de los cursores tradicionales que se abren y requieren del uso de fetch, al abrir estos cursores bind ya son ejecutados automáticamente y sus resultados almacenados en memoria. Solo resta imprimir las filas de la consulta.

SQL> print rcur
SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
ACUMULADO_CONSUMO TABLE
BIG_TABLE TABLE
BIG_TABLE_PK INDEX
CLIENTE TABLE
COUNTRY_C_ID_PK INDEX
CUENTA TABLE
INVENTARIO TABLE
IX_T INDEX
MUNICIPIO TABLE
NDX_ACUMCON INDEX
NDX_ACUMRENT INDEX
NDX_ACUMTERC INDEX
NDX_ACUMVAR INDEX

13 rows selected.


Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 2) - Sobre variables de sustitución

martes, 23 de octubre de 2007

Update condicional

¿Cómo se hace un UPDATE condicional?
Digamos que tengo una tabla t con 3 campos a, b y c. Quiero que si a=1 entonces actualizo b con algún valor, y si a=2 actualizo c.

Alguien de mi equipo se encontró con este dilema y lo resolvió de forma muy práctica: dos updates, uno para actualizar aquellos valores con a=1 y otro para actualizar los a=2:

1 BEGIN
2    UPDATE t
3   SET b=valor
4   WHERE a=1;
5

6   UPDATE t
7   SET c=valor
8   WHERE a=2;
9 END;

Otro programador del equipo refutó: -¿Para qué ejecutar dos sentencias update si en su lugar puedes hacer una?

1 BEGIN
2   UPDATE t
3   SET B=DECODE(A,1,valor,2,B),
4   C=DECODE(A,1,C,2,valor);
5 END;


¿Quién de los dos tiene razón?

...depende.

Dado que la performance parece ser lo que está en juego, la primera pregunta que cabe es:

¿Tiene la tabla t índice por A?

Si la tabla no tiene índice, entonces debemos recorrerla por entero. En el primer caso recorremos la tabla dos veces, mientras que en el segundo caso lo hacemos solamente una! Por lo tanto la primera solución en este escenario es el doble más lenta que la segunda. Al notar que la tabla no tiene índices, el Programador 2 asiente la cabeza con orgullo.

Inmediatamente el Programador 1 reacciona: -Pongámosle un índice a t entonces!
(el ávido Programador 1 sabe que la segunda consulta no puede sacar provecho de un índice por lo que su victoria parece asegurada)

Y aquí es donde cabe la segunda pregunta:

¿Cuál es la cardinalidad de la columna A?

Sería de gran ayuda saber si 1 y 2 son la mayoría de los valores que hay en la columna A, porque en ese caso, el optimizador va a continuar prefiriendo recorrer toda la tabla! Si la cardinalidad de los valores 1 y 2 son un porcentaje muy bajo del conjunto de valores distintos, entonces recién ahí los índices van a ser utilizados.
Cuando el índice es utilizado por la escasa presencia de valores 1 y 2, los tiempos de la primer solución es abismalmente inferior.

Conclusión:

La solución 1 es mejor si existen índice sobre la columna A y su cardinalidad es alta (hay pocos 1 y 2 en A).
La solución 2 es mejor si no existen índices o existe y la cardinalidad de A es baja para valores 1 y 2 (significa que 1 y 2 ocurren mucho en A).

Ver también:
Mis índices no funcionan!
Indices condicionales

miércoles, 26 de septiembre de 2007

tic... toc

¿Cómo medir la performance de un código PL/SQL?

Muchas veces nos encontramos con procedimientos lentos de muchos pasos, con llamadas a funciones, DML, loops con cursores, etc, que queremos revisar. Como SYSDATE nos da la cantidad de segundos como mínima fracción de tiempo, no nos es de mucha ayuda consultarlo entre líneas.

Afortunadamente, contamos con la función get_time del paquete dbms_utility para obtener la cantidad de centésimas de segundo que transcurren entre dos instantes de tiempo.
A sabiendas de eso, debemos incluir los statements que invoquen a dichas funciones y desplieguen los tiempos por la salida, en un archivo o en una tabla de logs.

Con el fin de evitar duplicar código y además aprovechar las bondades del cacheado de paquetes en el área de memoria compartida, creé un pequeño paquete exclusivo para ese fin. El mismo tiene dos procedimientos: tic y toc. Cuando quiero comenzar a medir tiempo, invoco a tic (con un mensaje para registro). Cuando quiero medir el tiempo transcurrido, uso toc. Así de simple.

CREATE OR REPLACE PACKAGE crono AS
  v_tic PLS_INTEGER;
  evento VARCHAR2(100);
  PROCEDURE tic(p_evento IN VARCHAR2);
  PROCEDURE toc;
END crono;
/


CREATE OR REPLACE PACKAGE BODY crono AS
 PROCEDURE tic(p_evento IN VARCHAR2) IS
 BEGIN
   v_tic := DBMS_UTILITY.GET_TIME;
   evento := p_evento;
   DBMS_OUTPUT.PUT_LINE('TIC: '||p_evento);
 END tic;

 PROCEDURE toc IS
   df pls_integer := CAST(DBMS_UTILITY.GET_TIME AS PLS_INTEGER)-v_tic;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('TOC: '||evento||', T(1/100s): '||df);
 END toc;
END crono;
/


El uso del paquete en un procedimiento cualquiera puede ser:

SQL> begin
2     crono.tic('creo tabla');
3     execute immediate 'CREATE TABLE testtable as select * from user_tables';
4     crono.toc;
5     crono.tic('elimino registros');
6     execute immediate 'delete from testtable';
7     crono.toc;
8     crono.tic('hago commit');
9     commit;
10     crono.toc;
11     crono.tic('borro tabla');
12     execute immediate 'drop table testtable';
13     crono.toc;
14 end;
15 /
TIC: creo tabla
TOC: creo tabla, T(1/100s): 34
TIC: elimino registros
TOC: elimino registros, T(1/100s): 5
TIC: hago commit
TOC: hago commit, T(1/100s): 0
TIC: borro tabla
TOC: borro tabla, T(1/100s): 50

Procedimiento PL/SQL terminado correctamente.

Aunque esta experiencia es meramente demostrativa, notar que no pasó un segundo en toda la operación, y sin embargo estamos sacando medidas ilustrativas en cada paso.

Es una solución práctica y disponible para todos los usuarios de la base de datos, solo tengo que dar permisos de ejecución a los desarrolladores o a public. Un sinónimo puede simplificar aún más las llamadas a tic y a toc.

Pese al mínimo costo de salida por pantalla, una desventaja es que el procedimiento tiene que terminar exitosamente para obtener las medidas (dbms_output despliega su salida al final del proceso). Una alternativa para lograr la medición "on the run", es implementar la salida a archivo (utilizando el paquete utl_file), o insertando en una tabla (con una transacción autónoma para no hacer commit sobre la actual).
Estos cambios pueden ser transparentes al usuario, ya que solo hará falta recompilar el body del paquete. Las ventajas están a la vista.

Ver también:
Uso del paquete utl_file

sábado, 22 de septiembre de 2007

El usuario Scott no existe

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Dependiendo de las opciones de instalación o de los schemas que vengan instalados en algunas versiones del RDBMS (como Express por ejemplo), el usuario más famoso de Oracle, Scott, puede no estar presente. Citando el caso de Oracle XE, el único schema de ejemplo que viene instalado por defecto es HR.

Pero no hay problema, lo único que tenemos que hacer para instalar el schema Scott con sus clásicas tablas EMP, DEPT, BONUS y SALGRADE, es correr (en SQL*Plus) el script utlsampl.sql que está en el directorio del servidor $ORACLE_HOME/RDBMS/ADMIN.

Como se requiere de ciertos permisos especiales que un usuario corriente no tiene, deberá hacerlo el DBA.

lunes, 10 de septiembre de 2007

Limpiando código DDL

A propósito de Un extractor de DDL en archivos separados, me preguntaron como evitaba exportar las cláusulas de storage en Oracle 9i, únicamente dejando las que especifican los tablespaces.

SQL> create table t (a int);

Tabla creada.

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

CREATE TABLE "LFERNANDEZ"."T"
( "A" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"


En la práctica, las cláusulas PCTFREE, PCTUSED, STORAGE, etc, no son útiles durante el desarrollo, sino cuando los objetos son puestos en producción. Es allí donde el detalle en la especificación de storage de los objetos tiene un impacto significativo sobre la performance de los sistemas.

Repasando las opciones disponibles en el paquete dbms_metadata y en particular la función set_transform_param, encontramos que podemos generar el código de asignación de tablespace pasando el valor 'TABLESPACE' como segundo parámetro y TRUE como tercero:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', TRUE);

Desafortunadamente, esta opción es ignorada cuando deshabilitamos todo el STORAGE usando:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);

No tenemos alternativa entre: o generar todo el storage, o no generarlo en absoluto.

El camino que termina siendo elegido es el de generar todo el código DDL y luego procesar el archivo de salida con algun lenguaje con buenas cualidades para procesamiento de texto, como por ejemplo Perl o AWK.

No se requiere ser experto en AWK para programar un sencillo script que elimine algunas líneas de archivos:

BEGIN {flag=2}
/^ *) PCTFREE/{print ")"; flag=0}
/^ *PCTFREE/{flag=0}
flag==0 && $0~/)$/{flag=1}
flag==2
flag==1{flag=2}


Esta es la idea: uso flags para saber cuándo imprimir y cuándo no, luego tengo dos posibles comienzos con PCTFREE, el primero para tablas comunes y el segundo para el caso de tablas particionadas (las cuales no son antecedidas por un paréntesis).

Cualquier comentario para mejorar la performance de este script será bienvenido!

Ver también
Un extractor de DDL en archivos separados

miércoles, 15 de agosto de 2007

ORA-00600 y recolección de estadísticas con ORA-00933

Esto puede ocurrir en 9iR2:
Al ejecutar un select se produce el error inesperado

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

Esto puede solucionarse generando las estadísticas nuevamente, invocando el paquete dbms_stats.
Al hacer eso, puede ocurrir lo siguiente al ejecutar el procedimiento dbms_stats.gather_table_stats (o gather_schema_stats):

SQL> exec dbms_stats.gather_table_stats(user,tabname => 'FACTURADOR', estimate_percent => 20, block_sample => false, method_opt => 'FOR TABLE FOR ALL COLUMNS SIZE 1', cascade => true);

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1

Verificar que este error no se produce si invocamos el procedimiento con cascade => false.

Esto esta relacionado con los parámetros de NLS como NLS_LANGUAGE, NLS_SORT y NLS_NUMERIC_CHARACTERS. Seguramente no tienen valores por defecto, los cuales se necesitan para la generación de histogramas.

La vista NLS_SESSION_PARAMETERS nos muestra cuáles son los valores actuales que se están usando (recordar que éstos tienen prioridad sobre los parámetros de la base de datos).

SQL> select PARAMETER, VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_SORT', 'NLS_NUMERIC_CHARACTERS'); NLS_SESSION_PARAMETERS;

PARAMETER VALUE
------------------------- --------------------
NLS_LANGUAGE AMERICAN
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY

Si alguno de estos parámetros no aparece como arriba, será necesario hacer un ALTER SESSION para arreglarlo y probar generar las estadísticas nuevamente con cascade => true.

miércoles, 8 de agosto de 2007

Un extractor de DDL en archivos separados

¿Cómo podemos exportar todos los objetos de un esquema de Oracle en archivos separados para poder versionar el código?

Algunos de los IDEs comerciales más populares de PL/SQL traen esta funcionalidad (lo pueden investigar), pero me parece una buena oportunidad para poder hacerlo sin ayuda de ninguna otra herramienta que... PL/SQL!

Para poder cristalizar esto, ejecutaremos desde sql*plus bloques de PL/SQL que realicen lo siguiente:
1) Extraer el DDL de los objetos - usaremos el paquete dbms_metadata para obtener el DDL de tablas, indices y constraints. También leeremos la tabla de diccionario user_source para capturar el código de procedimientos, funciones, paquetes y triggers.
2) Escribir en archivos el código generado - invocaremos al paquete utl_file para crear archivos en el sistema operativo.

Este procedimiento funciona en 9i, XE o 10g, tanto para Unix como para Windows. Los archivos quedarán alojados localmente en el servidor (el usuario necesitará los debidos permisos de acceso a estas carpetas).

Nota para usuarios Oracle XE:
El paquete utl_file no vine compilado por defecto luego de la instalación. Es necesario ejecutar el siguiente script con el usuario sys:
$ORACLE_HOME/RDBMS/ADMIN/utlfile.sql
Luego deberán revisar con el usuario sys si quedaron paquetes, vistas o procedimientos mal compilados y en tal caso, recompilarlos.


Bueno, ahora sí vamos con los pasos necesarios:

Paso 1) Crear directorios en el servidor - El usuario de la base de datos necesita privilegios para crear directories de Oracle, de lo contrario deberá solicitar al DBA que lo haga por él o le indique alguno que pueda usar. De lo contrario no vamos a poder escribir con el paquete utl_file.

Las carpetas deben existir en el sistema operativo y luego crearse los objetos directorios en Oracle, asociados a las mismas.
Por ejemplo, luego de crear las carpetas en Unix, ejecutar lo siguiente en SQL*Plus:

create directory tables as '/oracle/source/tables';
create directory indexes as '/oracle/source/indexes';
create directory procedures as '/oracle/source/procedures';
create directory functions as '/oracle/source/functions';
create directory packages as '/oracle/source/packages';
create directory packagebodies as '/oracle/source/packagebodies';
create directory triggers as '/oracle/source/triggers';

... etc

Paso 2) Especificar la información a ser generada con dbms_metadata - La función set_transform_param nos permite especificar que detalle de código debe generarse.

Ejecutar lo siguiente en SQL*Plus:
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER', TRUE ) ;
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR', TRUE ) ;
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', FALSE) ;


Paso 3) Finalmente, la parte interesante... codificar el PL/SQL! :)

Voy a mostrar dos ejemplos: el primero para capturar tablas y el segundo para capturar los procedimientos. El caso para capturar DDL de índices es completamente análogo al de tablas, y el caso de funciones, paquetes, body de paquetes, triggers es similar al de procedimientos.

Ejecutar en SQL*Plus:

1  DECLARE
2    f UTL_FILE.FILE_TYPE;
3  BEGIN
4    FOR cr IN (SELECT table_name FROM USER_tables)
5    LOOP
6      f := UTL_FILE.FOPEN ('TABLES', cr.table_name || '.sql', 'w');
7      UTL_FILE.PUT_LINE (f, dbms_metadata.get_ddl('TABLE', cr.table_name));
8      UTL_FILE.FCLOSE (f);
9    END LOOP;
10 END;
11 /

Las líneas 2, 6, 7 y 8 muestran el uso del paquete utl_file en cuatro pasos básicos: se declara el manejador, se abre el archivo para escritura, se escribe y finalmente se cierra.

La línea 4 selecciona todas las tablas de mi esquema.

En cada iteración del FOR, abrimos un nuevo archivo (línea 6) con el nombre de la tabla, y escribimos en el directorio TABLES (creado en el paso 1) la salida del procedimiento dbms_metadata.get_ddl.

Para capturar los índices es análogo, cambiando en la línea 7 el parámetro 'TABLE' por 'INDEX' y cambiando también si se desea el directorio destino en la línea 6.

Ahora veremos el caso de escribir código de usuario leyendo la tabla user_source. Tenemos que tener algunos detalles en cuenta, ya que la tabla user_source contiene todo el código separado una línea por fila. Vamos a ir escribiendo el archivo de a una línea y tendremos que saber cuándo termina un procedimiento y comienza otro.

1  DECLARE
2    f UTL_FILE.FILE_TYPE;
3    nombre VARCHAR2(4000) := NULL;
4  BEGIN
5    FOR cr IN (SELECT name, text FROM USER_SOURCE WHERE type='PROCEDURE' order by name, line)
6    LOOP
7      IF (nombre IS NULL OR cr.name <> nombre) THEN
8        IF (nombre IS NOT NULL) THEN
9          UTL_FILE.FCLOSE (f);
10       END IF;
11       f := UTL_FILE.FOPEN ('TABLES', cr.name || '.sql', 'w');
12       UTL_FILE.PUT (f, 'CREATE OR REPLACE ');
13     END IF;
14     nombre := cr.name;
15     UTL_FILE.PUT (f, cr.text);
16   END LOOP;
17   IF UTL_FILE.IS_OPEN(f) THEN
18     UTL_FILE.FCLOSE (f);
19   END IF;
20 END;
21 /

En la línea 3 declaramos una variable para leer cada línea.

En la línea 5 seleccionamos el tipo de objeto que queremos capturar. De la misma forma lo haremos para triggers, funciones, paquetes, etc.

Las líneas 7 y 8 tratan la apertura y cierre del archivo cuando se termina un procedimiento; al ir leyendo línea a línea de todos los objetos, necesito saber cuando comienza y cuando termina un procedimiento para podeer abrir un nuevo archivo de escritura.

En la línea 12 le agrego 'CREATE OR REPLACE' ya que la tabla user_source no almacena esta directiva.

Observar en las líneas 12 y 15 que utilizamos la función PUT en lugar de PUT_LINE. Esto es porque las líneas de user_source ya almacenan un retorno de carro por lo cual no es necesario que use PUT_LINE, de lo contrario voy a escribir un renglón por medio.

Con estos ejemplos es posible capturar todo el código de nuestro esquema y almacenar cada objeto en un archivo diferente. Traté de simplificar el código a costa de sacrificar algunas buenas prácticas, pero en este caso no quise complicar la visibilidad de la idea por lo cual cada uno podrá mejorar su propia implementación.

viernes, 27 de julio de 2007

SQL*loader: Field in data file exceeds maximum length

Recientemente tuve la oportunidad de ver un problema en un aplicativo que utilizaba SQL*loader 9.2.0.6 para cargar datos de un archivo plano en una tabla, originalmente separados por pipes y eventualmente conteniendo espacios en uno de sus campos.

Un ejemplo del contenido del archivo de datos (los pipes van en color para identificar mejor los campos):

A1|3|02/12/98|NN|A Suspender |34
A2|2|02/12/98|NS|No se presentó, fue enviado nuevo aviso         (**)      |27
A3|3|02/14/98|NS||9

(**) La segunda línea de datos contiene luego del texto y antes del siguiente pipe, más de 300 espacios.

El control file utilizado para cargar ese archivo tenía esta forma:

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO DATE "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

Al campo descripcion se le había agregado la función TRIM para eliminar los espacios sobrantes, sin embargo, cuando se ejecutaba SQL*loader la carga fallaba y parecía ignorar la función:

Record 2: Rejected - Error on table TMP_SUSPENSION, column DESCRIPCION.
Field in data file exceeds maximum length

Table TMP_SUSPENSION:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

La causa del problema no era que la función TRIM no estaba cortando los caracteres ni que el campo en la tabla no tuviera el largo suficiente, sino la mala práctica de no especificar tipos en los campos del control file. Recordemos que los campos que se definen allí no corresponden a la tabla destino sino a lo que se espera encontrar en el archivo fuente. Cuando no se especifica tipo y largo en un campo del control file, Oracle asume el largo por defecto de 255 caracteres. Por lo tanto, al encontrar mas de 255 caracteres entre dos pipes, se rechaza el mismo antes siquiera de aplicar la función TRIM. De la forma que opera SQL*loader, primero se validan los tipos en el archivo, y luego se aplican las funciones sobre los campos identificados.

Como solución, se arregló el control file para que tuviera el tipo especificado de un largo mayor al previsto. Esta simple buena práctica es muy recomendada para SQL*loader.

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO DATE "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION CHAR(600) "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

domingo, 22 de julio de 2007

Sobre estimar estadísticas

¿Que estadísticas conviene tomar? ¿La tabla completa o un porcentaje? ¿Qué valor uso?

Esta pregunta es relevante para tablas muy grandes o esquemas populosos donde el tiempo de extracción debe ser optimizado. Obviamente tomar las estadísticas completas es lo mejor por precisión, y en esquemas chicos nos puede llevar algunos pocos minutos, pero algunas veces tenemos ciertos requerimientos de performance o de utilización de recursos ya que es una operación que realiza mucha lectura física (I/O).

Para tablas y esquemas grandes yo suelo tomar un 20 por ciento para el estimado, con lo cual logro mis estadísticas de 'buena calidad'.

Oracle recomienda utilizar el paquete dbms_stats para tomar estadísticas de tablas, índices, esquemas o de la base de datos completa. Extrae más información que el comando ANALIZE además de otras características que podemos aprovechar como el procesamiento en paralelo.

Se puede invocar de la siguiente manera:

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'OUT_TAB',estimate_percent => 20, method_opt => 'FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES', cascade => true );

Algo interesante de este paquete es que nos permite que Oracle calcule un valor adecuado para el porcentaje estimado, y esto se hace poniendo dbms_stats.auto_sample_size en estimate_percent.

Si hacemos la prueba podemos constatar observando la tabla user_tables que Oracle toma las estadísticas al 100 por ciento hasta cierta cantidad de filas que depende de cada base de datos.

A tales efectos puede utilizarse una consulta como ésta:

SELECT table_name, num_rows, sample_size, TRUNC(sample_size*100/DECODE(num_rows,0,1,num_rows),2) porciento
FROM user_tables;

A modo de ejemplo, en una base 9iR2 sobre un esquema de desarrollo con estadísticas estimadas al 20 por ciento, constaté que hasta las tablas con 12500 filas Oracle tomaba el 100 por ciento de las filas como muestra, y luego a partir de ahí tomaba el 20 por ciento que yo le había solicitado.

También se puede comprobar que usando dbms_stats.auto_sample_size para ciertos casos, Oracle calcula la tabla entera sin importar la cantidad de tuplas.
Esto se debe a que Oracle utiliza un algoritmo para obtener un número adecuado: parte de cierta cantidad de tuplas y en base a ellas analiza si la calidad de las mismas es buena, si no lo es toma un nuevo porcentaje y así sucesivamente hasta llegar a un 25 por ciento, luego del cual decide computar la tabla entera.
Cuando esto sucede, los requerimientos de performance o recursos utilizados sufren; por este motivo en esos casos es mejor especificar el valor fijo.

Ver también:
Falla de estadísticas con ORA-00933
Error ORA-942 while gathering statistics

jueves, 19 de julio de 2007

Variables en SQL*Plus (parte 2)

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.
En la primera parte repasamos las variables de usuario, aquí van las variables de sustitución.

VARIABLES DE SUSTITUCION

Las variables de sustitución suelen confundirse con las variables de usuario, más que nada porque ambas aparecen precedidas con un símbolo ampersand (&). Sin embargo, hay diferencias en la definición, asignación y uso de las mismas. Como vimos en la parte 1, las variables de usuario se declaran explícitamente con el comando DEFINE, tienen asociado un tipo y se les pueden asignar valores en el script como lo hacemos en cualquier lenguaje de programación.
Las variables de sustitución en cambio cuando están indefinidas, solicitan el valor al usuario cada vez que aparecen, y el resultado es exactamente el mismo que si escribiéramos el texto directamente.

Por ejemplo: en la siguiente consulta en cada aparición de variable será solicitado el valor al usuario. Notar que aunque la variable COLUMNA aparece repetida, se trata como si fueran diferentes y el valor es solicitado dos veces.

SELECT &COLUMNA, SUM(MONTO)
FROM cuentas
GROUP BY &COLUMNA;

Enter value for columna: ID_REGION
old 1: SELECT &COLUMNA,
new 1: SELECT ID_REGION,
Enter value for columna: ID_REGION
old 1: GROUP BY &COLUMNA
new 1: GROUP BY ID_REGION

Mientras que el símbolo ampersand (&) declara una variable sustitución temporal (vale una única vez), dos ampersand consecutivos (&&) definen una variable permanente evitando que el usuario deba ingresar nuevamente su valor.

Scripts parametrizados

Las variables de sustitución se utilizan típicamente para pasar parámetros a un script a través del comando START. Dentro del script las variables deben enumerarse secuencialmente &1, &2, etc, y éstas serán asignadas en orden con los parámetros pasados en la llamada.

Ejemplo: Un script al que le pasamos dos parámetros, un nombre de columna y la tabla

programa.sql
SET VERIFY OFF
SELECT &&1, SUM(MONTO)

FROM &2
GROUP BY &1;

La llamada se realiza de la siguiente manera:

SQL> START programa.sql ID_REGION cuentas

  • La variable 1 tiene dos ampersand por lo que queda definida a la primera vez que aparece.
  • SET VERIFY OFF elimina el feedback de old y new values.
  • Las variables de sustitución 1 y 2, al igual que las variables de usuario también se pueden borrar de memoria, por ejemplo incluyendo la línea UNDEFINE 1 en el script.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 3) - Sobre variables bind

sábado, 14 de julio de 2007

lunes, 9 de julio de 2007

Cuestiones de idioma

Muchas veces necesitamos comparar días de la semana con SQL y usamos funciones como to_char, to_date, next_day, y otras. Con ellas podemos obtener el número de día de la semana o el nombre del día o del mes, y usarlo para evaluar alguna condición.
En estos casos deberíamos preguntarnos si nuestro código depende del idioma en que esté configurado el cliente o la base de datos. Si esto es así, estamos haciendo que nuestras aplicaciones sean dependientes del lugar donde se instalen y eso no es deseable.

Recordando que el orden de preferencia para los seteos del NLS es (de mayor a menor):

1) NLS explicitado en función
2) NLS configurado en la sesión del usuario
3) NLS configurado en el cliente
4) NLS configurado en la base de datos

...es una buena práctica 'asegurar' el idioma o territorio a utilizar cuando usamos una función que depende de eso.
Yo prefiero que mis aplicaciones dependan lo menos posible de una configuración especial en el cliente o la base de datos, por lo tanto siempre voy a aplicar 1) o 2).

Ejemplo 1: Días de la semana como palabra

Obtener las marcas de los empleados los días sábados:

SELECT timestamp, employee
FROM timer_tbl
WHERE to_char(timestamp,'DAY') = 'SATURDAY';

En este caso la consulta depende del idioma que este configurado en la sesión o en el cliente o la base de datos, y rezaría para que siempre fuera inglés!
Para evitar ese tipo de ligaduras con la región, puedo pasarle a to_char el parámetro NLS_DATE_LANGUAGE y entonces estoy en el caso 1) de máxima prioridad.
Me quedaría:

SELECT timestamp, employee
FROM timer_tbl
WHERE to_char(timestamp,'DAY','NLS_DATE_LANGUAGE=AMERICAN') = 'SATURDAY';

Ejemplo 2: Número de día de la semana

En algunos territorios como Portugal y USA el primer día de la semana es el domingo, en otros como España y América del Sur es el lunes. Esto hace que la máscara 'D' de to_char sea diferente según el territorio.
El número de dia de la semana depende del parámetro NLS_TERRITORY, pero no lo puedo pasar como tercer parámetro, no es válido.

En ese caso tendré que alterar la sesión para asegurar que el día 1 sea el lunes para mi aplicación.

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> select to_char(sysdate,'D DAY') HOY from dual;

HOY
-----------------------------
1 MONDAY

Ver también:
Acentos del idioma español

jueves, 5 de julio de 2007

INNER/OUTER JOINS (ANSI)

La pregunta del día es:
¿Usando la notación ANSI para joins, es lo mismo agregar condiciónes adicionales al where que en la cláusula ON?

Si no se entendió la pregunta, pongamos un ejemplo...

¿Son estas dos consultas equivalentes?
SELECT d.departamento_id, e.nombre
FROM (departamento d INNER JOIN empleado e
ON d.departamento_id = e.departamento_id)
WHERE d.seccion_id = e.seccion;

SELECT d.departamento_id, e.nombre
FROM (departamento d INNER JOIN empleado e
ON d.departamento_id = e.departamento_id
AND d.seccion_id = e.seccion);
Pensemos esto, si usáramos el viejo estilo de join (la sintaxis ANSI apareció en 9i), tendríamos todas las condiciones juntas, sin orden específico ni marca especial del tipo "esta condición es de join".
Sucede que luego que Oracle parsea la consulta, analiza todas las condiciones que haya encontrado y según lo que tiene sobre la mesa decide cuál aplicar primero. El hecho de que nosotros incluyamos una condición en el ON o en el where, no va a ser un factor decisivo sino una diferencia documental que aporta claridad. Hagamos sino la prueba de dejar una de las cláusulas de un join compuesto fuera del ON, y colocarla en el where.... el resultado va a ser el mismo!

Si alguien quiere pruebas experimentales concretas podrá comparar los planes de ejecución y notar que internamente es exactamente lo mismo.

jueves, 28 de junio de 2007

Variables en SQL*Plus (parte 1)

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.

Aquí va la primera de ellas, más adelante revisaremos las otras.

VARIABLES DE USUARIO

Se definen de la siguiente forma:

SQL> DEFINE u_nombre = ANDREA

Para confirmar que haya quedado el valor guardado:

SQL> DEFINE u_nombre
DEFINE U_VAR1 = "ANDREA" (CHAR)


El comando DEFINE nos permite definir una cadena que se sustituye textualmente en cualquier lugar de nuestro script. Aunque quede definida como CHAR realmente no importa el tipo ya que la sustitución se realiza antes de ejecutar el código y además no incluye comillas. Por estas razones podemos jugar a 'construir' el script con nuestras variables de sustitución.
Si queremos usar la entrada como varchar le agregamos las comillas nosotros, si queremos usarla como número no las agregamos. Para recuperar el contenido de la variable usamos el símbolo ampersand.

Por ejemplo: SELECT * FROM empleado WHERE nombre = '&u_var1';

Ahora definimos una variable con un número...

SQL> define u_edad = 25
SQL> define u_edad

DEFINE U_EDAD = "25" (CHAR)

...y la usamos de la misma forma pero sin incluir las comillas:

SELECT * FROM empleado WHERE edad = &u_edad;

Las variables de usuario son útiles cuando se define su valor al comienzo de un script y luego se utiliza varias veces en el código.

No solamente podemos sustituir valores de columnas, sino también nombres de columnas o de tablas, incluso fragmentos de código, permitiéndonos hacer scripts más dinámicos.

Un uso interesante es 'aceptar' el nombre de una columna y luego usarla para parametrizar el ORDER BY de un reporte.

Ver también:
Variables en SQLPlus (parte 2) - Sobre variables de sustitución
Variables en SQLPlus (parte 3) - Sobre variables bind

miércoles, 27 de junio de 2007

Webstats

La gente de MOTIGO Webstats toma las estadísticas de este blog, pero estoy comenzando a sospechar que sus números no son muy confiables...

For Export/Import

La base de datos Oracle XE es cada vez más popular en los hogares de los programadores que en sus trabajos utilizan la 9i.
Estos exportan un schema versión 9i y lo llevan a su flamante XE portátil. Hasta aquí no hay mayores dificultades, la instalación y configuración de XE se realiza sola, y basta leer el manual Database Utilities para aprender a utilizar exp e imp.

Un archivo dump de 9i se puede importar en 10g sin especificar ningún parámetro extra.

El asunto es que para migrar de la casa al trabajo con el programa exp tendremos un problema: el archivo dump generado por XE (10g) no se puede importar en 9i, no es compatible.

Import: Release 9.2.0.1.0 - Production on Thu Jun 28 18:31:04 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

La solución es exportar desde XE con un exp 9i. El imp y el exp deben tener la misma versión cuando exportamos algo que es mas nuevo que la base destino.

No es necesario que instalemos una base de datos 9i en casa, alcanza simplemente con instalar el cliente el cual se puede descargar gratuitamente desde:

http://www.oracle.com/technology/software/products/oracle9i/htdocs/winsoft.html

Luego de instalar el cliente 9i, hay que tener en cuenta que éste utiliza su propio tnsnames.ora, por lo que vamos a tener que anexarle el servicio de nuestra base de datos XE. Finalmente ejecutaremos el export del cliente (por ejemplo: c:\oracle\ora92\bin\exp.exe) y exportaremos un archivo dump versión 9i que podrá ser importado sin problemas por otra 9i.

Un detalle: tendremos que ser cuidadosos al ejecutar programas como sql*plus, exp, imp. Cada uno de ellos referirán a sus respectivos tnsnames.ora donde están registradas nuestras bases de datos. Como al iniciarse siempre muestran la versión, sabremos cual tnsnames.ora están leyendo.

miércoles, 23 de mayo de 2007

Problemas de TNS utilizando DB links

Al definir un DB link y utilizarlo en una consulta, puede obtenerse el error:

ORA-12154: TNS:could not resolve service name

Nada parece estar mal:
  • Creamos el link utilizando el service name del tnsnames.ora
  • Verificamos que la entrada del servicio está justamente definida en el archivo
  • Revisamos que nuestra aplicación esté leyendo el tnsnames.ora correcto (en caso de que tengamos muchos)
  • El usuario y password del link funciona OK si me conecto por SQL*plus
  • Tengo el privilegio CREATE DATABASE LINK
  • Al crear el link me retorna que el link fue creado exitosamente
Al utilizar el link en un select, se obtiene el error mencionado.
La razón por la cual no encuentra el conector aún la desconozco, pero la forma de evitar el problema es:

En lugar de definir el link de esta forma:

create public database link uat1prod_lnk
connect to oramain
identified by "0racl3"
using 'produat1db';

donde 'produat1db' es el nombre del service name para ese conector (tnsnames.ora)

Definirlo así:

create public database link uat1prod_lnk
connect to oramain
identified by "0racl3"
using '(description=(address=(protocol=TCP)
(host=192.168.3.32)(port=1521))(connect_data=(sid=uat1db)))';


donde 'uat1db' es el nombre del SID de la base de datos.

Crearlo de esta manera corrige el problema. Otra ventaja adicional que me proporciona esta sintaxis, es independencia del tnsnames.ora.
Lo único que necesito es: IP, puerto y SID de la base de datos.

viernes, 18 de mayo de 2007

PL/SQL haragán

¿Es PL/SQL 'lazy' para las comparaciones?
Es decir, para evaluar (expr_a AND expr_b): si expr_a es falso entonces ¿se evalúa expr_b?
Asimismo en (expr_a OR expr_b): si expr_a es verdadero entonces ¿se evalúa expr_b?

Opción 1 -> consultar la documentación

Opción 2 -> ¡probarlo!

SQL> create table t (a int);
Table created.
SQL> create or replace function fA return boolean is
2 begin
3 insert into t values (1);
4 return (false);
5 end;
6 /

Function created.

SQL> begin
2 if ((1=0) and fA) = true then
3 NULL;
4 end if ;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select * from t;

no rows selected

Conclusión: PL/SQL es lazy. No se insertó un 1 en la tabla ya que la función fA nunca se ejecutó.

¿Y en SQL? ¿Son las condiciones 'lazy' en las consultas?


Lo podemos probar con otra función similar:

SQL>
create or replace function fB return number is
2 begin
3 insert into t values (0);
4 return (0);
5 end;
6 /


En SQL no podría ejecutar esta función, ya que realiza una operación DML:

SQL> select * from dual where ((0 = 0) and (fB = 0));

ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "EMP01.FB", line 3


Sin embargo, si cambiamos AND por OR, notamos que la función fB ni siquiera es evaluada:


SQL> select * from dual where ((0 = 0) or (fB = 0));


D
-
X

1 row selected.

Conclusión: SQL también es lazy. Si se hubiera evaluado la segunda expresión, entonces tendría que haber retornado el error ORA-14551.

domingo, 13 de mayo de 2007

ORA-01461: can bind a LONG value only for insert into a LONG column

Este error puede ocurrir cuando se insertan o actualizan valores en la base de datos desde alguna aplicación cliente, como por ejemplo Forms o Java.

En mi caso utilizando Forms 10g, intentaba grabar en un textbox dos mil y tantos caracteres en un formulario, obteniendo repetidamente el error. El campo de formulario estaba definido como CHAR de largo máximo 4000 y la columna de la base de datos era VARCHAR2 de 4000; entonces donde estaba el problema?

El problema resultó en el character set de la base de datos y el character set del cliente.
La base de datos tenía un character set multibyte, es decir que puede utilizar varios bytes para representar un caracter. El cliente (en mi caso Application Server 10g), estaba configurado con un character set single byte. Resultado: el cliente enviaba 1 byte y la base de datos almacenaba en 3 bytes. Al enviar más de la tercera parte del tamaño de la columna, se produce un bug (conocido) de Oracle en el cual supone que se le está enviando un LONG. Forms internamente utiliza bind variables para las operaciones en formularios, por lo tanto se produce el error: can bind a LONG value only for insert into a LONG column.

La solución simple es modificar el character set del cliente, en este caso del Application Server. Particularmete elegí el mismo que tenía la base de datos (UTF8).

Los datos quedan intactos, y el cambio en mi cliente es transparente. Ahora cliente y servidor tienen los mismos juegos de caracteres, y evita que se produzcan las conversiones que llevan al bug mencionado.

Para conocer el character set de la base de datos, puede ejecutarse esta consulta desde SQL*Plus:

SELECT VALUE FROM v$nls_parameters WHERE PARAMETER = 'NLS_CHARACTERSET';

jueves, 10 de mayo de 2007

Manipulando HTML en SQL*plus

"Necesito ejecutar unos scripts SQL*plus que insertan líneas de código HTML en una tabla. El problema que tengo es que cuando los valores contienen el símbolo ampersand, Oracle me pide que ingrese un valor. Como hago para evitarlo?"

Es común en HTML encontrar cosas del estilo:

<font><B>Acentuaci&oacute;n en la Gram&aacute;tica</B></font>

El símbolo ampersand en SQL*Plus suele utilizarse como prefijo standard para indicar un parámetro de sustitución en un script.

Por lo pronto, el único problema que presenta el código HTML en SQL*Plus, es para escribir el símbolo ampersand (suerte que las comillas en HTML son dobles y no simples).

Bueno, quien me planteó el problema no tenía intenciones de modificar los scripts originales, así que le di una solución simple: crear un script que invoque a los demás (con @), y que al comienzo del mismo contenga la línea:

SET DEFINE OFF

Este comando de SQL*Plus inhibe los parámetros de entrada en SQL*Plus, por lo cual va a ignorar todos los ampersand que encuentre.

Aunque la solución es simple y funciona para muchos casos, es también un poco drástica: inhibe los verdaderos parámetros que contengan nuestros scripts. Si nuestro script recibe un parámetro &valor y en la siguiente línea inserta un texto que contiene un &aacute, entonces no hay manera de que SQL*plus se de cuenta cuál es parámetro y cuál no. Conclusión: los scripts deben alterarse.

Para esto hay algunas alternativas. La primera, es cambiar el prefijo para definir parámetros, por algún símbolo que no sea caracter especial en HTML, por ejemplo, el símbolo de pesos.

SET DEFINE $

De esta manera los ampersand de HTML serán ignorados.

Una segunda alternativa, es utilizar el caracter de escape (por defecto '\') para 'escapar' los ampersand del código. Luego los ampersand no son incluídos al guardarse en la tabla. Por lo general tendremos muchos menos parámetros que símbolos ampersand, así que particularmente elijo la primer opción.

En conclusión, cambiar el prefijo de parámetros con SET DEFINE $ es la opción más flexible. Usar SET DEFINE OFF al comienzo del script nos inhibe los parámetros, pero si no los necesitamos, se transforma en la opción más sencilla de implementar.

martes, 8 de mayo de 2007

Fallo de librerías en instalación de 10g en SUSE 10

Talvez hayan tenido este mismo problema.
Mientras se instala Oracle 10.2.0 en Suse 10.0 64 bits, el Oracle Universal Installer falla en un 64% de completado, cuando esta enlazando las librerías. Al mirar el log generado, aparece lo siguiente:

INFO: ./x86_64-suse-linux/bin/ld: skipping incompatible /usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../libpthread.so when searching for -lpthread
/usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../../x86_64-suse-linux/bin/ld: skipping incompatible /usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../libpthread.a when searching for -lpthread
/usr/lib64/gcc-lib/x86_64-suse-linux/3.3.3/../../../../x86_64-suse-linux/bin/ld: cannot find -lpthread

INFO: collect2:
INFO: ld returned 1 exit status

INFO: make: *** [ctxhx] Error 1

El problema que ocurre es que Oracle necesita algunas librerías de 32 bits, y la instalación por defecto de Suse tiene todas de 64 bits. Es necesario instalar las de 32 también.

Son estas dos:

1) glibc-devel-2.3.4-2.25 (i386)
2) compat-libstdc++-33-3.2.3-47 (x86_64)

Si alguna de estas librerías están disponibles en versiones más nuevas, no hay que elegirlas, hay que instalar exactamente las versiones que acabo de marcar. Las versiones más nuevas no funcionan.
Asimismo, si ya estuviera instalada la librería compat-libstdc++-33-3.2.3-47.3, hay que desinstalarla e instalar la que sirve: compat-libstdc++-33-3.2.3-47.

Realmente es el único problema serio que me encontré, el resto mas o menos está en el manual de instalación.

martes, 1 de mayo de 2007

Mis índices no funcionan!

TOP FIVE de casos en los cuales los índices que creamos 'inexplicablemente' no funcionan.

1 - CONSULTAS QUE RETORNAN MUCHOS REGISTROS
Este es el caso de consultas que retornan muchos registros. Según algunos autores, más del 5 por ciento del total de filas, pero en realidad es un porcentaje variable que se infiere según varios parámetros como cardinalidad, cantidad de filas, distribución en los bloques, etc.
En este tipo de operaciones, es mas 'barato' para la base de datos leer toda la tabla y colocarla en memoria que acceder a cada registro mediante el índice. Hay que pensar que un índice es una estructura más que debemos consultar para recién llegar al bloque que contiene nuestro registro y traerlo a memoria, por lo cual ese acceso trae un costo adicional para cada fila. Si consultamos pocos registros, el incremento del costo por acceso a índice se absorve con el tiempo total. Si consultamos muchos registros, el overhead incrementa significativamente el tiempo de completitud.
No está mal que los índices no se usen y se produzcan 'FULL SCAN TABLES'. El optimizador sabe los elementos que tiene a mano, cual es el costo asociado a cada método y por lo tanto puede resolver qué usar en cada caso.

2 - SE APLICAN FUNCIONES SOBRE COLUMNAS INDIZADAS
Cuando aplicamos cualquier función en una columna indizada en la cláusula WHERE, el índice deja de tener efecto.
Un simple TRUNC sobre una columna fecha, o un TRIM en una columna VARCHAR2, deja sin efecto a los índices sobre esas columnas.

SELECT nombre, TRIM(apellido)
FROM socios
WHERE SUBSTR(codigo,1,3) = 'DNO'
AND apellido > 'P';

En este ejemplo, un índice sobre la columna codigo no es de utilidad, ya que la función SUBSTR lo anula. Un índice sobre apellido puede funcionar sin problemas ya que no hay ninguna función aplicada en la cláusula WHERE (notar que sí la hay en el SELECT).

3 - CONVERSIONES IMPLICITAS
Por efecto del punto anterior, hay algunos casos en los que no tenemos en cuenta los tipos de datos en el WHERE, y dejamos que se realicen conversiones automáticas. Esto no es preferible, ya que Oracle siempre convierte la columna al tipo del elemento comparante.

Por ejemplo, una consulta como la siguiente:

SELECT nombre, TRIM(apellido)
FROM socios
WHERE nro_socio = '2232';

internamente es traducida como:

SELECT nombre, TRIM(apellido)
FROM socios
WHERE TO_CHAR(nro_socio) = '2232';

...y el índice sobre nro_socio es inhibido por la conversión implícita.

Siempre es bueno convertir explícitamente este tipo de condiciones en donde intervienen diferentes tipos de datos.
Otras opciones son: estandarizar el almacen de datos (por ejemplo guardando siempre los valores con la función aplicada), usar una función de conversión sobre el literal en lugar de en la columna, crear índices de función.

4 - EL PREDICADO NO ES ADECUADO
Por ejemplo, tenemos un índice compuesto formado por las columnas a, b y c. Se quiere realizar una consulta incluyendo en la cláusula WHERE la condicion b (por la cual queremos usar el índice), pero no se incluye a!
Aunque la columna a almacene el mismo valor para todos los registros, Oracle necesita explícitamente todas las columnas previas entre las condiciones del where. De la misma manera, si queremos usar una condición sobre c, deberíamos incluir en el where condiciones para a y b.
Otro caso es cuando tenemos un OR en el where, un distinto != sobre la columna indizada, o cuando la columna aparece a ambos lados de una expresión.

5 - ESTADISTICAS OBSOLETAS (O INEXISTENTES)
El optimizador de la base de datos construye un plan de ejecución basado enteramente en estadísticas de los objetos, almacenadas en el dicionario de datos. La recolección de estas estadísticas debe realizarse periódicamente para que Oracle sepa cuales son los costos asociados a cada método de acceso a los registros, y de esta manera elegir la mejor opción. Al insertar y eliminar datos frecuentemente, cambian las estadísticas quedan obsoletas, y esto puede traer aparejado un impacto directo en la performance de las consultas.

Pueden recolectarse estadísticas de todo el esquema, de una tabla, de un índice, del sistema. El paquete DBMS_STATS contiene procedimientos para hacer el trabajo: GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_SCHEMA_STATS.
Para saber si una tabla tiene estadísticas, puede utilizarse la siguiente consulta:

SELECT last_analyzed
FROM user_tables
WHERE table_name = 'mi_tabla';

Retorna la fecha de la ultima recolección. Si retorna NULL, entonces nunca fueron recolectadas.

Ver también:
Indices condicionales
11g y sus índices invisibles
Indices de función para mejorar un LIKE

viernes, 27 de abril de 2007

Generar spools de más de 2 Gigas en Unix

Hay varias formas de generar spools con sql*plus desde Unix. La forma tradicional (o directa) es escribir un shell que realice lo siguiente:

-Invocar a sql*plus en modo embebido.
-Ejecutar spool y realizar el SELECT.
-Finalizar el spool con spool off.
-Salir de sql*plus.
-Zipear el archivo de salida (recomendado si se va a enviar a otro destino).

El problema que presenta este enfoque, es que en algunos sistemas operativos, puede existir el límite de 2 Gigas de tamaño para archivos, por lo cual nuestro script va a alimentar el spool hasta llegar a ese tamaño. En el mejor de los casos obtendremos un error inesperado, en otros quizás ni nos enteremos.

Una forma alternativa (y para mi gusto más elegante), es utilizar el comando mknod de Unix. Este comando, entre otras funcionalidades, permite crear un pipe de tipo FIFO que funciona como un dispositivo que utiliza el buffer del sistema operativo y permite conectarlo directamente con un programa de compresion como compress o gzip, evitando que se utilice el file system para almacenar el spool sin comprimir. También es posible conectarlo al programa split para que particione el archivo zip, si este continúa superando el límite máximo.

Básicamente las tareas que hay que escribir en nuestro shell son:
-Borrar pipes que hayan quedado anteriormente (puede fallar si ya existe)
-Crear el pipe
-Asignarle permisos al pipe (con chmod)
-Invocar en modo background al programa destino (el que va a recibir el spool). Este podría ser gzip, split, o ambos.
-Invocar a sqlplus para que realice el spool
-Borrar el pipe creado

Resulta poco intuitivo llamar primero al programa destino y luego en definitiva a quien genera el spool, pero veamos un ejemplo de código para visualizarlo más claramente.

Este script genera un spool y lo comprime en un zip:

1   #/bin/ksh
2 rm -f ${ARCHIVO}.pipe 2>/dev/null
3 mknod ${ARCHIVO}.pipe p
4 gzip -f < ${ARCHIVO}.pipe > ${ARCHIVO}.txt.Z 2>>${LOG} &
5 sqlplus -s usuario/password << EOF > /dev/null 2>> ${LOG}
6 spool ${ARCHIVO}.pipe
7 select * FROM user_objects;
8 spool off
9 EOF
10 rm -f ${ARCHIVO}.pipe 2>/dev/null
En la línea 2 eliminamos pipes que hayan podido quedar abiertos por alguna razón. De quedar abierto obtendríamos un error al crearlo de nuevo.

En la línea 3 creamos el pipe. El primer parámetro es el nombre y el segundo el tipo (p=FIFO)

En la línea 4 invocamos en background a gzip (notar el & al final de la linea). El pipe (el cual todavia no ha recibido datos), va a ser la entrada fuente para gzip. Luego gzip generará el archivo .txt.Z.

Las líneas 5 a 8 llaman a sqlplus y comienzan a enviar datos al pipe, luego gzip que esta corriendo en background zipea el contenido, sin necesidad de tener un archivo en file system.

La línea 10 elimina el pipe.

Si el archivo final .zip alcanza los 2 Gigas, vamos a tener el mismo problema, gzip no va a poder crearlo. Es necesario adicionarle el comando split y conectarlo con gzip para que parta el archivo en secciones de un tamaño manejable.
Para eso, hay que insertar entre la línea 3 y la 4 del script anterior, la siguiente línea:

3.5 split -b 1024m < ${ARCHIVO}.txt.Z &

Conviene leer primero la línea 4 y luego la 3.5, observar que ambos programas corren en background.

La línea 4 es la misma, genera el archivo .txt.Z.

La línea 3.5 recibe el archivo .txt.Z y lo divide en archivos de 1024 megas, el tamaño es en bytes (especificado por el parámetro b).

Sea cual sea el programa que genera el archivo, siempre podemos manipular con pipes los datos sin llegar a utilizar un archivo del file system para pasos intermedios.

Próximos pasos:
MAN del comando mknod
MAN del comando split

lunes, 23 de abril de 2007

Ahí Vamos!

Como siempre, hay una primera vez para todo!!
La idea de comenzar mi blog surgió por sugerencia de mi amigo Fede Brubacher, adicto a RoR y otras tecnologías emergentes. El, que me conoce y ha sufrido mi orientación a las bases de datos relacionales, movilizó algunos mecanismos de motivación y curiosidad, con el simple hecho de mostrarme su trabajo:
http://fbrubacher.com/

"Dedicado a aquellos que con sus blogs técnicos me ahorraron tantas horas de trabajo y esfuerzo"

Sin más reflexiones doy por inagurado este blog.

Sobre el autor

Lucio Fernández Herrera
Ingeniero de Sistemas (UDELAR, 2003)
Oracle OCP DBA Certified (2006)
Consultor Oracle Independiente

OCP DBA Certification logo


Perfil Técnico


:: Bases de datos ::
Oracle databases 8i/9i/10g/11g, MySQL, SQL Server

:: Tecnologías ::
Unix, PL/SQL, Apex, Forms, PHP, J2EE, Data Guard, Sun Cluster

:: Áreas de especialización ::
Desarrollo, análisis y diseño, administración, datawarehousing


Me desempeño como DBA y especialista en PL/SQL en proyectos con J2EE. Actualmente resido en Rio de Janeiro, Brasil.
Realizo consultorías de instalación, configuración, administración, tunning, entre otras diversas áreas de desarrollo.
En los diversos proyectos en los cuales he participado aparece un común denominador que es la falta de un conocimiento avanzado de la herramienta que se está utilizando. Esto lleva a efectos tales como la mala performance de los sistemas y sobreesfuerzo de las personas. En ese sentido el propósito de este blog es dar mi pequeño aporte para aliviar y ayudar a que el trabajo sea cada vez más efectivo.
En la carrera de bases de datos, mi motivación es seguir creciendo y aprendiendo día a día en un campo que debe soportar y acompañar a las nuevas tecnologías.

Contactar al autor