Paralelismo: Mitos e Fatos

Paralelismo

Objetivo: Mostrar através da teoria e alguns exemplos práticos que o uso do paralelismo deve ser testado e alinhado juntamente com os DBAs, já que nem sempre utilizá-lo resulta em um ganho de desempenho.

Quando uma consulta SQL é executada em paralelo, cria-se um processo chamado Query Coordinator (QC) e vários outros processos Queries Slaves, chamados Pn, Onde n varia de acordo com o número de paralelismo indicado no comando.
Por exemplo, quando utilizamos o hint /*+ parallel (P,4) */, estamos criando um processo QC e os slaves: P1, P2, P3 e P4.

Na figura 1, temos o funcionamento de uma parallel query tomando como ponto de partida a consulta:
select /*+ paralel (e, 4) */ from emp order by ename ;

Figura1: Funcionamento Interno

O Query Coordinator (QC) é responsável por coordenar a execução do Queries Slaves, reunir as informações recebidas por cada processo slave e apresentar o conjunto de resultados para o usuário que enviou a consulta SQL.
Os Queries Slaves são os verdadeiros responsáveis pela coleta dos dados da consulta SQL em si. QuandoQueries Slaves terminam a leitura dos dados,estes enviam esses dados para o QC através de mensagens de buffer.

Essas mensagens têm seu tamanho especificado pelo parâmetro parallel_execution_message_size e são trocadas em uma área específica de buffer.

O parâmetro parallel_automatic_tuning define o lugar deste buffer de mensagens.
Se o parâmetro estiver setado para TRUE, o buffer será utilizado na large pool, se estiver setado para FALSE a área de memória utilizada será a shared pool. Portanto, é recomendado que se o banco de dados executa muitas consultas paralelas, estudar um tamanho apropriado para esta área de memória e, além disso, configurar valores mínimos para estas áreas de memória.

Isso demonstra que a utilização de paralelismo em SQLs deve ser alinhada com o DBA e bem testada, para que os parâmetros de memória sejam configurados de forma coerente.
Outro ponto importante a ser mencionado é que a abertura de vários processos paralelos em um SQL ocasiona lentidão também na própria abertura dos processos.
Vamos a um exemplo prático:

SELECT /*+ FULL (c) PARALLEL (c, 5,2) */ C.*, E.*, C.*, P.*, T.*
FROM EMP_CONTROL EC, EMP EM, PRODUCT_CRB T, CONTRACT C, PRODUCT P
WHERE (C.EMP = EC.EMP) AND (EC.EMP = EM.EMP)
AND (C.CURSO = ’10’) AND (C.PRE_POS = ’10’)
AND (C.PRODUTO = P.PRODUTO) AND (C.PRODUTO = T.PRODUTO)
AND (T.CURSO = C.CURSO) AND (C.DT_CANC IS NULL)
AND (C.DT_LIQ_EFETIVA IS NULL)
AND ((C.VAL_APRDO_LIQ = 0 OR C.VAL_APRDO_LIQ IS NULL) OR (C.VAL_APRDO_LIQ > 0 AND C.DT_CESSAO IS NOT NULL))
AND (C.DT_EFETIVA <= EC.DT_FECHAMENTO)
AND (C.DT_ULT_APRO < EC.DT_FECHAMENTO)
AND (EC.NUM_AUDSID = 58900238) AND (C.STATUS IN (‘0’, ‘4’))
AND (C.RENDAS_A_APROP <> C.VAL_APRDO)
AND (C.TX_APROP > 0) AND ((T.PROC_PERM = ‘0’) OR (T.PROC_PERM = ‘1’ AND C.DT_ULTI_VCT < E.DT_FECHAMENTO))
AND ((EC.DT_FECHAMENTO < (SELECT MIN(DT_LIQ_EFETIVA) FROM CONTRACT_REA CR1
WHERE CR1.CONTRATO = C.CONTRATO)) OR
((NOT EXISTS (SELECT 1 FROM CONTRACT_REA CR3
WHERE (CR3.CONTRATO = C.CONTRATO AND E.DT_FECHAMENTO >= CR3.DT_LIQ_EFETIVA
AND  E.DT_FECHAMENTO < CR3.DT_REAB_LIQ) AND ROWNUM = 1))) OR
((EC.DT_FECHAMENTO > (SELECT MAX(DT_REAB_LIQ) FROM CONTRATO_REA CR4
WHERE CR4.CONTRATO = C.CONTRATO))))
AND EXISTS (SELECT /*+ FULL (p) PARALLEL (p, 4,2) */ 1 FROM PARC P1 WHERE C.CONTRATO = P1.CONTRATO
AND P1.STATUS IN (‘0’, ‘1’) AND ((ST_TIPO_CESSAO IS NULL) OR (ST_CESSAO IS NULL)) AND ROWNUM = 1)
ORDER BY C.EMP ;

———————————————————————————
| Id | Operation | Name | Rows |
———————————————————————————
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT ORDER BY | | 1 |
|* 2 | FILTER | | |
| 3 | PX COORDINATOR | | |
| 4 | PX SEND QC (RANDOM) | :TQ20001 | 4 |
| 5 | NESTED LOOPS | | 4 |
| 6 | NESTED LOOPS | | 4 |
|* 7 | HASH JOIN | | 4 |
| 8 | BUFFER SORT | | |
| 9 | PX RECEIVE | | 32 |
| 10 | PX SEND BROADCAST | :TQ20000 | 32 |
| 11 | MERGE JOIN CARTESIAN | | 32 |
| 12 | TABLE ACCESS BY INDEX ROWID| EMP_CONTROL | 1 |
|* 13 | INDEX RANGE SCAN | EMP_CONTROL_01 | 1 |
| 14 | BUFFER SORT | | 32 |
|* 15 | TABLE ACCESS FULL | PRODUCT_CRB | 32 |
| 16 | PX BLOCK ITERATOR | | 827K|
|* 17 | TABLE ACCESS FULL | CONTRACT | 827K|
| 18 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 1 |
|* 19 | INDEX UNIQUE SCAN | SYS_C0049229 | 1 |
| 20 | TABLE ACCESS BY INDEX ROWID | EMP | 1 |
|* 21 | INDEX UNIQUE SCAN | SYS_C0049040 | 1 |
|* 22 | COUNT STOPKEY | | |
| 23 | PX COORDINATOR | | |
| 24 | PX SEND QC (RANDOM) | :TQ10000 | 2 |
|* 25 | COUNT STOPKEY | | |
| 26 | PX BLOCK ITERATOR | | 2 |
|* 27 | TABLE ACCESS FULL | PARC | 2 |
| 28 | SORT AGGREGATE | | 1 |
| 29 | TABLE ACCESS BY INDEX ROWID | CONTRACT_REA | 1 |
|* 30 | INDEX RANGE SCAN | SYS_C0048917 | 1 |
|* 31 | COUNT STOPKEY | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | CONTRACT_REA | 1 |
|* 33 | INDEX RANGE SCAN | SYS_C0048917 | 1 |
| 34 | SORT AGGREGATE | | 1 |
| 35 | FIRST ROW | | 1 |
|* 36 | INDEX RANGE SCAN (MIN/MAX) | SYS_C0048917 | 1 |
———————————————————————————

Note a presença dos processos PX Coordinator e a diferença no plano de execução comparada ao plano de execução 2 abaixo, destaquei também a linha que apresenta o MERGE JOIN CARTESIAN já que este método de join realiza a multiplicação cartesiana das linhas de duas fontes de dados.

—————————————————————————–
| Id | Operation | Name | Rows |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT ORDER BY | | 1 |
|* 2 | FILTER | | |
|* 3 | HASH JOIN | | 4 |
| 4 | NESTED LOOPS | | 4 |
| 5 | NESTED LOOPS | | 4 |
| 6 | NESTED LOOPS | | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID | EMP_CONTROL | 1 |
|* 8 | INDEX RANGE SCAN | EMP_CONTROL_01 | 1 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMP | 1 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0049040 | 1 |
| 11 | INLIST ITERATOR | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | CONTRACT | 4 |
|* 13 | INDEX RANGE SCAN | CON_13 | 286 |
| 14 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 1 |
|* 15 | INDEX UNIQUE SCAN | SYS_C0049229 | 1 |
|* 16 | TABLE ACCESS FULL | PRODUCT_CTB | 32 |
|* 17 | COUNT STOPKEY | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | PARC | 2 |
|* 19 | INDEX RANGE SCAN | INDX_PARC_04 | 17 |
| 20 | SORT AGGREGATE | | 1 |
| 21 | TABLE ACCESS BY INDEX ROWID | CONTRACT_REA | 1 |
|* 22 | INDEX RANGE SCAN | SYS_C0048917 | 1 |
|* 23 | COUNT STOPKEY | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | CONTRACT_REA | 1 |
|* 25 | INDEX RANGE SCAN | SYS_C0048917 | 1 |
| 26 | SORT AGGREGATE | | 1 |
| 27 | FIRST ROW | | 1 |
|* 28 | INDEX RANGE SCAN (MIN/MAX)| SYS_C0048917 | 1 |
—————————————————————————–

Note os métodos de joins totalmente diferentes realizados pelo otimizador para a resolução do SQL, neste caso a sequência de NESTED LOOPS é muito mais eficiente para o término do comando.
As tabelas abaixo mostram as estatísticas e o tempo de execução de cada consulta:

EstatÝstica
———————————————————-
349 recursive calls
6 db block gets
68 consistent gets
0 physical reads
1240 redo size
1135 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
1 rows processed

Tempo decorrido: 00.00:02.25

EstatÝstica
———————————————————-
8 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1135 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Tempo decorrido: 00.00:00.25

No caso deste SQL a diferença em buffer gets é de aproximadamente 96% e em tempo de resposta aproximadamente 89%.
A análise acima comprova que o aumento do número de processos paralelos para a realização da consulta (grau de paralelismo) não torna sua execução mais performática.

 

José Eduardo Fiamengui Júnior
jeduardounicamp@gmail.com