Quando estamos analisando um plano de execução gerado pelo pacote "DBMS_XPLAN" normalmente não temos as informações estatísticas reais daquela execução pois a disponibilidade dessas informações esta condicionada a definição do parâmetro STATISTICS_LEVEL que na maioria das instalações de produção é definido como TYPICAL para evitar "OVERHEAD" na base. As informações…
Utilizar HINT em consulta ajuda ou atrapalha?
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.
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:
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/
