{"id":1129,"date":"2017-09-12T14:48:02","date_gmt":"2017-09-12T17:48:02","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1129"},"modified":"2019-11-11T18:50:15","modified_gmt":"2019-11-11T21:50:15","slug":"utilizar-hint-em-consulta-ajuda-ou-atrapalha","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/utilizar-hint-em-consulta-ajuda-ou-atrapalha\/","title":{"rendered":"Utilizar HINT em consulta ajuda ou atrapalha?"},"content":{"rendered":"

Muitas aplica\u00e7\u00f5es antigas utilizam HINTs em suas consultas pois o otimizador do Oracle Database nas vers\u00f5es mais antigas possu\u00eda muitas limita\u00e7\u00f5es e para compensa-las os desenvolvedores ou DBAs utilizavam HINTs para obter um plano de execu\u00e7\u00e3o com melhor desempenho. Nas vers\u00f5es mais atuais do Oracle Database o Otimizador atingiu um n\u00edvel de excel\u00eancia muito significativo e essas consultas mais antigas que ainda utilizam HINTs podem atrapalhar o Otimizador na escolha do melhor plano de execu\u00e7\u00e3o. Neste artigo vamos ver como podemos desativar os HINTs de uma consulta utilizando o recurso SQL_PATCH e como incluir um HINT sem alterar a instru\u00e7\u00e3o SQL utilizando este mesmo recurso. \u00c9 importante frisar que para as vers\u00f5es mais atuais do Oracle Database uma boa pr\u00e1tica \u00e9 n\u00e3o utilizar HINTs em ambientes de produ\u00e7\u00e3o, em seu lugar podemos utilizar SQL PLAN MANAGEMENT.<\/span><\/p>\n

 
\n <\/p>\n

Neste artigo vamos fazer duas simula\u00e7\u00f5es:<\/span><\/p>\n

1.<\/strong> Executar uma consulta com HINT que utiliza um determinado plano de execu\u00e7\u00e3o e com o recurso do SQL_PATCH fazer com que o Otimizador passe a ignorar o HINT e utilize outro plano de execu\u00e7\u00e3o.<\/span><\/p>\n

2.<\/strong> Executar uma consulta sem HINT que utiliza um determinado plano de execu\u00e7\u00e3o e incluir um HINT na consulta com recurso do SQL_PATCH de forma que o Otimizador construa um plano de execu\u00e7\u00e3o diferente levando em considera\u00e7\u00e3o o que o HINT determinou.<\/span><\/p>\n

Utiliza\u00e7\u00e3o SQL_Patch para ignorar HINT<\/span><\/h2>\n

1) Executando a consulta com HINT<\/span><\/h3>\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
<\/div><\/td>
SQL><\/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> 64bit Production
\n
\nSQL><\/span> set tab off;<\/span>
\nSQL><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nSQL><\/span>
\nSQL><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL><\/span> select \/*+ FULL(co) *\/<\/span> \/* dbtw01 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n  from countries co,<\/span>
\n       customers cu
\n where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\n   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     ..<\/span>    ..<\/span>                   ..<\/span>
\n     ..<\/span>    ..<\/span>                   ..<\/span>
\n     52787<\/span> Ramkumar             Riyadh
\n     52787<\/span> Pablo                Riyadh
\n     52787<\/span> Joshua               Riyadh
\n
\n75<\/span> rows selected.<\/span>
\n
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%dbtw01%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncxz4qsrx1s7s7            0<\/span>
\n
\nSQL><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n  old   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n  new<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'cxz4qsrx1s7s7'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  cxz4qsrx1s7s7,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/*+ FULL(co) *\/<\/span> \/* dbtw01 *\/<\/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 =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 649886770<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   408<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.33<\/span> |<\/span>    1464<\/span> |<\/span>    713<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS      |<\/span>           |<\/span>      1<\/span> |<\/span>     70<\/span> |<\/span>  1890<\/span> |<\/span>   408<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.33<\/span> |<\/span>    1464<\/span> |<\/span>    713<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> COUNTRIES |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     5<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>       3<\/span> |<\/span>      2<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>     70<\/span> |<\/span>  1540<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.28<\/span> |<\/span>    1461<\/span> |<\/span>    711<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/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
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na log de execu\u00e7\u00e3o acima podemos observar que o plano de execu\u00e7\u00e3o faz um JOIN das duas tabelas com NESTED LOOPS e a tabela COUNTRIES \u00e9 lida com uma opera\u00e7\u00e3o TABLE ACCESS FULL.<\/span><\/p>\n

2) Criando SQL_PATCH para ignorar HINTS<\/span><\/h3>\n

Para utilizar a pacote DBMS_SQLDIAG_INTERNAL precisamos estar conectados com usu\u00e1rio SYS ou um usu\u00e1rio que tenha o privil\u00e9gio de EXECUTE neste pacote, com ele podemos criar um SQL_PATCH para ignorar os HINTs que estejam codificados na instru\u00e7\u00e3o SQL.<\/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
<\/div><\/td>
SQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%dbtw01%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncxz4qsrx1s7s7            0<\/span>
\n
\nSQL><\/span> set serveroutput on size 9999<\/span>
\nSQL><\/span> declare<\/span>
\n  2<\/span>      m_clob  clob;<\/span>
\n  3<\/span>  begin
\n  4<\/span>      select sql_fulltext into m_clob
\n  5<\/span>        from v$sql<\/span>
\n  6<\/span>       where sql_id =<\/span> '&m_sql_id'<\/span>
\n  7<\/span>         and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\n  8<\/span>    
\n  9<\/span>      sys.<\/span>dbms_sqldiag_internal.<\/span>i_create_patch(<\/span>
\n 10<\/span>          sql_text    =><\/span> m_clob,<\/span>
\n 11<\/span>          hint_text   =><\/span> 'IGNORE_OPTIM_EMBEDDED_HINTS'<\/span>,<\/span>
\n 12<\/span>          name        =><\/span> 'Patch_&m_sql_id'<\/span>
\n 13<\/span>          )<\/span>;<\/span>
\n 14<\/span>  end<\/span><\/a>;<\/span>
\n 15<\/span>  \/<\/span>
\nold   6<\/span>:<\/span>      where sql_id =<\/span> '&m_sql_id'<\/span>
\nnew<\/span>   6<\/span>:<\/span>      where sql_id =<\/span> 'cxz4qsrx1s7s7'<\/span>
\nold   7<\/span>:<\/span>        and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\nnew<\/span>   7<\/span>:<\/span>        and child_number =<\/span>          0<\/span> ;<\/span>
\nold  12<\/span>:<\/span>         name        =><\/span> 'Patch_&m_sql_id'<\/span>
\nnew<\/span>  12<\/span>:<\/span>         name        =><\/span> 'Patch_cxz4qsrx1s7s7'<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> set lines 200<\/span>
\nSQL><\/span> select NAME,<\/span> CREATED,<\/span>  SQL_TEXT from DBA_SQL_PATCHES;<\/span>
\n
\nNAME                  CREATED                          SQL_TEXT
\n---------------------<\/span> --------------------------------<\/span> --------------------------------------------------------------------------------<\/span>
\nPatch_cxz4qsrx1s7s7   08<\/span>-<\/span>SEP-<\/span>17<\/span> 09.<\/span>58.<\/span>53.<\/span>000000 PM     select \/*+ FULL(co) *\/<\/span> \/* dbtw01 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_C
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Executando a consulta com HINT novamente<\/span><\/h3>\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
<\/div><\/td>
SQL><\/span> select \/*+ FULL(co) *\/<\/span> \/* dbtw01 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n  from countries co,<\/span>
\n       customers cu
\n where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\n   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     ..<\/span>    ..<\/span>                   ..<\/span>
\n     ..<\/span>    ..<\/span>                   ..<\/span>
\n     52787<\/span> Ramkumar             Riyadh
\n     52787<\/span> Pablo                Riyadh
\n     52787<\/span> Joshua               Riyadh
\n
\n75<\/span> rows selected.<\/span>
\n
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%dbtw01%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ncxz4qsrx1s7s7            0<\/span>
\n
\nSQL><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n  old   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n  new<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'cxz4qsrx1s7s7'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-----------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  cxz4qsrx1s7s7,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/*+ FULL(co) *\/<\/span> \/* dbtw01 *\/<\/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 =<\/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>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   405<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>06.90<\/span> |<\/span>    1462<\/span> |<\/span>   1454<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS      |<\/span>              |<\/span>      1<\/span> |<\/span>     70<\/span> |<\/span>  1890<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>06.90<\/span> |<\/span>    1462<\/span> |<\/span>   1454<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX UNIQUE SCAN|<\/span> COUNTRIES_PK |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     5<\/span> |<\/span>     0<\/span>   (<\/span>0<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       1<\/span> |<\/span>      1<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>     70<\/span> |<\/span>  1540<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>06.89<\/span> |<\/span>    1461<\/span> |<\/span>   1453<\/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
\nNote
\n-----<\/span>
\n   -<\/span> SQL patch "Patch_cxz4qsrx1s7s7"<\/span> used for<\/span> this statement
\n
\n
\n27<\/span> rows selected.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> BEGIN
\n  sys.<\/span>DBMS_SQLDIAG.<\/span>drop_sql_patch(<\/span>name =><\/span> 'Patch_cxz4qsrx1s7s7'<\/span>)<\/span>;<\/span>
\nEND<\/span><\/a>;<\/span>
\n\/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando a log de execu\u00e7\u00e3o acima podemos constatar que o plano de execu\u00e7\u00e3o esta diferente, o Otimizador ignorou o HINT que pedia para acessar a tabela COUNTRIES com uma opera\u00e7\u00e3o TABLE ACCESS FULL e utilizou o indice COUNTRIES_PK para acessar esta tabela, tamb\u00e9m podemos observar na se\u00e7\u00e3o NOTE que um SQL_PATCH foi utilizado (SQL patch “Patch_cxz4qsrx1s7s7” used for this statement).<\/span><\/p>\n

Utiliza\u00e7\u00e3o SQL Patch para incluir um HINT<\/span><\/h2>\n

1) Executando a consulta sem HINT<\/span><\/h3>\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
<\/div><\/td>
SQL><\/span> set tab off;<\/span>
\nSQL><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nSQL><\/span>
\nSQL><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL><\/span> select \/* dbtw02 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n  from countries co,<\/span>
\n       customers cu
\n where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\n   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     ..<\/span>    ..<\/span>                   ..<\/span>
\n     ..<\/span>    ..<\/span>                   ..<\/span>
\n     52787<\/span> Ramkumar             Riyadh
\n     52787<\/span> Pablo                Riyadh
\n     52787<\/span> Joshua               Riyadh
\n
\n75<\/span> rows selected.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%dbtw02%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n  2<\/span>    3<\/span>    4<\/span>
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n74y93xtd4b242            0<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n  old   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n  new<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'74y93xtd4b242'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  74y93xtd4b242,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw02 *\/<\/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>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>   405<\/span> (<\/span>100<\/span>)<\/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>     70<\/span> |<\/span>  1890<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/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>     5<\/span> |<\/span>     0<\/span>   (<\/span>0<\/span>)<\/span>|<\/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>     70<\/span> |<\/span>  1540<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/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
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na log de execu\u00e7\u00e3o acima podemos observar que o plano de execu\u00e7\u00e3o n\u00e3o utiliza HINT e faz um JOIN das duas tabelas com NESTED LOOPS, a tabela COUNTRIES \u00e9 lida utilizando o indice COUNTRIES_PK.<\/span><\/p>\n

2) Criando SQL_PATCH para adicionar um HINT<\/span><\/h3>\n

Utilizando a pacote DBMS_SQLDIAG_INTERNAL, procedure I_CREATE_PATCH, vamos criar um SQL_PATCH incluindo o HINT [FULL(@”SEL$1″ “CO”@”SEL$1”] no par\u00e2metro HINT_TEXT, este HINT pede para o Otimizador utilizar a opera\u00e7\u00e3o TABLE FULL SCAN para leitura da tabela COUNTRIES.<\/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
<\/div><\/td>
SQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\n  FROM v$sql<\/span>
\n WHERE sql_text LIKE '%dbtw02%'<\/span>
\n   AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n74y93xtd4b242            0<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> set serveroutput on size 9999<\/span>
\nSQL><\/span> declare<\/span>
\n  2<\/span>      m_clob  clob;<\/span>
\n  3<\/span>  begin
\n  4<\/span>      select sql_fulltext into m_clob
\n  5<\/span>        from v$sql<\/span>
\n  6<\/span>       where sql_id =<\/span> '&m_sql_id'<\/span>
\n  7<\/span>         and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\n  8<\/span>
\n  9<\/span>  sys.<\/span>dbms_sqldiag_internal.<\/span>i_create_patch(<\/span>
\n  10<\/span>          sql_text    =><\/span> m_clob,<\/span>
\n  11<\/span>          hint_text   =><\/span> 'FULL(@"SEL$1" "CO"@"SEL$1")'<\/span>,<\/span>
\n  12<\/span>          name        =><\/span> 'Patch_&m_sql_id'<\/span>
\n  13<\/span>          )<\/span>;<\/span>
\n  14<\/span> end<\/span><\/a>;<\/span>
\n  15<\/span> \/<\/span>
\nold   6<\/span>:<\/span>      where sql_id =<\/span> '&m_sql_id'<\/span>
\nnew<\/span>   6<\/span>:<\/span>      where sql_id =<\/span> '74y93xtd4b242'<\/span>
\nold   7<\/span>:<\/span>        and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\nnew<\/span>   7<\/span>:<\/span>        and child_number =<\/span>          0<\/span> ;<\/span>
\nold  12<\/span>:<\/span>         name        =><\/span> 'Patch_&m_sql_id'<\/span>
\nnew<\/span>  12<\/span>:<\/span>         name        =><\/span> 'Patch_74y93xtd4b242'<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> set lines 200<\/span>
\nSQL><\/span> select NAME,<\/span> CREATED,<\/span>  SQL_TEXT from DBA_SQL_PATCHES;<\/span>
\n
\nNAME                  CREATED                         SQL_TEXT
\n---------------------<\/span> -------------------------------<\/span> --------------------------------------------------------------------------------<\/span>
\nPatch_74y93xtd4b242   08<\/span>-<\/span>SEP-<\/span>17<\/span> 10.<\/span>22.<\/span>46.<\/span>000000 PM    select \/* dbtw02 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\n                                                                                                             from count<\/span><\/a>
\n
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Executando a consulta sem HINT novamente<\/span><\/h3>\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
<\/div><\/td>
SQL><\/span> select \/* dbtw02 *\/<\/span> co.<\/span>COUNTRY_ID,<\/span> cu.<\/span>CUST_FIRST_NAME,<\/span> cu.<\/span>CUST_CITY
\nSQL><\/span>   from countries co,<\/span>
\nSQL><\/span>        customers cu
\nSQL><\/span>  where co.<\/span>COUNTRY_ID =<\/span> cu.<\/span>COUNTRY_ID
\nSQL><\/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     ..<\/span>    ..<\/span>                   ..<\/span>
\n     ..<\/span>    ..<\/span>                   ..<\/span>
\n     52787<\/span> Ramkumar             Riyadh
\n     52787<\/span> Pablo                Riyadh
\n     52787<\/span> Joshua               Riyadh
\n
\n75<\/span> rows selected.<\/span>
\n
\nSQL><\/span> SQL><\/span>
\nSQL><\/span> column sql_id new_value m_sql_id
\nSQL><\/span> column child_number new_value m_child_no
\nSQL><\/span>
\nSQL><\/span> SELECT sql_id,<\/span> child_number
\nSQL><\/span>   FROM v$sql<\/span>
\nSQL><\/span>  WHERE sql_text LIKE '%dbtw02%'<\/span>
\nSQL><\/span>    AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n74y93xtd4b242            0<\/span>
\n
\nSQL><\/span>
\nSQL><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n  old   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n  new<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'74y93xtd4b242'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  74y93xtd4b242,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw02 *\/<\/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> 649886770<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   408<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1464<\/span> |<\/span>      2<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  NESTED LOOPS      |<\/span>           |<\/span>      1<\/span> |<\/span>     70<\/span> |<\/span>  1890<\/span> |<\/span>   408<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1464<\/span> |<\/span>      2<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> COUNTRIES |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     5<\/span> |<\/span>     3<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>       3<\/span> |<\/span>      2<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>     70<\/span> |<\/span>  1540<\/span> |<\/span>   405<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     75<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1461<\/span> |<\/span>      0<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/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
\nNote
\n-----<\/span>
\n   -<\/span> SQL patch "Patch_74y93xtd4b242"<\/span> used for<\/span> this statement
\n
\n
\n27<\/span> rows selected.<\/span>
\n
\nSQL><\/span> BEGIN
\nSQL><\/span>    sys.<\/span>DBMS_SQLDIAG.<\/span>drop_sql_patch(<\/span>name =><\/span> 'Patch_74y93xtd4b242'<\/span>)<\/span>;<\/span>
\nSQL><\/span> END<\/span><\/a>;<\/span>
\nSQL><\/span> \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando a log de execu\u00e7\u00e3o acima podemos verificar que o plano de execu\u00e7\u00e3o esta diferente, a tabela COUNTRIES foi acessada com uma opera\u00e7\u00e3o TABLE FULL SCAN, tamb\u00e9m podemos observar na se\u00e7\u00e3o NOTE que um SQL_PATCH foi utilizado (SQL patch “Patch_74y93xtd4b242” used for this statement)<\/span><\/p>\n

 <\/p>\n

 <\/p>\n

<\/h2>\n

 <\/p>\n

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

 <\/p>\n

https:\/\/orastory.wordpress.com\/2012\/03\/06\/sql-patch-ii\/<\/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":"

Muitas aplica\u00e7\u00f5es antigas utilizam HINTs em suas consultas pois o otimizador do Oracle Database nas vers\u00f5es mais antigas possu\u00eda muitas limita\u00e7\u00f5es e para compensa-las os desenvolvedores ou DBAs utilizavam HINTs para obter um plano de execu\u00e7\u00e3o com melhor desempenho. Nas vers\u00f5es mais atuais do Oracle Database o Otimizador atingiu um n\u00edvel de excel\u00eancia muito significativo e essas consultas mais antigas<\/p>\n","protected":false},"author":2,"featured_media":1128,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[152],"tags":[177,7,176,153],"class_list":["post-1129","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-patch","tag-dbms_sqldiag_internal","tag-dbms_xplan","tag-hint","tag-sql-patch"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=1129"}],"version-history":[{"count":14,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1129\/revisions"}],"predecessor-version":[{"id":2082,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1129\/revisions\/2082"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1128"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1129"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}