{"id":826,"date":"2016-08-21T23:07:15","date_gmt":"2016-08-22T02:07:15","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=826"},"modified":"2019-11-11T18:54:34","modified_gmt":"2019-11-11T21:54:34","slug":"sql-patch-voce-ja-usou-esse-recurso","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/sql-patch-voce-ja-usou-esse-recurso\/","title":{"rendered":"SQL Patch: Voc\u00ea j\u00e1 usou esse recurso?"},"content":{"rendered":"

Quando estamos analisando um plano de execu\u00e7\u00e3o gerado pelo pacote “DBMS_XPLAN” normalmente n\u00e3o temos as informa\u00e7\u00f5es estat\u00edsticas reais daquela execu\u00e7\u00e3o pois a disponibilidade dessas informa\u00e7\u00f5es esta condicionada a defini\u00e7\u00e3o do par\u00e2metro STATISTICS_LEVEL que na maioria das instala\u00e7\u00f5es de produ\u00e7\u00e3o \u00e9 definido como TYPICAL para evitar “OVERHEAD” na base. As informa\u00e7\u00f5es estat\u00edsticas reais do plano de execu\u00e7\u00e3o s\u00e3o muito importantes na analise do plano pois s\u00e3o elas que mostram qual ou quais opera\u00e7\u00f5es no plano s\u00e3o respons\u00e1veis pelo desempenho ruim da instru\u00e7\u00e3o SQL, para contornar esta dificuldade quando realizamos um trabalho de tuning normalmente utilizamos os seguintes recursos:<\/span><\/p>\n

1. Cria\u00e7\u00e3o de um “Test Case”: Reproduzir a instru\u00e7\u00e3o SQL com todas as suas BINDs definindo o par\u00e2metro STATISTICS_LEVEL=ALL a n\u00edvel de sess\u00e3o, com isso conseguimos gerar um plano de execu\u00e7\u00e3o com todas as estat\u00edsticas dessa instru\u00e7\u00e3o SQL. Essa tarefa em algumas situa\u00e7\u00f5es \u00e9 complexa e as vezes precisamos da ajuda do desenvolvedor da aplica\u00e7\u00e3o para realiza-la.<\/span><\/p>\n

2. Utiliza\u00e7\u00e3o do SQL MONITORING no OEM Cloud Control: Esta op\u00e7\u00e3o \u00e9 \u00f3tima mas o ambiente precisa ter as\u00a0 “Options Diagnostic and Tuning Pack” e se o tempo de execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL for\u00a0 inferior a 3 segundos, ela n\u00e3o ser\u00e1 capturada por padr\u00e3o pelo SQL MONITORING.<\/span><\/p>\n

3. Um Ambiente de Homologa\u00e7\u00e3o onde podemos alterar o par\u00e2metro STATISTICS_LEVEL=ALL a n\u00edvel de inst\u00e2ncia: Tamb\u00e9m \u00e9 uma \u00f3tima op\u00e7\u00e3o mas muitas instala\u00e7\u00f5es n\u00e3o tem um ambiente de homologa\u00e7\u00e3o onde possamos realizar as atividades de tuning.<\/span><\/p>\n

Neste artigo vamos mostrar como podemos utilizar o SQL Patch para gerar as estat\u00edsticas reais da execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL de forma simples e r\u00e1pida e sem causar impactos na produ\u00e7\u00e3o.<\/span><\/p>\n

Simulando a utiliza\u00e7\u00e3o SQL Patch<\/span><\/h2>\n

Quando ativamos a coleta de\u00a0estat\u00edsticas no\u00a0n\u00edvel m\u00e1ximo\u00a0de uma instru\u00e7\u00e3o SQL especifica, h\u00e1 um OVERHEAD adicional no processamento dessa instru\u00e7\u00e3o e o tempo de execu\u00e7\u00e3o aumenta, para evitar impactos em produ\u00e7\u00e3o devemos escolher um per\u00edodo onde o numero de execu\u00e7\u00f5es dessa instru\u00e7\u00e3o seja menor para realizar o procedimento que permitir\u00e1 a gera\u00e7\u00e3o de um plano de execu\u00e7\u00e3o com todas as informa\u00e7\u00f5es estat\u00edsticas.<\/span><\/p>\n

Para realizar nossa simula\u00e7\u00e3o vamos executar os seguintes passos:<\/span><\/p>\n

1) Executar a consulta e gerar o plano de execu\u00e7\u00e3o utilizando o pacote DBMS_XPLAN para constatar que n\u00e3o est\u00e3o dispon\u00edveis as estat\u00edsticas de execu\u00e7\u00e3o dessa consulta, numa inst\u00e2ncia onde o par\u00e2metro STATISTICS_LEVEL=TYPICAL.<\/span><\/p>\n

2) Criar um SQL Patch para o SQL_ID da nossa consulta incluindo o HINT GATHER_PLAN_STATISTICS que tem o mesmo efeito do par\u00e2metro STATISTICS_LEVEL=ALL.<\/span><\/p>\n

3) Executar novamente a consulta e gerar o plano de execu\u00e7\u00e3o utilizando o pacote DBMS_XPLAN que dessa vez vai gerar um plano com as estat\u00edsticas de execu\u00e7\u00e3o da consulta.<\/span><\/p>\n

4) Eliminar o SQL Patch criado.<\/span><\/p>\n

1) Plano de execu\u00e7\u00e3o padr\u00e3o<\/span><\/h3>\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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
<\/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
\n
\nSQL ><\/span> SELECT \/* DBTW01 *\/<\/span> country_name,<\/span>
\n  2<\/span>         cust_income_level,<\/span>
\n  3<\/span>         count<\/span><\/a>(<\/span>country_name)<\/span> country_cust_count
\n  4<\/span>    FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n  5<\/span>                 a.<\/span>country_name
\n  6<\/span>            FROM sh.<\/span>customers b
\n  7<\/span>            JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span> c
\n  8<\/span>  HAVING count<\/span><\/a>(<\/span>country_name)<\/span> ><\/span> (<\/span>SELECT count<\/span><\/a>(<\/span>*<\/span>)<\/span> *<\/span> .01<\/span> FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n  9<\/span>                                                                   a.<\/span>country_name
\n 10<\/span>           FROM sh.<\/span>customers b
\n 11<\/span>           JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span> c2)<\/span>
\n 12<\/span>      OR count<\/span><\/a>(<\/span>cust_income_level)<\/span> >=<\/span> (<\/span>SELECT median(<\/span>income_level_count)<\/span>
\n 13<\/span>                                        FROM (<\/span>SELECT cust_income_level,<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> *<\/span>.25<\/span> income_level_count
\n 14<\/span>        FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n 15<\/span>                       a.<\/span>country_name
\n 16<\/span>             FROM sh.<\/span>customers b
\n 17<\/span>             JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span>
\n 18<\/span>       GROUP BY cust_income_level)<\/span>
\n 19<\/span>                                     )<\/span>
\n 20<\/span>   GROUP BY country_name,<\/span> cust_income_level
\n 21<\/span>   ORDER BY 1<\/span>,<\/span>2<\/span>;<\/span>
\n
\nCOUNTRY_NAME                             CUST_INCOME_LEVEL              COUNTRY_CUST_COUNT
\n----------------------------------------<\/span> ------------------------------<\/span> ------------------<\/span>
\nFrance                                   E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                           585<\/span>
\nFrance                                   F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                          651<\/span>
\nGermany                                  C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                            638<\/span>
\nGermany                                  D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                            844<\/span>
\nGermany                                  E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          1261<\/span>
\nGermany                                  F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         1469<\/span>
\nGermany                                  G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                          843<\/span>
\nGermany                                  H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                          850<\/span>
\nGermany                                  I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                          658<\/span>
\nItaly                                    A:<\/span> Below 30<\/span>,<\/span>000<\/span>                               600<\/span>
\nItaly                                    C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                            659<\/span>
\nItaly                                    D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                            700<\/span>
\nItaly                                    E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          1074<\/span>
\nItaly                                    F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         1851<\/span>
\nItaly                                    G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                          657<\/span>
\nItaly                                    H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                          715<\/span>
\nItaly                                    I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                          714<\/span>
\nUnited Kingdom                           C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                            611<\/span>
\nUnited Kingdom                           D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                            738<\/span>
\nUnited Kingdom                           E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          1165<\/span>
\nUnited Kingdom                           F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         1573<\/span>
\nUnited Kingdom                           G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                          628<\/span>
\nUnited Kingdom                           H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                          734<\/span>
\nUnited Kingdom                           I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                          727<\/span>
\nUnited States of America                 B:<\/span> 30<\/span>,<\/span>000<\/span> -<\/span> 49<\/span>,<\/span>999<\/span>                           1054<\/span>
\nUnited States of America                 C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                           1272<\/span>
\nUnited States of America                 D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                           1641<\/span>
\nUnited States of America                 E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          2525<\/span>
\nUnited States of America                 F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         2837<\/span>
\nUnited States of America                 G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                         2004<\/span>
\nUnited States of America                 H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                         1857<\/span>
\nUnited States of America                 I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                         1395<\/span>
\nUnited States of America                 J:<\/span> 190<\/span>,<\/span>000<\/span> -<\/span> 249<\/span>,<\/span>999<\/span>                         1390<\/span>
\nUnited States of America                 K:<\/span> 250<\/span>,<\/span>000<\/span> -<\/span> 299<\/span>,<\/span>999<\/span>                         1062<\/span>
\nUnited States of America                 L:<\/span> 300<\/span>,<\/span>000<\/span> and above                          982<\/span>
\n
\n35<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.23<\/span>
\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 '%DBTW01%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n97qppk7bs52xn            0<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/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>'allstats 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>'allstats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'97qppk7bs52xn'<\/span>,<\/span>         0<\/span>,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  97qppk7bs52xn,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW01 *\/<\/span> country_name,<\/span>        cust_income_level,<\/span>
\ncount<\/span><\/a>(<\/span>country_name)<\/span> country_cust_count   FROM (<\/span>SELECT
\nb.<\/span>cust_income_level,<\/span>                a.<\/span>country_name           FROM
\nsh.<\/span>customers b           JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span>
\nb.<\/span>country_id)<\/span> c HAVING count<\/span><\/a>(<\/span>country_name)<\/span> ><\/span> (<\/span>SELECT count<\/span><\/a>(<\/span>*<\/span>)<\/span> *<\/span>
\n:<\/span>"SYS_B_0"<\/span> FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n                                      a.<\/span>country_name          FROM
\nsh.<\/span>customers b          JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span>
\nb.<\/span>country_id)<\/span> c2)<\/span>     OR count<\/span><\/a>(<\/span>cust_income_level)<\/span> >=<\/span> (<\/span>SELECT
\nmedian(<\/span>income_level_count)<\/span>                                       FROM
\n(<\/span>SELECT cust_income_level,<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> *:<\/span>"SYS_B_1"<\/span> income_level_count
\n FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>                      a.<\/span>country_name
\n          FROM sh.<\/span>customers b            JOIN<\/span><\/a> sh.<\/span>countries a ON
\na.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span>      GROUP BY cust_income_level)<\/span>
\n                         )<\/span>  GROUP BY country_name,<\/span> cust_income_level
\nORDE
\n
\nPlan hash<\/span><\/a> value:<\/span> 1730152010<\/span>
\n
\n--------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                 |<\/span> Name         |<\/span> E-<\/span>Rows |<\/span>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n--------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT          |<\/span>              |<\/span>        |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY            |<\/span>              |<\/span>     20<\/span> |<\/span>  6144<\/span> |<\/span>  6144<\/span> |<\/span> 6144<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER                  |<\/span>              |<\/span>        |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>    HASH<\/span><\/a> GROUP BY          |<\/span>              |<\/span>     20<\/span> |<\/span>  6890K|<\/span>  2507K|<\/span> 2516K (<\/span>0<\/span>)<\/span>|<\/span>
\n|*<\/span>  4<\/span> |<\/span>     HASH<\/span><\/a> JOIN<\/span><\/a>             |<\/span>              |<\/span>  55500<\/span> |<\/span>  1519K|<\/span>  1519K|<\/span> 1568K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   5<\/span> |<\/span>      TABLE ACCESS FULL    |<\/span> COUNTRIES    |<\/span>     23<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   6<\/span> |<\/span>      TABLE ACCESS FULL    |<\/span> CUSTOMERS    |<\/span>  55500<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   7<\/span> |<\/span>    SORT<\/span><\/a> AGGREGATE<\/span><\/a>         |<\/span>              |<\/span>      1<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span>  8<\/span> |<\/span>     HASH<\/span><\/a> JOIN<\/span><\/a>             |<\/span>              |<\/span>  55500<\/span> |<\/span>  2168K|<\/span>  2168K|<\/span> 1571K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   9<\/span> |<\/span>      INDEX FULL SCAN      |<\/span> COUNTRIES_PK |<\/span>     23<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  10<\/span> |<\/span>      TABLE ACCESS FULL    |<\/span> CUSTOMERS    |<\/span>  55500<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  11<\/span> |<\/span>    SORT<\/span><\/a> GROUP BY          |<\/span>              |<\/span>      1<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>  12<\/span> |<\/span>     VIEW                  |<\/span>              |<\/span>     12<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  13<\/span> |<\/span>      SORT<\/span><\/a> GROUP BY        |<\/span>              |<\/span>     12<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>  14<\/span> |<\/span>       NESTED LOOPS        |<\/span>              |<\/span>    162<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  15<\/span> |<\/span>        VIEW               |<\/span> VW_GBF_8     |<\/span>    162<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  16<\/span> |<\/span>         SORT<\/span><\/a> GROUP BY     |<\/span>              |<\/span>    162<\/span> |<\/span> 22528<\/span> |<\/span> 22528<\/span> |<\/span>20480<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>  17<\/span> |<\/span>          TABLE ACCESS FULL|<\/span> CUSTOMERS    |<\/span>  55500<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span> 18<\/span> |<\/span>        INDEX UNIQUE SCAN  |<\/span> COUNTRIES_PK |<\/span>      1<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n--------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>><\/span> OR COUNT<\/span><\/a>(<\/span>"B"<\/span>.<\/span>"CUST_INCOME_LEVEL"<\/span>)<\/span>>=<\/span>)<\/span>)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"B"<\/span>.<\/span>"COUNTRY_ID"<\/span>)<\/span>
\n   8<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"B"<\/span>.<\/span>"COUNTRY_ID"<\/span>)<\/span>
\n  18<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"ITEM_1"<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\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
\n59<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.78<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na console acima executamos uma consulta no esquema SH e em seguida geramos o plano de execu\u00e7\u00e3o com o pacote DBMS_XPLAN, como podemos observar esta plano n\u00e3o informa os tempos de execu\u00e7\u00e3o de cada opera\u00e7\u00e3o e o numero de linhas selecionado em cada opera\u00e7\u00e3o \u00e9 apenas uma estimativa, n\u00e3o temos a quantidade real de linhas selecionadas em cada opera\u00e7\u00e3o. Dessa forma fica muito dif\u00edcil identificar qual parte do plano de execu\u00e7\u00e3o esta levando mais tempo para ser executada.<\/span><\/p>\n

2) Adicionando o HINT GATHER_PLAN_STATISTICS<\/span><\/h3>\n

Para gerar as estat\u00edsticas mais completas da execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL\u00a0e conseguir exibi-las no plano de execu\u00e7\u00e3o utilizando\u00a0o pacote DBMS_XPLAN, podemos adicionar o HINT GATHER_PLAN_STATISTICS sem alterar o c\u00f3digo da aplica\u00e7\u00e3o utilizando o recurso SQL Patch, a seguir vamos ver como podemos fazer isso:<\/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
<\/div><\/td>
SQL ><\/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 '%DBTW01%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n97qppk7bs52xn            0<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> set serveroutput on size 9999<\/span>
\nSQL ><\/span>
\nSQL ><\/span> declare<\/span>
\n  2<\/span>      m_clob  clob;<\/span>
\n  3<\/span>  begin
\n  4<\/span>      select
\n  5<\/span>          sql_fulltext
\n  6<\/span>      into
\n  7<\/span>          m_clob
\n  8<\/span>      from
\n  9<\/span>          v$sql<\/span>
\n 10<\/span>      where
\n 11<\/span>          sql_id =<\/span> '&m_sql_id'<\/span>
\n 12<\/span>      and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\n 13<\/span>
\n 14<\/span>      sys.<\/span>dbms_sqldiag_internal.<\/span>i_create_patch(<\/span>
\n 15<\/span>          sql_text    =><\/span> m_clob,<\/span>
\n 16<\/span>          hint_text   =><\/span> 'GATHER_PLAN_STATISTICS'<\/span>,<\/span>
\n 17<\/span>          name        =><\/span> 'Patch_&m_sql_id'<\/span>
\n 18<\/span>          )<\/span>;<\/span>
\n 19<\/span>  end<\/span><\/a>;<\/span>
\n 20<\/span>  \/<\/span>
\nantigo  11<\/span>:<\/span>         sql_id =<\/span> '&m_sql_id'<\/span>
\nnovo  11<\/span>:<\/span>         sql_id =<\/span> '97qppk7bs52xn'<\/span>
\nantigo  12<\/span>:<\/span>     and child_number =<\/span> &<\/span>m_child_no ;<\/span>
\nnovo  12<\/span>:<\/span>     and child_number =<\/span>          0<\/span> ;<\/span>
\nantigo  17<\/span>:<\/span>         name        =><\/span> 'Patch_&m_sql_id'<\/span>
\nnovo  17<\/span>:<\/span>         name        =><\/span> 'Patch_97qppk7bs52xn'<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>02.15<\/span>
\nSQL ><\/span>
\nSQL ><\/span> set lines 200<\/span>
\nSQL ><\/span> select NAME,<\/span> CREATED,<\/span>  SQL_TEXT from DBA_SQL_PATCHES;<\/span>
\n
\nNAME                           CREATED                        SQL_TEXT
\n------------------------------<\/span> ------------------------------<\/span> --------------------------------------------------------------------------------<\/span>
\nPatch_97qppk7bs52xn            12<\/span>\/<\/span>08<\/span>\/<\/span>16<\/span> 22<\/span>:<\/span>49<\/span>:<\/span>45<\/span>,<\/span>000000<\/span>       SELECT \/* DBTW01 *\/<\/span> country_name,<\/span>
\n                                                                     cust_income_level,<\/span>
\n                                                                     count<\/span><\/a>(<\/span>country
\n
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

3) Plano de execu\u00e7\u00e3o com todas as estat\u00edsticas<\/span><\/h3>\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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
<\/div><\/td>
SQL ><\/span> SELECT \/* DBTW01 *\/<\/span> country_name,<\/span>
\n  2<\/span>         cust_income_level,<\/span>
\n  3<\/span>         count<\/span><\/a>(<\/span>country_name)<\/span> country_cust_count
\n  4<\/span>    FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n  5<\/span>                 a.<\/span>country_name
\n  6<\/span>            FROM sh.<\/span>customers b
\n  7<\/span>            JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span> c
\n  8<\/span>  HAVING count<\/span><\/a>(<\/span>country_name)<\/span> ><\/span> (<\/span>SELECT count<\/span><\/a>(<\/span>*<\/span>)<\/span> *<\/span> .01<\/span> FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n  9<\/span>                                                                   a.<\/span>country_name
\n 10<\/span>           FROM sh.<\/span>customers b
\n 11<\/span>           JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span> c2)<\/span>
\n 12<\/span>      OR count<\/span><\/a>(<\/span>cust_income_level)<\/span> >=<\/span> (<\/span>SELECT median(<\/span>income_level_count)<\/span>
\n 13<\/span>                                        FROM (<\/span>SELECT cust_income_level,<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> *<\/span>.25<\/span> income_level_count
\n 14<\/span>        FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n 15<\/span>                       a.<\/span>country_name
\n 16<\/span>             FROM sh.<\/span>customers b
\n 17<\/span>             JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span>
\n 18<\/span>       GROUP BY cust_income_level)<\/span>
\n 19<\/span>                                     )<\/span>
\n 20<\/span>   GROUP BY country_name,<\/span> cust_income_level
\n 21<\/span>   ORDER BY 1<\/span>,<\/span>2<\/span>;<\/span>
\n
\nCOUNTRY_NAME                             CUST_INCOME_LEVEL              COUNTRY_CUST_COUNT
\n----------------------------------------<\/span> ------------------------------<\/span> ------------------<\/span>
\nFrance                                   E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                           585<\/span>
\nFrance                                   F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                          651<\/span>
\nGermany                                  C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                            638<\/span>
\nGermany                                  D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                            844<\/span>
\nGermany                                  E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          1261<\/span>
\nGermany                                  F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         1469<\/span>
\nGermany                                  G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                          843<\/span>
\nGermany                                  H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                          850<\/span>
\nGermany                                  I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                          658<\/span>
\nItaly                                    A:<\/span> Below 30<\/span>,<\/span>000<\/span>                               600<\/span>
\nItaly                                    C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                            659<\/span>
\nItaly                                    D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                            700<\/span>
\nItaly                                    E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          1074<\/span>
\nItaly                                    F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         1851<\/span>
\nItaly                                    G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                          657<\/span>
\nItaly                                    H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                          715<\/span>
\nItaly                                    I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                          714<\/span>
\nUnited Kingdom                           C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                            611<\/span>
\nUnited Kingdom                           D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                            738<\/span>
\nUnited Kingdom                           E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          1165<\/span>
\nUnited Kingdom                           F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         1573<\/span>
\nUnited Kingdom                           G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                          628<\/span>
\nUnited Kingdom                           H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                          734<\/span>
\nUnited Kingdom                           I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                          727<\/span>
\nUnited States of America                 B:<\/span> 30<\/span>,<\/span>000<\/span> -<\/span> 49<\/span>,<\/span>999<\/span>                           1054<\/span>
\nUnited States of America                 C:<\/span> 50<\/span>,<\/span>000<\/span> -<\/span> 69<\/span>,<\/span>999<\/span>                           1272<\/span>
\nUnited States of America                 D:<\/span> 70<\/span>,<\/span>000<\/span> -<\/span> 89<\/span>,<\/span>999<\/span>                           1641<\/span>
\nUnited States of America                 E:<\/span> 90<\/span>,<\/span>000<\/span> -<\/span> 109<\/span>,<\/span>999<\/span>                          2525<\/span>
\nUnited States of America                 F:<\/span> 110<\/span>,<\/span>000<\/span> -<\/span> 129<\/span>,<\/span>999<\/span>                         2837<\/span>
\nUnited States of America                 G:<\/span> 130<\/span>,<\/span>000<\/span> -<\/span> 149<\/span>,<\/span>999<\/span>                         2004<\/span>
\nUnited States of America                 H:<\/span> 150<\/span>,<\/span>000<\/span> -<\/span> 169<\/span>,<\/span>999<\/span>                         1857<\/span>
\nUnited States of America                 I:<\/span> 170<\/span>,<\/span>000<\/span> -<\/span> 189<\/span>,<\/span>999<\/span>                         1395<\/span>
\nUnited States of America                 J:<\/span> 190<\/span>,<\/span>000<\/span> -<\/span> 249<\/span>,<\/span>999<\/span>                         1390<\/span>
\nUnited States of America                 K:<\/span> 250<\/span>,<\/span>000<\/span> -<\/span> 299<\/span>,<\/span>999<\/span>                         1062<\/span>
\nUnited States of America                 L:<\/span> 300<\/span>,<\/span>000<\/span> and above                          982<\/span>
\n
\n35<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.23<\/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 '%DBTW01%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n97qppk7bs52xn            0<\/span>
\n97qppk7bs52xn            1<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/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>'allstats 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>'allstats last'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'97qppk7bs52xn'<\/span>,<\/span>         1<\/span>,<\/span>'allstats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  97qppk7bs52xn,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW01 *\/<\/span> country_name,<\/span>        cust_income_level,<\/span>
\ncount<\/span><\/a>(<\/span>country_name)<\/span> country_cust_count   FROM (<\/span>SELECT
\nb.<\/span>cust_income_level,<\/span>                a.<\/span>country_name           FROM
\nsh.<\/span>customers b           JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span>
\nb.<\/span>country_id)<\/span> c HAVING count<\/span><\/a>(<\/span>country_name)<\/span> ><\/span> (<\/span>SELECT count<\/span><\/a>(<\/span>*<\/span>)<\/span> *<\/span>
\n:<\/span>"SYS_B_0"<\/span> FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>
\n                                      a.<\/span>country_name          FROM
\nsh.<\/span>customers b          JOIN<\/span><\/a> sh.<\/span>countries a ON a.<\/span>country_id =<\/span>
\nb.<\/span>country_id)<\/span> c2)<\/span>     OR count<\/span><\/a>(<\/span>cust_income_level)<\/span> >=<\/span> (<\/span>SELECT
\nmedian(<\/span>income_level_count)<\/span>                                       FROM
\n(<\/span>SELECT cust_income_level,<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> *:<\/span>"SYS_B_1"<\/span> income_level_count
\n FROM (<\/span>SELECT b.<\/span>cust_income_level,<\/span>                      a.<\/span>country_name
\n          FROM sh.<\/span>customers b            JOIN<\/span><\/a> sh.<\/span>countries a ON
\na.<\/span>country_id =<\/span> b.<\/span>country_id)<\/span>      GROUP BY cust_income_level)<\/span>
\n                         )<\/span>  GROUP BY country_name,<\/span> cust_income_level
\nORDE
\n
\nPlan hash<\/span><\/a> value:<\/span> 1730152010<\/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>  OMem |<\/span>  1Mem |<\/span> Used-<\/span>Mem |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT          |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>     35<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    4375<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> ORDER BY            |<\/span>              |<\/span>      1<\/span> |<\/span>     20<\/span> |<\/span>     35<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    4375<\/span> |<\/span>  6144<\/span> |<\/span>  6144<\/span> |<\/span> 6144<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   FILTER                  |<\/span>              |<\/span>      1<\/span> |<\/span>        |<\/span>     35<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.09<\/span> |<\/span>    4375<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>    HASH<\/span><\/a> GROUP BY          |<\/span>              |<\/span>      1<\/span> |<\/span>     20<\/span> |<\/span>    236<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.04<\/span> |<\/span>    1458<\/span> |<\/span>  6890K|<\/span>  2507K|<\/span> 2522K (<\/span>0<\/span>)<\/span>|<\/span>
\n|*<\/span>  4<\/span> |<\/span>     HASH<\/span><\/a> JOIN<\/span><\/a>             |<\/span>              |<\/span>      1<\/span> |<\/span>  55500<\/span> |<\/span>  55500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1458<\/span> |<\/span>  1519K|<\/span>  1519K|<\/span> 1526K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   5<\/span> |<\/span>      TABLE ACCESS FULL    |<\/span> COUNTRIES    |<\/span>      1<\/span> |<\/span>     23<\/span> |<\/span>     23<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   6<\/span> |<\/span>      TABLE ACCESS FULL    |<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>  55500<\/span> |<\/span>  55500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1456<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>   7<\/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>    1457<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span>  8<\/span> |<\/span>     HASH<\/span><\/a> JOIN<\/span><\/a>             |<\/span>              |<\/span>      1<\/span> |<\/span>  55500<\/span> |<\/span>  55500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1457<\/span> |<\/span>  2168K|<\/span>  2168K|<\/span> 1526K (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>   9<\/span> |<\/span>      INDEX FULL SCAN      |<\/span> COUNTRIES_PK |<\/span>      1<\/span> |<\/span>     23<\/span> |<\/span>     23<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       1<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  10<\/span> |<\/span>      TABLE ACCESS FULL    |<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>  55500<\/span> |<\/span>  55500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1456<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  11<\/span> |<\/span>    SORT<\/span><\/a> GROUP BY          |<\/span>              |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1460<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>  12<\/span> |<\/span>     VIEW                  |<\/span>              |<\/span>      1<\/span> |<\/span>     12<\/span> |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1460<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  13<\/span> |<\/span>      SORT<\/span><\/a> GROUP BY        |<\/span>              |<\/span>      1<\/span> |<\/span>     12<\/span> |<\/span>     13<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1460<\/span> |<\/span>  2048<\/span> |<\/span>  2048<\/span> |<\/span> 2048<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>  14<\/span> |<\/span>       NESTED LOOPS        |<\/span>              |<\/span>      1<\/span> |<\/span>    162<\/span> |<\/span>    236<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1460<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  15<\/span> |<\/span>        VIEW               |<\/span> VW_GBF_8     |<\/span>      1<\/span> |<\/span>    162<\/span> |<\/span>    236<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1456<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|<\/span>  16<\/span> |<\/span>         SORT<\/span><\/a> GROUP BY     |<\/span>              |<\/span>      1<\/span> |<\/span>    162<\/span> |<\/span>    236<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.03<\/span> |<\/span>    1456<\/span> |<\/span> 22528<\/span> |<\/span> 22528<\/span> |<\/span>20480<\/span>  (<\/span>0<\/span>)<\/span>|<\/span>
\n|<\/span>  17<\/span> |<\/span>          TABLE ACCESS FULL|<\/span> CUSTOMERS    |<\/span>      1<\/span> |<\/span>  55500<\/span> |<\/span>  55500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1456<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n|*<\/span> 18<\/span> |<\/span>        INDEX UNIQUE SCAN  |<\/span> COUNTRIES_PK |<\/span>    236<\/span> |<\/span>      1<\/span> |<\/span>    236<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>       |<\/span>       |<\/span>          |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>(<\/span>COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>><\/span> OR COUNT<\/span><\/a>(<\/span>"B"<\/span>.<\/span>"CUST_INCOME_LEVEL"<\/span>)<\/span>>=<\/span>)<\/span>)<\/span>
\n   4<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"B"<\/span>.<\/span>"COUNTRY_ID"<\/span>)<\/span>
\n   8<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"B"<\/span>.<\/span>"COUNTRY_ID"<\/span>)<\/span>
\n  18<\/span> -<\/span> access(<\/span>"A"<\/span>.<\/span>"COUNTRY_ID"<\/span>=<\/span>"ITEM_1"<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL patch "Patch_97qppk7bs52xn"<\/span> used for<\/span> this statement
\n
\n
\n57<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.32<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Comparando o plano de execu\u00e7\u00e3o acima com o plano gerado antes da inclus\u00e3o do HINT, podemos observar que algumas colunas de informa\u00e7\u00e3o foram adicionadas ao plano: STARTS, A-ROWS, A-TIME e BUFFERS. Todas essas informa\u00e7\u00f5es s\u00e3o da execu\u00e7\u00e3o real do plano e nos permitem entender mais facilmente onde o plano de execu\u00e7\u00e3o esta levando mais tempo para ser executado.<\/span><\/p>\n

4) Eliminar o SQL Patch<\/span><\/h3>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
<\/div><\/td>
SQL ><\/span> BEGIN
\n  2<\/span>    sys.<\/span>DBMS_SQLDIAG.<\/span>drop_sql_patch(<\/span>name =><\/span> 'Patch_97qppk7bs52xn'<\/span>)<\/span>;<\/span>
\n  3<\/span>  END<\/span><\/a>;<\/span>
\n  4<\/span>  \/<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01.19<\/span>
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

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

O recurso SQL Patch \u00e9 muito poderoso pois permite a adi\u00e7\u00e3o de HINTs numa instru\u00e7\u00e3o SQL sem a altera\u00e7\u00e3o de uma linha de c\u00f3digo na aplica\u00e7\u00e3o, neste artigo apresentamos um exemplo de utiliza\u00e7\u00e3o desse recurso que nos permite agilizar o trabalho de Tuning fazendo com que o plano de execu\u00e7\u00e3o apresente mais informa\u00e7\u00f5es estat\u00edsticas, no entanto existem outras situa\u00e7\u00f5es onde podemos utilizar esse fant\u00e1stico recurso.<\/span><\/p>\n

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

https:\/\/orastory.wordpress.com\/2012\/03\/06\/sql-patch-ii\/<\/a><\/span><\/strong><\/p>\n

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

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

Quando estamos analisando um plano de execu\u00e7\u00e3o gerado pelo pacote “DBMS_XPLAN” normalmente n\u00e3o temos as informa\u00e7\u00f5es estat\u00edsticas reais daquela execu\u00e7\u00e3o pois a disponibilidade dessas informa\u00e7\u00f5es esta condicionada a defini\u00e7\u00e3o do par\u00e2metro STATISTICS_LEVEL que na maioria das instala\u00e7\u00f5es de produ\u00e7\u00e3o \u00e9 definido como TYPICAL para evitar “OVERHEAD” na base. As informa\u00e7\u00f5es estat\u00edsticas reais do plano de execu\u00e7\u00e3o s\u00e3o muito importantes na<\/p>\n","protected":false},"author":2,"featured_media":834,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[152],"tags":[7,155,154,153],"class_list":["post-826","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-patch","tag-dbms_xplan","tag-estatisticas-do-plano-de-execucao","tag-execution-plan-statistics","tag-sql-patch"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/826","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=826"}],"version-history":[{"count":19,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/826\/revisions"}],"predecessor-version":[{"id":2091,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/826\/revisions\/2091"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/834"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=826"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}