jueves, junio 20, 2013

Manejo de subconsultas en la cláusula SELECT. Parte II

(Continúa de Parte I)

Este post podría llamarse "La paradoja del increíble coste menguante" como si de un relato de G. K. Chesterton se tratara.

Si alguien pensó por la lectura de la parte I de este post que las subconsultas en la cláusula SELECT mejoraban el rendimiento, pues permitían reproducir consultas en estrella sin necesidad de tener un modelo en estrella, ni dimensiones ni jerarquías, está al borde de cometer un grave error.

El optimizador ignora los costes de combinación de las subconsultas en la cláusula SELECT, contando únicamente con el coste de acceso a los objetos de esa subconsulta. Esto sucede incluso en versión Oracle11gR2.

Como ejemplo sirva la siguiente consulta formulada sobre VUELOS (57.711 filas), RESERVAS (171.113 filas) y CLIENTES (9999 filas).

Consulta de reservas, con datos de vuelos y clientes expresado con dos joins

select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
    from vuelos, reservas, clientes
    where vuelos.id_vuelo=reservas.vue_id_vuelo
      and reservas.cli_nif=clientes.nif;


Consulta de reservas, con datos de vuelos y clientes expresado con una join y una subconsulta en la cláusula SELECT


select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos
       where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
    clientes.apellidos
    from reservas, clientes
    where reservas.cli_nif=clientes.nif;


Consulta de reservas, con datos de vuelos y clientes expresado con dos subconsultas en la cláusula SELECT


select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos   
       where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
     (select clientes.apellidos    from clientes 
       where reservas.cli_nif=clientes.nif) cliente
    from reservas;


Los correspondientes planes de ejecución parecen evidenciar lo mencionado anteriormente: el optimizador de costes no es capaz de evaluar el impacto de la combinación de elementos de la consulta principal con los de las subconsultas en la cláusula SELECT. Por este motivo, los costes de los planes de ejecución cada vez son inferiores.


Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con dos joins con el plan de ejecución asociado y la traza de AUTOTRACE


SQL> select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
  2      from vuelos, reservas, clientes
  3      where vuelos.id_vuelo=reservas.vue_id_vuelo
  4        and reservas.cli_nif=clientes.nif;

171113 filas seleccionadas.

Transcurrido: 00:00:01.54

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

----------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |   171K|    13M|       |   904   (2)| 00:00:11 |
|*  1 |  HASH JOIN          |          |   171K|    13M|       |   904   (2)| 00:00:11 |
|   2 |   TABLE ACCESS FULL | CLIENTES |  9999 |   361K|       |    27   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |          |   171K|  7686K|  1528K|   875   (2)| 00:00:11 |
|   4 |    TABLE ACCESS FULL| VUELOS   | 57711 |   845K|       |   137   (1)| 00:00:02 |
|   5 |    TABLE ACCESS FULL| RESERVAS |   171K|  5180K|       |   311   (2)| 00:00:04 |
----------------------------------------------------------------------------------------

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

   1 - access("RESERVAS"."CLI_NIF"="CLIENTES"."NIF")
   3 - access("VUELOS"."ID_VUELO"="RESERVAS"."VUE_ID_VUELO")


Estadísticas
----------------------------------------------------------
         15  recursive calls
          0  db block gets
      13013  consistent gets
         96  physical reads
          0  redo size
    7835592  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed


A grandes rasgos, el resumen de la ejecución puede ser una lectura de 13.013 bloques en memoria, un tiempo de ejecución de 1 minuto y 54 segundos y un coste de 904.


Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con una join y una subconsulta en la cláusula SELECT con el plan de ejecución asociado y la traza de AUTOTRACE


SQL> select reservas.id_reserva, reservas.importe,
  2     (select vuelos.detalles from vuelos
  3         where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
  4      clientes.apellidos
  5      from reservas, clientes
  6      where reservas.cli_nif=clientes.nif;

171113 filas seleccionadas.

Transcurrido: 00:00:02.40

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

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   171K|    11M|   340   (2)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VUELOS   |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | VUE_PK   |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN                  |          |   171K|    11M|   340   (2)| 00:00:05 |
|   4 |   TABLE ACCESS FULL         | CLIENTES |  9999 |   361K|    27   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL         | RESERVAS |   171K|  5180K|   311   (2)| 00:00:04 |
----------------------------------------------------------------------------------------

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

   2 - access("VUELOS"."ID_VUELO"=:B1)
   3 - access("RESERVAS"."CLI_NIF"="CLIENTES"."NIF")


Estadísticas
----------------------------------------------------------
         15  recursive calls
          0  db block gets
     374003  consistent gets
          0  physical reads
          0  redo size
    7835589  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed


En esta ejecución, el número de bloques leídos en memoria ha aumentado a 374.003 y el tiempo de ejecución ha aumentado a 2 minutos 40 segundos. Sin embargo el coste de la ejecución se ha reducido a 340 (menos de la mitad). El número de bytes estimado como total de la ejecución también se estima mejorado: de 13 millones a 11 millones.


Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con dos subconsultas en la cláusula SELECT con el plan de ejecución asociado y la traza de AUTOTRACE


SQL> select reservas.id_reserva, reservas.importe,
  2       (select vuelos.detalles from vuelos   
  3          where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
  4       (select clientes.apellidos    from clientes 
  5          where reservas.cli_nif=clientes.nif) cliente
  6      from reservas;

171113 filas seleccionadas.

Transcurrido: 00:00:02.39

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

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   171K|  5180K|   311   (2)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VUELOS   |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | VUE_PK   |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| CLIENTES |     1 |    37 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN         | CLI_PK   |     1 |       |     1   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL          | RESERVAS |   171K|  5180K|   311   (2)| 00:00:04 |
----------------------------------------------------------------------------------------

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

   2 - access("VUELOS"."ID_VUELO"=:B1)
   4 - access("CLIENTES"."NIF"=:B1)


Estadísticas
----------------------------------------------------------
         15  recursive calls
          0  db block gets
     406374  consistent gets
          0  physical reads
          0  redo size
    7835587  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed


En este caso, el tiempo de ejecución es prácticamente el mismo, mientras que el coste se muestra aun mejor que el de la ejecución anterior (340 anteriores frente a 311) pero el número de bloques leídos en memoria aumenta (374.003 anteriores frente a 406.374).

Las trazas generadas por la utilidad tkprof vienen a confirmar prácticamente lo mostrado en la traza de autotrace.


Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con dos joins


select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
    from vuelos, reservas, clientes
    where vuelos.id_vuelo=reservas.vue_id_vuelo
      and reservas.cli_nif=clientes.nif

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    11409      0.39       0.51         96      13009          0      171113
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    11411      0.39       0.52         96      13009          0      171113


Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con una join y una subconsulta en la cláusula SELECT


select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
    clientes.apellidos
    from reservas, clientes
    where reservas.cli_nif=clientes.nif

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    11409      1.26       1.27          0     373999          0      171113
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    11411      1.27       1.27          0     373999          0      171113


Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con dos subconsultas en la cláusula SELECT


select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos   
       where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
     (select clientes.apellidos    from clientes 
       where reservas.cli_nif=clientes.nif) cliente
    from reservas

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    11409      1.32       1.24          0     406370          0      171113
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    11411      1.32       1.24          0     406370          0      171113

En las dos ejecuciones con subconsultas en la cláusula SELECT se aprecia, además, el aumento de tiempo de CPU por el mayor número de bloques a procesar en memoria.


Cuidado, por tanto, con las subconsultas expresadas a ese nivel de ejecución, pues el optimizador no evalua sus pesos correctamente, al quedar fuera del estudio de accesos y combinaciones entre tablas, mediante joins y filtros convencionales. Los resultados expresados por los planes de ejecución de su estimación en coste pueden confundir, ya que muestran costes mejores sobre ejecuciones claramente más ineficientes.