{"id":300,"date":"2016-02-14T23:24:36","date_gmt":"2016-02-15T01:24:36","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=300"},"modified":"2019-10-10T22:55:07","modified_gmt":"2019-10-11T01:55:07","slug":"nested-loop-ou-hash-join-qual-a-melhor-opcao-para-juncao-de-tabelas","status":"publish","type":"post","link":"https:\/\/dbtimewizard.com.br\/blog\/nested-loop-ou-hash-join-qual-a-melhor-opcao-para-juncao-de-tabelas\/","title":{"rendered":"NESTED LOOP ou HASH JOIN? Qual a melhor op\u00e7\u00e3o para jun\u00e7\u00e3o de tabelas?"},"content":{"rendered":"

O NESTED LOOP e o HASH JOIN n\u00e3o s\u00e3o as \u00fanicas op\u00e7\u00f5es utilizadas pelo Otimizador do Oracle para fazer a jun\u00e7\u00e3o de linhas de duas tabelas, mas s\u00e3o as op\u00e7\u00f5es mais utilizadas. Neste artigo vamos explorar um pouco como funcionam essas opera\u00e7\u00f5es e verificar qual delas \u00e9 a melhor op\u00e7\u00e3o para o Otimizador.<\/span><\/p>\n

Vamos come\u00e7ar respondendo a quest\u00e3o que tamb\u00e9m \u00e9 o titulo desse artigo, NESTED LOOP ou HASH JOIN? Qual a melhor op\u00e7\u00e3o para o Otimizador realizar uma opera\u00e7\u00e3o de jun\u00e7\u00e3o de duas tabelas? A resposta \u00e9: depende, sim depende do tamanho das tabelas envolvidas, se existem \u00edndices nas colunas utilizadas como chave da jun\u00e7\u00e3o, da seletividade das colunas do filtro da cl\u00e1usula WHERE, etc… Para facilitar o entendimento vamos come\u00e7ar com uma breve descri\u00e7\u00e3o das duas opera\u00e7\u00f5es e em seguida vamos fazer simula\u00e7\u00f5es para consolidar essa teoria.<\/span><\/p>\n

Defini\u00e7\u00e3o NESTED LOOP<\/span><\/h2>\n

Opera\u00e7\u00e3o utilizada para jun\u00e7\u00e3o de duas tabelas, nessa opera\u00e7\u00e3o as linhas da tabela externa (Condutora) s\u00e3o recuperados e para cada linha dessa tabela s\u00e3o recuperadas linhas da tabela interna (Conduzida). Essa opera\u00e7\u00e3o \u00e9 utilizada pelo Otimizador quando um pequeno conjunto de linhas de cada uma das tabela s\u00e3o utilizadas na jun\u00e7\u00e3o das duas tabelas e o m\u00e9todo de acesso normalmente utilizado para recuperar as linhas da tabela interna \u00e9 um \u00edndice.<\/span><\/p>\n

Defini\u00e7\u00e3o HASH JOIN<\/span><\/h2>\n

Opera\u00e7\u00e3o utilizada para jun\u00e7\u00e3o de duas tabelas quando o conjunto de linhas a ser recuperado dessas tabelas \u00e9 grande, o Otimizador utiliza a tabela que possui o menor conjunto de linhas (BUILD TABLE) para criar um “HASH TABLE” na memoria (in-memory hash) e busca na outra tabela (PROBE TABLE) as linhas que corresponde a fun\u00e7\u00e3o HASH da primeira tabela.<\/span><\/p>\n

Descri\u00e7\u00e3o da simula\u00e7\u00e3o<\/span><\/h2>\n

Para compreender melhor essas defini\u00e7\u00f5es vamos fazer tr\u00eas simula\u00e7\u00f5es utilizando o “SAMPLE SCHEMA SH<\/a><\/strong>“, na primeira simula\u00e7\u00e3o nossa consulta vai selecionar poucas linhas e o Otimizador vai escolher um plano de execu\u00e7\u00e3o com NESTED LOOP, na segunda simula\u00e7\u00e3o vamos alterar o filtro da cl\u00e1usula WHERE para selecionar uma grande quantidade de linhas e neste caso o Otimizador escolher\u00e1 a opera\u00e7\u00e3o HASH JOIN, na ultima simula\u00e7\u00e3o vamos utilizar um HINT para for\u00e7ar o Otimizador utilizar NESTED LOOP para fazer o JOIN da mesma consulta utilizada na segunda simula\u00e7\u00e3o e vamos comparar os resultados em termos de desempenho.<\/span><\/p>\n

Caso tenha alguma d\u00favida na leitura do plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN consulte a s\u00e9rie de artigos que vai ajuda-lo a interpretar essas informa\u00e7\u00f5es:<\/span><\/p>\n

1. Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas<\/a><\/span><\/strong>
\n2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/span><\/strong>
\n3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/span><\/strong>
\n4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/span><\/strong><\/p>\n

 <\/p>\n

Jun\u00e7\u00e3o de tabelas com NESTED LOOP
\n<\/span><\/h2>\n

Nesta simula\u00e7\u00e3o nossa consulta vai selecionar 75 linhas, o Otimizador decide que a melhor op\u00e7\u00e3o ser\u00e1 um NESTED LOOP, pois o JOIN consistir\u00e1 da leitura de 1 linha da tabela COUNTRIES e 75 linhas da tabela CUSTOMERS.<\/span><\/p>\n

 <\/p>\n

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\n
\nsh@<\/span>LAB11 ><\/span> set tab off;<\/span>
\nsh@<\/span>LAB11 ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> select \/* tst001 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n  2<\/span>    from countries co,<\/span>
\n  3<\/span>         customers cu
\n  4<\/span>   where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\n  5<\/span>     and co.<\/span>COUNTRY_ID =<\/span> 52787<\/span>;<\/span>
\n
\nCOUNTRY_ID CUST_FIRST_NAME      CUST_CITY
\n----------<\/span> --------------------<\/span> ------------------------------<\/span>
\n     52787<\/span> Arnold               Riyadh
\n     52787<\/span> Augustus             Riyadh
\n     52787<\/span> Bertilde             Riyadh
\n     52787<\/span> Bertram              Riyadh
\n     52787<\/span> Beulah               Riyadh
\n     52787<\/span> Byron                Riyadh
\n     52787<\/span> Bryant               Riyadh
\n     .....<\/span>
\n     .....<\/span>
\n     .....<\/span>
\n     52787<\/span> Pavani               Riyadh
\n     52787<\/span> Sydney               Riyadh
\n     52787<\/span> Ramkumar             Riyadh
\n     52787<\/span> Pablo                Riyadh
\n     52787<\/span> Joshua               Riyadh
\n
\n75<\/span> rows selected.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncc7vkhbu2thgt            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'cc7vkhbu2thgt'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst001 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\nfrom countries co,<\/span>        customers cu  where co.<\/span>COUNTRY_ID =<\/span>
\ncu.<\/span>COUNTRY_ID    and co.<\/span>COUNTRY_ID =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 763797669<\/span>
\n
\n---------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1462<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS      |<\/span>              |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1462<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX UNIQUE SCAN|<\/span> COUNTRIES_PK |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       1<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>     75<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1461<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n   3<\/span> -<\/span> filter(<\/span>"CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n23<\/span> rows selected.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Jun\u00e7\u00e3o de tabelas com HASH JOIN<\/span><\/h2>\n

Nesta segunda simula\u00e7\u00e3o vamos filtrar tr\u00eas pa\u00edses no campo COUNTRY_ID da cl\u00e1usula WHERE o que vai aumentar nossa sele\u00e7\u00e3o de linhas para aproximadamente 34 mil linhas, com essa estimativa o Otimizador decide que a melhor op\u00e7\u00e3o para realizar esta jun\u00e7\u00e3o \u00e9 a opera\u00e7\u00e3o HASH JOIN.<\/span><\/p>\n

 <\/p>\n

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> select \/* tst002 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n  2<\/span>    from countries co,<\/span>
\n  3<\/span>         customers cu
\n  4<\/span>   where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\n  5<\/span>     and co.<\/span>COUNTRY_ID in (<\/span>52770<\/span>,<\/span>52776<\/span>,<\/span>52790<\/span>)<\/span>;<\/span>
\n
\nCOUNTRY_ID CUST_FIRST_NAME      CUST_CITY
\n----------<\/span> --------------------<\/span> ------------------------------<\/span>
\n     52770<\/span> Abigail              Ede
\n     52770<\/span> Abigail              Hoofddorp
\n     52770<\/span> Abigail              Schimmert
\n     52770<\/span> Abigail              Scheveningen
\n     52770<\/span> Abigail              Haarlem
\n     52770<\/span> Abigail              Lelystad
\n     52776<\/span> Abner                Murnau
\n     52790<\/span> Abner                Los Angeles
\n     52776<\/span> Abner                Stuttgart
\n     52790<\/span> Abner                Montara
\n     .....<\/span>
\n     .....<\/span>
\n     .....<\/span>
\n     52790<\/span> Jordan               Molino
\n     52790<\/span> Sydney               Evinston
\n     52790<\/span> Boriana              Orlinda
\n     52790<\/span> Jade                 Norman
\n     52790<\/span> Joshua               Cleveland
\n     52790<\/span> Andrew               Duncan
\n
\n34473<\/span> rows selected.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n9z5f0k26an9vy            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'9z5f0k26an9vy'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst002 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\nfrom countries co,<\/span>        customers cu  where co.<\/span>COUNTRY_ID =<\/span>
\ncu.<\/span>COUNTRY_ID    and co.<\/span>COUNTRY_ID in (<\/span>:<\/span>"SYS_B_0"<\/span>,:<\/span>"SYS_B_1"<\/span>,:<\/span>"SYS_B_2"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1865765122<\/span>
\n
\n----------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n----------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span> |<\/span>    3697<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  HASH<\/span><\/a> JOIN<\/span><\/a>          |<\/span>              |<\/span>      1<\/span> |<\/span>   5443<\/span> |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.08<\/span> |<\/span>    3697<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   INLIST ITERATOR   |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>      3<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX UNIQUE SCAN|<\/span> COUNTRIES_PK |<\/span>      3<\/span> |<\/span>      3<\/span> |<\/span>      3<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  4<\/span> |<\/span>   TABLE ACCESS FULL |<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>  34473<\/span> |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    3695<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> access(<\/span>"CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>)<\/span>
\n   3<\/span> -<\/span> access(<\/span>(<\/span>"CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_0 OR "CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_1 OR
\n              "CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_2)<\/span>)<\/span>
\n   4<\/span> -<\/span> filter(<\/span>(<\/span>"CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_0 OR "CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_1 OR
\n              "CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\n
\n27<\/span> rows selected.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Jun\u00e7\u00e3o de tabelas usando HINT<\/span><\/h2>\n

Nas duas primeiras simula\u00e7\u00f5es observamos que o Otimizador escolheu o tipo de opera\u00e7\u00e3o JOIN baseado nas defini\u00e7\u00f5es de cada uma delas, mas ser\u00e1 que essas escolhas foram realmente as melhores em termos de desempenho? Vamos fazer uma terceira simula\u00e7\u00e3o utilizando a segunda consulta, mas desta vez vamos utilizar um HINT para for\u00e7ar a utiliza\u00e7\u00e3o de uma opera\u00e7\u00e3o de JOIN diferente da escolhida pelo Otimizando na segunda simula\u00e7\u00e3o e comparar o desempenho dessa consulta com sua antecessora.<\/span><\/p>\n

 <\/p>\n

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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> select \/*+ USE_NL(co cu) *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n  2<\/span>    from countries co,<\/span>
\n  3<\/span>         customers cu
\n  4<\/span>   where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\n  5<\/span>     and co.<\/span>COUNTRY_ID in (<\/span>52770<\/span>,<\/span>52776<\/span>,<\/span>52790<\/span>)<\/span>;<\/span>
\n
\nCOUNTRY_ID CUST_FIRST_NAME      CUST_CITY
\n----------<\/span> --------------------<\/span> ------------------------------<\/span>
\n     52770<\/span> Abigail              Ede
\n     52770<\/span> Abigail              Hoofddorp
\n     52770<\/span> Abigail              Schimmert
\n     52770<\/span> Abigail              Scheveningen
\n     52770<\/span> Abigail              Haarlem
\n     52770<\/span> Abigail              Lelystad
\n     52776<\/span> Abner                Murnau
\n     52790<\/span> Abner                Los Angeles
\n     52776<\/span> Abner                Stuttgart
\n     52790<\/span> Abner                Montara
\n     52776<\/span> Abner                Neuss
\n     52776<\/span> Abner                Schwaebisch Gmuend
\n     ...<\/span>
\n     ...<\/span>
\n     ...<\/span>
\n     52790<\/span> Boriana              Orlinda
\n     52790<\/span> Jade                 Norman
\n     52790<\/span> Joshua               Cleveland
\n     52790<\/span> Andrew               Duncan
\n
\n34473<\/span> rows selected.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%USE_NL(co cu)%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n69sh8pmxx610j            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nold   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'69sh8pmxx610j'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/*+ USE_NL(co cu) *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span>
\ncu.<\/span>CUST_CITY   from countries co,<\/span>        customers cu  where
\nco.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID    and co.<\/span>COUNTRY_ID in
\n(<\/span>:<\/span>"SYS_B_0"<\/span>,:<\/span>"SYS_B_1"<\/span>,:<\/span>"SYS_B_2"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1539906852<\/span>
\n
\n---------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name         |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.13<\/span> |<\/span>    5998<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS      |<\/span>              |<\/span>      1<\/span> |<\/span>   5443<\/span> |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.13<\/span> |<\/span>    5998<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>  34473<\/span> |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>    3696<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>   INDEX UNIQUE SCAN|<\/span> COUNTRIES_PK |<\/span>  34473<\/span> |<\/span>      1<\/span> |<\/span>  34473<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span> |<\/span>    2302<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>"CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_0 OR "CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_1 OR
\n              "CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_2)<\/span>)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"CU"<\/span>.<\/span>"COUNTRY_ID"<\/span>)<\/span>
\n       filter(<\/span>(<\/span>"CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_0 OR "CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_1 OR
\n              "CO"<\/span>.<\/span>"COUNTRY_ID"<\/span>=:<\/span>SYS_B_2)<\/span>)<\/span>
\n
\n
\n27<\/span> rows selected.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Comparando os planos de execu\u00e7\u00e3o da segunda e terceira simula\u00e7\u00e3o podemos observar que a terceira simula\u00e7\u00e3o teve um desempenho inferior a segunda, o tempo de resposta aumentou de 8 para 13 cent\u00e9simos de segundos e o n\u00famero de buffers lidos aumento de 3697 para 5998, parece pouco mas se essa consulta fizer parte de uma aplica\u00e7\u00e3o que precisa execut\u00e1-la milh\u00f5es de vezes durante o dia a diferen\u00e7a passa ser significativa. O plano de execu\u00e7\u00e3o da segunda simula\u00e7\u00e3o teve um desempenho melhor e a opera\u00e7\u00e3o de jun\u00e7\u00e3o foi uma escolha acertada do Otimizador.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Como pudemos observar em nossas simula\u00e7\u00f5es o Otimizador \u00e9 muito competente na escolha do tipo de opera\u00e7\u00e3o JOIN a ser utilizada na jun\u00e7\u00e3o de duas tabelas, mas a escolha dele \u00e9 feita baseada em objetos existentes no banco de dados naquele momento, em determinadas situa\u00e7\u00f5es a escolha feita pelo Otimizador pode n\u00e3o ser a melhor devido a falta de um \u00edndice, estat\u00edsticas insuficientes ou desatualizadas, incompatibilidade de tipos de coluna de dados, etc… Nesse momento podemos aplicar os conceitos abordados neste artigo para analisar o plano de execu\u00e7\u00e3o, identificar as causas que levaram o Otimizador a tomar uma decis\u00e3o incorreta e corrigir este problema para melhorar o desempenho da instru\u00e7\u00e3o SQL.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

http:\/\/docs.oracle.com\/cd\/E11882_01\/server.112\/e41573\/optimops.htm#PFGRF94639<\/a><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

O NESTED LOOP e o HASH JOIN n\u00e3o s\u00e3o as \u00fanicas op\u00e7\u00f5es utilizadas pelo Otimizador do Oracle para fazer a jun\u00e7\u00e3o de linhas de duas tabelas, mas s\u00e3o as op\u00e7\u00f5es mais utilizadas. Neste artigo vamos explorar um pouco como funcionam essas opera\u00e7\u00f5es e verificar qual delas \u00e9 a melhor op\u00e7\u00e3o para o Otimizador. Vamos come\u00e7ar respondendo a quest\u00e3o que tamb\u00e9m<\/p>\n","protected":false},"author":2,"featured_media":301,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[87],"tags":[65,6,42,36,89,88,82,10,29,11],"class_list":["post-300","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-table-join","tag-ajuste-de-desempenho","tag-cbo","tag-cost-base-optimizer","tag-execution-plan","tag-hash-join","tag-nested-loop","tag-optimizer","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=300"}],"version-history":[{"count":17,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/300\/revisions"}],"predecessor-version":[{"id":2050,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/300\/revisions\/2050"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/301"}],"wp:attachment":[{"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}