Quando estamos realizando um trabalho de tuning SQL muitas vezes nos deparamos com situações em que não podemos alterar o código da aplicação pois esta pertence a terceiros. Essa limitação pode ser superada facilmente utilizando o recurso SQL PROFILE do banco Oracle, disponível a partir da versão 10g. Neste artigo vamos…
SQL Patch: Você já usou esse recurso?
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 estatísticas reais do plano de execução são muito importantes na analise do plano pois são elas que mostram qual ou quais operações no plano são responsáveis pelo desempenho ruim da instrução SQL, para contornar esta dificuldade quando realizamos um trabalho de tuning normalmente utilizamos os seguintes recursos:
1. Criação de um “Test Case”: Reproduzir a instrução SQL com todas as suas BINDs definindo o parâmetro STATISTICS_LEVEL=ALL a nível de sessão, com isso conseguimos gerar um plano de execução com todas as estatísticas dessa instrução SQL. Essa tarefa em algumas situações é complexa e as vezes precisamos da ajuda do desenvolvedor da aplicação para realiza-la.
2. Utilização do SQL MONITORING no OEM Cloud Control: Esta opção é ótima mas o ambiente precisa ter as “Options Diagnostic and Tuning Pack” e se o tempo de execução da instrução SQL for inferior a 3 segundos, ela não será capturada por padrão pelo SQL MONITORING.
3. Um Ambiente de Homologação onde podemos alterar o parâmetro STATISTICS_LEVEL=ALL a nível de instância: Também é uma ótima opção mas muitas instalações não tem um ambiente de homologação onde possamos realizar as atividades de tuning.
Neste artigo vamos mostrar como podemos utilizar o SQL Patch para gerar as estatísticas reais da execução de uma instrução SQL de forma simples e rápida e sem causar impactos na produção.
Simulando a utilização SQL Patch
Quando ativamos a coleta de estatísticas no nível máximo de uma instrução SQL especifica, há um OVERHEAD adicional no processamento dessa instrução e o tempo de execução aumenta, para evitar impactos em produção devemos escolher um período onde o numero de execuções dessa instrução seja menor para realizar o procedimento que permitirá a geração de um plano de execução com todas as informações estatísticas.
Para realizar nossa simulação vamos executar os seguintes passos:
1) Executar a consulta e gerar o plano de execução utilizando o pacote DBMS_XPLAN para constatar que não estão disponíveis as estatísticas de execução dessa consulta, numa instância onde o parâmetro STATISTICS_LEVEL=TYPICAL.
2) Criar um SQL Patch para o SQL_ID da nossa consulta incluindo o HINT GATHER_PLAN_STATISTICS que tem o mesmo efeito do parâmetro STATISTICS_LEVEL=ALL.
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:
3) Executar novamente a consulta e gerar o plano de execução utilizando o pacote DBMS_XPLAN que dessa vez vai gerar um plano com as estatísticas de execução da consulta.
4) Eliminar o SQL Patch criado.
1) Plano de execução padrão
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 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 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL > SELECT /* DBTW01 */ country_name, 2 cust_income_level, 3 count(country_name) country_cust_count 4 FROM (SELECT b.cust_income_level, 5 a.country_name 6 FROM sh.customers b 7 JOIN sh.countries a ON a.country_id = b.country_id) c 8 HAVING count(country_name) > (SELECT count(*) * .01 FROM (SELECT b.cust_income_level, 9 a.country_name 10 FROM sh.customers b 11 JOIN sh.countries a ON a.country_id = b.country_id) c2) 12 OR count(cust_income_level) >= (SELECT median(income_level_count) 13 FROM (SELECT cust_income_level, count(*) *.25 income_level_count 14 FROM (SELECT b.cust_income_level, 15 a.country_name 16 FROM sh.customers b 17 JOIN sh.countries a ON a.country_id = b.country_id) 18 GROUP BY cust_income_level) 19 ) 20 GROUP BY country_name, cust_income_level 21 ORDER BY 1,2; COUNTRY_NAME CUST_INCOME_LEVEL COUNTRY_CUST_COUNT ---------------------------------------- ------------------------------ ------------------ France E: 90,000 - 109,999 585 France F: 110,000 - 129,999 651 Germany C: 50,000 - 69,999 638 Germany D: 70,000 - 89,999 844 Germany E: 90,000 - 109,999 1261 Germany F: 110,000 - 129,999 1469 Germany G: 130,000 - 149,999 843 Germany H: 150,000 - 169,999 850 Germany I: 170,000 - 189,999 658 Italy A: Below 30,000 600 Italy C: 50,000 - 69,999 659 Italy D: 70,000 - 89,999 700 Italy E: 90,000 - 109,999 1074 Italy F: 110,000 - 129,999 1851 Italy G: 130,000 - 149,999 657 Italy H: 150,000 - 169,999 715 Italy I: 170,000 - 189,999 714 United Kingdom C: 50,000 - 69,999 611 United Kingdom D: 70,000 - 89,999 738 United Kingdom E: 90,000 - 109,999 1165 United Kingdom F: 110,000 - 129,999 1573 United Kingdom G: 130,000 - 149,999 628 United Kingdom H: 150,000 - 169,999 734 United Kingdom I: 170,000 - 189,999 727 United States of America B: 30,000 - 49,999 1054 United States of America C: 50,000 - 69,999 1272 United States of America D: 70,000 - 89,999 1641 United States of America E: 90,000 - 109,999 2525 United States of America F: 110,000 - 129,999 2837 United States of America G: 130,000 - 149,999 2004 United States of America H: 150,000 - 169,999 1857 United States of America I: 170,000 - 189,999 1395 United States of America J: 190,000 - 249,999 1390 United States of America K: 250,000 - 299,999 1062 United States of America L: 300,000 and above 982 35 linhas selecionadas. Decorrido: 00:00:00.23 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 2 FROM v$sql 3 WHERE sql_text LIKE '%DBTW01%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 97qppk7bs52xn 0 Decorrido: 00:00:00.04 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('97qppk7bs52xn', 0,'allstats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 97qppk7bs52xn, child number 0 ------------------------------------- SELECT /* DBTW01 */ country_name, cust_income_level, count(country_name) country_cust_count FROM (SELECT b.cust_income_level, a.country_name FROM sh.customers b JOIN sh.countries a ON a.country_id = b.country_id) c HAVING count(country_name) > (SELECT count(*) * :"SYS_B_0" FROM (SELECT b.cust_income_level, a.country_name FROM sh.customers b JOIN sh.countries a ON a.country_id = b.country_id) c2) OR count(cust_income_level) >= (SELECT median(income_level_count) FROM (SELECT cust_income_level, count(*) *:"SYS_B_1" income_level_count FROM (SELECT b.cust_income_level, a.country_name FROM sh.customers b JOIN sh.countries a ON a.country_id = b.country_id) GROUP BY cust_income_level) ) GROUP BY country_name, cust_income_level ORDE Plan hash value: 1730152010 -------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | SORT ORDER BY | | 20 | 6144 | 6144 | 6144 (0)| |* 2 | FILTER | | | | | | | 3 | HASH GROUP BY | | 20 | 6890K| 2507K| 2516K (0)| |* 4 | HASH JOIN | | 55500 | 1519K| 1519K| 1568K (0)| | 5 | TABLE ACCESS FULL | COUNTRIES | 23 | | | | | 6 | TABLE ACCESS FULL | CUSTOMERS | 55500 | | | | | 7 | SORT AGGREGATE | | 1 | | | | |* 8 | HASH JOIN | | 55500 | 2168K| 2168K| 1571K (0)| | 9 | INDEX FULL SCAN | COUNTRIES_PK | 23 | | | | | 10 | TABLE ACCESS FULL | CUSTOMERS | 55500 | | | | | 11 | SORT GROUP BY | | 1 | 2048 | 2048 | 2048 (0)| | 12 | VIEW | | 12 | | | | | 13 | SORT GROUP BY | | 12 | 2048 | 2048 | 2048 (0)| | 14 | NESTED LOOPS | | 162 | | | | | 15 | VIEW | VW_GBF_8 | 162 | | | | | 16 | SORT GROUP BY | | 162 | 22528 | 22528 |20480 (0)| | 17 | TABLE ACCESS FULL| CUSTOMERS | 55500 | | | | |* 18 | INDEX UNIQUE SCAN | COUNTRIES_PK | 1 | | | | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((COUNT(*)> OR COUNT("B"."CUST_INCOME_LEVEL")>=)) 4 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 8 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 18 - access("A"."COUNTRY_ID"="ITEM_1") Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 59 linhas selecionadas. Decorrido: 00:00:00.78 SQL > |
Na console acima executamos uma consulta no esquema SH e em seguida geramos o plano de execução com o pacote DBMS_XPLAN, como podemos observar esta plano não informa os tempos de execução de cada operação e o numero de linhas selecionado em cada operação é apenas uma estimativa, não temos a quantidade real de linhas selecionadas em cada operação. Dessa forma fica muito difícil identificar qual parte do plano de execução esta levando mais tempo para ser executada.
2) Adicionando o HINT GATHER_PLAN_STATISTICS
Para gerar as estatísticas mais completas da execução de uma instrução SQL e conseguir exibi-las no plano de execução utilizando o pacote DBMS_XPLAN, podemos adicionar o HINT GATHER_PLAN_STATISTICS sem alterar o código da aplicação utilizando o recurso SQL Patch, a seguir vamos ver como podemos fazer isso:
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 | SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%DBTW01%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 97qppk7bs52xn 0 Decorrido: 00:00:00.04 SQL > SQL > SQL > set serveroutput on size 9999 SQL > SQL > declare 2 m_clob clob; 3 begin 4 select 5 sql_fulltext 6 into 7 m_clob 8 from 9 v$sql 10 where 11 sql_id = '&m_sql_id' 12 and child_number = &m_child_no ; 13 14 sys.dbms_sqldiag_internal.i_create_patch( 15 sql_text => m_clob, 16 hint_text => 'GATHER_PLAN_STATISTICS', 17 name => 'Patch_&m_sql_id' 18 ); 19 end; 20 / antigo 11: sql_id = '&m_sql_id' novo 11: sql_id = '97qppk7bs52xn' antigo 12: and child_number = &m_child_no ; novo 12: and child_number = 0 ; antigo 17: name => 'Patch_&m_sql_id' novo 17: name => 'Patch_97qppk7bs52xn' Procedimento PL/SQL concluído com sucesso. Decorrido: 00:00:02.15 SQL > SQL > set lines 200 SQL > select NAME, CREATED, SQL_TEXT from DBA_SQL_PATCHES; NAME CREATED SQL_TEXT ------------------------------ ------------------------------ -------------------------------------------------------------------------------- Patch_97qppk7bs52xn 12/08/16 22:49:45,000000 SELECT /* DBTW01 */ country_name, cust_income_level, count(country Decorrido: 00:00:00.04 SQL > |
3) Plano de execução com todas as estatísticas
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 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | SQL > SELECT /* DBTW01 */ country_name, 2 cust_income_level, 3 count(country_name) country_cust_count 4 FROM (SELECT b.cust_income_level, 5 a.country_name 6 FROM sh.customers b 7 JOIN sh.countries a ON a.country_id = b.country_id) c 8 HAVING count(country_name) > (SELECT count(*) * .01 FROM (SELECT b.cust_income_level, 9 a.country_name 10 FROM sh.customers b 11 JOIN sh.countries a ON a.country_id = b.country_id) c2) 12 OR count(cust_income_level) >= (SELECT median(income_level_count) 13 FROM (SELECT cust_income_level, count(*) *.25 income_level_count 14 FROM (SELECT b.cust_income_level, 15 a.country_name 16 FROM sh.customers b 17 JOIN sh.countries a ON a.country_id = b.country_id) 18 GROUP BY cust_income_level) 19 ) 20 GROUP BY country_name, cust_income_level 21 ORDER BY 1,2; COUNTRY_NAME CUST_INCOME_LEVEL COUNTRY_CUST_COUNT ---------------------------------------- ------------------------------ ------------------ France E: 90,000 - 109,999 585 France F: 110,000 - 129,999 651 Germany C: 50,000 - 69,999 638 Germany D: 70,000 - 89,999 844 Germany E: 90,000 - 109,999 1261 Germany F: 110,000 - 129,999 1469 Germany G: 130,000 - 149,999 843 Germany H: 150,000 - 169,999 850 Germany I: 170,000 - 189,999 658 Italy A: Below 30,000 600 Italy C: 50,000 - 69,999 659 Italy D: 70,000 - 89,999 700 Italy E: 90,000 - 109,999 1074 Italy F: 110,000 - 129,999 1851 Italy G: 130,000 - 149,999 657 Italy H: 150,000 - 169,999 715 Italy I: 170,000 - 189,999 714 United Kingdom C: 50,000 - 69,999 611 United Kingdom D: 70,000 - 89,999 738 United Kingdom E: 90,000 - 109,999 1165 United Kingdom F: 110,000 - 129,999 1573 United Kingdom G: 130,000 - 149,999 628 United Kingdom H: 150,000 - 169,999 734 United Kingdom I: 170,000 - 189,999 727 United States of America B: 30,000 - 49,999 1054 United States of America C: 50,000 - 69,999 1272 United States of America D: 70,000 - 89,999 1641 United States of America E: 90,000 - 109,999 2525 United States of America F: 110,000 - 129,999 2837 United States of America G: 130,000 - 149,999 2004 United States of America H: 150,000 - 169,999 1857 United States of America I: 170,000 - 189,999 1395 United States of America J: 190,000 - 249,999 1390 United States of America K: 250,000 - 299,999 1062 United States of America L: 300,000 and above 982 35 linhas selecionadas. Decorrido: 00:00:00.23 SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%DBTW01%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 97qppk7bs52xn 0 97qppk7bs52xn 1 Decorrido: 00:00:00.04 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'allstats last')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('97qppk7bs52xn', 1,'allstats last')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 97qppk7bs52xn, child number 1 ------------------------------------- SELECT /* DBTW01 */ country_name, cust_income_level, count(country_name) country_cust_count FROM (SELECT b.cust_income_level, a.country_name FROM sh.customers b JOIN sh.countries a ON a.country_id = b.country_id) c HAVING count(country_name) > (SELECT count(*) * :"SYS_B_0" FROM (SELECT b.cust_income_level, a.country_name FROM sh.customers b JOIN sh.countries a ON a.country_id = b.country_id) c2) OR count(cust_income_level) >= (SELECT median(income_level_count) FROM (SELECT cust_income_level, count(*) *:"SYS_B_1" income_level_count FROM (SELECT b.cust_income_level, a.country_name FROM sh.customers b JOIN sh.countries a ON a.country_id = b.country_id) GROUP BY cust_income_level) ) GROUP BY country_name, cust_income_level ORDE Plan hash value: 1730152010 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 35 |00:00:00.09 | 4375 | | | | | 1 | SORT ORDER BY | | 1 | 20 | 35 |00:00:00.09 | 4375 | 6144 | 6144 | 6144 (0)| |* 2 | FILTER | | 1 | | 35 |00:00:00.09 | 4375 | | | | | 3 | HASH GROUP BY | | 1 | 20 | 236 |00:00:00.04 | 1458 | 6890K| 2507K| 2522K (0)| |* 4 | HASH JOIN | | 1 | 55500 | 55500 |00:00:00.03 | 1458 | 1519K| 1519K| 1526K (0)| | 5 | TABLE ACCESS FULL | COUNTRIES | 1 | 23 | 23 |00:00:00.01 | 2 | | | | | 6 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 55500 |00:00:00.01 | 1456 | | | | | 7 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1457 | | | | |* 8 | HASH JOIN | | 1 | 55500 | 55500 |00:00:00.03 | 1457 | 2168K| 2168K| 1526K (0)| | 9 | INDEX FULL SCAN | COUNTRIES_PK | 1 | 23 | 23 |00:00:00.01 | 1 | | | | | 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 55500 |00:00:00.01 | 1456 | | | | | 11 | SORT GROUP BY | | 1 | 1 | 1 |00:00:00.03 | 1460 | 2048 | 2048 | 2048 (0)| | 12 | VIEW | | 1 | 12 | 13 |00:00:00.03 | 1460 | | | | | 13 | SORT GROUP BY | | 1 | 12 | 13 |00:00:00.03 | 1460 | 2048 | 2048 | 2048 (0)| | 14 | NESTED LOOPS | | 1 | 162 | 236 |00:00:00.03 | 1460 | | | | | 15 | VIEW | VW_GBF_8 | 1 | 162 | 236 |00:00:00.03 | 1456 | | | | | 16 | SORT GROUP BY | | 1 | 162 | 236 |00:00:00.03 | 1456 | 22528 | 22528 |20480 (0)| | 17 | TABLE ACCESS FULL| CUSTOMERS | 1 | 55500 | 55500 |00:00:00.01 | 1456 | | | | |* 18 | INDEX UNIQUE SCAN | COUNTRIES_PK | 236 | 1 | 236 |00:00:00.01 | 4 | | | | ------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((COUNT(*)> OR COUNT("B"."CUST_INCOME_LEVEL")>=)) 4 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 8 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 18 - access("A"."COUNTRY_ID"="ITEM_1") Note ----- - SQL patch "Patch_97qppk7bs52xn" used for this statement 57 linhas selecionadas. Decorrido: 00:00:00.32 SQL > |
Comparando o plano de execução acima com o plano gerado antes da inclusão do HINT, podemos observar que algumas colunas de informação foram adicionadas ao plano: STARTS, A-ROWS, A-TIME e BUFFERS. Todas essas informações são da execução real do plano e nos permitem entender mais facilmente onde o plano de execução esta levando mais tempo para ser executado.
4) Eliminar o SQL Patch
1 2 3 4 5 6 7 8 9 | SQL > BEGIN 2 sys.DBMS_SQLDIAG.drop_sql_patch(name => 'Patch_97qppk7bs52xn'); 3 END; 4 / Procedimento PL/SQL concluído com sucesso. Decorrido: 00:00:01.19 SQL > |
Conclusão
O recurso SQL Patch é muito poderoso pois permite a adição de HINTs numa instrução SQL sem a alteração de uma linha de código na aplicação, neste artigo apresentamos um exemplo de utilização desse recurso que nos permite agilizar o trabalho de Tuning fazendo com que o plano de execução apresente mais informações estatísticas, no entanto existem outras situações onde podemos utilizar esse fantástico recurso.
Referências
https://orastory.wordpress.com/2012/03/06/sql-patch-ii/
