Conexiones en Windows vs. Linux

Recientemente queriendo hacer unas pruebas (sobre algo que no pude demostrar), me pregunté sobre las conexiones en windows vs. conexiones en Linux.

De forma arquitectónica ambas son diferentes, en windows, la arquitectura está basada en threads, los threads son objetos dentro de un proceso que ejecutan instrucciones, y permiten la ejecución "simultanea" de instrucciones dentro del proceso, para que código se ejecute en diversos procesadores. En teoría, las ventajas de este modelo son:

1.- Context Switches más rápidos.

2.- Implementación más sencilla de SGA (ya que no requiere un shared memory).

3.- Creación más rápida de conexiones (ya que crear un thread es más rápido que crear un proceso).

4.- Decremento en el uso de memoria. Esto es porque se pueden compartir más estructuras de memoria.

Estoy muy de acuerdo en la teoría con todo lo anterior, sin embargo, como diría Jonathan Lewis "Just because it's written, it doesn't mean it's true...".

Para hacer las pruebas, hice un pequeño código en Perl que abre "n" número de conexiones a la base de datos y mide el tiempo que tarda en realizar las conexiones.

el código es el siguiente:

conecta.pl

use DBI;
use Time::HiRes qw(gettimeofday tv_interval);
$cont = 100;
$t0=[gettimeofday];
for ($inicio=1;$inicio<=$cont;$inicio++){
    $dbh[$inicio] = DBI->connect( 'dbi:Oracle:HECG',
                            'system',
                            '******',
                          { RaiseError => 1, AutoCommit => 0 }
                          ) || die "No se pudo conectar a la base de datos: $DBI::errstr";
 #$sql = qq{select count(1) from v\$database};
 #$dbh[$inicio]->do($sql);
 }
$elapsed = tv_interval ($t0);
print "Las ".$cont." conexiones se realizaron en ";
print $elapsed ." segundos\n\n\n";
print "presiona cualquier tecla para terminar... ";
$dummy = < STDIN> ;
for ($fin=1;$fin<=$cont;$fin++){           
$dbh[$fin]->disconnect;
}

¿Por qué lo hice en Perl?, por dos sencillas razones:

1.- No soy programador, y decidí no complicarme la vida.
2.- Porque quería ejecutar el mismo código en Linux que en Windows.

Antes de ejecutar mi script, mi proceso Oracle se encontraba de esta forma:



Ejecuté el código para 100 conexiones e windows, y los resultados son los siguientes

C:\oracle\product\10.2.0\NETWORK\ADMIN>%PH%\perl.exe conecta.pl
Las 100 conexiones se realizaron en 4.53125 segundos

presiona cualquier tecla para terminar...

Vemos que el proceso incrementó el número de threads en casi 100 (quizá en el inicio se tenían algunos jobs corriendo, y por eso subió de 23 a 122).

El consumo de memoria fue de 70 mb. por las 100 sesiones, lo cuál significa que cada sesión inicialmente ocupa 700k.

El tiempo que tardaron las 100 conexiones fue de 4.5 segundos.

Ahora el código del script se lanzó en Linux, y el resultado fue el siguiente:

$ perl conecta.pl
Las 100 conexiones se realizaron en 3.14312 segundos

presiona cualquier tecla para terminar...

Las conexiones se ejecutaron mucho más rapidas en Linux que en Windows. Intentamos con 200 y 400 conexiones.

Para 200 el tiempo fue lineal:

Windows - 9.02403 Segundos
Linux   - 6.32876 Segundos

La sorpresa en 400 conexiones fue la siguiente:

Windows - 18.23311 Segundos
Linux   - 32.75434 Segundos

El tiempo fue lineal para windows, pero para linux no, la razón...

En linux cada sesión ocupaba 1.8 mb de memoria aproximadamente (720 mb sólo en sesiones), más el tamaño del SGA ~200Mb, más la memoria ocupada por el sistema operativo... nos daban un total mayor a la memoria física del equipo, y por esta razón el equipo empezó a hacer uso de Swap.

Lo cual nos lleva a concluir lo siguiente:

1.- Efectivamente la arquitectura de Windows utiliza mejor los recursos de memoria, ya que usa threads en lugar de procesos.

2.- La creación de threads NO ES MÁS RÁPIDA que la creación de procesos.

¿Qué arquitectura recomiendo yo?, definitivamente depende de muchos factores, pero se deben de tener en cuenta todos los factores antes de decidir la arquitectura correcta.

En un sistema que no cuenta con mucha memoria física el servidor de base de datos y que va a recibir muchas conexiones dedicadas, podría decir que un windows sería algo bueno, sin embargo, si las conexiones no van a ser persistentes, esta arquitectura podría traer problemas con memoria "non paged pool".

Por el otro lado, si se usa un equipo Linux, el equipo podría quedarse muy rápido sin memoría física, y el sistema operativo se degradaría en cuanto a desempeño.

Sé que hay muchas cosas más que probar, quizás el uso de Bequeath en lugar de SQL*Net, el comportamiento a través de Shared Servers, etc., pero eso se queda para otro post.

Selects y commits a través de DB Links

Me he encontrado en muchas ocasiones problemas de desempeño por un mal diseño de la programación, generalmente por el no usar bind variables, algunas por usar bind variables cuando se podría usar una literal en histogramas. Pero generalmente los problemas más comunes son los excesos de commits.

El "Commit" es un serializador en oracle, es algo que por un pequeño instante frena todo lo que está sucediendo para lograr un:

"Log File Sync"


Una vez que se realiza el log file sync, oracle a través de redo logs nos permite recuperar una base de datos en caso de un crash en el momento hasta el punto en donde dimos commit.

Pero hay desarrolladores que abusan del uso del commit, no sé si sea que vienen desarrollando para otros manejadores de bases de datos que requieren commits a cada rato para poder permitir lecturas a más usuarios sobre bloques de datos o simplemente porque desconocen cómo ejecutar código de forma óptima dentro de oracle.

Hay un punto que no siempre es considerado por los desarrolladores, los "SELECTS" a través de db links son transacciones y que el estar dando commits muy frecuentes, puede ocasionar un problema de desempeño. Para ejemplificarlo, haremos a prueba enb un ciclo que tiene un commit dentro del ciclo, y otro ejemplo con commit fuera del ciclo:

SQL> CREATE DATABASE link LOOPBACK
  2  CONNECT TO system
  3  identified by alanis11
  4  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hugo-win)
  5         (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

Enlace con la base de datos creado.

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10046 trace name context forever, level 8;
Sentencia procesada.

SQL> set timing on
SQL> declare
  2    numero number:=0;
  3  begin
  4    for registro in (select object_id from dba_objects)
  5    loop
  6    select /*+ Sin Commit*/
  7    count(1) into numero
  8    from dba_objects@loopback
  9    where object_id = registro.object_id;
 10    end loop;
 11    commit;
 12  end;
 13  /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:19.84

SQL> declare
  2    numero number:=0;
  3  begin
  4    for registro in (select object_id from dba_objects)
  5    loop
  6    select /*+ Con Commit*/
  7    count(1) into numero
  8    from dba_objects@loopback
  9    where object_id = registro.object_id;
 10    commit;
 11    end loop;
 12  end;
 13  /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:02:53.26

Como se puede ver, sólo estamos haciendo un select, no existe update,delete o insert, sin embargo, al ser a través de un dblink, entra como transacción y al tener los commits, se pierde mucho tiempo en esperas.

Al revisar las esperas encontramos lo siguiente. Para el commit fuera del ciclo:

SELECT /*+ Sin Commit*/ COUNT(1) 
FROM
 DBA_OBJECTS@LOOPBACK WHERE OBJECT_ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute  50660      5.73       8.83          0          0          0           0
Fetch    50660      5.75       9.95          0          0          0       50660
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   101321     11.48      18.80          0          0          0       50660

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to dblink                  101322        0.00          0.24
  SQL*Net message from dblink                101322        0.00         14.17

Y para el caso del commit dentro del ciclo:

SELECT /*+ Con Commit*/ COUNT(1) 
FROM
 DBA_OBJECTS@LOOPBACK WHERE OBJECT_ID = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  50660     33.84     142.56      50630      14211     182788           0
Fetch    50660      7.18      15.94          0          0          0       50660
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   101321     41.03     158.50      50630      14211     182788       50660

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to dblink                  151981        0.00          0.46
  SQL*Net message from dblink                151981        0.00         28.84
  db file sequential read                     50754        0.26         59.72
  latch: cache buffers lru chain                  1        0.00          0.00
  rdbms ipc reply                               621        0.21          5.01
  control file sequential read                 2294        0.13          6.28
  Data file init write                          684        0.26         11.90
  flashback buf free by RVWR                     63        0.03          0.06
  control file parallel write                   434        0.06          0.77
  db file single write                           62        0.16          0.21
  latch: object queue header operation            1        0.00          0.00
  enq: CF - contention                            1        0.01          0.01

El manual de conceptos dice lo siguiente:

"Cuando un objeto es referenciado, o el uso de una función remota es ejecutada, los database links establecen una conexión a una sesión en la base de datos remota a nombre de la sesión local. La conexión remota y la sesión son creadas únicamente si no han sido previamente creadas para el usuario que la solicitó.

Las conexiones y sesiones establecidas, persisten durante la existencia de la sesión local, a menos que la aplicación, o el usuario force de manera manual la terminación de la mima.

Hay que notar, que cuando se ejecuta en select a través de un database link, un bloqueo transaccional es requerido en los segmentos de undo. Para liberar los segmentos de undo de estos bloqueos, es necesario dar un commit o rollback."

Y efectivamente las esperas por "db file sequential read" son todas por algún segmento de undo:

WAIT #4: nam='db file sequential read' ela= 222 file#=2 block#=54448 blocks=1 obj#=0 tim=88041128812

SQL> SELECT /*+RULE*/
  2  segment_name, segment_type
  3  FROM dba_extents
  4  WHERE file_id = 2
  5   AND 54448 BETWEEN block_id
  6   AND block_id + blocks -1;

SEGMENT_NAME      SEGMENT_TYPE
----------------- ------------------
_SYSSMU2$         TYPE2 UNDO

Cuando por primera vez me topé con algo similar con un cliente, la optimización de su proceso consistió en eliminar los commits innecesarios, y dejar sólo un commit al final de su procedimiento, de esta forma se respetó la atomicidad del procedimiento, y se logró tener un desempeño óptimo.

Uso de RMAN para Respaldos

Recuerdo a mi primer Jefe cuando empecé a desempeñar mi trabajo como DBA, lo que más me recalcó fue mi responsabilidad no sólo de poder realizar backups, sino de poder restaurar la información de mis backups. Esta responsabilidad es la que me gusta transmitir a todos los DBAs con los cuáles he interactuado.

De nada sirve tener un backup si no vas a poder recuperar la información.

A menudo me preguntan sobre las formas de respaldos de bases de datos oracle, y siempre respondo que lo mejor es el uso de RMAN. ¿Qué tan complejo puede ser RMAN?, la realidad es que ya configurado es muy sencillo, o en su forma más simple, sacar un respaldo es cuestión de un comando en 2 palabras:

C:\Documents and Settings\Hugo\Escritorio>rman target /

Recovery Manager : Release 10.2.0.3.0 - Production on Mié Jul 23 13:21:30 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

conectado a la base de datos destino: ORCL (DBID=524232147)

RMAN> backup database;

¿Y el restore?

C:\Documents and Settings\Hugo\Escritorio>rman target /

Recovery Manager : Release 10.2.0.3.0 - Production on Mié Jul 23 13:22:35 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

conectado a la base de datos destino: ORCL (DBID=524232147)

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

Claro es sencillo porque la base de datos está en modo archive, a pesar que no estoy usando un catálogo de RMAN (que sería lo más recomendable), el restore es muy sencillo.

La realidad es que no siempre tenemos este esquema "utópico" a nivel base de datos. En alguna ocasión me tocó clonar la base de datos de un cliente con RMAN, tenían la base de datos en modo Archive, hicieron un hotbackup y al final del día, la información que se restauró sobre la instancia de desarrollo estaba corrupta.

Lo que sucedió fue lo siguiente:

El cliente estaba realizando algunas cargas de información sobre sus tablas.

El "DBA" para realizar las cargas más rápidas, decidió usar el método de "Direct Load" (que me pareció muy buena opción), sobre la tabla alterada con nologging (que me pareció una pésima opción).

A la hora de realizar el respaldo, e incluso los archives, no se tenía rastro de los insert sobre la tabla; se tenían rastros de los incrementos de extents y del movimiento del HWM. Para ejemplificar lo sucedido podemos hacer lo siguiente con dos sesiones:

Primera sesión en SQL*Plus:

SQL> alter table prueba nologging;

Tabla modificada.

SQL> begin
  2  for a in 1..100
  3  loop
  4  insert /*+APPEND*/ into prueba (select substr(object_name,1,3),CREATED,substr(owner,1,3)from dba_objects);
  5  commit;
  6  dbms_lock.sleep(2);
  7  end loop;
  8  end;
  9  /

y en una sesión de RMAN

C:\Documents and Settings\Hugo\Escritorio>rman target /

Recovery Manager : Release 10.2.0.3.0 - Production on Mié Jul 23 15:58:03 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

conectado a la base de datos destino: HECG (DBID=524232147)

RMAN> backup database plus archivelog;

Con el comando plus archivelog, nos aseguramos de que todos los archives necesarios para restaurar este backup se incluyan, e incluso realiza un switch log al finalizar y respalda los archives nuevamente.

La sesión de SQL*Plus termina, el backup termina y empezamos con el restore:

Ahora, pensando en que tuviéramos una falla en ese momento y queremos recuperar, podemos empezar un restore a cierta fecha (que es cuando el respaldo terminó):

RMAN> shutdown abort;

RMAN> startup mount

instancia Oracle iniciada

Total del Área Global del Sistema     289406976 bytes

Fixed Size                     1290184 bytes
Variable Size                272629816 bytes
Database Buffers               8388608 bytes
Redo Buffers                   7098368 bytes

base de datos montada

RMAN> run {
2> set until time "to_date('23-07-2008 16:01:12','dd-mm-yyyy hh24:mi:ss')";
3> restore database;
4> recover database;
5> }

ejecutando el comando: SET until clause

Iniciando restore en 21-JUL-08
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=156 devtype=DISK

canal ORA_DISK_1: iniciando restauraci+¦n del juego de copias de seguridad de archivos de datos
canal ORA_DISK_1: especificando archivo(s) de datos para restaurar del juego de copias de seguridad
restaurando el archivo de datos 00001 en C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
restaurando el archivo de datos 00002 en C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
restaurando el archivo de datos 00003 en C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
restaurando el archivo de datos 00004 en C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
restaurando el archivo de datos 00005 en C:\ORACLE\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
restaurando el archivo de datos 00006 en C:\CRYPTO\ORCL\DATAFILE\O1_MF_ORCL_42K6N37T_.DBF
restaurando el archivo de datos 00007 en C:\PRUEBA01.DBF
canal ORA_DISK_1: leyendo desde la parte de copia de seguridad C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_07_21\O1_MF_NNNDF_TAG20080721T093726_4897S8PY_.BKP

RMAN> alter database open resetlogs;

Se debe de utilizar un resetlogs ya se usó una recuperación incompleta.

Ahora entramos a SQL*Plus y ejecutamos un sql contra la tabla prueba

SQL> select count(1) from prueba;
select count(1) from prueba
*
ERROR en línea 1:
ORA-01578: bloque de datos ORACLE corrupto (archivo número 4, bloque número 10251)
ORA-01110: archivo de datos 4: 'C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF'
ORA-26040: Se ha cargado el bloque de datos utilizando la opción NOLOGGING

La tabla queda en un estado inconsistente porque el diccionario de datos (que sí genera redo) registró el crecimiento de bloques en la tabla, pero el objeto como tal no.

Hay algunas formas de recuperar algo de información si es que los índices estaban en modo logging, pero creanme... estamos en el peor caso de recuperación.

Dado todo lo anterior, creo que sólo se me ocurre una posibilidad para evitar esto durante un backup. Y es uno de los comandos que se suelen usar en dataguard

RMAN> run {
2> sql "alter database force logging";
3> backup database include current controlfile plus archivelog;
4> sql "alter database no force logging";
5> }

Iniciando backup en 23-JUL-08
log actual archivado
usando el canal ORA_DISK_1
canal ORA_DISK_1: iniciando juego de copias de seguridad de archive log
canal ORA_DISK_1: especificando archive log(s) en el juego de copias de seguridad
thread de archive log de entrada=1 secuencia=1 recid=122 marca=660762972
thread de archive log de entrada=1 secuencia=2 recid=123 marca=660763141
thread de archive log de entrada=1 secuencia=3 recid=124 marca=660763187
canal ORA_DISK_1: iniciando parte 1 en 22-JUL-08
...
canal ORA_DISK_1: especificando archivo(s) de datos en el juego de copias de seguridad
incluyendo el archivo de control actual en el juego de copias de seguridad
manejador de parte=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_07_23\O1_MF_NCNNF_TAG20080723T172009_48DQF74D_.BKP etiqueta=TAG20080723T172009 comentario=NONE
canal ORA_DISK_1: juego de copias de seguridad terminado, tiempo transcurrido: 00:00:06
backup terminado en 23-JUL-08
thread de archive log de entrada=1 secuencia=4 recid=125 marca=660763348
thread de archive log de entrada=1 secuencia=5 recid=126 marca=660763352
backup terminado en 23-JUL-08

En este caso estoy incluyendo el controlfile para hacer una especie de recuperación de desastre (que fue lo que hice realmente como clonado). Al mismo tiempo lancé el insert sobre la tabla prueba a través del ciclo y con la tabla en nologging.

Ya que es una recuperación de desastre, simularemos la pérdida del controlfile, y para poder recuperarlo, necesitamos el DBID de nuestra base de datos (se puede obtener conectándonos a rman o desde v$database).

Hay que tener en cuenta el nombre del archivo que guarda el controlfile para poder extraerlo, así como las secuencias de archives que se tienen registradas en el bacjup para restaurar y recuperar.

RMAN> shutdown abort;

instancia Oracle cerrada

RMAN> startup nomount;

conectado a la base de datos destino (no iniciada)
instancia Oracle iniciada

Total del Área Global del Sistema     289406976 bytes

Fixed Size                     1290184 bytes
Variable Size                226492472 bytes
Database Buffers              54525952 bytes
Redo Buffers                   7098368 bytes

RMAN> SET DBID 524232147;

ejecutando el comando: SET DBID

RMAN> RUN
2> {
3>   RESTORE CONTROLFILE FROM 'C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_07_23\O1_MF_NCNNF_TAG20080723T172009_48DQF74D_.BKP
4> }

Iniciando restore en 22-JUL-08
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=156 devtype=DISK

canal ORA_DISK_1: restaurando archivo de control
canal ORA_DISK_1: restauración terminada, tiempo transcurrido: 00:00:04
archivo de salida=C:\ORACLE\PRODUCT\ORADATA\ORCL\CONTROL01.CTL
archivo de salida=C:\ORACLE\PRODUCT\ORADATA\ORCL\CONTROL02.CTL
archivo de salida=C:\ORACLE\PRODUCT\ORADATA\ORCL\CONTROL03.CTL
restore terminado en 22-JUL-08

RMAN> alter database mount;

base de datos montada
canal liberado: ORA_DISK_1

RMAN> RESTORE DATABASE UNTIL SEQUENCE 5;

RMAN> RECOVER DATABASE UNTIL SEQUENCE 5;

RMAN> ALTER DATABASE OPEN RESETLOGS;

Y ahora podemos conectarnos a SQL*Plus

SQL> select count(1) from prueba;

  COUNT(1)
----------
   9813490

Este ejemplo es muy raro que se encuentre en un ambiente productivo, y más que poder lanzar los comandos sql de "alter database force logging", como DBAs debemos de analizar que nuestros objetos tengan activado el logging. Habrá quien diga que los índices pueden ir en nologging, pero les preguntaría... ¿Cuánto tiempo tardarían en recrear los índices que no tuvieran activado el logging?

En algún otro Post pondré los scripts que se pueden usar para extraer datos de índices correspondientes a una tabla perdida.

Character Set en SqlPlus de Windows en Español

Hasta que no empecé este blog, nunca me había preocupado por tener el characterset correcto en mi sesión de sqlplus en DOS.

SQL> commit;

Confirmaci¾n terminada.

El caracter ¾ debería en realidad ser "ó". lo primero que intenté fue cambiar el characterset a lo que generalmente pudiera usar en un sistema linux.

C:\oracle\product\10.2.0\BIN>set NLS_LANG=SPANISH_AMERICA.WE8ISO8859P1

SQL> commit;

Confirmaci¾n terminada.

C:\oracle\product\10.2.0\BIN>set NLS_LANG=SPANISH_AMERICA.UTF8

SQL> commit;

Confirmaci├│n terminada.

Como nada de esto funcionó, lo primero fue averiguar lo que realmente estaba usando como characterset:

C:\oracle\product\10.2.0\database>echo %NLS_LANG%
%NLS_LANG%

SQL> @.[%NLS_LANG%].
SP2-0310: no se ha podido abrir el archivo ".[SPANISH_SPAIN.WE8MSWIN1252]..sql"

La línea de comando en windows (DOS) no usa los códigos de página Western European, por lo cual debemos de obtener el código que se usa realmente.

Para eso windows tiene la utilería chcp

C:\oracle\product\10.2.0\BIN>chcp
Tabla de códigos activa: 850

Con esta información, podemos buscar el characterset correcto en la siguiente lista:

MS-DOS code page      Oracle Client character set (3rd part of NLS_LANG)
   437                   US8PC437 
   737                   EL8PC737  
   850                   WE8PC850 
   852                   EE8PC852  
   857                   TR8PC857 
   858                   WE8PC858
   861                   IS8PC861
   862                   IW8PC1507
   865                   N8PC865
   866                   RU8PC866 

C:\oracle\product\10.2.0\BIN>set NLS_LANG=SPANISH_AMERICA.WE8PC850

SQL> commit;

Confirmación terminada.

Group by VS. Distinct

Mucho se habla sobre la diferencia entre group by y Distinct, la verdad es que en oracle parece no tener diferencia.

Hice algunas pruebas para poder decir que son prácticamente lo mismo.

Empecé con los siguientes queries:

SQL> explain plan for 
  2  SELECT DISTINCT campo1 FROM prueba;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 643035693

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |        |     1 |     5 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PRUEBA |     1 |     5 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


SQL> explain plan for
  2  SELECT campo1 FROM prueba GROUP BY campo1;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

Plan hash value: 287650557

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |        |     1 |     5 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PRUEBA |     1 |     5 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Como se puede ver lo único que cambia es el "HASH UNIQUE" por "HASH GROUP", el resto del plan parece ser igual.

Al ser una función hash, decidí incrementar la prueba a un set de datos más grande (suficiente para que mi PGA se quedara corta y se tuviera que pasar la tabla de hash a disco). Decidí también poner un trace nivel 10104 al proceso para ver la creación de la tabla de hash.

Las sentencias SQL son las siguientes:

WITH registros AS
(SELECT /*+MATERIALIZE*/ 
     owner,
     object_type
   FROM dba_objects)
SELECT COUNT(*)
FROM
  (SELECT owner,
     object_type || rownum
     FROM
      (SELECT a.owner,
              b.object_type
       FROM registros a,
            registros b
       WHERE rownum < 1000000)
   GROUP BY owner,
     object_type || rownum);


WITH registros AS
  (SELECT /*+MATERIALIZE*/
     owner,
     object_type
   FROM dba_objects)
SELECT COUNT(*)
FROM
  (SELECT DISTINCT owner,
     object_type || rownum
   FROM
    (SELECT a.owner,
       b.object_type
     FROM registros a,
       registros b
     WHERE rownum < 1000000))
;

Las pruebas que realicé fueron las siguientes:

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10104 trace name context forever, level 12;
Sentencia procesada.
SQL> WITH registros AS
  2    (SELECT /*+MATERIALIZE*/
...
 15       WHERE rownum < 1000000));

  COUNT(*)
----------
    999999

SQL> oradebug tracefile_name
c:\oracle\product\admin\orcl\udump\orcl_ora_5272.trc

En ambas situaciones, las tablas de hash fueron exactamente las mismas...

*** RowSrcId: 6 HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of partitions: 8
Number of partitions which could fit in memory: 8
Number of partitions left in memory: 8
Total number of slots in in-memory partitions: 8
Total number of rows in in-memory partitions: 69
   (used as preliminary number of buckets in hash table)
Estimated max # of build rows that can fit in avail memory: 81720
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Requested size of hash table: 16
Actual size of hash table: 16
Number of buckets: 128
Match bit vector allocated: FALSE
kxhfResize(enter): resize to 14 slots (numAlloc=8, max=12)
kxhfResize(exit): resized to 14 slots (numAlloc=8, max=14)
  freeze work area size to: 2321K (14 slots)
### Hash table overall statistics ###
Total buckets: 128 Empty buckets: 73 Non-empty buckets: 55
Total number of rows: 69
Maximum number of rows in a bucket: 3
Average number of rows in non-empty buckets: 1.254545

En ambas ocasiones, la tabla de hash fue exactamente la misma, con el mismo número de operaciones. A nivel SQL trace, las ejecuciones también fueron similares:

Group By

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.31       0.38          0       5395        143           0
Fetch        2      2.89       4.07       2615        144          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.20       4.45       2615       5539        144           1

Distinct

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.28       0.33          0       5395        143           0
Fetch        2      2.92       4.01       2615        144          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.20       4.34       2615       5539        144           1

Intenté hacer algunas pruebas para ver si alguno era más eficiente que otros en Joins y no encontré ninguna diferencia.

Después de muchas pruebas sólo encontré una diferencia...

SQL> select sql_text,
  2    sharable_mem
  3  FROM v$sql
  4  WHERE sql_text LIKE 'SELECT%campo1%prueba%';

SQL_TEXT                                  SHARABLE_MEM
----------------------------------------- ------------
SELECT DISTINCT campo1 FROM prueba                8535
SELECT campo1 FROM prueba GROUP BY campo1         8542

La sentencia sql ocupa más caracteres en el group by, por lo mismo ocupa más memoria dentro del shared_pool.

Ya que esto es insignificante, se puede decir que el "group by" y el "disticnt" son similares.

Mejora de desempeño con Oracle Text

Existen muchos casos de sentencias SQL que lo que intentan a partir de varios campos, saber qué registros cumplen con una palabra "clave". Imaginemos lo siguiente, llega una persona a generar una factura en un grupo que tiene 1 millón de clientes, y el dato que podemos dar para buscar el cliente a nombre de quién se factura es el nombre "Hugo", pero la definición de la tabla tiene nombre, segundo_nombre, primer_apellido, segundo_apellido y razon_social.

Imaginemos también que no se tiene ningún tipo de constraint para evitar el uso indistinto de Mayúsculas/Minúsculas.

Así que aquí tenemos un ejemplo:

SQL> create table catalogo
  2  (nombre varchar2(30),
  3   segundo_nombre varchar2(30),
  4   primer_apellido varchar2(30),
  5   segundo_apellido varchar2(30),
  6   razon_social varchar2(100));

Tabla creada.
 
SQL> insert into CATALOGO
  2  with registros as (
  3     select  OWNER, OBJECT_NAME, OBJECT_TYPE
  4     from    dba_objects
  5     )
  6  select
  7     t2.owner,
  8     substr(t2.OBJECT_NAME,mod(rownum,10),5),
  9     substr(t2.OBJECT_TYPE,1,mod(rownum,10)),
 10     substr(t2.OBJECT_NAME,1,mod(rownum,10)),
 11     t2.owner||' '||t2.object_type||' '||t2.object_name
 12  from
 13     registros       t1,
 14     registros   t2
 15  where rownum < 1000000;
 
999999 filas creadas.

 

SQL> insert into catalogo values('Hugo','Enrique','Contreras','Gamiño',null);

1 fila creada.

SQL> commit;

Ahora ya tenemos 1 millón de registros, y podemos hacer uso de nuestra palabra clave "ENRIQUE" que sabemos de antemano que sólo nos regresará un registro.

SQL> set timing on
SQL> set autot traceonly stat
SQL> select * from catalogo 
  2  where upper(nombre||segundo_nombre||primer_apellido||segundo_apellido||razon_social)
  3  like '%ENRIQUE%';

Transcurrido: 00:00:14.67

EstadÝsticas
----------------------------------------------------------
        285  recursive calls
          0  db block gets
      10087  consistent gets
      10031  physical reads
        116  redo size
        712  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

>

Cómo se puede ver, hay varias situaciones aquí, la primera es que el desarrollador tuvo que concatenar todos los campos descriptivos y además se aplicó la función "UPPER". La segunda es que no importa qué índice de tipo B-TREE se cree, se terminará haciendo un Full Table Scan o Full Index Scan.

La solución qu eparece ser más óptima es con el uso de Oracle Text.

Se eligió para este ejemplo un índice de tipo "Context". Como primer paso se debe de crear un datastore de multicolumna que englobe los campos descriptivos de la tabla que queremos indexar.

SQL> begin
  2  ctx_ddl.create_preference('mi_datastore', 'multi_column_datastore');
  3  ctx_ddl.set_attribute('mi_datastore', 'columns',
  4  'nombre,segundo_nombre,primer_apellido,segundo_apellido,razon_social');
  5  end;
  6  /

Procedimiento PL/SQL terminado correctamente.

Una vez creado el datastore, se puede crear el índice.

SQL> create index cat_texto_idx on catalogo(nombre)
  2  indextype is ctxsys.context
  3  parameters('datastore mi_datastore');
  
Indice creado.

El índice es creado sobre la columns "nombre" y cada registro es tomado encuenta como un documento, por lo cual, oracle text nos permite buscar por el campo nombre y hacer referencia a el datastore múltiple.

SQL> select * from catalogo
  2  where contains(nombre,'enrique',1)>0;

Transcurrido: 00:00:00.02

EstadÝsticas
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

En este ejemplo observamos que se usa la función "Contains" de oracle text para buscar sobre un campo indexado (que en nuestro ejemplo es nombre). Se puede observar también que para este ejemplo, es indistinto el uso de mayúsculas o minúsculas. y que en lugar de leer más de 10,000 bloques de datos, sólo se leen 21 bloques. en lugar de tardar más de 10 segundos, sólo se tardó el query 0.020 segundos.

Hay algo que se debe de tomar en cuenta, un índice de contexto no es transaccional por default, es decir, a medida que los datos se van modificando (Cualquier DML) el índice queda fuera de sincronía.

SQL> insert into catalogo values('Diego','Armando','Maradona',null,null);

1 fila creada.

SQL> commit;

Confirmaci¾n terminada.

SQL> select * from catalogo
  2  where contains(nombre,'maradona',1)>0;
  
ninguna fila seleccionada.

Para mantener la sincronía, existe el siguiente comando

SQL> begin
  2  CTX_DDL.SYNC_INDEX('CAT_TEXTO_IDX','50K');
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL> select * from catalogo
  2  where contains(nombre,'maradona',1)>0;
  
NOMBRE     SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Diego      Armando    Maradona

Ya que esto puede ser inaceptable para muchos modelos de sistemas, oracle tiene una propiedad (que no es default) para poder sincronizar los índices.

SQL> drop index cat_texto_idx;

Indice borrado.

SQL> create index cat_texto_idx on catalogo(nombre)
  2     indextype is ctxsys.context
  3     parameters('datastore mi_datastore sync (on commit)');

Indice creado.

SQL> insert into catalogo values('Eric','Daniel','Cantona',null,null);

1 fila creada.

SQL> select * from catalogo where contains(nombre,'cantona',1)>0;

ninguna fila seleccionada

SQL> commit;

Confirmaci¾n terminada.

SQL> select * from catalogo where contains(nombre,'cantona',1)>0;

NOMBRE     SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Eric       Daniel     Cantona

El índice quedará sincronizado cada que nosotros hagamos commit;

Todo esto es sólo un ejemplo práctico que me ayudó a resolver un problema de desempeño en un sistema, sin embargo es muy limitado en cuanto al uso de Oracle text, por eso recomiendo leer el Manual de referencia de oracle text.

Recuperar un datafile borrado accidentalmente sin respaldo

Recientemente me encontré con un problema en producción donde una base de datos misteriosamente había perdido un datafile recientemente agregado. Pareciera como si le hubieran dado un "rm /data/file.dbf" lo cual parece ser el problema, ya que la referencia al datafile quería ser eliminada. Vale la pena decir que en Locally Managed Tablespaces sólo se pueden eliminar datafiles que están online, por esta razón se debía de recuperar el datafile perdido para poder eliminarlo.

La base de datos en cuestión estaba en estado de abierta, pero el tablespace al que pertenecía este datafile se quedó offline y no se podía poner online.

Hay muchas formas de sobrepasar este problema, si el tablespace hubiera sido de índices, se podrían haber recreado todos los índices en un nuevo tablespace y simplemente haber realizado un drop del tablespace.

En este caso, lo que se realizo fue recrear el datafile nuevamente y recupear para poner online el tablespace y poder hacer el drop del datafile.

Voy a ejemplificar el problema en windows (que por la naturaleza del sistema operativo, no se puede borrar accidentalmente el datafile).

SQL> create tablespace prueba 
  2>   datafile 'c:\prueba01.dbf' size 10m,  
  3>   'c:\prueba02.dbf' size 10m;

Tablespace creado.

SQL> shutdown ;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.

SQL> $ del c:\prueba02.dbf

SQL> startup
Instancia ORACLE iniciada.

Total System Global Area  289406976 bytes
Fixed Size                  1290184 bytes
Variable Size             264241208 bytes
Database Buffers           16777216 bytes
Redo Buffers                7098368 bytes
Base de datos montada.
ORA-01157: no se puede identificar/bloquear el archivo de datos 8 - consulte el archivo de rastreo del DBWR
ORA-01110: archivo de datos 8: 'C:\PRUEBA02.DBF'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR
---------- ------- ------- --------------
         8 ONLINE  ONLINE  FILE NOT FOUND

Como se puede ver, la base de datos no encuentra el datafile que hemos borrado. No se tiene un respaldo del mismo, pero hay una forma de recuperarlo.

SQL> alter database create datafile 8 as 'C:\PRUEBA02.DBF' size 10m;

Base de datos modificada.

SQL> recover datafile 8;
Recuperaci¾n del medio fÝsico terminada.
SQL> alter database open;

Base de datos modificada.

Se puede recrear usando "alter database create datafile 8 as..." o bien "alter database create datafile 'c:\PRUEBA02.DBF' as...".

De esta forma recreamos el datafile al origen del mismo, y al recuperar el datafile aplicamos los cambios que la base tiene registrados para el mismo.

La recuperación del datafile se puede realizar con rman:

C:\Documents and Settings>rman target /

Recovery Manager : Release 10.2.0.3.0 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

conectado a la base de datos destino: ORCL (DBID=524232147, no abierto)

RMAN> restore datafile 8;

Iniciando restore en 11/07/08
se utiliza el archivo de control de la base de datos destino en lugar del catßlogo de recuperaci¾n
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=155 devtype=DISK

creando archivo de datos fno=8 nombre=C:\PRUEBA02.DBF
no se ha realizado la restauraci¾n; todos los archivos son de s¾lo lectura, offline o ya se han restaur
restore terminado en 11/07/08

RMAN> recover datafile 8;

Iniciando recover en 11/07/08
se utiliza el archivo de control de la base de datos destino en lugar del catßlogo de recuperaci¾n
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=159 devtype=DISK

iniciando la recuperaci¾n del medio fÝsico
recuperaci¾n del medio fÝsico terminada, tiempo transcurrido: 00:00:02

recover terminado en 11/07/08

RMAN> exit

C:\Documents and Settings>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database open;   

Sistema modificado.

Una vez recuperado el datafile, se puede eliminar de la siguiente forma:

SQL> alter tablespace prueba drop datafile 8;

Tablespace modificado.