Maximizando a eficiência do Hash Join

fevereiro 28, 2021 V. J.A. Table Join

Analisando um plano de execução eventualmente podemos chegar a conclusão que o método de junção mais eficiente para duas tabelas neste plano de execução seja o HASH JOIN, nesta operação podemos utilizar alguns recursos do Oracle para deixa-la mais eficiente, porem alguns desses recursos dependem do tipo de licenciamento do banco, podemos usar paralelismo, particionamento de tabelas, Exadata ou Serial Bloom Filter. Neste artigo vamos mostrar um exemplo de como utilizar o Serial Bloom Filter para deixar seu plano de execução que utiliza a operação Hash JOIN mais eficiente.

O banco de dados Oracle usa a técnica Bloom Filter nas seguintes situações:

Para reduzir a transferência de dados entre “Slave processes” em Junções paralelas
Para implementar Join-Filter Pruning
Para suportar Result Caches
Para reduzir a transferência de dados entre os Storage Disks e as instâncias do banco no Exadata

A utilização do Bloom Filter em Junções paralelas foi implementada na versão 10gR2 e as demais foram implementadas a partir da versão 11gR1.

Na demonstração prática vamos verificar como o uso do Bloom Filter poder maximizar a eficiência da operação Hash Join, para isso vamos executar 3 etapas:

1) Criar duas tabelas para a demonstração prática
2) Executar uma consulta com Hash Join nas duas tabelas
3) Executar uma consulta com Hash Join nas duas tabelas usando Bloom Filter

1) Criar duas tabelas para a demonstração prática

Vamos criar as duas tabelas necessárias para realizar a nossa demonstração.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

SQL > SELECT BANNER_LEGACY FROM V$VERSION where rownum < 2;

BANNER_LEGACY
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

1 linha selecionada.

SQL >
SQL > CREATE TABLE dbtw068_1
2 AS
3 WITH tabpad AS (
4 SELECT rownum id
5 FROM dual
6 CONNECT BY level <= 1e4 )
7 SELECT rownum id,
8 LPAD(rownum,30,'0') vseq
9 FROM tabpad t1,
10 tabpad t2
11 WHERE rownum <= 1e7;

Tabela criada.

SQL >
SQL >
SQL > CREATE TABLE dbtw068_2
2 AS
3 WITH tabpad AS (
4 SELECT rownum id
5 FROM dual
6 CONNECT BY level <= 1e4 )
7 SELECT ROUND(rownum + 0.5,2) id,
8 MOD(rownum,1e5) tipo,
9 LPAD(rownum,10) vseq
10 FROM tabpad t1,
11 tabpad t2
12 WHERE rownum <= 1e7;

Tabela criada.

SQL >

2) Executar uma consulta com Hash Join nas duas tabelas

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

Decorrido: 00:00:00.01
SQL >
SQL >
SQL > SELECT /* dbtw-068.1 */
2 t1.vseq, t2.vseq
3 FROM dbtw068_2 t2,
4 dbtw068_1 t1
5 WHERE t2.tipo = 0
6 AND t1.id = t2.id;

não há linhas selecionadas

Decorrido: 00:00:03.81
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
2 FROM v$sql
3 WHERE sql_text LIKE '%dbtw-068.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
bn86xps6jz1qj 0

1 linha selecionada.

Decorrido: 00:00:00.08
SQL >
SQL >
SQL > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bn86xps6jz1qj, child number 0
-------------------------------------
SELECT /* dbtw-068.1 */ t1.vseq, t2.vseq FROM dbtw068_2 t2,
dbtw068_1 t1 WHERE t2.tipo = 0 AND t1.id = t2.id

Plan hash value: 2988268432

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:03.84 | 96484 | 96476 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 0 |00:00:03.84 | 96484 | 96476 | 1506K| 1506K| 1617K (0)|
|* 2 | TABLE ACCESS FULL| DBTW068_2 | 1 | 99 | 100 |00:00:00.30 | 38473 | 38469 | | | |
| 3 | TABLE ACCESS FULL| DBTW068_1 | 1 | 10M| 10M|00:00:01.25 | 58011 | 58007 | | | |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ID"="T2"."ID")
2 - filter("T2"."TIPO"=0)

22 linhas selecionadas.

Decorrido: 00:00:00.27
SQL >

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

Observe no plano de execução que o Otimizador utilizou a operação Hash Join para juntar as duas tabelas e o tempo de execução da consulta foi de 3.84 segundos.

3) Executar uma consulta com Hash Join nas duas tabelas usando Bloom Filter

Para forçar a utilização do Bloom Filter vamos utilizar o HINT PX_JOIN_FILTER(T1).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

SQL > SELECT /*+ px_join_filter(t1) */ /* dbtw-068.2 */
2 t1.vseq, t2.vseq
3 FROM dbtw068_2 t2,
4 dbtw068_1 t1
5 WHERE t2.tipo = 0
6 AND t1.id = t2.id;

não há linhas selecionadas

Decorrido: 00:00:00.81
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
2 FROM v$sql
3 WHERE sql_text LIKE '%dbtw-068.2%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
ff290kagtx0cy 0

1 linha selecionada.

Decorrido: 00:00:00.02
SQL >
SQL >
SQL > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ff290kagtx0cy, child number 0
-------------------------------------
SELECT /*+ px_join_filter(t1) */ /* dbtw-068.2 */ t1.vseq,
t2.vseq FROM dbtw068_2 t2, dbtw068_1 t1 WHERE t2.tipo = 0
AND t1.id = t2.id

Plan hash value: 629010780

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.81 | 96484 | 96476 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 0 |00:00:00.81 | 96484 | 96476 | 1506K| 1506K| 1231K (0)|
| 2 | JOIN FILTER CREATE| :BF0000 | 1 | 99 | 100 |00:00:00.19 | 38473 | 38469 | | | |
|* 3 | TABLE ACCESS FULL| DBTW068_2 | 1 | 99 | 100 |00:00:00.18 | 38473 | 38469 | | | |
| 4 | JOIN FILTER USE | :BF0000 | 1 | 10M| 15102 |00:00:00.62 | 58011 | 58007 | | | |
|* 5 | TABLE ACCESS FULL| DBTW068_1 | 1 | 10M| 15102 |00:00:00.62 | 58011 | 58007 | | | |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ID"="T2"."ID")
3 - filter("T2"."TIPO"=0)
5 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."ID"))

26 linhas selecionadas.

Decorrido: 00:00:00.07
SQL >

Com a utilização do Bloom Filter no plano de execução o tempo de reposta da consulta caiu para 81 centésimos de segundo, esta redução de tempo ocorre porque durante a execução do plano o Oracle passa o resultado de cada operação (Row Source) para o operação pai, no nosso plano apesar da operação 5 (TABLE ACCESS FULL) ter lido 10 milhões de linhas, devido a utilização da operação JOIN FILTER USE (Bloom Filter) ela só vai passar para operação pai 1 (HASH JOIN) cerca de 15 mil linhas. Esta redução faz toda a diferença pois a operação HASH JOIN vai trabalhar com um numero de linhas bem menor comparada com as 10 milhões de linhas tratadas na primeira consulta sem a utilização do Bloom Filter.