(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.
