Oracle SQL Developer 4.0 en OpenSuse y usando PostgreSQL!

Ayer estuve probando la nueva versión del utilitario Oracle SQL Developer que se liberó en diciembre (4.0.0.13), y entre muchas cosas nuevas (ver las 10 razones para usarlo, en inglés), encontré que se puede configurar una conexión a una base PostgreSQL, algo que me interesaba desde hace tiempo.

Si bien PostgreSQL no está dentro de las bases de terceros soportadas, ahora funciona, aunque algunas operaciones dan error (como por ejemplo ver los índices de una tabla), y no tiene las mismas funcionalidades que el utilitario nativo de PostgreSQL psql.

Pero es una buena señal, y con el tiempo se puede convertir en la herramienta necesaria para cualquier DBA que administra varios motores.  En mi caso uso habitualmente Oracle, MySQL y PostgreSQL, y ocasionalmente SQL Server, todas administrables desde SQL Developer. Esperemos que en breve se tenga la funcionalidad completa sobre bases PostgreSQL.

Para quienes les interese probarlo, les dejo el detalle de como instalarlo y hacerlo funcionar en OpenSuse 12.3 x64, conectando a una base PostgreSQL 9.2.4 que corre local (instalado del repositorio "openSUSE BuildService - Database").


1) Descargar software

• SQL Developer RPM para linux: sqldeveloper-4.0.0.13.80-1.noarch.rpm
• Java JDK 7: jdk-7u51-linux-x64.rpm
• driver JDBC 4.1 para Postgres 9.3: postgresql-9.3-1100.jdbc41.jar

2) Instalar

Dejé los archivos anteriores en el directorio /local/soft/oracle, y desde ahí lo instalé con root:

oraculo:/local/soft/oracle # rpm -Uvh sqldeveloper-4.0.0.13.80-1.noarch.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:sqldeveloper-4.0.0.13.80-1       ################################# [ 50%]
Cleaning up / removing...
   2:sqldeveloper-3.2.20.09.87-1      ################################# [100%]


Si se trata de usar SQL Developer sin instalar la JDK 1.7, da este error:

ncalero@oraculo:> sqldeveloper 

 Oracle SQL Developer
 Copyright (c) 1997, 2013, Oracle and/or its affiliates. All rights reserved.


Esto es porque OpenSuse incluye OpenJDK 1.7, que trae solo JRE (openjdk-1.7.0.6-8.28.3.x86_64):

oraculo:~ # rpm -qa | grep -i jdk
java-1_7_0-openjdk-1.7.0.6-8.28.3.x86_64

oraculo:~ # rpm -ql java-1_7_0-openjdk-1.7.0.6-8.28.3.x86_64
/usr/lib64/jvm-exports/java-1.7.0-openjdk
/usr/lib64/jvm-exports/java-1.7.0-openjdk-1.7.0
...
/usr/lib64/jvm/java-1.7.0-openjdk-1.7.0/jre
...

oraculo:~ # /usr/lib64/jvm/java-1.7.0-openjdk-1.7.0/jre/bin/java -version
java version "1.7.0_45"
OpenJDK Runtime Environment (IcedTea 2.4.3) (suse-8.28.3-x86_64)
OpenJDK 64-Bit Server VM (build 24.45-b08, mixed mode)


Así que se debe instalar la JDK 1.7:

oraculo:/local/soft/oracle # rpm -Uvh jdk-7u51-linux-x64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:jdk-2000:1.7.0_51-fcs            ################################# [100%]
Unpacking JAR files...
        rt.jar...
        jsse.jar...
        charsets.jar...
        tools.jar...
        localedata.jar...
        jfxrt.jar...


Podemos validar donde quedó instalado:

oraculo:/local/soft/oracle # rpm -ql jdk-2000:1.7.0_51-fcs
/etc
/etc/.java
/etc/.java/.systemPrefs
...
/usr/java/jdk1.7.0_51/bin
...

oraculo:/local/soft/oracle # /usr/java/jdk1.7.0_51/bin/java -version
java version "1.7.0_51"
Java(TM) SE Runtime Environment (build 1.7.0_51-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.51-b03, mixed mode)


3) Configurar JDK en SQL Developer

Antes de ejecutar SQL Developer se debe modificar su configuración para que use el JDK recién instalado, agregando el path correcto en el archivo de configuración sqldeveloper.conf en la variable SetJavaHome. Lo modifiqué con un editor de texto (vi), dejando comentada la configuración original (../../jdk):

oraculo: # grep SetJavaHome /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf

#SetJavaHome ../../jdk
SetJavaHome /usr/java/jdk1.7.0_51


4) Agregar conexión a PostgreSQL

Ahora que está resuelta la instalación, abrimos SQL Developer:

ncalero@oraculo:~> sqldeveloper 

 Oracle SQL Developer
 Copyright (c) 1997, 2013, Oracle and/or its affiliates. All rights reserved.


Nos muestra la pantalla de inicio. En mi caso tenía una versión anterior (3.2) por lo que me migró algunas conexiones:

Ahora vamos a la configuración para agregar el driver de PostgreSQL. Esto se hace presionando Tools en el menú superior, y ahí eligiendo Preferences:

Ahora hay que elegir Database, y allí Third Party JDBC Drivers, donde se muestran los drivers ya configurados (en mi caso MySQL y SQL Server):

Con el botón "Add Entry ..." se debe elegir el archivo que bajamos antes, ubicado en /local/soft/oracle/postgresql-9.3-1100.jdbc41.jar.

Una vez elegido, presionamos el botón Select en este diálogo, y OK en el anterior para confirmar el cambio.

Para probar su funcionamiento creamos una nueva conexión. En este ejemplo usando el botón con el símbolo de más en el menú "Connections" de la izquierda:

Se abre el diáologo para ingresar los datos de la conexión, donde aparece una nueva pestaña "PostgreSQL":

En este ejemplo me voy a conectar a una base en el mismo equipo donde corre SQL Developer.
Si la base estuviera en otro servidor hay que revisar que se tenga permiso de acceso, tanto a nivel de IP (archivo pg_hba.conf) como en el usuario que se conecta a la base de datos.

Una vez que se ingresan los datos (hostname, port, username, password), se puede validar que la conexión funciona presionando el botón "Choose Database" para que nos muestre las bases a las que podemos conectarnos, y debemos elegir una de ellas para esta conexión:

Luego de elegida una base, podemos probar que la conexión funciona con el botón "Test" (aunque al funcionar la carga del combo de bases anteriores ya tenemos la pista de que va a funcionar). El resultado de este test se muestra en la parte inferior izquierda de esta ventana, donde dice Status:, como se ve en la siguiente captura marcado en rojo.

Al presionar Connect abrimos la conexión y podemos navegar en los objetos de la base tal como lo hacemos en Oracle, y ejecutar sentencias SQL.

Espero les sea útil.

Particiones, datos y optimización

Tenemos una tabla particionada y una consulta que anda lento porque la condición de where no incluye la columna de partición y usa columnas que no están indexadas.

¿Podemos hacer algo más que dar la recomendación obvia de crear un nuevo índice global?

Seguro!, por lo menos hacer estas dos revisiones rápidas.

Lo primero es ver si se usa la columna de particionamento en la condición del where.
Si se usa, la consulta solo leerá datos de esa partición (el optimizador aplica partition pruning), por lo tanto un nuevo índice podría ser útil siempre que las particiones tengan un volumen de datos que lo justifique (muchas particiones con pocos registros no se van a beneficiar de un nuevo índice).

Si no se usa la columna de particionamento en el where, todavía queda algo más a analizar: ¿el criterio de búsqueda tiene alguna correlación con el criterio de particionamiento? Esto es una relación funcional sobre los datos y que no podemos inferir a partir del texto de la consulta.

Podemos analizar los datos buscando este vínculo, y si se llega a confirmar podríamos hacer que la consulta sea más eficiente (recorra menos registros) modificandola, agregando una simple condición columna_partición=valor.

¿Cómo podemos hacer este análisis? Con la ayuda de la función DBMS_MVIEW.PMARKER, que dado un registro (rowid) retorna el nombre de la partición donde está almacenado.

Todo esto se puede ver mejor con un ejemplo. Usé Oracle 11.2.0.2 Enterprise Edition


Tenemos la tabla bigtbl_part particionada por rangos de la columna tipo, y un índice global:

12:40:43 PRU@ent11g> create table bigtbl_part (id, estado, tipo, clase, dato)
partition by range(tipo) (
     partition p0 values less than (1),
     partition p1 values less than (2),
     partition p2 values less than (3),
     partition p3 values less than (4),
     partition p4 values less than (5)
) as
select  rownum               id,
        floor(dbms_random.value(1,20)) estado,
        mod(rownum,5)        tipo,
        mod(rownum,10)       clase,
        'relleno'            dato
from  all_objects, all_objects

Table created.

Elapsed: 00:00:53.14

12:41:37 PRU@ent11g> create index idx1 on bigtbl_part(id) local;

Index created.

Elapsed: 00:00:15.90

12:42:20 PRU@ent11g> execute dbms_stats.gather_table_stats(user,'bigtbl_part')

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.99

Vemos cómo quedaron generados los datos. Nos vamos a enfocar en las clases y tipos:

12:42:40 PRU@ent11g>select count(*), clase from bigtbl_part group by clase order by clase;

  COUNT(*)      CLASE
---------- ----------
    100000          0
    100000          1
    100000          2
    100000          3
    100000          4
    100000          5
    100000          6
    100000          7
    100000          8
    100000          9

10 rows selected.

Los valores distintos de CLASE por cada partición (columna TIPO):

12:46:29 PRU@ent11g> select count(distinct clase), tipo
from bigtbl_part
group by tipo
order by tipo;

COUNT(DISTINCTCLASE)       TIPO
-------------------- ----------
                   2          0
                   2          1
                   2          2
                   2          3
                   2          4

Y cómo quedaron distribuidos los datos en cada partición:

12:47:40 PRU@ent11g> col partition_name for a10
12:47:41 PRU@ent11g> select partition_name, num_rows, high_value
from user_TAB_PARTITIONS
where table_name='BIGTBL_PART';

PARTITION_   NUM_ROWS HIGH_VALUE
---------- ---------- --------------------------------------------------------------------------------
P0             200000 1
P1             200000 2
P2             200000 3
P3             200000 4
P4             200000 5

Esta es la consulta que nos interesa mejorar:

  SELECT count(*)
  FROM bigtbl_part
  WHERE clase = :clase and estado = :estado;

Para fijar ideas usamos una combinación de valores cualquiera: buscamos registros de clase 3 y estado 15:

12:49:52 PRU@ent11g> var clase number;
12:49:52 PRU@ent11g> var estado number;

12:49:52 PRU@ent11g> exec :clase := 3;

PL/SQL procedure successfully completed.

12:49:52 PRU@ent11g> exec :estado := 15;

PL/SQL procedure successfully completed.

12:49:53 PRU@ent11g> set autotrace on explain

SELECT count(*)
FROM bigtbl_part
WHERE clase = :clase and estado = :estado;

  COUNT(*)
----------
      5265

Execution Plan
----------------------------------------------------------
Plan hash value: 1165574620

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     6 |  1047   (2)| 00:00:13 |       |       |
|   1 |  SORT AGGREGATE      |             |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|             |  5263 | 31578 |  1047   (2)| 00:00:13 |     1 |     5 |
|*  3 |    TABLE ACCESS FULL | BIGTBL_PART |  5263 | 31578 |  1047   (2)| 00:00:13 |     1 |     5 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ESTADO"=TO_NUMBER(:ESTADO) AND "CLASE"=TO_NUMBER(:CLASE))

Vemos que para resolverla el optimizador tuvo que recorrer todas las particiones de la tabla, y cada una sin usar índice.

Podemos tener facilmente una idea de la cantidad máxima de registros que se necesitan analizar para responder esta consulta (peor caso):

12:53:04 PRU@ent11g>
select count(*), min(count(*)), max(count(*))
from BIGTBL_part
group by clase, estado;

  COUNT(*) MIN(COUNT(*)) MAX(COUNT(*))
---------- ------------- -------------
       190          5071          5500

Pero si queremos además saber en qué particiones están estos datos, no alcanza con ver la cantidad de registros que tiene cada partición (columna num_rows de DBA_TAB_PARTITIONS), ya que no estamos buscando dentro de una sola partición.
Tenemos que agregar a la consulta original el uso de la función dbms_mview.pmarker, y así ver si hay afinidad de los datos buscados (de la condición de agrupación) con las particiones.

Esta es una primera versión simple de la consulta, donde podemos ver los distintos valores de CLASES almacenados en cada partición:

12:54:12 PRU@ent11g>
select count(*)                      registros
      ,dbms_mview.pmarker(rowid)     data_object_id
      ,count(distinct clase)         clase_x_tipo
FROM bigtbl_part
group by dbms_mview.pmarker(rowid);

 REGISTROS DATA_OBJECT_ID CLASE_X_TIPO
---------- -------------- ------------
    200000          75537            2
    200000          75539            2
    200000          75540            2
    200000          75538            2
    200000          75536            2

Elapsed: 00:00:05.93
 

Agregando la condición original, podemos ver en qué particiones están los datos obtenidos:

12:56:51 PRU@ent11g>
select count(*)                      registros
      ,dbms_mview.pmarker(rowid)     data_object_id
FROM bigtbl_part
WHERE clase = :clase and estado = :estado
12:56:51   5  group by dbms_mview.pmarker(rowid);

 REGISTROS DATA_OBJECT_ID
---------- --------------
      5265          75539

Los 5265 registros que retorna la consulta se obtienen de la partición 75539.
Podemos ver el nombre de la partición consultando USER_OBJECTS:

12:58:17 PRU@ent11g>
select subobject_name
from user_objects
WHERE data_object_id=75539;

SUBOBJECT_NAME
------------------------------------------------------------------------------------------
P3

En resumen, aunque los datos se obtienen todos de la misma partición, la consulta recorre todas las particiones porque no conoce la relación entre los datos (clase y tipo).

En este ejemplo los datos se generaron de esta forma para ilustrar el caso donde se puede mejorar la consulta con la ayuda de los programadores de la aplicación, quienes pueden agregar una condición más al where sobre la columna TIPO con el valor correspondiente (ya que la relación entre los valores de estas columnas pueden ser conocidos), y así mejorar la peformance de la misma.
La consulta modificada quedaría así:

12:59:28 PRU@ent11g>
SELECT count(*)
FROM bigtbl_part
WHERE clase = :clase and estado = :estado and tipo = :tipo;

Plan hash value: 4104149755

-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |     1 |     9 |   211   (2)| 00:00:03 |       |       |
|   1 |  SORT AGGREGATE         |             |     1 |     9 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|             |  1053 |  9477 |   211   (2)| 00:00:03 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | BIGTBL_PART |  1053 |  9477 |   211   (2)| 00:00:03 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Si bien se mantiene el acceso sin índice, ahora es sobre la partición y no sobre todas las particiones.
Esto todavía puede mejorarse incluyendo un índice local por clase y estado si el volumen de las particiones lo amerita.
Si no se hubiera podido modificar la consulta, la recomendación clásica habría sido un índice global por clase y estado.

Otro tema relacionado a mejorar la performance de consultas sobre tablas particionadas, es la utilidad de tener índices que incluyan la columna de partición (conocidos como prefixed local index). Una buena discusión sobre el tema se puede encontrar en este thread de los foros de OTN

Espero les sea útil.

Un saludo y feliz comienzo de año!

Alta disponibilidad en bases de datos

Sobre este tema se habla mucho, hay mucha bibliografía, y se dedica mucho esfuerzo ($$) para lograrlo.

Cada instalación tiene sus particularidades y cada bases de datos ofrece distintas funcionalidades, por lo que implementar y automatizar la respuesta y recuperación ante fallas es una tarea no tan trivial.

Para esta automatización Oracle provee Clusterware, que nació como parte de la solución de cluster para base de datos Oracle RAC. Soporta además configurar cualquier recurso gestionable con scripts, lo que por ejemplo permite configurar una base MySQL como un recurso de Oracle Clusterware.
En la versión 11.2 (año 2009) se ofreció como producto independiente con la licencia de Oracle Enterprise Linux, y desde este año con la versión 12.1 se ofrece de forma gratuita, con soporte oficial teniendo cualquier otro producto de Oracle.

Este mismo concepto está desde hace mucho tiempo en la comunidad open source, siendo la solución más antigua y popular Heartbeat. A evolucionado desde su origen en 1999, y a partir del 2003 se generó el proyecto Pacemaker que tomó la implementación de una solución completa de gestión de recursos en cluster, además de monitoreo. Pacemaker es muy popular por estar disponible en muchas plataformas, además de ser gratuito y robusto.

Yo lo vengo usando desde el 2007 con Postgres y MySQL, y sigue siendo una buena alternativa para instalaciones que no tienen disponible licencias de Oracle. El pasado 15 y 16 de Octubre se realizó en Buenos Aires la conferencia "MySQL / NoSQL & Cloud", y fui aceptado para  presentar sobre cómo implementar alta disponibilidad en bases de datos con Pacemaker. Está dirigida a gente que no lo ha usado, y cuento todos los detalles con ejemplos usando máquinas virtuales. Dejo acá la presentación y los scripts usados.