1. Documentación en Tahiti -> Masters Book List -> VLDB and Partitioning Guide -> 8 Using Parallel Execution
Documentación en Tahiti -> Masters Book List -> Data Warehousing Guide -> 6 Parallel Execution in Data Warehouses
2. La ejecución en paralelo (Parallel Execution) nos permite dividir el trabajo de una operación entre varios procesos con la consiguiente reducción de tiempo de ejecución. Se suele utilizar para operaciones intensivas en entornos donde tenemos varios procesadores simétricos (SMP) con suficiente capacidad I/O. No es recomendable utilizarlo para entornos con muchas transacciones cortas (OLTP) o sistemas con los recursos al límite. Aunque, incluso en sistemas OLTP, nos puede resultar útil de forma puntual para operaciones como creación de índices o procesado batch.
Los requisitos fundamentales para obtener provecho de la ejecución paralela es tener varias CPU y discos. Si no cumplimos algunos estos requisitos y activamos la ejecución paralela, es posible que tengamos el efecto contrario y veamos una caída del rendimiento. Estas son algunas de las operaciones que pueden beneficiarse de la ejecución en paralelo:
· Método de acceso como TABLE SCANS, INDEX FAST FULL SCANS, PARTITIONED INDEX RANGE SCANS
· JOINS como NESTED LOOP, SORT MERGE, HASH, START TRANSFORMATION
· Sentencias DDL (CTAS, CREATE INDEX, REBUILD INDEX, REBUILD INDEX PATTITON, MOVE, SPLIT, COALESCE, …)
· Sentencias DML (INSERT AS SELECT, UPDATE, DELETE y MERGE)
· Parallel Query (SELECT)
· Varias operaciones SQL (GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, ROLLUP)
· SQL*Loader con el parámetro PARALLEL=TRUE
Os recomiendo encarecidamente que os leáis este documento de como funciona Parallel Execution. También podéis leer este White Paper que muestra los conceptos con bastante claridad.
3. Vamos a ver el plan de ejecución de una consulta en la que forzamos un grado de paralelismo (DOP – Degree Of Parallelism) de 4.
-- Obtenemos el plan de ejecución de la consulta EXPLAIN PLAN FOR SELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name, MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD) FROM sh.sales, sh.customers WHERE sales.cust_id=customers.cust_id GROUP BY customers.cust_first_name, customers.cust_last_name; -- Mostramos el plan de ejecución @?/rdbms/admin/utlxplp -- Este es el plan de ejecución que obtenemos -- -------------------------------------------------------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -- -------------------------------------------------------------------------------------------------------------------------------------------------- -- | 0 | SELECT STATEMENT | | 7059 | 441K| | 265 (4)| 00:00:01 | | | | | | -- | 1 | PX COORDINATOR | | | | | | | | | | | | -- | 2 | PX SEND QC (RANDOM) | :TQ10003 | 7059 | 441K| | 265 (4)| 00:00:01 | | | Q1,03 | P->S | QC (RAND) | -- | 3 | HASH GROUP BY | | 7059 | 441K| 536K| 265 (4)| 00:00:01 | | | Q1,03 | PCWP | | -- | 4 | PX RECEIVE | | 7059 | 441K| | 264 (4)| 00:00:01 | | | Q1,03 | PCWP | | -- | 5 | PX SEND HASH | :TQ10002 | 7059 | 441K| | 264 (4)| 00:00:01 | | | Q1,02 | P->P | HASH | -- |* 6 | HASH JOIN | | 7059 | 441K| | 264 (4)| 00:00:01 | | | Q1,02 | PCWP | | -- | 7 | PX RECEIVE | | 7059 | 303K| | 151 (5)| 00:00:01 | | | Q1,02 | PCWP | | -- | 8 | PX SEND BROADCAST | :TQ10001 | 7059 | 303K| | 151 (5)| 00:00:01 | | | Q1,01 | P->P | BROADCAST | -- | 9 | VIEW | VW_GBC_5 | 7059 | 303K| | 151 (5)| 00:00:01 | | | Q1,01 | PCWP | | -- | 10 | HASH GROUP BY | | 7059 | 56472 | | 151 (5)| 00:00:01 | | | Q1,01 | PCWP | | -- | 11 | PX RECEIVE | | 7059 | 56472 | | 151 (5)| 00:00:01 | | | Q1,01 | PCWP | | -- | 12 | PX SEND HASH | :TQ10000 | 7059 | 56472 | | 151 (5)| 00:00:01 | | | Q1,00 | P->P | HASH | -- | 13 | HASH GROUP BY | | 7059 | 56472 | | 151 (5)| 00:00:01 | | | Q1,00 | PCWP | | -- | 14 | PX BLOCK ITERATOR | | 918K| 7178K| | 145 (1)| 00:00:01 | 1 | 28 | Q1,00 | PCWC | | -- | 15 | TABLE ACCESS FULL| SALES | 918K| 7178K| | 145 (1)| 00:00:01 | 1 | 28 | Q1,00 | PCWP | | -- | 16 | PX BLOCK ITERATOR | | 55500 | 1083K| | 112 (0)| 00:00:01 | | | Q1,02 | PCWC | | -- | 17 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1083K| | 112 (0)| 00:00:01 | | | Q1,02 | PCWP | | -- -------------------------------------------------------------------------------------------------------------------------------------------------- -- ... -- ... -- Note -- ----- -- - Degree of Parallelism is 4 because of hint
En el plan podemos observar operaciones específicas de Parallel Execution como «PX COORDINATOR» o «PX RECEIVE». Es importante sabe que es la intra-paralelización e inter-paralelización. La primera de ellas se produce cuando paralelizamos una operación en la que hay varios Parallel Servers trabajando sobre ella. La inter-paralelización consiste en realizar varias operaciones a la vez. Debido a la naturaleza de Oracle, sólo se pueden realizar dos operaciones a la vez, en la que tendremos N procesos paralelos repartidos entre ellas. No voy a entrar en la interpretación del plan de ejecución ya que corresponde al objetivo «Interpret Execution Plan».
Nuestro objetivo ahora es saber cómo configurar Parallel Execution.
4. Volvamos a obtener el plan de ejecución de la consulta anterior, pero esta vez sin indicar el grado de paralelismo.
-- Obtenemos el plan de ejecución de la consulta sin HINTS
EXPLAIN PLAN FOR
SELECT customers.cust_first_name, customers.cust_last_name,
MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sh.sales, sh.customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;
-- Mostramos el plan de ejecución
-- Vemos como ya no aparecen operaciones de PX (Parallel eXecution)
@?/rdbms/admin/utlxplp
-- Consultamos el número de threads por instancia que se utiliza para leer las tablas utilizadas o DOP
-- Vemos que no tenemos configurada la paralelización en estas tablas (DEGREE = 1)
-- Cuando DEGREE = DEFAULT => Se utilizan el DOP automático que calcule Oracle
SELECT TABLE_NAME, DEGREE FROM DBA_TABLES WHERE TABLE_NAME IN ('SALES','CUSTOMERS') AND OWNER = 'SH';
-- Modificamos el DOP de las tablas
ALTER TABLE SH.SALES PARALLEL 8;
ALTER TABLE SH.CUSTOMERS PARALLEL 4;
-- Validamos el cambio
SELECT TABLE_NAME, DEGREE FROM DBA_TABLES WHERE TABLE_NAME IN ('SALES','CUSTOMERS') AND OWNER = 'SH';
-- Obtenemos el plan de la misma consulta pero tras la modificación del DOP
EXPLAIN PLAN FOR
SELECT customers.cust_first_name, customers.cust_last_name,
MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sh.sales, sh.customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;
-- Utilizamos otra forma para ver el plan de ejecución que obtenemos con EXPLAIN PLAN
-- Comprobamos que ya no hemos necesitado el HINT para usar Parallel Execution
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Ahora podemos configurar la tablas para que Oracle escoja el DOP
ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);
ALTER TABLE SH.CUSTOMERS PARALLEL (DEGREE DEFAULT);
-- Validamos el cambio
SELECT TABLE_NAME, DEGREE FROM DBA_TABLES WHERE TABLE_NAME IN ('SALES','CUSTOMERS') AND OWNER = 'SH';
-- Ahora si volvieramos a obtener el plan de ejecución, veríamos que también utiliza Parallel Execution
-- Resetamos el DOP de las tablas al valor original
ALTER TABLE SH.SALES PARALLEL 1;
ALTER TABLE SH.CUSTOMERS PARALLEL 1;
5. El parámetro PARALLEL_DEGREE_POLICY determina el comportamiento de la BD a la hora de decidir si aplica Parallel Execution o no.
· PARALLEL_DEGREE_POLICY = MANUAL => DOP automático desactivado a no ser que se específique explicitamente
· PARALLEL_DEGREE_POLICY = LIMITED => DOP automático para tablas e índices con DEGREE = DEFAULT
· PARALLEL_DEGREE_POLICY = AUTO => DOP automático en cualquier caso
-- Activamos la política
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO SCOPE=BOTH;
-- Comprobamos si Oracle aplica Parallel Execution
EXPLAIN PLAN FOR
SELECT customers.cust_first_name, customers.cust_last_name,
MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sh.sales, sh.customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;
-- Veremos que no lo aplica porque no hemos recolectado estadísticas IO
-- Note
-- -----
-- - automatic DOP: skipped because of IO calibrate statistics are missing
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Calibramos las capacidades de I/O de nuestra BD
-- Documentación en http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_resmgr.htm#CJGHGFEA
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
-- Vemos el resultado
SELECT * FROM DBA_RSRC_IO_CALIBRATE;
-- Comprobamos si Oracle aplica Parallel Execution
EXPLAIN PLAN FOR
SELECT customers.cust_first_name, customers.cust_last_name,
MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sh.sales, sh.customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;
-- Veremos que no lo aplica porque el tiempo de ejecución está por debajo del umbral mínimo
-- Una sentencia debe durar más de PARALLEL_MIN_TIME_THRESHOLD segundos para que aplique PX
-- Note
-- -----
-- - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);
Merece la pena hacer una pausa ahora para explicar el cálculo del DOP automático. Si el cálculo del tiempo de ejecución de una consulta tarda menos de lo establecido en el parámetro PARALLEL_MIN_TIME_THRESHOLD (normalmente 10 segundos), entonces el optimizador entiende que no vamos a tener ningún beneficio aplicando PX, así que la sentencia se ejecuta en «serie».
Si fijamos el parámetro PARALLEL_MIN_TIME_THRESHOLD a 0, en mi laboratorio de pruebas obtengo un DOP de 1 (automatic DOP: Computed Degree of Parallelism is 1). La razón principal es que utilizo discos de estado sólido (SSD) por lo que los tiempos estimado de lectura son bajísimos y Oracle entiende que no merece la pena aplicar PX. Os detallo los pasos realizados para conseguir PX en la consulta que estamos utilizando:
· Clonar la máquina virtual sobre un disco USB externo
· Configurar un nuevo disco para crear una nueva tabla más grande que SALES
· Aumentar el número de CPUs a 8 en la VM
· Lanzar de nuevo la calibración de I/O con la función CALIBRATE_IO
· Crear una copia de la tabla SALES (SALES_TEST) con 58M de registros en un TBS dedicado (TEST)
· Pasar estadísticas a la nueva tabla SALES_TEST
· Lanzar la consulta de nuevo sobre SALES_TEST
Siguiendo estos pasos he obtenido una paralelización de 10 (ver resultado más abajo), por lo que muchas veces no sólo es necesario utilizar el parámetro PARALLEL_DEGREE_POLICY, sino que además hace falta tener recursos suficiente y la necesidad de aplicar PX. Así que podemos decir que el optimizar es bastante «inteligente» en esta versión.
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 441K| | 8088 (3)| 00:00:04 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 7059 | 441K| | 8088 (3)| 00:00:04 | | | Q1,04 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 7059 | 441K| 536K| 8088 (3)| 00:00:04 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 7059 | 441K| | 8087 (3)| 00:00:04 | | | Q1,04 | PCWP | |
| 5 | PX SEND HASH | :TQ10003 | 7059 | 441K| | 8087 (3)| 00:00:04 | | | Q1,03 | P->P | HASH |
|* 6 | HASH JOIN BUFFERED | | 7059 | 441K| | 8087 (3)| 00:00:04 | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 7059 | 303K| | 8042 (3)| 00:00:04 | | | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 7059 | 303K| | 8042 (3)| 00:00:04 | | | Q1,01 | P->P | HASH |
| 9 | VIEW | VW_GBC_5 | 7059 | 303K| | 8042 (3)| 00:00:04 | | | Q1,01 | PCWP | |
| 10 | HASH GROUP BY | | 7059 | 56472 | | 8042 (3)| 00:00:04 | | | Q1,01 | PCWP | |
| 11 | PX RECEIVE | | 7059 | 56472 | | 8042 (3)| 00:00:04 | | | Q1,01 | PCWP | |
| 12 | PX SEND HASH | :TQ10000 | 7059 | 56472 | | 8042 (3)| 00:00:04 | | | Q1,00 | P->P | HASH |
| 13 | HASH GROUP BY | | 7059 | 56472 | | 8042 (3)| 00:00:04 | | | Q1,00 | PCWP | |
| 14 | PX BLOCK ITERATOR | | 58M| 448M| | 7886 (1)| 00:00:04 | 1 | 28 | Q1,00 | PCWC | |
| 15 | TABLE ACCESS FULL| SALES_TEST | 58M| 448M| | 7886 (1)| 00:00:04 | 1 | 28 | Q1,00 | PCWP | |
| 16 | PX RECEIVE | | 55500 | 1083K| | 45 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 17 | PX SEND HASH | :TQ10002 | 55500 | 1083K| | 45 (0)| 00:00:01 | | | Q1,02 | P->P | HASH |
| 18 | PX BLOCK ITERATOR | | 55500 | 1083K| | 45 (0)| 00:00:01 | | | Q1,02 | PCWC | |
| 19 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1083K| | 45 (0)| 00:00:01 | | | Q1,02 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("ITEM_1"="CUSTOMERS"."CUST_ID")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 10
6. Este tema es bastante denso, así que vamos a resumir los procedimientos que podemos usar para poder utilizar PX en nuestra BD. En el objetivo de «Administer, Manage and Tune Parallel Execution» veremos una lista completa de los parámetros que controlan PX.
· Activar el parámetro PARALLEL_DEGREE_LIMIT a AUTO
· Cambiar el DEGREE por defecto que tienen los objetos sobre los que queremos usar PX
ALTER TABLE SH.SALES PARALLEL 4; ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);
· Utilizar HINTS
SELECT /*+ PARALLEL(SALES,4) */ SUM(AMOUNT_SOLD) FROM SH.SALES;
· Mofidicar parámetro PARALLEL_MIN_TIME_THRESHOLD para consultas que duren menos 10 segundos
· Modificar la configuración de la sesión para habilitat PX (Querys, DDL o DML)
ALTER SESSION ENABLE PARALLEL QUERY; ALTER SESSION ENABLE PARALLEL QUERY PARALLEL 5; ALTER SESSION ENABLE PARALLEL DML; ALTER SESSION ENABLE PARALLEL DDL;
· Modificar la configuración de la sesión para forzar PX (para Querys, DDL o DML)
ALTER SESSION FORCE PARALLEL QUERY; ALTER SESSION FORCE PARALLEL QUERY PARALLEL 5; ALTER SESSION FORCE PARALLEL DML; ALTER SESSION FORCE PARALLEL DDL;