Oracle, Heterogeneous Services, Teradata y el ‘EXPLAIN’ mentiroso.

Actualmente estoy buscando soluciones para poder comparar datos de dos entornos Teradata (desarrollo y producción). Se trata de intentar ‘JOINs’ entre tablas procedentes de los dos sistemas para diferentes comprobaciones y verificaciones.

El asunto parece fácil, pero no lo es tanto: se necesita ‘ver’ tablas de los dos sistemas como si perteneciesen al mismo servidor y poder ejecutar así las ‘JOINs’ necesarias. Y lamentablemente, Teradata no tiene nada parecido a los ‘dblinks‘ de Oracle.

Dada mi poca afición a las herramientas ‘GUI’ y ‘automágicas’, y aprovechando lo visto aquí, me decidí a intentar la prueba con un Oracle XE y dos ‘dblinks‘ a dichos sistemas mediante ‘Heterogeneous Services‘.

El asunto iba OK con tablas pequeñas (unas pocas filas), pero había que probar si la cosa iba tan bien con tablas más grandes, así que comencé con las pruebas:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1)
  FROM DW_WORK.TABLA_GRANDE_DE_PRUEBA;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 2 seconds.

   Count(1)
-----------
    4297128

Así pues me dispuse a probar una ‘query‘ desde :

SQL*Plus: Release 10.2.0.1.0 - Production on Mar Mar 16 17:35:33 2010

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

Introduzca el nombre de usuario: carlos@XE.localhost
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

CARLOS@XE.localhost> set timing on;
CARLOS@XE.localhost> SELECT COUNT(1)
  2    FROM DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.prod a,
  3         DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.desa b
  4   WHERE a.NUM_SERIE  = b.NUM_SERIE
  5     AND a.FEC_INICIO = b.FEC_INICIO
  6     AND a.FEC_FIN    = b.FEC_FIN
  7  ;

  COUNT(1)
----------
 107695093

Transcurrido: 00:03:24.41
CARLOS@XE.localhost>

Bueno, 3:24 no está demasiado mal para una query remota de tablas de más de 4 millones de filas, pero ¿qué pasará si hago la misma ‘JOIN‘ entre tablas de uno sólo de los entornos?

CARLOS@XE.localhost> SELECT COUNT(1)
  2    FROM DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.desa a,
  3         DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.desa b
  4   WHERE a.NUM_SERIE  = b.NUM_SERIE
  5     AND a.FEC_INICIO = b.FEC_INICIO
  6     AND a.FEC_FIN    = b.FEC_FIN
  7   ;

  COUNT(1)
----------
 107695093

Transcurrido: 00:00:34.60
CARLOS@XE.localhost>

¡34 segundos contra 3 minutos y 24 segundos! ¿Cómo es posible?

Voy a ver qué pasa si hago la ‘query‘ en Teradata:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1)
  FROM DW_WORK.TABLA_GRANDE_DE_PRUEBA a,
       DW_WORK.TABLA_GRANDE_DE_PRUEBA b
 WHERE a.NUM_SERIE  = b.NUM_SERIE
   AND a.FEC_INICIO = b.FEC_INICIO
   AND a.FEC_FIN    = b.FEC_FIN
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 35 seconds.

   Count(1)
-----------
  107695093

¡Y tarda lo mismo (35 segundos)! Vamos a ver los ‘query plans‘ de Oracle:

CARLOS@XE.localhost> set autotrace on;
CARLOS@XE.localhost> SELECT COUNT(1)
  2    FROM DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.desa a,
  3         DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.desa b
  4   WHERE a.NUM_SERIE  = b.NUM_SERIE
  5     AND a.FEC_INICIO = b.FEC_INICIO
  6     AND a.FEC_FIN    = b.FEC_FIN
  7   ;

  COUNT(1)
----------
 107695093

Transcurrido: 00:03:24.00

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1536167140

-----------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                        |     1 |    62 |   105   (1)| 00:00:02 |        |   |
|   1 |  SORT AGGREGATE  |                        |     1 |    62 |            |          |        |   |
|*  2 |   HASH JOIN      |                        |  2000 |   121K|   105   (1)| 00:00:02 |        |   |
|   3 |    REMOTE        | TABLA_GRANDE_DE_PRUEBA |  2000 | 62000 |    52   (0)| 00:00:01 | TERAD~ | R->S |
|   4 |    REMOTE        | TABLA_GRANDE_DE_PRUEBA |  2000 | 62000 |    52   (0)| 00:00:01 | TERAD~ | R->S |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("A"."NUM_SERIE"="B"."NUM_SERIE" AND "A"."FEC_INICIO"="B"."FEC_INICIO" AND
              "A"."FEC_FIN"="B"."FEC_FIN")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "NUM_SERIE","FEC_INICIO","FEC_FIN" FROM "DW_WORK"."TABLA_GRANDE_DE_PRUEBA" (accessing
       'TERADATA.PROD' )

   4 - SELECT "NUM_SERIE","FEC_INICIO","FEC_FIN" FROM "DW_WORK"."TABLA_GRANDE_DE_PRUEBA" (accessing
       'TERADATA.DESA' )

Estadísticas
----------------------------------------------------------
        275  recursive calls
          1  db block gets
          0  consistent gets
      50295  physical reads
        304  redo size
        433  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Parece todo normal: dos ‘queries’ remotas a los Teradata y el ‘MERGE‘ y el ‘SORT AGGREGATE‘ local en Oracle.

Pero, entonces ¿qué hace cuando las dos tablas se consultan de un mismo sitio (prod)?

CARLOS@XE.localhost>  select count(1)
  2   FROM DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.prod a,
  3        DW_WORK.TABLA_GRANDE_DE_PRUEBA@teradata.prod b
  4  WHERE a.NUM_SERIE  = b.NUM_SERIE
  5    AND a.FEC_INICIO = b.FEC_INICIO
  6    AND a.FEC_FIN    = b.FEC_FIN ;

  COUNT(1)
----------
 107695093

Transcurrido: 00:00:34.70

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1536167140

-----------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                        |     1 |    62 |   105   (1)| 00:00:02 |        |   |
|   1 |  SORT AGGREGATE  |                        |     1 |    62 |            |          |        |   |
|*  2 |   HASH JOIN      |                        |  2000 |   121K|   105   (1)| 00:00:02 |        |   |
|   3 |    REMOTE        | TABLA_GRANDE_DE_PRUEBA |  2000 | 62000 |    52   (0)| 00:00:01 | TERAD~ | R->S |
|   4 |    REMOTE        | TABLA_GRANDE_DE_PRUEBA |  2000 | 62000 |    52   (0)| 00:00:01 | TERAD~ | R->S |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("A"."NUM_SERIE"="B"."NUM_SERIE" AND "A"."FEC_INICIO"="B"."FEC_INICIO" AND
              "A"."FEC_FIN"="B"."FEC_FIN")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "NUM_SERIE","FEC_INICIO","FEC_FIN" FROM "DW_WORK"."TABLA_GRANDE_DE_PRUEBA" (accessing
       'TERADATA.PROD' )

   4 - SELECT "NUM_SERIE","FEC_INICIO","FEC_FIN" FROM "DW_WORK"."TABLA_GRANDE_DE_PRUEBA" (accessing
       'TERADATA.PROD' )

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

Lo mismo: dos ‘queries‘ remotas a los Teradata y el ‘MERGE’ y el ‘SORT AGGREGATE’ local en Oracle.
¡Eh! pero ahí pasa algo: 0 ‘consistent gets‘ y 0 ‘physical reads‘. ¿Qué esta pasando?

Vamos a ver qué dice Teradata de esto:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT TOP 3 StatementText
  FROM DBC.AccessLog
 WHERE UserName = 'Carlos'
 ORDER BY LogDate DESC, LogTime DESC;
;

 *** Query completed. Three rows found. One column returned.
 *** Total elapsed time was 2 seconds.

StatementText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT TOP 3 StatementText FROM DBC.AccessLog WHERE UserName = 'Carlos'
ORDER BY LogDate DESC, LogTime DESC;

SELECT COUNT ( * ) AS c000 FROM "DW_WORK"."TABLA_GRANDE_DE_PRUEBA"
"A2" , "DW_WORK"."TABLA_GRANDE_DE_PRUEBA" "A1"
WHERE ( "A2"."NUM_SERIE" = "A1"."NUM_SERIE" AND "A2"."FEC_INICIO" =
"A1"."FEC_INICIO" AND "A2"."FEC_FIN" = "A1"."FEC_FIN")

SELECT "T0000"."NUM_SERIE" AS c000, "T0000"."FEC_INICIO" AS c001,
"T0000"."FEC_FIN" AS c002 FROM "DW_WORK"."TABLA_GRANDE_DE_PRUEBA"
"T0000"

Lo que está ocurriendo es que aunque el ‘query plan‘ de Oracle dice que se trae los dos ‘result sets‘ y luego hace en local el ‘MERGE‘ y el ‘SORT AGGREGATE‘ está mintiendo: Si las dos tablas pertenecen al mismo sistema (prod), Oracle pasa la ‘query‘ completa a Teradata (como un ‘pass thru’) y luego recibe los resultados. ¡Por eso no hay ‘consistent gets‘ ni ‘physical reads‘: todo lo hace Teradata!

En la query inmediatamente anterior generada por el ‘JOIN‘ de tablas de los diferentes sistemas -desarrollo y producción- se aprecia que ahí SÍ está devolviendo el ‘resultset’ completo a Oracle para que éste haga el ‘JOIN.

¡El ‘EXPLAIN‘ de Oracle es un mentiroso! 😉

Saludos.

Carlos.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: