{"id":1303,"date":"2017-12-10T21:42:06","date_gmt":"2017-12-10T23:42:06","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1303"},"modified":"2019-11-11T18:48:05","modified_gmt":"2019-11-11T21:48:05","slug":"sql-tuning-advisor-voce-acredita-em-papai-noel","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sql-tuning-advisor-voce-acredita-em-papai-noel\/","title":{"rendered":"SQL Tuning Advisor: Voc\u00ea acredita em papai noel?"},"content":{"rendered":"

Ferramentas de Tuning<\/strong><\/a> s\u00e3o fant\u00e1sticos recursos que nos ajudam muito em nosso trabalho, a m\u00e1 noticia \u00e9 que diferente do que muito gente pensa elas n\u00e3o fazem todo o trabalho. Existem algumas ferramentas que at\u00e9 se prop\u00f5e a fazer o trabalho de Tuning completo, como a ferramenta da Oracle SQL Tuning Advisor (STA), porem quem j\u00e1 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\u00e3o consegue oferecer a melhor solu\u00e7\u00e3o de Tuning.<\/span><\/p>\n

 
\n 
\nPara realizar esta simula\u00e7\u00e3o vamos:<\/span><\/strong><\/p>\n

1) Criar\u00a0uma tabela com seu respectivo \u00edndice para simula\u00e7\u00e3o da consulta<\/span>
\n2) Executar a consulta normalmente <\/span>
\n3) Utilizar o STA para fazer o Tuning da consulta<\/span>
\n4) Aplicar a solu\u00e7\u00e3o proposta pelo STA<\/span>
\n5) Aplicar uma solu\u00e7\u00e3o de Tuning alternativa<\/span><\/p>\n

 <\/p>\n

1) Criar a tabela da simula\u00e7\u00e3o<\/span><\/h2>\n

Para esta simula\u00e7\u00e3o vamos criar uma tabela clone da tabela SALES do esquema SH,\u00a0nesta tabela vamos criar um \u00edndice BTREE composto por dois campos da tabela e gerar as estat\u00edsticas.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\nSQL ><\/span>
\nSQL ><\/span> create table VENDAS as<\/span> select *<\/span> from SH.<\/span>SALES;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index VENDAS_PROD_CUST_IDX on VENDAS(<\/span>PROD_ID,<\/span>CUST_ID)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>GATHER_TABLE_STATS(<\/span>ownname =><\/span>USER ,<\/span>tabname =><\/span>'VENDAS'<\/span>,<\/span>method_opt=><\/span>'FOR ALL INDEXED COLUMNS SIZE 254'<\/span>,<\/span>cascade=><\/span>true<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar a consulta normalmente<\/span><\/h2>\n

Vamos executar uma consulta simples e verificar o plano de execu\u00e7\u00e3o que o Otimizador vai criar.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> set echo<\/span> off;<\/span>
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW111 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM vendas
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n     907<\/span>,<\/span>98<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW111%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n4wnsfqhdwmfyg            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'4wnsfqhdwmfyg'<\/span>,<\/span>         0<\/span>,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* DBTW111 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM vendas  WHERE
\ncust_id =<\/span> 100872<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3725955559<\/span>
\n
\n---------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>        |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>    4440<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>        |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>    4440<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> VENDAS |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>    4440<\/span> |<\/span>
\n---------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No plano de execu\u00e7\u00e3o podemos verificar que o Otimizador decidiu acessar a tabela utilizando a opera\u00e7\u00e3o TABLE ACCESS FULL apesar de existir um \u00edndice com a coluna que foi utilizada como filtro na cl\u00e1usula WHERE. Caso tenha curiosidade de saber por que o Otimizador n\u00e3o utilizou o \u00edndice leia o artigo: A ordem das colunas num \u00edndice composto faz diferen\u00e7a?<\/strong><\/a><\/span><\/p>\n

3) Utilizar o STA para fazer o Tuning da consulta<\/span><\/h2>\n

Agora vamos utilizar o SQL Tuning Advisor para analisar o plano de execu\u00e7\u00e3o da consulta e verificar as solu\u00e7\u00f5es de melhoria propostas. Vale lembrar que a utiliza\u00e7\u00e3o do STA est\u00e1 condicionada a aquisi\u00e7\u00e3o das OPTIONS DIAGNOSTIC & TUNING PACK.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SET LONG 10000<\/span>;<\/span>
\nSQL ><\/span> SET PAGESIZE 9999<\/span>
\nSQL ><\/span> SET LINESIZE 155<\/span>
\nSQL ><\/span> set verify off
\nSQL ><\/span> col recommendations for<\/span> a150
\nSQL ><\/span> DECLARE<\/span>
\n  2<\/span>
\n  3<\/span>   ret_val VARCHAR2(<\/span>4000<\/span>)<\/span>;<\/span>
\n  4<\/span>
\n  5<\/span>  BEGIN
\n  6<\/span>
\n  7<\/span>  ret_val :=<\/span> dbms_sqltune.<\/span>create_tuning_task(<\/span>task_name=><\/span>'Task_name-4wnsfqhdwmfyg'<\/span>,<\/span> sql_id=><\/span>'4wnsfqhdwmfyg'<\/span>,<\/span> time_limit=><\/span>120<\/span>)<\/span>;<\/span>
\n  8<\/span>
\n  9<\/span>
\n 10<\/span>  dbms_sqltune.<\/span>execute_tuning_task(<\/span>'Task_name-4wnsfqhdwmfyg'<\/span>)<\/span>;<\/span>
\n 11<\/span>
\n 12<\/span>  END<\/span><\/a>;<\/span>
\n 13<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> SELECT DBMS_SQLTUNE.<\/span>report_tuning_task(<\/span>'Task_name-4wnsfqhdwmfyg'<\/span>)<\/span> AS<\/span> recommendations FROM dual;<\/span>
\n
\nRECOMMENDATIONS
\n------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nGENERAL INFORMATION SECTION
\n-------------------------------------------------------------------------------<\/span>
\nTuning Task Name   :<\/span> Task_name-<\/span>4wnsfqhdwmfyg
\nTuning Task Owner  :<\/span> CURSO01
\nWorkload Type      :<\/span> Single SQL Statement
\nScope              :<\/span> COMPREHENSIVE
\nTime<\/span><\/a> Limit(<\/span>seconds)<\/span>:<\/span> 120<\/span>
\nCompletion Status  :<\/span> COMPLETED
\nStarted at         :<\/span> 12<\/span>\/<\/span>06<\/span>\/<\/span>2017<\/span> 18<\/span>:<\/span>36<\/span>:<\/span>45<\/span>
\nCompleted at       :<\/span> 12<\/span>\/<\/span>06<\/span>\/<\/span>2017<\/span> 18<\/span>:<\/span>36<\/span>:<\/span>46<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\nSchema Name:<\/span> CURSO01
\nSQL ID     :<\/span> 4wnsfqhdwmfyg
\nSQL Text   :<\/span> SELECT \/* DBTW111 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n               FROM vendas
\n              WHERE cust_id =<\/span> 100872<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\nFINDINGS SECTION (<\/span>1<\/span> finding)<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\n1<\/span>-<\/span> SQL Profile Finding (<\/span>see explain plans section below)<\/span>
\n--------------------------------------------------------<\/span>
\n  Foi encontrado um plano de execu\u00e7\u00e3o potencialmente melhor para esta
\n  instru\u00e7\u00e3o.<\/span>
\n
\n  Recommendation (<\/span>estimated benefit:<\/span> 98.17<\/span>%<\/span>)<\/span>
\n  ------------------------------------------<\/span>
\n  -<\/span> Considere a aceita\u00e7\u00e3o do<\/span> perfil SQL recomendado.<\/span>
\n    execute dbms_sqltune.<\/span>accept_sql_profile(<\/span>task_name =><\/span>
\n            'Task_name-4wnsfqhdwmfyg'<\/span>,<\/span> task_owner =><\/span> 'CURSO01'<\/span>,<\/span> replace =><\/span>
\n            TRUE<\/span>)<\/span>;<\/span>
\n
\n  Validation results
\n  ------------------<\/span>
\n  O SQL profile foi testado executando-<\/span>se tanto o seu plano quanto o plano
\n  original e medindo suas respectivas estat\u00edsticas de execu\u00e7\u00e3o.<\/span> Um plano pode
\n  ter sido somente parcialmente executado se o outro puder ser executado at\u00e9 a
\n  conclus\u00e3o em menos tempo.<\/span>
\n
\n                           Original Plan  With SQL Profile  %<\/span> Improved
\n                           -------------<\/span>  ----------------<\/span>  ----------<\/span>
\n  Completion Status:<\/span>            COMPLETE          COMPLETE
\n  Elapsed Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>             .021395<\/span>           .000448<\/span>       97.9<\/span> %<\/span>
\n  CPU Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>                 .021396<\/span>             .0005<\/span>      97.66<\/span> %<\/span>
\n  User I\/<\/span>O Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>                  0<\/span>                 0<\/span>
\n  Buffer Gets:<\/span>                     4440<\/span>                81<\/span>      98.17<\/span> %<\/span>
\n  Physical Read Requests:<\/span>             0<\/span>                 0<\/span>
\n  Physical Write Requests:<\/span>            0<\/span>                 0<\/span>
\n  Physical Read Bytes:<\/span>                0<\/span>                 0<\/span>
\n  Physical Write Bytes:<\/span>               0<\/span>                 0<\/span>
\n  Rows Processed:<\/span>                     1<\/span>                 1<\/span>
\n  Fetches:<\/span>                            1<\/span>                 1<\/span>
\n  Executions:<\/span>                         1<\/span>                 1<\/span>
\n
\n  Notes
\n  -----<\/span>
\n  1<\/span>.<\/span> As<\/span> estat\u00edsticas para the original plan foram calculadas com m\u00e9dia com
\n  base em 10<\/span> execu\u00e7\u00f5es.<\/span>
\n  2<\/span>.<\/span> As<\/span> estat\u00edsticas para the SQL profile plan foram calculadas com m\u00e9dia com
\n  base em 10<\/span> execu\u00e7\u00f5es.<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\nEXPLAIN PLANS SECTION
\n-------------------------------------------------------------------------------<\/span>
\n
\n1<\/span>-<\/span> Original With Adjusted Cost
\n------------------------------<\/span>
\nPlan hash<\/span><\/a> value:<\/span> 3725955559<\/span>
\n
\n-----------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation          |<\/span> Name   |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n-----------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT   |<\/span>        |<\/span>     1<\/span> |<\/span>    18<\/span> |<\/span>   161<\/span>  (<\/span>12<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>        |<\/span>     1<\/span> |<\/span>    18<\/span> |<\/span>            |<\/span>          |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> VENDAS |<\/span>     1<\/span> |<\/span>    18<\/span> |<\/span>   161<\/span>  (<\/span>12<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n-----------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n2<\/span>-<\/span> Using SQL Profile
\n--------------------<\/span>
\nPlan hash<\/span><\/a> value:<\/span> 2521981677<\/span>
\n
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name                 |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>                      |<\/span>     1<\/span> |<\/span>    18<\/span> |<\/span>    76<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>              |<\/span>                      |<\/span>     1<\/span> |<\/span>    18<\/span> |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> VENDAS               |<\/span>     1<\/span> |<\/span>    18<\/span> |<\/span>    76<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX SKIP SCAN           |<\/span> VENDAS_PROD_CUST_IDX |<\/span>     1<\/span> |<\/span>       |<\/span>    74<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n       filter(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No relat\u00f3rio produzido pelo STA verificamos que ele sugere a aplica\u00e7\u00e3o de uma SQL_PROFILE que deve proporcionar um ganho de 98% no tempo de execu\u00e7\u00e3o da consulta, al\u00e9m disso o relat\u00f3rio apresenta estat\u00edsticas detalhadas da consulta antes e ap\u00f3s a utiliza\u00e7\u00e3o da SQL_PROFILE, tamb\u00e9m mostra o plano de execu\u00e7\u00e3o atual e o que ser\u00e1 utilizado ap\u00f3s a aplica\u00e7\u00e3o da SQL_PROFILE.<\/p>\n

4) Aplicar a solu\u00e7\u00e3o proposta pelo STA<\/span><\/h2>\n

Para verificar a efic\u00e1cia da solu\u00e7\u00e3o proposta vamos aceitar a SQL_PROFILE para que ela seja utilizada pelo Otimizador e na sequ\u00eancia verificar o plano de execu\u00e7\u00e3o criado.<\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> execute dbms_sqltune.<\/span>accept_sql_profile(<\/span>task_name =><\/span> 'Task_name-4wnsfqhdwmfyg'<\/span>,<\/span> task_owner =><\/span> 'CURSO01'<\/span>,<\/span> replace =><\/span>  TRUE<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set echo<\/span> off;<\/span>
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW111 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM vendas
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n     907<\/span>,<\/span>98<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW111%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>
\n  5<\/span>     AND sql_text NOT LIKE '%SQL Analyze%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n4wnsfqhdwmfyg            0<\/span>
\n4wnsfqhdwmfyg            1<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.07<\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* DBTW111 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM vendas  WHERE
\ncust_id =<\/span> 100872<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2521981677<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name                 |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>                      |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      81<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>              |<\/span>                      |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      81<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> VENDAS               |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      81<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX SKIP SCAN           |<\/span> VENDAS_PROD_CUST_IDX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      80<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n       filter(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL profile SYS_SQLPROF_02602d8ca9640001 used for<\/span> this statement
\n
\n
\n26<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando a se\u00e7\u00e3o NOTE do plano de execu\u00e7\u00e3o, verificamos que a SQL profile SYS_SQLPROF_02602d8ca9640001 foi utilizada e no plano de execu\u00e7\u00e3o a tabela VENDAS foi acessada utilizando o \u00edndice VENDAS_PROD_CUST_IDX. Comparando os dois planos de execu\u00e7\u00e3o constatamos que houve uma melhora significativa em termos de utiliza\u00e7\u00e3o de recursos do Oracle, a quantidade Buffers acessados caiu de 4.440 para 81. A\u00a0d\u00favida que permanece \u00e9: Ser\u00e1 que existe um plano de execu\u00e7\u00e3o melhor que este da SQL_PROFILE? vamos responder a esta pergunta no pr\u00f3ximo t\u00f3pico.<\/span><\/p>\n

5) Aplicar uma solu\u00e7\u00e3o de Tuning alternativa<\/span><\/h2>\n

A documenta\u00e7\u00e3o da Oracle sobre o STA diz que alem da sugest\u00e3o de SQL_PROFILES o STA pode sugerir a cria\u00e7\u00e3o de \u00edndices, a atualiza\u00e7\u00e3o de estat\u00edsticas e at\u00e9 que a consulta seja reescrita. Apesar do STA n\u00e3o ter sugerido em seu relat\u00f3rio vamos criar um \u00edndice simples na coluna utilizada como filtro na cl\u00e1usula WHERE e verificar o plano de execu\u00e7\u00e3o que o Otimizador vai criar.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> create index VENDAS_CUST_IDX on vendas(<\/span>cust_id)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> set echo<\/span> off;<\/span>
\nSQL ><\/span> set tab off;<\/span>
\nSQL ><\/span> set lines 300<\/span> pages 100<\/span>;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW112 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold
\n  2<\/span>    FROM vendas
\n  3<\/span>   WHERE cust_id =<\/span> 100872<\/span>;<\/span>
\n
\nAMOUNT_SOLD
\n-----------<\/span>
\n     907<\/span>,<\/span>98<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW112%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nd8869gmm58v7h            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nSELECT \/* DBTW112 *\/<\/span> sum(<\/span>amount_sold)<\/span> amount_sold   FROM vendas  WHERE
\ncust_id =<\/span> 100872<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2997106929<\/span>
\n
\n----------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name            |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n----------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>                 |<\/span>      1<\/span> |<\/span>        |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>              |<\/span>                 |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> VENDAS          |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> VENDAS_CUST_IDX |<\/span>      1<\/span> |<\/span>    130<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"CUST_ID"<\/span>=<\/span>100872<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ap\u00f3s a cria\u00e7\u00e3o do \u00edndice o Otimizador passa a considerar essa op\u00e7\u00e3o para acessar a tabela VENDAS, quando comparamos o plano de execu\u00e7\u00e3o utilizado pela SQL_PROFILE e este ultimo com a utiliza\u00e7\u00e3o do \u00edndice, 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.<\/span><\/p>\n

6)\u00a0\u00a0CONCLUS\u00c3O<\/span><\/h2>\n

Utilizamos um exemplo simples para evidenciar que o SQL Tuning Advisor pode n\u00e3o ser aquela ferramenta que vai resolver todos os seus problemas de Tuning de instru\u00e7\u00f5es SQL, assim como neste exemplo, j\u00e1 constatamos dezenas de situa\u00e7\u00f5es onde o STA n\u00e3o consegue realizar o trabalho de Tuning que precisamos. Neste artigo n\u00e3o temos a pretens\u00e3o de dizer que o STA seja um ferramenta que podemos descartar em nossos trabalhos de Tuning, nossa inten\u00e7\u00e3o \u00e9 alertar que o STA tem suas limita\u00e7\u00f5es e que precisamos buscar conhecimentos de SQL Tuning para realizar o trabalho que ferramentas como o STA n\u00e3o conseguem resolver.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

https:\/\/docs.oracle.com\/cd\/E25178_01\/server.1111\/e16638\/sql_tune.htm<\/a><\/span><\/strong>
\n 
\n 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video#001\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Ferramentas de Tuning s\u00e3o fant\u00e1sticos recursos que nos ajudam muito em nosso trabalho, a m\u00e1 noticia \u00e9 que diferente do que muito gente pensa elas n\u00e3o fazem todo o trabalho. Existem algumas ferramentas que at\u00e9 se prop\u00f5e a fazer o trabalho de Tuning completo, como a ferramenta da Oracle SQL Tuning Advisor (STA), porem quem j\u00e1 utilizou esta ferramenta sabe<\/p>\n","protected":false},"author":2,"featured_media":1274,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[185],"tags":[114,117,186,116,15],"class_list":["post-1303","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-tuning-advisor","tag-ferramentas-de-tuning","tag-sql-tuning-advisor","tag-sql_profile","tag-sta","tag-tuning-tools"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=1303"}],"version-history":[{"count":16,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1303\/revisions"}],"predecessor-version":[{"id":2079,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1303\/revisions\/2079"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1274"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1303"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}