« Monitorear o no monitorear , he allí el problemaCalculando el tamaño actual de la Shared Pool »

Tuning SQL : Lo malo de usar UNION en los select :)



Hace poco acabo de terminar un curso de Tuning de SQL ,el código es el D19165GC20 , en ese curso hay muchos detalles los cuales uno maneja a diario, pero de lo cual en mi caso nunca he escrito.

He aquí un ejemplo, el porque siempre se debiese ocupar el UNION ALL , en vez del UNION , esto último debido a que el UNION provoca un ordenamiento implicito de los datos dentro de la PGA , a pesar de que como todos sabemos el UNION ALL entrega todas las filas a pesar de que estas se repitan , el UNION sólo entrega aquellas diferentes.

He aquí el caso del porque es tan caro el simple, común y pequeño UNION ;)

Para realizar los ejemplos, hemos creado 7 tablas las cuales tienen informacion desde la dba_objects, o sea, cada una de estas tablas posee como 55 mil registros, mas o menos.

Vaciamos la memoria, de todo bloque para que la prueba sea más acertiva.

alter system flush shared_pool ;
alter system flush buffer_cache ;

Primero utilizamos el simple UNION

select * from tabla_full_1
union
select * from tabla_full_2
union
select * from tabla_full_3
union
select * from tabla_full_4
union
select * from tabla_full_5
union
select * from tabla_full_6
union
select * from tabla_full_7
;

Cantidad de filas traídas en la consulta

50610 rows selected.

Y observamos el plan de ejecución

Execution Plan
----------------------------------------------------------
Plan hash value: 3784107405

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 708K| 28M| | 8800 (86)| 00:01:46 |
| 1 | SORT UNIQUE | | 708K| 28M| 76M| 8800 (86)| 00:01:46 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS FULL| TABLA_FULL_1 | 101K| 4151K| | 151 (3)| 00:00:02 |
| 4 | TABLE ACCESS FULL| TABLA_FULL_2 | 101K| 4151K| | 152 (3)| 00:00:02 |
| 5 | TABLE ACCESS FULL| TABLA_FULL_3 | 101K| 4151K| | 152 (3)| 00:00:02 |
| 6 | TABLE ACCESS FULL| TABLA_FULL_4 | 101K| 4151K| | 152 (3)| 00:00:02 |
| 7 | TABLE ACCESS FULL| TABLA_FULL_5 | 101K| 4151K| | 152 (3)| 00:00:02 |
| 8 | TABLE ACCESS FULL| TABLA_FULL_6 | 101K| 4151K| | 152 (3)| 00:00:02 |
| 9 | TABLE ACCESS FULL| TABLA_FULL_7 | 101K| 4151K| | 152 (3)| 00:00:02 |
--------------------------------------------------------------------------------------------

Como lo aprecian , esta ordenando 28MB de información lo cual consume 76MB de espacio temporal (o sea , se esta utilizando disco) , lo cual produce I/O muy costoso.

Statistics
----------------------------------------------------------
1423 recursive calls
0 db block gets
4876 consistent gets
4679 physical reads
0 redo size
2262291 bytes sent via SQL*Net to client
37503 bytes received via SQL*Net from client
3375 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
50610 rows processed

Nuevamente borramos todo vestigio desde memoria.

alter system flush shared_pool ;
alter system flush buffer_cache ;

Y ejecutamos la misma consulta, pero esta vez con UNION ALL

select * from tabla_full_1
union all
select * from tabla_full_2
union all
select * from tabla_full_3
union all
select * from tabla_full_4
union all
select * from tabla_full_5
union all
select * from tabla_full_6
union all
select * from tabla_full_7
;

Trae efectivamente mas de 700 mil registros, los cuales debiesen ser procesados con un cursor, para así descartar los repetidos, es más barato y más rápido

708540 rows selected.

El plan de ejecución de la sentencia con UNION ALL

Execution Plan
----------------------------------------------------------
Plan hash value: 2473866688

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 708K| 28M| 1061 (87)| 00:00:13 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| TABLA_FULL_1 | 101K| 4151K| 151 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TABLA_FULL_2 | 101K| 4151K| 152 (3)| 00:00:02 |
| 4 | TABLE ACCESS FULL| TABLA_FULL_3 | 101K| 4151K| 152 (3)| 00:00:02 |
| 5 | TABLE ACCESS FULL| TABLA_FULL_4 | 101K| 4151K| 152 (3)| 00:00:02 |
| 6 | TABLE ACCESS FULL| TABLA_FULL_5 | 101K| 4151K| 152 (3)| 00:00:02 |
| 7 | TABLE ACCESS FULL| TABLA_FULL_6 | 101K| 4151K| 152 (3)| 00:00:02 |
| 8 | TABLE ACCESS FULL| TABLA_FULL_7 | 101K| 4151K| 152 (3)| 00:00:02 |
-----------------------------------------------------------------------------------

Y como se puede apreciar, no hay uso de espacio temporal , sólo se procesan los 28MB de datos.

Statistics
----------------------------------------------------------
1423 recursive calls
0 db block gets
51700 consistent gets
4679 physical reads
0 redo size
27134781 bytes sent via SQL*Net to client
519985 bytes received via SQL*Net from client
47237 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
708540 rows processed

Para que se tenga en cuenta al momento de realizar un programa

Espero les sirva

by Ligarius
17.08.09. 09:28:17. 626 words, 8236 views. Categories: Oracle 10g, Tuning / Performance ,