{"id":392,"date":"2016-03-27T22:15:28","date_gmt":"2016-03-28T01:15:28","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=392"},"modified":"2022-07-22T15:24:02","modified_gmt":"2022-07-22T18:24:02","slug":"sql-profile-uma-poderosa-aliada-no-processo-de-tuning-2","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sql-profile-uma-poderosa-aliada-no-processo-de-tuning-2\/","title":{"rendered":"SQL Profile: uma poderosa aliada no processo de Tuning"},"content":{"rendered":"\n

Quantas vezes voc\u00ea encontrou uma consulta usando um \u00edndice X quando voc\u00ea queria que ela usasse o \u00edndice Y, ou uma consulta realizando “Nested loop” para fazer um “join” entre duas tabelas quando um “Hash Join” realizaria esse trabalho muito mais r\u00e1pido. Ou uma inst\u00e2ncia em que a aplica\u00e7\u00e3o de repente come\u00e7a a utilizar um plano de execu\u00e7\u00e3o ineficiente ap\u00f3s o banco de dados reiniciar. Utilizando SQL Profile podemos resolver esses problemas sem alterar uma linha de c\u00f3digo da aplica\u00e7\u00e3o.<\/span><\/p>\n

O que \u00e9 uma SQL Profile<\/span><\/h2>\n

SQL Profile s\u00e3o metadados armazenados no banco de dados que s\u00e3o compostos do c\u00f3digo da instru\u00e7\u00e3o SQL e um conjunto de sugest\u00f5es que ser\u00e3o utilizados pelo Otimizador para montar o plano de execu\u00e7\u00e3o. Ao contr\u00e1rio da “Stored Outlines”, os “Hints” armazenados na SQL Profile n\u00e3o tentam ditar as opera\u00e7\u00f5es que o Otimizador dever\u00e1 utilizar no plano de execu\u00e7\u00e3o. Em vez disso, fornecer fatores de corre\u00e7\u00e3o aritm\u00e9tica que ser\u00e3o utilizados pelo otimizador para melhorar a estimativa de cardinalidade.<\/span><\/p>\n

Apesar da SQL Profile gerada pelo SQL Tuning Advisor n\u00e3o possuir Hints semelhantes aos utilizados pelas “Stored Outlines”, podemos criar manualmente uma SQL Profile que utiliza Hints como as “Stored Outlines” para ditar ao Otimizador como deve ser o plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Como s\u00e3o criadas as SQL Profile<\/span><\/h2>\n

Uma SQL Profile pode ser criada atrav\u00e9s do “SQL Tuning Advisor” (STA), esse tipo SQL Profile como foi dito anteriormente n\u00e3o fixa um plano de execu\u00e7\u00e3o apenas fornece fatores de corre\u00e7\u00e3o aritm\u00e9tica para melhorar as estimativas de cardinalidade, dando ao Otimizador informa\u00e7\u00f5es mais qualificadas para escolha do plano de execu\u00e7\u00e3o.<\/span><\/p>\n

Outra op\u00e7\u00e3o para cria\u00e7\u00e3o de SQL Profile \u00e9 utilizar o script coe_xfr_sql_profile.sql, elaborado por Carlos Sierra (Oracle Support forces), esse script gera outro script para cria\u00e7\u00e3o SQL Profile a partir das “Outlines” de um SQL_ID armazenadas na “Library Cache”. A SQL Profile gerada por este script \u00e9 diferente da gerada pelo “SQL Tuning Advisor”, os “Hints” dessa profile dizem ao Otimizador que tipo de opera\u00e7\u00f5es utilizar na elabora\u00e7\u00e3o do plano, tornando o plano de execu\u00e7\u00e3o fixo.<\/span><\/p>\n

Exemplo pr\u00e1tico de cria\u00e7\u00e3o SQL Profile<\/span><\/h2>\n

A seguir vamos demonstrar a cria\u00e7\u00e3o de uma SQL Profile utilizado “SQL Tuning Advisor” e outra com o script coe_xfr_sql_profile.sql, em cada um dos exemplos vamos listar no final os “Hints” de cada uma das SQL Profile que ser\u00e3o utilizados para influenciar o Otimizador na elabora\u00e7\u00e3o do plano de execu\u00e7\u00e3o.<\/p>\n

Cria\u00e7\u00e3o SQL Profile com STA<\/span><\/h3>\n

Primeiro vamos criar uma tabela que ser\u00e1 utilizada na consulta do nosso exemplo.<\/span><\/p>\n

.<\/p>\n\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
<\/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> Production
\n
\nSQL ><\/span>
\nSQL ><\/span> create table tab01
\n  2<\/span>  (<\/span>pk_col number primary key<\/span><\/a>,<\/span>
\n  3<\/span>  col1 number,<\/span>
\n  4<\/span>  col2 varchar2 (<\/span>30<\/span>)<\/span>,<\/span>
\n  5<\/span>  col3 date<\/span><\/a>,<\/span>
\n  6<\/span>  col4 varchar2(<\/span>1<\/span>)<\/span>)<\/span>
\n  7<\/span>  \/<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span> create sequence tab01_seq
\n  2<\/span>  \/<\/span>
\n
\nSequ\u00eancia criada.<\/span>
\n
\nSQL ><\/span> begin
\n  2<\/span>  for<\/span> i in 1<\/span>..<\/span>100000<\/span> loop
\n  3<\/span>  insert into tab01 (<\/span>pk_col,<\/span> col1,<\/span> col2,<\/span> col3,<\/span> col4)<\/span>
\n  4<\/span>  select tab01_seq.<\/span>nextval,<\/span> tab01_seq.<\/span>currval,<\/span> 'asddsadasd'<\/span>,<\/span>
\n  5<\/span>  sysdate\u00cd(<\/span>tab01_seq.<\/span>currval\/<\/span>3600<\/span>)<\/span>,<\/span> 'Y'<\/span> from dual;<\/span>
\n  6<\/span>  end<\/span><\/a> loop;<\/span>
\n  7<\/span>  end<\/span><\/a>;<\/span>
\n  8<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> select count<\/span><\/a>(<\/span>*<\/span>)<\/span> from tab01;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n----------<\/span>
\n    100000<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span> update tab01 set col1 =<\/span> 1<\/span> where rownum <<\/span> 100000<\/span>;<\/span>
\n
\n99999<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> update tab01 set col4 =<\/span> 'N'<\/span> where rownum <<\/span> 100<\/span>;<\/span>
\n
\n99<\/span> linhas atualizadas.<\/span>
\n
\nSQL ><\/span> commit;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span> create index tab01_col1 on tab01(<\/span>col1)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname =><\/span> USER,<\/span> tabname =><\/span> 'TAB01'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/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\n

.<\/p>\n

Agora vamos executar uma consulta e verificar o plano de execu\u00e7\u00e3o gerado pelo Otimizador<\/span>.<\/p>\n

.<\/p>\n\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> select col1,<\/span> col2,<\/span> col3 from tab01 where col1 =<\/span> 77150<\/span>;<\/span>
\n
\n      COL1 COL2                           COL3
\n----------<\/span> ------------------------------<\/span> --------<\/span>
\n     77150<\/span> asddsadasd                     06<\/span>\/<\/span>03<\/span>\/<\/span>16<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span> @<\/span>find_sql
\n'========================================================================'<\/span>
\n'= INFORME UM DOS PARAMETROS ABAIXO:  SQL_TEXT OU  SQL_ID               ='<\/span>
\n'========================================================================'<\/span>
\nSQL_TEXT..:<\/span> from tab01 where col1
\nSQL_ID....:<\/span>
\n
\nSQL_ID         CHILD  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME      SQL_TEXT
\n-------------<\/span> ------<\/span> ----------<\/span> ----------<\/span> -------------<\/span> -------------<\/span> -------------<\/span> -----------------------------------------<\/span>
\narrf7tftg4pxg      0<\/span> 2044041692<\/span>          3<\/span>           .03<\/span>           .01<\/span> VALTER        select col1,<\/span> col2,<\/span> col3 from tab01 where
\n                                                                                     col1 =<\/span> 77150<\/span>
\n
\n
\nSQL ><\/span> @<\/span>dplan
\n'========================================================================'<\/span>
\n'= INFORME UM DOS PARAMETROS ABAIXO:  SQL_ID E\/OU  CHILD_NO             ='<\/span>
\n'========================================================================'<\/span>
\nSQL_ID...:<\/span> arrf7tftg4pxg
\nCHILD_NO.:<\/span> 0<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  arrf7tftg4pxg,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect col1,<\/span> col2,<\/span> col3 from tab01 where col1 =<\/span> 77150<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2044041692<\/span>
\n
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name  |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>   137<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     499<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> TAB01 |<\/span>      1<\/span> |<\/span>  50000<\/span> |<\/span>  1123K|<\/span>   137<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     499<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"COL1"<\/span>=<\/span>77150<\/span>)<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n

.<\/p>\n

Podemos observar que o plano de execu\u00e7\u00e3o gerado pelo Otimizador faz um “table full scan” na tabela TAB01, e acessa 499 Buffers para retornar 1 linha. Na sequ\u00eancia vamos utilizar “SQL Tuning Advisor” para verificar se o Otimizador consegue gerar uma plano de execu\u00e7\u00e3o de melhor qualidade.<\/span><\/p>\n

.<\/p>\n\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
<\/div><\/td>
SQL ><\/span> @<\/span>create_tuning_task.<\/span>sql;<\/span>
\nTask_Name:<\/span> tuning_arrf7tftg4pxg
\nInforme o valor para sql_id:<\/span> arrf7tftg4pxg
\nInforme o valor para time_limit:<\/span> 3000<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nRECOMMENDATIONS
\n------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nGENERAL INFORMATION SECTION
\n-------------------------------------------------------------------------------<\/span>
\nTuning Task Name   :<\/span> tuning_arrf7tftg4pxg
\nTuning Task Owner  :<\/span> VALTER
\nWorkload Type      :<\/span> Single SQL Statement
\nScope              :<\/span> COMPREHENSIVE
\nTime<\/span><\/a> Limit(<\/span>seconds)<\/span>:<\/span> 3000<\/span>
\nCompletion Status  :<\/span> COMPLETED
\nStarted at         :<\/span> 03<\/span>\/<\/span>27<\/span>\/<\/span>2016<\/span> 11<\/span>:<\/span>53<\/span>:<\/span>33<\/span>
\nCompleted at       :<\/span> 03<\/span>\/<\/span>27<\/span>\/<\/span>2016<\/span> 11<\/span>:<\/span>53<\/span>:<\/span>36<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\nSchema Name:<\/span> VALTER
\nSQL ID     :<\/span> arrf7tftg4pxg
\nSQL Text   :<\/span> select col1,<\/span> col2,<\/span> col3 from tab01 where col1 =<\/span> 77150<\/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> 99.39<\/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            'tuning_arrf7tftg4pxg'<\/span>,<\/span> task_owner =><\/span> 'VALTER'<\/span>,<\/span> replace =><\/span> 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>             .002539<\/span>           .000041<\/span>      98.38<\/span> %<\/span>
\n  CPU Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>                 .002599<\/span>                 0<\/span>        100<\/span> %<\/span>
\n  User I\/<\/span>O Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>                  0<\/span>                 0<\/span>
\n  Buffer Gets:<\/span>                      499<\/span>                 3<\/span>      99.39<\/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> 2044041692<\/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>    23<\/span> |<\/span>   137<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> TAB01 |<\/span>     1<\/span> |<\/span>    23<\/span> |<\/span>   137<\/span>   (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02<\/span> |<\/span>
\n---------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"COL1"<\/span>=<\/span>77150<\/span>)<\/span>
\n
\n2<\/span>-<\/span> Using SQL Profile
\n--------------------<\/span>
\nPlan hash<\/span><\/a> value:<\/span> 330808811<\/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>    23<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> TAB01      |<\/span>     1<\/span> |<\/span>    23<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> TAB01_COL1 |<\/span>     1<\/span> |<\/span>       |<\/span>     1<\/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   2<\/span> -<\/span> access(<\/span>"COL1"<\/span>=<\/span>77150<\/span>)<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n

.<\/p>\n

No relat\u00f3rio acima podemos verificar que o STA recomenda a cria\u00e7\u00e3o de uma SQL Profile que vai proporcionar uma melhoria de 99.39% no plano de execu\u00e7\u00e3o, se observarmos o plano de execu\u00e7\u00e3o sugerido vamos constatar a utiliza\u00e7\u00e3o do \u00edndice TAB01_COL1 para acessar as linhas solicitadas pela consulta.<\/span>
Para validar a sugest\u00e3o do STA vamos criar a SQL Profile sugerida e executar a consulta novamente.<\/span><\/p>\n

.<\/p>\n\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
<\/div><\/td>
SQL ><\/span> execute dbms_sqltune.<\/span>accept_sql_profile(<\/span>task_name =><\/span> 'tuning_arrf7tftg4pxg'<\/span>,<\/span> task_owner =><\/span> 'VALTER'<\/span>,<\/span> replace =><\/span> TRUE<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> alter system<\/span><\/a> flush<\/span><\/a> shared_pool;<\/span>
\n
\nSistema alterado.<\/span>
\n
\nSQL ><\/span> select col1,<\/span> col2,<\/span> col3 from tab01 where col1 =<\/span> 77150<\/span>;<\/span>
\n
\n      COL1 COL2                           COL3
\n----------<\/span> ------------------------------<\/span> --------<\/span>
\n     77150<\/span> asddsadasd                     06<\/span>\/<\/span>03<\/span>\/<\/span>16<\/span>
\n
\nSQL ><\/span> @<\/span>dplan
\n'========================================================================'<\/span>
\n'= INFORME UM DOS PARAMETROS ABAIXO:  SQL_ID E\/OU  CHILD_NO             ='<\/span>
\n'========================================================================'<\/span>
\nSQL_ID...:<\/span> arrf7tftg4pxg
\nCHILD_NO.:<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  arrf7tftg4pxg,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect col1,<\/span> col2,<\/span> col3 from tab01 where col1 =<\/span> 77150<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 330808811<\/span>
\n
\n-------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name       |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span>
\n-------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT            |<\/span>            |<\/span>        |<\/span>       |<\/span>     2<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> TAB01      |<\/span>      1<\/span> |<\/span>    23<\/span> |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> TAB01_COL1 |<\/span>      1<\/span> |<\/span>       |<\/span>     1<\/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   2<\/span> -<\/span> access(<\/span>"COL1"<\/span>=<\/span>77150<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL profile SYS_SQLPROF_0153b8917ef00000 used for<\/span> this statement
\n   -<\/span> Warning:<\/span> basic plan statistics not available.<\/span> These are only collected when:<\/span>
\n       *<\/span> hint 'gather_plan_statistics'<\/span> is used for<\/span> the statement or
\n       *<\/span> parameter 'statistics_level'<\/span> is set to 'ALL'<\/span>,<\/span> at session or system<\/span><\/a> level
\n
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n

.<\/p>\n

Ao examinar o plano de execu\u00e7\u00e3o da consulta verificamos que o plano sugerido (que usa o \u00edndice TAB01_COL1) foi utilizado e na se\u00e7\u00e3o “Note” encontramos a confirma\u00e7\u00e3o da utiliza\u00e7\u00e3o da SQL Profile “SQL profile SYS_SQLPROF_0153b8917ef00000 used for this statement”.<\/span>
Agora vamos verificar quais foram os “Hints” utilizados por essa SQL Profile para influenciar o Otimizador na cria\u00e7\u00e3o do plano de execu\u00e7\u00e3o.<\/span><\/p>\n

.<\/p>\n\n

1
2
3
4
5
6
7
8
9
10
11
12
13
<\/div><\/td>
SQL ><\/span> conn sys@<\/span>lab11 as<\/span> sysdba;<\/span>
\nInforme a senha:<\/span>
\nConectado.<\/span>
\nSQL ><\/span> @<\/span>sql_profile_hints11;<\/span>
\nInforme o valor para name:<\/span> SYS_SQLPROF_0153b8917ef00000
\n
\nHINT
\n------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nOPT_ESTIMATE(<\/span>@<\/span>"SEL$1<\/span>"<\/span>,<\/span> TABLE,<\/span> "TAB01"<\/span>@<\/span>"SEL$1<\/span>"<\/span>,<\/span> SCALE_ROWS=<\/span>2e-05<\/span>)<\/span>
\nOPT_ESTIMATE(<\/span>@<\/span>"SEL$1<\/span>"<\/span>,<\/span> INDEX_SCAN,<\/span> "TAB01"<\/span>@<\/span>"SEL$1<\/span>"<\/span>,<\/span> "TAB01_COL1"<\/span>,<\/span> SCALE_ROWS=<\/span>2e-05<\/span>)<\/span>
\nOPTIMIZER_FEATURES_ENABLE(<\/span>default<\/span>)<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n

.<\/p>\n

Cria\u00e7\u00e3o SQL Profile com script coe_xfr_sql_profile<\/span><\/h3>\n

Vamos criar uma SQL Profile do plano otimizado pelo STA, utilizando o script coe_xfr_sql_profile.<\/span><\/p>\n

.<\/p>\n\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
<\/div><\/td>
SQL><\/span> select col1,<\/span> col2,<\/span> col3 from tab01 where col1 =<\/span> 77150<\/span>;<\/span>
\n
\n      COL1 COL2                           COL3
\n----------<\/span> ------------------------------<\/span> --------<\/span>
\n     77150<\/span> asddsadasd                     06<\/span>\/<\/span>03<\/span>\/<\/span>16<\/span>
\n
\nSQL><\/span> @<\/span>coe_xfr_sql_profile.<\/span>sql;<\/span>
\n
\nParameter 1<\/span>:<\/span>
\nSQL_ID (<\/span>required)<\/span>
\n
\nInforme o valor para 1<\/span>:<\/span> arrf7tftg4pxg
\n
\n
\nPLAN_HASH_VALUE AVG_ET_SECS
\n---------------<\/span> -----------<\/span>
\n      330808811<\/span>        ,<\/span>011<\/span>
\n
\nParameter 2<\/span>:<\/span>
\nPLAN_HASH_VALUE (<\/span>required)<\/span>
\n
\nInforme o valor para 2<\/span>:<\/span> 330808811<\/span>
\n
\nValues passed:<\/span>
\n~~~~~~~~~~~~~
\nSQL_ID         :<\/span> "arrf7tftg4pxg"<\/span>
\nPLAN_HASH_VALUE:<\/span> "330808811"<\/span>
\n
\n
\nExecute coe_xfr_sql_profile_arrf7tftg4pxg_330808811.<\/span>sql
\non TARGET system<\/span><\/a> in order to create a custom SQL Profile
\nwith plan 330808811<\/span> linked to adjusted sql_text.<\/span>
\n
\n
\nCOE_XFR_SQL_PROFILE completed.<\/span>
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n

.<\/p>\n

A execu\u00e7\u00e3o do script coe_xfr_sql_profile produz um outro script no diret\u00f3rio “c:\\temp” preparado para gerar uma SQL Profile, vamos executar esse script e verificar quais s\u00e3o os “Hints” utilizados por essa profile para influenciar o Otimizador na cria\u00e7\u00e3o do plano de execu\u00e7\u00e3o.<\/span><\/p>\n

.<\/p>\n\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
<\/div><\/td>
SQL>@<\/span>c:<\/span>\\temp\\coe_xfr_sql_profile_arrf7tftg4pxg_330808811.<\/span>sql;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM $Header<\/span>:<\/span> 215187.1<\/span> coe_xfr_sql_profile_arrf7tftg4pxg_330808811.<\/span>sql 11.<\/span>4.<\/span>1.<\/span>4 2016<\/span>\/<\/span>03<\/span>\/<\/span>27<\/span> csierra $
\nSQL><\/span>REM
\nSQL><\/span>REM Copyright (<\/span>c)<\/span> 2000<\/span>-<\/span>2010<\/span>,<\/span> Oracle Corporation.<\/span> All rights reserved.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM AUTHOR
\nSQL><\/span>REM   carlos.<\/span>sierra@<\/span>oracle.<\/span>com
\nSQL><\/span>REM
\nSQL><\/span>REM SCRIPT
\nSQL><\/span>REM   coe_xfr_sql_profile_arrf7tftg4pxg_330808811.<\/span>sql
\nSQL><\/span>REM
\nSQL><\/span>REM DESCRIPTION
\nSQL><\/span>REM   This script is generated by coe_xfr_sql_profile.<\/span>sql
\nSQL><\/span>REM   It contains the SQL*<\/span>Plus commands to create a custom
\nSQL><\/span>REM   SQL Profile for<\/span> SQL_ID arrf7tftg4pxg based on plan hash<\/span><\/a>
\nSQL><\/span>REM   value 330808811<\/span>.<\/span>
\nSQL><\/span>REM   The custom SQL Profile to be created by this script
\nSQL><\/span>REM   will affect plans for<\/span> SQL commands with signature
\nSQL><\/span>REM   matching the one for<\/span> SQL Text below.<\/span>
\nSQL><\/span>REM   Review SQL Text and adjust accordingly.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM PARAMETERS
\nSQL><\/span>REM   None.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM EXAMPLE
\nSQL><\/span>REM   SQL><\/span> START coe_xfr_sql_profile_arrf7tftg4pxg_330808811.<\/span>sql;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>REM NOTES
\nSQL><\/span>REM   1<\/span>.<\/span> Should be run as<\/span> SYSTEM<\/span><\/a> or SYSDBA.<\/span>
\nSQL><\/span>REM   2<\/span>.<\/span> User must have CREATE ANY SQL PROFILE privilege.<\/span>
\nSQL><\/span>REM   3<\/span>.<\/span> SOURCE and TARGET systems can be the same or similar.<\/span>
\nSQL><\/span>REM   4<\/span>.<\/span> To drop this custom SQL Profile after it has been created:<\/span>
\nSQL><\/span>REM  EXEC<\/span><\/a> DBMS_SQLTUNE.<\/span>DROP_SQL_PROFILE(<\/span>'coe_arrf7tftg4pxg_330808811'<\/span>)<\/span>;<\/span>
\nSQL><\/span>REM   5<\/span>.<\/span> Be aware that using DBMS_SQLTUNE requires a license
\nSQL><\/span>REM  for<\/span> the Oracle Tuning Pack<\/span><\/a>.<\/span>
\nSQL><\/span>REM
\nSQL><\/span>WHENEVER SQLERROR EXIT<\/span><\/a> SQL.<\/span>SQLCODE;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>VAR<\/span> signature NUMBER;<\/span>
\nSQL><\/span>REM
\nSQL><\/span>DECLARE<\/span>
\n  2<\/span>  sql_txt CLOB;<\/span>
\n  3<\/span>  h       SYS.<\/span>SQLPROF_ATTR;<\/span>
\n  4<\/span>  BEGIN
\n  5<\/span>  sql_txt :=<\/span> q'[
\n  6  select col1, col2, col3 from tab01 where col1 = 77150
\n  7  ]'<\/span>;<\/span>
\n  8<\/span>  h :=<\/span> SYS.<\/span>SQLPROF_ATTR(<\/span>
\n  9<\/span>  q'[BEGIN_OUTLINE_DATA]'<\/span>,<\/span>
\n 10<\/span>  q'[IGNORE_OPTIM_EMBEDDED_HINTS]'<\/span>,<\/span>
\n 11<\/span>  q'[OPTIMIZER_FEATURES_ENABLE('<\/span>11.<\/span>2.<\/span>0.<\/span>4')]'<\/span>,<\/span>
\n 12<\/span>  q'[DB_VERSION('<\/span>11.<\/span>2.<\/span>0.<\/span>4')]'<\/span>,<\/span>
\n 13<\/span>  q'[ALL_ROWS]'<\/span>,<\/span>
\n 14<\/span>  q'[OUTLINE_LEAF(@"SEL$1")]'<\/span>,<\/span>
\n 15<\/span>  q'[INDEX_RS_ASC(@"SEL$1" "TAB01"@"SEL$1" ("TAB01"."COL1"))]'<\/span>,<\/span>
\n 16<\/span>  q'[END_OUTLINE_DATA]'<\/span>)<\/span>;<\/span>
\n 17<\/span>  :<\/span>signature :=<\/span> DBMS_SQLTUNE.<\/span>SQLTEXT_TO_SIGNATURE(<\/span>sql_txt)<\/span>;<\/span>
\n 18<\/span>  DBMS_SQLTUNE.<\/span>IMPORT_SQL_PROFILE (<\/span>
\n 19<\/span>  sql_text    =><\/span> sql_txt,<\/span>
\n 20<\/span>  profile     =><\/span> h,<\/span>
\n 21<\/span>  name        =><\/span> 'coe_arrf7tftg4pxg_330808811'<\/span>,<\/span>
\n 22<\/span>  description =><\/span> 'coe arrf7tftg4pxg 330808811 '<\/span>||:<\/span>signature||<\/span>''<\/span>,<\/span>
\n 23<\/span>  category    =><\/span> 'DEFAULT'<\/span>,<\/span>
\n 24<\/span>  validate    =><\/span> TRUE<\/span>,<\/span>
\n 25<\/span>  replace     =><\/span> TRUE<\/span>,<\/span>
\n 26<\/span>  force_match =><\/span> FALSE<\/span> \/* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) *\/<\/span> )<\/span>;<\/span>
\n 27<\/span>  END<\/span><\/a>;<\/span>
\n 28<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL><\/span>WHENEVER SQLERROR CONTINUE<\/span>
\nSQL><\/span>SET ECHO<\/span> OFF;<\/span>
\n
\n            SIGNATURE
\n---------------------<\/span>
\n  9480737169037303819<\/span>
\n
\n
\n...<\/span> manual custom SQL Profile has been created
\n
\n
\nCOE_XFR_SQL_PROFILE_arrf7tftg4pxg_330808811 completed
\nSQL><\/span>
\nSQL><\/span> conn sys@<\/span>lab11 as<\/span> sysdba
\nInforme a senha:<\/span>
\nConectado.<\/span>
\nSQL><\/span> @<\/span>sql_profile_hints11;<\/span>
\nInforme o valor para name:<\/span> coe_arrf7tftg4pxg_330808811
\n
\nHINT
\n----------------------------------------------------------------------------------------------------<\/span>
\nBEGIN_OUTLINE_DATA
\nIGNORE_OPTIM_EMBEDDED_HINTS
\nOPTIMIZER_FEATURES_ENABLE(<\/span>'11.2.0.4'<\/span>)<\/span>
\nDB_VERSION(<\/span>'11.2.0.4'<\/span>)<\/span>
\nALL_ROWS
\nOUTLINE_LEAF(<\/span>@<\/span>"SEL$1<\/span>"<\/span>)<\/span>
\nINDEX_RS_ASC(<\/span>@<\/span>"SEL$1<\/span>"<\/span> "TAB01"<\/span>@<\/span>"SEL$1<\/span>"<\/span> (<\/span>"TAB01"<\/span>.<\/span>"COL1"<\/span>)<\/span>)<\/span>
\nEND_OUTLINE_DATA
\n
\nSQL><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n

.<\/p>\n

Observando os “Hints” utilizados na profile gerada pelo script verificamos que eles d\u00e3o instru\u00e7\u00f5es especificas para Otimizador produzir o plano de execu\u00e7\u00e3o, tais como o Hint INDEX_RS_ASC que sugere ao Otimizador para utilizar a opera\u00e7\u00e3o INDEX RANGE SCAN para acessar o \u00edndice TAB01_COL1.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

A utiliza\u00e7\u00e3o de SQL Profile no processo de Tuning de consultas abre um leque de possibilidades, seja utilizando o SQL Tuning Advisor ou o script coe_xfr_sql_profile. Esse recurso agiliza o processo de Tuning pois n\u00e3o necessita de altera\u00e7\u00e3o no c\u00f3digo da aplica\u00e7\u00e3o e o SQL Tuning Advisor ajuda no processo de Tuning pois ele tem condi\u00e7\u00f5es de reparar aqueles planos de baixa qualidade produzidos pelo Otimizador que tem um tempo extremamente limitado para analisar e gerar um plano de execu\u00e7\u00e3o para instru\u00e7\u00e3o SQL quando ela \u00e9 submetida para execu\u00e7\u00e3o no banco.<\/span><\/p>\n

Scripts<\/span><\/h2>\n

Abaixo a lista o scripts utilizados nesse artigo: <\/span><\/p>\n

find_sql<\/a> <\/span><\/strong><\/p>\n

dplan<\/a><\/span><\/strong><\/p>\n

create_tuning_task<\/a><\/span><\/strong><\/p>\n

sql_profile_hints11<\/a><\/span><\/strong><\/p>\n

coe_xfr_sql_profile<\/span><\/strong><\/a><\/p>\n

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

http:\/\/kerryosborne.oracle-guy.com\/2009\/04\/oracle-sql-profiles\/<\/a><\/span><\/strong><\/p>\n

https:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:::::P11_QUESTION_ID:22829633914543<\/a><\/span><\/strong><\/p>\n

https:\/\/carlos-sierra.net\/2012\/04\/09\/custom-sql-profile-and-plan-stability-on-10g\/<\/a><\/span><\/strong><\/p>\n

http:\/\/docs.oracle.com\/cd\/E28271_01\/server.1111\/e16638\/sql_tune.htm#i36634<\/a><\/span><\/strong><\/p>\n

\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a><\/p>\n\n\n\n

\n\n
1
<\/div><\/td>
 <\/div><\/td><\/tr><\/tbody><\/table><\/div>\n\n<\/pre>\n\n\n\n
<\/pre>\n","protected":false},"excerpt":{"rendered":"
Quantas vezes voc\u00ea encontrou uma consulta usando um \u00edndice X quando voc\u00ea queria que ela usasse o \u00edndice Y, ou uma consulta realizando “Nested loop” para fazer um “join” entre duas tabelas quando um “Hash Join” realizaria esse trabalho muito mais r\u00e1pido. Ou uma inst\u00e2ncia em que a aplica\u00e7\u00e3o de repente come\u00e7a a utilizar um plano de execu\u00e7\u00e3o ineficiente ap\u00f3s<\/p>\n","protected":false},"author":2,"featured_media":513,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[115],"tags":[6,42,10,118,117,116],"class_list":["post-392","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-profile","tag-cbo","tag-cost-base-optimizer","tag-otimizador","tag-sql-profile","tag-sql-tuning-advisor","tag-sta"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/392","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=392"}],"version-history":[{"count":16,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/392\/revisions"}],"predecessor-version":[{"id":2356,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/392\/revisions\/2356"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/513"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=392"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=392"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=392"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}