Utilizar HINT em consulta ajuda ou atrapalha?

hint

Muitas aplicações antigas utilizam HINTs em suas consultas pois o otimizador do Oracle Database nas versões mais antigas possuía muitas limitações e para compensa-las os desenvolvedores ou DBAs utilizavam HINTs para obter um plano de execução com melhor desempenho. Nas versões mais atuais do Oracle Database o Otimizador atingiu um nível de excelência muito significativo e essas consultas mais antigas que ainda utilizam HINTs podem atrapalhar o Otimizador na escolha do melhor plano de execução. 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ção SQL utilizando este mesmo recurso. É importante frisar que para as versões mais atuais do Oracle Database uma boa prática é não utilizar HINTs em ambientes de produção, em seu lugar podemos utilizar SQL PLAN MANAGEMENT.

 
 

Neste artigo vamos fazer duas simulações:

1. Executar uma consulta com HINT que utiliza um determinado plano de execução e com o recurso do SQL_PATCH fazer com que o Otimizador passe a ignorar o HINT e utilize outro plano de execução.

2. Executar uma consulta sem HINT que utiliza um determinado plano de execução e incluir um HINT na consulta com recurso do SQL_PATCH de forma que o Otimizador construa um plano de execução diferente levando em consideração o que o HINT determinou.

Utilização SQL_Patch para ignorar HINT

1) Executando a consulta com HINT

 

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
SQL> SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> set tab off;
SQL> set lines 300 pages 100;
SQL>
SQL> ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL> ALTER SESSION SET current_schema=SH;

Session altered.

SQL> select /*+ FULL(co) */ /* dbtw01 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
  from countries co,
       customers cu
 where co.COUNTRY_ID = cu.COUNTRY_ID
   and co.COUNTRY_ID = 52787;

COUNTRY_ID CUST_FIRST_NAME      CUST_CITY
---------- -------------------- ------------------------------
     52787 Arnold               Riyadh
     52787 Augustus             Riyadh
     52787 Bertilde             Riyadh
     52787 Bertram              Riyadh
     52787 Beulah               Riyadh
     ..    ..                   ..
     ..    ..                   ..
     52787 Ramkumar             Riyadh
     52787 Pablo                Riyadh
     52787 Joshua               Riyadh

75 rows selected.

SQL> column sql_id new_value m_sql_id
SQL> column child_number new_value m_child_no
SQL> SELECT sql_id, child_number
  FROM v$sql
 WHERE sql_text LIKE '%dbtw01%'
   AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
cxz4qsrx1s7s7            0

SQL> SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
  old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
  new   2:   FROM TABLE (dbms_xplan.display_cursor ('cxz4qsrx1s7s7',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cxz4qsrx1s7s7, child number 0
-------------------------------------
select /*+ FULL(co) */ /* dbtw01 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME,
cu.CUST_CITY   from countries co,        customers cu  where
co.COUNTRY_ID = cu.COUNTRY_ID    and co.COUNTRY_ID = :"SYS_B_0"

Plan hash value: 649886770

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |       |   408 (100)|          |     75 |00:00:02.33 |    1464 |    713 |
|   1 |  NESTED LOOPS      |           |      1 |     70 |  1890 |   408   (1)| 00:00:01 |     75 |00:00:02.33 |    1464 |    713 |
|*  2 |   TABLE ACCESS FULL| COUNTRIES |      1 |      1 |     5 |     3   (0)| 00:00:01 |      1 |00:00:00.05 |       3 |      2 |
|*  3 |   TABLE ACCESS FULL| CUSTOMERS |      1 |     70 |  1540 |   405   (1)| 00:00:01 |     75 |00:00:02.28 |    1461 |    711 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("CO"."COUNTRY_ID"=:SYS_B_0)
   3 - filter("CU"."COUNTRY_ID"=:SYS_B_0)


23 rows selected.

SQL>

 

Na log de execução acima podemos observar que o plano de execução faz um JOIN das duas tabelas com NESTED LOOPS e a tabela COUNTRIES é lida com uma operação TABLE ACCESS FULL.

2) Criando SQL_PATCH para ignorar HINTS

Para utilizar a pacote DBMS_SQLDIAG_INTERNAL precisamos estar conectados com usuário SYS ou um usuário que tenha o privilégio de EXECUTE neste pacote, com ele podemos criar um SQL_PATCH para ignorar os HINTs que estejam codificados na instrução SQL.

 

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
SQL> column sql_id new_value m_sql_id
SQL> column child_number new_value m_child_no
SQL> SELECT sql_id, child_number
  FROM v$sql
 WHERE sql_text LIKE '%dbtw01%'
   AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
cxz4qsrx1s7s7            0

SQL> set serveroutput on size 9999
SQL> declare
  2      m_clob  clob;
  3  begin
  4      select sql_fulltext into m_clob
  5        from v$sql
  6       where sql_id = '&m_sql_id'
  7         and child_number = &m_child_no ;
  8    
  9      sys.dbms_sqldiag_internal.i_create_patch(
 10          sql_text    => m_clob,
 11          hint_text   => 'IGNORE_OPTIM_EMBEDDED_HINTS',
 12          name        => 'Patch_&m_sql_id'
 13          );
 14  end;
 15  /
old   6:      where sql_id = '&m_sql_id'
new   6:      where sql_id = 'cxz4qsrx1s7s7'
old   7:        and child_number = &m_child_no ;
new   7:        and child_number =          0 ;
old  12:         name        => 'Patch_&m_sql_id'
new  12:         name        => 'Patch_cxz4qsrx1s7s7'

PL/SQL procedure successfully completed.

SQL>
SQL> set lines 200
SQL> select NAME, CREATED,  SQL_TEXT from DBA_SQL_PATCHES;

NAME                  CREATED                          SQL_TEXT
--------------------- -------------------------------- --------------------------------------------------------------------------------
Patch_cxz4qsrx1s7s7   08-SEP-17 09.58.53.000000 PM     select /*+ FULL(co) */ /* dbtw01 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_C

SQL>

 

3) Executando a consulta com HINT novamente

 

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
SQL> select /*+ FULL(co) */ /* dbtw01 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
  from countries co,
       customers cu
 where co.COUNTRY_ID = cu.COUNTRY_ID
   and co.COUNTRY_ID = 52787;

COUNTRY_ID CUST_FIRST_NAME      CUST_CITY
---------- -------------------- ------------------------------
     52787 Arnold               Riyadh
     52787 Augustus             Riyadh
     52787 Bertilde             Riyadh
     52787 Bertram              Riyadh
     52787 Beulah               Riyadh
     ..    ..                   ..
     ..    ..                   ..
     52787 Ramkumar             Riyadh
     52787 Pablo                Riyadh
     52787 Joshua               Riyadh

75 rows selected.

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
  FROM v$sql
 WHERE sql_text LIKE '%dbtw01%'
   AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
cxz4qsrx1s7s7            0

SQL> SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
  old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
  new   2:   FROM TABLE (dbms_xplan.display_cursor ('cxz4qsrx1s7s7',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  cxz4qsrx1s7s7, child number 0
-------------------------------------
select /*+ FULL(co) */ /* dbtw01 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME,
cu.CUST_CITY   from countries co,        customers cu  where
co.COUNTRY_ID = cu.COUNTRY_ID    and co.COUNTRY_ID = :"SYS_B_0"

Plan hash value: 763797669

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |       |   405 (100)|          |     75 |00:00:06.90 |    1462 |   1454 |
|   1 |  NESTED LOOPS      |              |      1 |     70 |  1890 |   405   (1)| 00:00:01 |     75 |00:00:06.90 |    1462 |   1454 |
|*  2 |   INDEX UNIQUE SCAN| COUNTRIES_PK |      1 |      1 |     5 |     0   (0)|          |      1 |00:00:00.01 |       1 |      1 |
|*  3 |   TABLE ACCESS FULL| CUSTOMERS    |      1 |     70 |  1540 |   405   (1)| 00:00:01 |     75 |00:00:06.89 |    1461 |   1453 |
--------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("CO"."COUNTRY_ID"=:SYS_B_0)
   3 - filter("CU"."COUNTRY_ID"=:SYS_B_0)

Note
-----
   - SQL patch "Patch_cxz4qsrx1s7s7" used for this statement


27 rows selected.

SQL>
SQL> BEGIN
  sys.DBMS_SQLDIAG.drop_sql_patch(name => 'Patch_cxz4qsrx1s7s7');
END;
/

PL/SQL procedure successfully completed.

SQL>

 

Observando a log de execução acima podemos constatar que o plano de execução esta diferente, o Otimizador ignorou o HINT que pedia para acessar a tabela COUNTRIES com uma operação TABLE ACCESS FULL e utilizou o indice COUNTRIES_PK para acessar esta tabela, também podemos observar na seção NOTE que um SQL_PATCH foi utilizado (SQL patch “Patch_cxz4qsrx1s7s7” used for this statement).

Utilização SQL Patch para incluir um HINT

1) Executando a consulta sem HINT

 

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
SQL> set tab off;
SQL> set lines 300 pages 100;
SQL>
SQL> ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL> ALTER SESSION SET current_schema=SH;

Session altered.

SQL> select /* dbtw02 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
  from countries co,
       customers cu
 where co.COUNTRY_ID = cu.COUNTRY_ID
   and co.COUNTRY_ID = 52787;

COUNTRY_ID CUST_FIRST_NAME      CUST_CITY
---------- -------------------- ------------------------------
     52787 Arnold               Riyadh
     52787 Augustus             Riyadh
     52787 Bertilde             Riyadh
     52787 Bertram              Riyadh
     52787 Beulah               Riyadh
     ..    ..                   ..
     ..    ..                   ..
     52787 Ramkumar             Riyadh
     52787 Pablo                Riyadh
     52787 Joshua               Riyadh

75 rows selected.

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
  FROM v$sql
 WHERE sql_text LIKE '%dbtw02%'
   AND sql_text NOT LIKE '%v$sql%';
  2    3    4
SQL_ID        CHILD_NUMBER
------------- ------------
74y93xtd4b242            0

SQL>
SQL> SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
  old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
  new   2:   FROM TABLE (dbms_xplan.display_cursor ('74y93xtd4b242',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  74y93xtd4b242, child number 0
-------------------------------------
select /* dbtw02 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
from countries co,        customers cu  where co.COUNTRY_ID =
cu.COUNTRY_ID    and co.COUNTRY_ID = :"SYS_B_0"

Plan hash value: 763797669

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |       |   405 (100)|          |     75 |00:00:00.01 |    1462 |
|   1 |  NESTED LOOPS      |              |      1 |     70 |  1890 |   405   (1)| 00:00:01 |     75 |00:00:00.01 |    1462 |
|*  2 |   INDEX UNIQUE SCAN| COUNTRIES_PK |      1 |      1 |     5 |     0   (0)|          |      1 |00:00:00.01 |       1 |
|*  3 |   TABLE ACCESS FULL| CUSTOMERS    |      1 |     70 |  1540 |   405   (1)| 00:00:01 |     75 |00:00:00.01 |    1461 |
-----------------------------------------------------------------------------------------------------------------------------

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

   2 - access("CO"."COUNTRY_ID"=:SYS_B_0)
   3 - filter("CU"."COUNTRY_ID"=:SYS_B_0)


23 rows selected.

SQL>

 

Na log de execução acima podemos observar que o plano de execução não utiliza HINT e faz um JOIN das duas tabelas com NESTED LOOPS, a tabela COUNTRIES é lida utilizando o indice COUNTRIES_PK.

2) Criando SQL_PATCH para adicionar um HINT

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âmetro HINT_TEXT, este HINT pede para o Otimizador utilizar a operação TABLE FULL SCAN para leitura da tabela COUNTRIES.

 

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
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
  FROM v$sql
 WHERE sql_text LIKE '%dbtw02%'
   AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
74y93xtd4b242            0

SQL>
SQL> set serveroutput on size 9999
SQL> declare
  2      m_clob  clob;
  3  begin
  4      select sql_fulltext into m_clob
  5        from v$sql
  6       where sql_id = '&m_sql_id'
  7         and child_number = &m_child_no ;
  8
  9  sys.dbms_sqldiag_internal.i_create_patch(
  10          sql_text    => m_clob,
  11          hint_text   => 'FULL(@"SEL$1" "CO"@"SEL$1")',
  12          name        => 'Patch_&m_sql_id'
  13          );
  14 end;
  15 /
old   6:      where sql_id = '&m_sql_id'
new   6:      where sql_id = '74y93xtd4b242'
old   7:        and child_number = &m_child_no ;
new   7:        and child_number =          0 ;
old  12:         name        => 'Patch_&m_sql_id'
new  12:         name        => 'Patch_74y93xtd4b242'

PL/SQL procedure successfully completed.

SQL>
SQL> set lines 200
SQL> select NAME, CREATED,  SQL_TEXT from DBA_SQL_PATCHES;

NAME                  CREATED                         SQL_TEXT
--------------------- ------------------------------- --------------------------------------------------------------------------------
Patch_74y93xtd4b242   08-SEP-17 10.22.46.000000 PM    select /* dbtw02 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
                                                                                                             from count


SQL>

 

3) Executando a consulta sem HINT novamente

 

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
SQL> select /* dbtw02 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
SQL>   from countries co,
SQL>        customers cu
SQL>  where co.COUNTRY_ID = cu.COUNTRY_ID
SQL>    and co.COUNTRY_ID = 52787;

COUNTRY_ID CUST_FIRST_NAME      CUST_CITY
---------- -------------------- ------------------------------
     52787 Arnold               Riyadh
     52787 Augustus             Riyadh
     52787 Bertilde             Riyadh
     52787 Bertram              Riyadh
     52787 Beulah               Riyadh
     ..    ..                   ..
     ..    ..                   ..
     52787 Ramkumar             Riyadh
     52787 Pablo                Riyadh
     52787 Joshua               Riyadh

75 rows selected.

SQL> 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
SQL>   FROM v$sql
SQL>  WHERE sql_text LIKE '%dbtw02%'
SQL>    AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
74y93xtd4b242            0

SQL>
SQL> SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
  old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
  new   2:   FROM TABLE (dbms_xplan.display_cursor ('74y93xtd4b242',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  74y93xtd4b242, child number 0
-------------------------------------
select /* dbtw02 */ co.COUNTRY_ID, cu.CUST_FIRST_NAME, cu.CUST_CITY
from countries co,        customers cu  where co.COUNTRY_ID =
cu.COUNTRY_ID    and co.COUNTRY_ID = :"SYS_B_0"

Plan hash value: 649886770

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |       |   408 (100)|          |     75 |00:00:00.03 |    1464 |      2 |
|   1 |  NESTED LOOPS      |           |      1 |     70 |  1890 |   408   (1)| 00:00:01 |     75 |00:00:00.03 |    1464 |      2 |
|*  2 |   TABLE ACCESS FULL| COUNTRIES |      1 |      1 |     5 |     3   (0)| 00:00:01 |      1 |00:00:00.02 |       3 |      2 |
|*  3 |   TABLE ACCESS FULL| CUSTOMERS |      1 |     70 |  1540 |   405   (1)| 00:00:01 |     75 |00:00:00.01 |    1461 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("CO"."COUNTRY_ID"=:SYS_B_0)
   3 - filter("CU"."COUNTRY_ID"=:SYS_B_0)

Note
-----
   - SQL patch "Patch_74y93xtd4b242" used for this statement


27 rows selected.

SQL> BEGIN
SQL>    sys.DBMS_SQLDIAG.drop_sql_patch(name => 'Patch_74y93xtd4b242');
SQL> END;
SQL> /

PL/SQL procedure successfully completed.

SQL>

 

Observando a log de execução acima podemos verificar que o plano de execução esta diferente, a tabela COUNTRIES foi acessada com uma operação TABLE FULL SCAN, também podemos observar na seção NOTE que um SQL_PATCH foi utilizado (SQL patch “Patch_74y93xtd4b242” used for this statement)

 

 

 

Referências:

 

https://orastory.wordpress.com/2012/03/06/sql-patch-ii/

 

 

Mídia social

 

 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *