SQL Tuning Advisor: Você acredita em papai noel?

SQL Tuning Advisor

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 >

 

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
 
 
 
Promo-D75L

video#001
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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