O processo de Tuning de instruções SQL não é uma tarefa fácil, existem ótimas ferramentas para auxiliar nesse trabalho, aquelas que requerem investimento normalmente não estão a nossa disposição nos ambientes onde trabalhamos. Neste artigo vamos listar 9 ferramentas de uso gratuito que podem ser baixadas e utilizadas sem custo,…
SQL Tuning Advisor: Você acredita em papai noel?
Ferramentas de Tuning são fantásticos recursos que nos ajudam muito em nosso trabalho, a má noticia é que diferente do que muito gente pensa elas não fazem todo o trabalho. Existem algumas ferramentas que até se propõe a fazer o trabalho de Tuning completo, como a ferramenta da Oracle SQL Tuning Advisor (STA), porem quem já utilizou esta ferramenta sabe que ela ainda precisa evoluir muito para atender as nossas expectativas. Neste artigo vamos utilizar o STA para fazer o Tuning de uma consulta simples e vamos constatar que ele não consegue oferecer a melhor solução de Tuning.
Para realizar esta simulação vamos:
1) Criar uma tabela com seu respectivo índice para simulação da consulta
2) Executar a consulta normalmente
3) Utilizar o STA para fazer o Tuning da consulta
4) Aplicar a solução proposta pelo STA
5) Aplicar uma solução de Tuning alternativa
1) Criar a tabela da simulação
Para esta simulação vamos criar uma tabela clone da tabela SALES do esquema SH, nesta tabela vamos criar um índice BTREE composto por dois campos da tabela e gerar 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 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL > SQL > create table VENDAS as select * from SH.SALES; Tabela criada. SQL > SQL > create index VENDAS_PROD_CUST_IDX on VENDAS(PROD_ID,CUST_ID); Índice criado. SQL > SQL > exec dbms_stats.GATHER_TABLE_STATS(ownname =>USER ,tabname =>'VENDAS',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254',cascade=>true); Procedimento PL/SQL concluído com sucesso. SQL > |
2) Executar a consulta normalmente
Vamos executar uma consulta simples e verificar o plano de execução que o Otimizador vai criar.
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 | SQL > set echo off; SQL > set tab off; SQL > set lines 300 pages 100; SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* DBTW111 */ sum(amount_sold) amount_sold 2 FROM vendas 3 WHERE cust_id = 100872; AMOUNT_SOLD ----------- 907,98 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 '%DBTW111%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 4wnsfqhdwmfyg 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('4wnsfqhdwmfyg', 0,'basic iostats last')) PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ SELECT /* DBTW111 */ sum(amount_sold) amount_sold FROM vendas WHERE cust_id = 100872 Plan hash value: 3725955559 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 4440 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 4440 | |* 2 | TABLE ACCESS FULL| VENDAS | 1 | 130 | 2 |00:00:00.02 | 4440 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("CUST_ID"=100872) 20 linhas selecionadas. 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:
No plano de execução podemos verificar que o Otimizador decidiu acessar a tabela utilizando a operação TABLE ACCESS FULL apesar de existir um índice com a coluna que foi utilizada como filtro na cláusula WHERE. Caso tenha curiosidade de saber por que o Otimizador não utilizou o índice leia o artigo: A ordem das colunas num índice composto faz diferença?
3) Utilizar o STA para fazer o Tuning da consulta
Agora vamos utilizar o SQL Tuning Advisor para analisar o plano de execução da consulta e verificar as soluções de melhoria propostas. Vale lembrar que a utilização do STA está condicionada a aquisição das OPTIONS DIAGNOSTIC & TUNING PACK.
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 | SQL > SET LONG 10000; SQL > SET PAGESIZE 9999 SQL > SET LINESIZE 155 SQL > set verify off SQL > col recommendations for a150 SQL > DECLARE 2 3 ret_val VARCHAR2(4000); 4 5 BEGIN 6 7 ret_val := dbms_sqltune.create_tuning_task(task_name=>'Task_name-4wnsfqhdwmfyg', sql_id=>'4wnsfqhdwmfyg', time_limit=>120); 8 9 10 dbms_sqltune.execute_tuning_task('Task_name-4wnsfqhdwmfyg'); 11 12 END; 13 / Procedimento PL/SQL concluído com sucesso. SQL > SELECT DBMS_SQLTUNE.report_tuning_task('Task_name-4wnsfqhdwmfyg') AS recommendations FROM dual; RECOMMENDATIONS ------------------------------------------------------------------------------------------------------------------------------------------------------ GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : Task_name-4wnsfqhdwmfyg Tuning Task Owner : CURSO01 Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 120 Completion Status : COMPLETED Started at : 12/06/2017 18:36:45 Completed at : 12/06/2017 18:36:46 ------------------------------------------------------------------------------- Schema Name: CURSO01 SQL ID : 4wnsfqhdwmfyg SQL Text : SELECT /* DBTW111 */ sum(amount_sold) amount_sold FROM vendas WHERE cust_id = 100872 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- Foi encontrado um plano de execução potencialmente melhor para esta instrução. Recommendation (estimated benefit: 98.17%) ------------------------------------------ - Considere a aceitação do perfil SQL recomendado. execute dbms_sqltune.accept_sql_profile(task_name => 'Task_name-4wnsfqhdwmfyg', task_owner => 'CURSO01', replace => TRUE); Validation results ------------------ O SQL profile foi testado executando-se tanto o seu plano quanto o plano original e medindo suas respectivas estatísticas de execução. Um plano pode ter sido somente parcialmente executado se o outro puder ser executado até a conclusão em menos tempo. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .021395 .000448 97.9 % CPU Time (s): .021396 .0005 97.66 % User I/O Time (s): 0 0 Buffer Gets: 4440 81 98.17 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. As estatísticas para the original plan foram calculadas com média com base em 10 execuções. 2. As estatísticas para the SQL profile plan foram calculadas com média com base em 10 execuções. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3725955559 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 161 (12)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | TABLE ACCESS FULL| VENDAS | 1 | 18 | 161 (12)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("CUST_ID"=100872) 2- Using SQL Profile -------------------- Plan hash value: 2521981677 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 76 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 18 | | | | 2 | TABLE ACCESS BY INDEX ROWID| VENDAS | 1 | 18 | 76 (0)| 00:00:01 | |* 3 | INDEX SKIP SCAN | VENDAS_PROD_CUST_IDX | 1 | | 74 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("CUST_ID"=100872) filter("CUST_ID"=100872) ------------------------------------------------------------------------------- SQL > |
No relatório produzido pelo STA verificamos que ele sugere a aplicação de uma SQL_PROFILE que deve proporcionar um ganho de 98% no tempo de execução da consulta, além disso o relatório apresenta estatísticas detalhadas da consulta antes e após a utilização da SQL_PROFILE, também mostra o plano de execução atual e o que será utilizado após a aplicação da SQL_PROFILE.
4) Aplicar a solução proposta pelo STA
Para verificar a eficácia da solução proposta vamos aceitar a SQL_PROFILE para que ela seja utilizada pelo Otimizador e na sequência verificar o plano de execução criado.
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 | SQL > execute dbms_sqltune.accept_sql_profile(task_name => 'Task_name-4wnsfqhdwmfyg', task_owner => 'CURSO01', replace => TRUE); Procedimento PL/SQL concluído com sucesso. SQL > SQL > set echo off; SQL > set tab off; SQL > set lines 300 pages 100; SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* DBTW111 */ sum(amount_sold) amount_sold 2 FROM vendas 3 WHERE cust_id = 100872; AMOUNT_SOLD ----------- 907,98 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 '%DBTW111%' 4 AND sql_text NOT LIKE '%v$sql%' 5 AND sql_text NOT LIKE '%SQL Analyze%'; SQL_ID CHILD_NUMBER ------------- ------------ 4wnsfqhdwmfyg 0 4wnsfqhdwmfyg 1 Decorrido: 00:00:00.07 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ SELECT /* DBTW111 */ sum(amount_sold) amount_sold FROM vendas WHERE cust_id = 100872 Plan hash value: 2521981677 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 81 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 81 | | 2 | TABLE ACCESS BY INDEX ROWID| VENDAS | 1 | 1 | 2 |00:00:00.01 | 81 | |* 3 | INDEX SKIP SCAN | VENDAS_PROD_CUST_IDX | 1 | 1 | 2 |00:00:00.01 | 80 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("CUST_ID"=100872) filter("CUST_ID"=100872) Note ----- - SQL profile SYS_SQLPROF_02602d8ca9640001 used for this statement 26 linhas selecionadas. SQL > |
Observando a seção NOTE do plano de execução, verificamos que a SQL profile SYS_SQLPROF_02602d8ca9640001 foi utilizada e no plano de execução a tabela VENDAS foi acessada utilizando o índice VENDAS_PROD_CUST_IDX. Comparando os dois planos de execução constatamos que houve uma melhora significativa em termos de utilização de recursos do Oracle, a quantidade Buffers acessados caiu de 4.440 para 81. A dúvida que permanece é: Será que existe um plano de execução melhor que este da SQL_PROFILE? vamos responder a esta pergunta no próximo tópico.
5) Aplicar uma solução de Tuning alternativa
A documentação da Oracle sobre o STA diz que alem da sugestão de SQL_PROFILES o STA pode sugerir a criação de índices, a atualização de estatísticas e até que a consulta seja reescrita. Apesar do STA não ter sugerido em seu relatório vamos criar um índice simples na coluna utilizada como filtro na cláusula WHERE e verificar o plano de execução que o Otimizador vai criar.
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 | SQL > create index VENDAS_CUST_IDX on vendas(cust_id); Índice criado. SQL > SQL > set echo off; SQL > set tab off; SQL > set lines 300 pages 100; SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* DBTW112 */ sum(amount_sold) amount_sold 2 FROM vendas 3 WHERE cust_id = 100872; AMOUNT_SOLD ----------- 907,98 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 '%DBTW112%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ d8869gmm58v7h 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ SELECT /* DBTW112 */ sum(amount_sold) amount_sold FROM vendas WHERE cust_id = 100872 Plan hash value: 2997106929 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 5 | | 2 | TABLE ACCESS BY INDEX ROWID| VENDAS | 1 | 130 | 2 |00:00:00.01 | 5 | |* 3 | INDEX RANGE SCAN | VENDAS_CUST_IDX | 1 | 130 | 2 |00:00:00.01 | 4 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("CUST_ID"=100872) 21 linhas selecionadas. SQL > |
Após a criação do índice o Otimizador passa a considerar essa opção para acessar a tabela VENDAS, quando comparamos o plano de execução utilizado pela SQL_PROFILE e este ultimo com a utilização do índice, podemos constatar que houve uma melhora significativa no plano alternativo que reduziu a quantidade de acesso aos Buffers do banco de dados de 81 para 5.
6) CONCLUSÃO
Utilizamos um exemplo simples para evidenciar que o SQL Tuning Advisor pode não ser aquela ferramenta que vai resolver todos os seus problemas de Tuning de instruções SQL, assim como neste exemplo, já constatamos dezenas de situações onde o STA não consegue realizar o trabalho de Tuning que precisamos. Neste artigo não temos a pretensão de dizer que o STA seja um ferramenta que podemos descartar em nossos trabalhos de Tuning, nossa intenção é alertar que o STA tem suas limitações e que precisamos buscar conhecimentos de SQL Tuning para realizar o trabalho que ferramentas como o STA não conseguem resolver.
Referências
https://docs.oracle.com/cd/E25178_01/server.1111/e16638/sql_tune.htm
