Tipos de Datos Fecha

muchas veces me he encontrado con problemas relacionados a la búsqueda de información basada en fechas.

El siguiente tipo de query es muy común:

SQL> select count(1)
2 from ra_customer_trx_all
3 where trunc(creation_date) between
4 to_date('01/04/2008','dd/mm/yyyy')
5 and to_date('30/04/2008','dd/mm/yyyy');

COUNT(1)
----------
206171

Y siempre encuentro el mismo razonamiento de los desarrolladores. "Debido a que la fehca viene guardada con un timestamp, es necesario poner un trunc()".

Y mi respuesta siempre es la siguiente:

Oracle no es que guarde una fecha con o sin formato. Oracle guarda una fecha! Creo que el paradigma de que oracle guarda un formato es erroneo, Oracle solo guarda un tipo de dato fecha, y las sesiones son las que llevan un formato. por esa rozón siempre recomiendo cambiar el tipo de query de arriba por el siguiente:

SQL> select count(1)
2 from ra_customer_trx_all
3 where creation_date >= to_date('01/04/2008','dd/mm/yyyy')
4* and creation_date < to_date('30/04/2008','dd/mm/yyyy') + 1

COUNT(1)
----------
206171

El resultado es el mismo, y en caso de tener un índice creado en el campo creation_date seguramente será usado, de forma contraria al trunc(), ya que al aplicar una función al campo, se evita el uso del índice.

Para poder revisar cómo se guarda un tipo de dato fecha en oracle, hacemos lo siguiente:

SQL> create table prueba (campo1 char(3),campo2 date, campo3 char(3));

Tabla creada.

SQL> insert into prueba values('XXX',to_date('01/01/2000','dd/mm/yyyy'),'YYY');

1 fila creada.

SQL> commit;

Confirmaci¾n terminada.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) "Datafile",
  2    dbms_rowid.rowid_block_number(rowid) "Bloque"
  3  FROM prueba;

  Datafile     Bloque
---------- ----------
         1      63314

SQL> alter system dump datafile 1 block 63314;

Sistema modificado.

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug tracefile_name;
c:\oracle\product\admin\orcl\udump\orcl_ora_3196.trc
 

Una vez que hacemos el dump del bloque y sabiendo que el heade del bloque crece de arriba hacia abajo y los datos del bloque de abajo hacia arriba, nos vamos al final del bloque

ABCC7D0 19330E1E 066C7807 353B1303 30303213  [..3..xl...;5.200]
ABCC7E0 38302D35 3A30332D 03012C31 58585803  [5-08-30:1,...XXX]
ABCC7F0 01647807 01010101 59595903 68820601  [.xd......YYY...h]
Block header dump:  0x0040f752

Vemos que se repiten 3 veces el 0x58 y 3 veces el 0x59

0x58 = 88
0x89 = 89

Con estos valores vemos lo siguiente

SQL> select chr(88) from dual;

C
-
X

SQL> select chr(89) from dual;

C
-
Y

De esta forma sabemos que en el registro que insertamos, la fecha se guarda entre 58585803 y 59595903 y la parte que nos interesa correspondiente a la fecha es la siguiente

01647807 01010101

0x01 = 1 = Mes 1
0x64 = 100 = Año 00
0x78 = 120 = Siglo 20
0x07 = 7 = Longitud de 7 bytes
0x01 = 1 = Hora 00
0x01 = 1 = Minuto 00
0x01 = 1 = Segundo 00
0x01 = 1 = Día 1

De esta forma sabemos que Oracle, se use o no un formato, siempre guarda un tipo de datos fecha y no un formato. y que Oracle los datos que guardan son:

Siglo

Año

Mes

Dia

Hora

Minuto

Segundo

y todo esto ya sea insertando una fecha 'dd/mm/yyyy hh24:mi:ss' o simplemente 'dd/mm/rr'.

Carga del sistema y excesivos context switches

Muchas veces creemos que todos los problemas de desempeño en Oracle se encuentran dentro de la instancia, es decir, algún query mal afinado, una estructura de memoria mal definida, etc...

En alguna ocasión, tras haber realizado un upgrade de 9.2.0.6 a 10.2.0.2 en un servidor Linux, sucedió algo que no se esperaba y que en las "Pruebas de estrés" (que fueron casi nulas) no se detectó.

El problema era el siguiente, la carga del servidor, al empezar a recibir múltiples conexiones de oracle se elevaba a un 80% de consumo de CPU de systema, el encolamiento en CPU llegaba a 120 puntos de carga y el sistema de forma global se sentía completamente lento.

La configuración era la siguiente

Base de datos: 10.2.0.2 con CPUs aplicados.
Servidor: Red Hat 4, 40gb de memoria 32-bit
SGA: 16gb con INDIRECT_DATA_BUFFERS sobre ramfs

Se pudo comprobar que la carga del equipo se elevaba si se generaban conexiones de forma simultánea con los siguientes scripts.

prueba.sql

select * from dual;
exit;

conexiones.sh

#!/usr/bin/ksh
export ORACLE_HOME=/u01/oracle/product/10.2.0
export ORACLE_SID=ORCL

sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &

Previo a enviar el script conexiones.sh, la carga del sistema era similar a esta

oracle@server1:~> w
 12:36:53 up 13 days, 20:09,  5 users,  load average: 0.64, 0.45, 0.39

Una vez ejecutado el script

oracle@server1:~> nohup ./conexiones.sh

oracle@server1:~> nohup: appending output to `nohup.out'

oracle@server1:~> w
 12:36:53 up 13 days, 20:09,  5 users,  load average: 110.22, 30.57, 4.31

Una vez que teníamos nuestro test case, lo intentamos reproducir en algún otro equipo similar con menor SGA (sin uso de INDIRECT_DATA_BUFFERS) y la carga del servidor no incrementaba drásticamente.

Se hizo una prueba en producción y se disminuyó el SGA para poder usar la memoria sin necesidad de ramfs. La prueba del script funcionó correctamente, y de esta forma utilizamos el Workaround, pero el db_block_buffers de 8gb se redujo a menos de 1gb
en el db_cache_size, y por lo mismo sólo podía ser aceptado como un workaround.

Se mandaron varios "strace" para ver en qué perdía el tiempo o generaba carga de sistema la conexión de oracle, y el resultante fueron dos funciones a nivel sistema operativo.

mmap y remap_file_pages

La parte de remap_file_pages se solucionó con un parche a nivel RDBMS, pero la mejoría fue muy poca, digamos que el sistema mejoró en un 5% su desempeño.

La parte de mmap, se solucionó con una variable a nivel sistema operativo

DISABLE_MAP_LOCK=1

Esta variable debe de estar puesta en la sesión que levanta la base de datos Oracle y por supuesto en la sesión que levante el listener. La mejora en este caso fue de un 80%.

El workaround quedó atrás y con esta variable, el sistema volvió a la normalidad. Al parecer este problema es un backport de un bug a la versión 10.2.0.1 y se corrigió nuevamente en la versión 10.2.0.3

Muchas veces los problemas de desempeño van relacionados a bugs, o a configuraciones específicas, esta es una de las razones por las cuales Oracle nos invita a llevar siempre nuestros RDBMSs a las versiones más nuevas.

Bind Variables

Mucho se ha dicho sobre los bind variables y los problemas que pueden causar en una aplicación.

Hay muchas entradas en asktom que ejemplifican perfectamente las ventajas sobre el uso de bind variables.

Me he llegado a encontrar con muchas aplicaciones que fueron desarrolladas para SQL server y que los proveedores simplemente cambian el código de la aplicación sin tener en cuenta las consideraciones mínimas sobre las mejores prácticas de programación en Oracle.

Oracle tiene un mecanismo para convertir los valores literales en variables bind. La forma de hacerlo es alterando el sistema o sesión para poner el parámetro "CURSOR_SHARING" en similar o en force. Antes de intentar este cambio, consulten bien la documentación de oracle para saber las implicaciones que esto pueda tener.


La forma en que funciona es la siguiente...

SQL> SELECT /*+PRUEBA 1*/ 1234 from dual;

      1234
----------
      1234

SQL> SELECT /*+PRUEBA 1*/ 4321 from dual;

      4321
----------
      4321

SQL> select sql_text,executions
  2  from v$sql
  3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Ahora ponemos el cursor_sharing en similar...

SQL> alter session set cursor_sharing=similar;

Sesión modificada.

SQL> SELECT /*+PRUEBA 2*/ 1234 from dual;

      1234
----------
      1234

SQL> SELECT /*+PRUEBA 2*/ 4321 from dual;

      4321
----------
      4321

SQL> select sql_text,executions
  2  from v$sql
  3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 2*/ :"SYS_B_0" from dual                   2
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Como se puede ver, funciona de maravilla, no?. La realidad es que no siempre sucede esto...

SQL> DECLARE numero NUMBER;
  2  BEGIN
  3    SELECT
  4     /*+PRUEBA 3*/ 1234
  5    INTO numero
  6    FROM dual;
  7  END;
  8  /

Procedimiento PL/SQL terminado correctamente.

SQL> select sql_text,executions
  2  from v$sql
  3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 2*/ :"SYS_B_0" from dual                   2
SELECT /*+PRUEBA 3*/ 1234 FROM DUAL                         1
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Como se puede observar, a pesar de que se tiene cursor_sharing en similar o incluso force, el código no cambia de literal a bind variable.

Para esto existe una solución...

SQL> create or replace procedure usa_bind(num number)
  2  is
  3  numero number;
  4  BEGIN
  5    SELECT
  6     /*+PRUEBA 4*/ num
  7    INTO numero
  8    FROM dual;
  9  END;
 10  /

Procedimiento creado.

SQL> begin
  2  usa_bind(4444);
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL> select sql_text,executions
  2  from v$sql
  3  where sql_text like 'SELECT%PRUEBA%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
SELECT /*+PRUEBA 2*/ :"SYS_B_0" from dual                   2
SELECT /*+PRUEBA 3*/ 1234 FROM DUAL                         1
SELECT /*+PRUEBA 1*/ 4321 from dual                         1
SELECT /*+PRUEBA 4*/ :B1 FROM DUAL                          1
SELECT /*+PRUEBA 1*/ 1234 from dual                         1

Como se puede observar, la entrada como "PRUEBA 4" ya está usando un bind variable a través de un procedimiento. Pero esto nos genera un nuevo problema...

SQL> begin
  2  usa_bind(1234);
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL>
SQL> exec usa_bind(3214);

Procedimiento PL/SQL terminado correctamente.

SQL> select sql_text,executions
  2  from v$sql
  3  where upper(sql_text) like '%USA_BIND%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
begin usa_bind(4444); end;                                  1
BEGIN usa_bind(3214); END;                                  1
begin usa_bind(1234); end;                                  1

Así que esto nos pone en el mismo lugar en el que empezamos. Aparentemente esta es una funcionalidad de oracle y no un bug. Todo está documentado en la nota de Metalink 285447.1. Donde nos indica que el código que está entre un begin y un end, no será modificado de literal a bind.

Pero existe aún una solución a este problema.

SQL> call usa_bind(4444);

Llamada terminada.

SQL> call usa_bind(1234);

Llamada terminada.

SQL> call usa_bind(1111);

Llamada terminada.

SQL> call usa_bind(2222);

Llamada terminada.

SQL> select sql_text,executions
  2  from v$sql
  3  where upper(sql_text) like '%CALL USA_BIND%';

SQL_TEXT                                           EXECUTIONS
-------------------------------------------------- ----------
call usa_bind(:"SYS_B_0")                                   4

De esta forma estaremos evitando un hard parse a la hora de mandar llamar nuestras ejecuciónes. Desgraciadamente, esto sugiere un cambio en la programación y por lo mismo muchas veces imposible si el código no está disponible