En muchas ocasiones he dicho para entender bien cómo funciona Oracle hay que saber bien dos cosas
- teoría de conjuntos
- estadística
La optimización del desempeño no es ni un arte y mucho menos magia, se necesita entender el modelo matemático y proponer otro modelo equivalente pero con un tiempo de ejecución menor.
Las propiedades de la multiplicación son un ejemplo básico para modelos matemáticos equivalentes.
Primero vamos a enfocarnos en la propiedad distributiva de la intersección. La propiedad dice que la unión de dos o más conjuntos, intersectados por otro conjunto, es igual a la unión de la intersección de cada conjunto con este último.
Lo más importante de esta técnica es que las igualdades propuestas sean en efecto equivalentes a la igualdad original.
Los conjuntos están definidos
A = {2,4,6}
B = {4,6,8,10,12}
C = {10,11,12,13,14,15,16,17}
La sentencia #1 es la que se va a afinar y sobre la cual buscamos una sentencia #2 que le sea equivalente. De este modo tenemos
Sentencia #1
select * from a, b where a1 = b1
union
select * from a, c where a1 = c1;
Con el plan de ejecución
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 338 | 9 (34)| 00:00:01 |
|*1 | HASH JOIN | | 13 | 338 | 9 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 3 | 39 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 13 | 169 | 6 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 13 | 169 | 6 (67)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS FULL | B | 5 | 65 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | C | 8 | 104 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Si aplicamos la propiedad distributiva de la intersección obtenemos la sentencia #2 que es equivalente
Sentencia #2
select * from a,
(select * from b
union
select * from c) d
where a1 = b1;
Con un plan de ejecución más eficiente
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 11 (64)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 156 | 11 (64)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|*3 | HASH JOIN | | 3 | 78 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS FULL | A | 3 | 39 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | B | 5 | 65 | 2 (0)| 00:00:01 |
|*6 | HASH JOIN | | 3 | 78 | 5 (20)| 00:00:01 |
| 7 | TABLE ACCESS FULL | A | 3 | 39 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | C | 8 | 104 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Para demostrar la mejora en los tiempos de ejecución creé otro caso de pruebas, elegí tres tablas. Las primeras dos son enormes pero la tercera es gigantesca. Todas las columnas sobre las cuales se hace la búsqueda están indexadas así que crear un índice no es una opción. Las tablas fueron creadas con datos distribuidos normalmente, así que el escenario no presenta sesgos.
Les presento las tablas
STAGE // tabla gigantesca
Name Null? Type
-------------------------------------------
ID NUMBER // columna indexada
DATA VARCHAR2(128)
...
LARGE // tabla grande
Name Null? Type
-------------------------------------------
UNIQUEID NUMBER
ID NUMBER // columna indexada
...
LARGE1 // tabla grande
Name Null? Type
-------------------------------------------
UNIQUEID NUMBER
ID NUMBER // columna indexada
...
La sentencia original es la siguiente y la llamaremos sentencia#1
SELECT * FROM stage a, large b
WHERE a.id = b.id
UNION
SELECT * FROM stage a, large1 c
WHERE a.id = c.id;
y su plan de ejecución
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 255M| 14G| | 2607K (51)| 09:11:58 |
| 1 | SORT UNIQUE | | 255M| 14G| 18G| 2607K (51)| 09:11:58 |
| 2 | UNION-ALL | | | | | | |
|*3 | HASH JOIN | | 127M| 7568M| 37M| 6989 (22)| 00:01:29 |
| 4 | TABLE ACCESS FULL | LARGE | 999K| 25M| | 387 (8)| 00:00:05 |
| 5 | TABLE ACCESS FULL | STAGE | 1455K| 48M| | 624 (6)| 00:00:08 |
|*6 | HASH JOIN | | 127M| 7568M| 37M| 6991 (22)| 00:01:29 |
| 7 | TABLE ACCESS FULL | LARGE1 | 999K| 25M| | 389 (8)| 00:00:05 |
| 8 | TABLE ACCESS FULL | STAGE | 1455K| 48M| | 624 (6)| 00:00:08 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 – access(“A”.”ID”=”B”.”ID”)
6 – access(“A”.”ID”=”C”.”ID”)
Haciendo la misma optimización para tener una operación equivalente obtenemos de la sentencia#1 la sentencia#2
SELECT * FROM stage a,
(SELECT * FROM large
UNION
SELECT * FROM large1) d
WHERE a.id = d.id;
Nota: para fines de la agrupación creamos un nuevo conjunto D.
El plan de ejecución para la sentencia#2 es el siguiente
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 255M| 27G| | 14643 (22)| 00:03:06 |
|*1 | HASH JOIN | | 255M| 27G| 65M| 14643 (22)| 00:03:06 |
| 2 | TABLE ACCESS FULL | STAGE | 1455K| 48M| | 624 (6)| 00:00:08 |
| 3 | VIEW | | 1999K| 156M| | 8491 (4)| 00:01:48 |
| 4 | SORT UNIQUE | | 1999K| 51M| 84M| 8491 (52)| 00:01:48 |
| 5 | UNION-ALL | | | | | | |
| 6 | TABLE ACCESS FULL| LARGE | 999K| 25M| | 387 (8)| 00:00:05 |
| 7 | TABLE ACCESS FULL| LARGE1 | 999K| 25M| | 389 (8)| 00:00:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 – access(“A”.”ID”=”D”.”ID”)
¿Se ven parecidos? únicamente si 9 horas es parecido a 3 minutos.
Para los que se lo sigan preguntando, ninguna de las sentencias es eficiente, basta con ver el plan de ejecución y sobre todo el espacio temporal necesario. Para nuestro ejemplo, ¿cuál es la razón del mal desempeño? el diseño mismo … pero tener un diseño ineficiente para el desarrollo de este caso de prueba es lo que precísamente buscábamos.
Conclusión:
En muchas ocasiones nos encontramos con aplicaciones que no permiten la creación de índices o bien donde aún el haqueo de las estadísticas no es de gran ayuda. Es probable que en esas situaciones tampoco podamos tener acceso al código de la aplicación, en el mejor de los casos debería de abrirse un incidente con la compañía dueña de la aplicación para corregir la aplicación, pero cuando ésto no es posible entonces pueden usarse outlines.
