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.
2 comentarios:
Cool!
Gracias !
Publicar un comentario