{"id":2005,"date":"2019-05-31T23:31:25","date_gmt":"2019-06-01T02:31:25","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2005"},"modified":"2019-11-11T18:31:37","modified_gmt":"2019-11-11T21:31:37","slug":"por-que-minha-consulta-esta-alternando-planos-de-execucao-diferentes","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/por-que-minha-consulta-esta-alternando-planos-de-execucao-diferentes\/","title":{"rendered":"Por que minha consulta esta alternando planos de execu\u00e7\u00e3o diferentes?"},"content":{"rendered":"

A utiliza\u00e7\u00e3o de BIND VARIABLE \u00e9 muito recomendada quando enviamos instru\u00e7\u00f5es SQL para execu\u00e7\u00e3o no banco de dados Oracle pois esta pr\u00e1tica evita que o Otimizador tenha que realizar um quantidade excessiva de HARD PARSE o que evita o consumo desnecess\u00e1rio de CPU. No entanto quando utilizamos BIND VARIABLE num filtro da cl\u00e1usula WHERE cuja coluna da tabela possui um HISTOGRAM<\/strong><\/a>, esta combina\u00e7\u00e3o pode levar o Otimizador a criar planos de execu\u00e7\u00e3o diferentes para garantir o desempenho da instru\u00e7\u00e3o SQL dependendo do valor passado na BIND VARIABLE.<\/span><\/p>\n

Este recurso do Oracle \u00e9 conhecido como ADAPTIVE CURSOR SHARING e neste artigo vamos simular este mecanismo para que voc\u00ea entenda como ele funciona.<\/span><\/p>\n

Para realizar esta simula\u00e7\u00e3o vamos seguir as etapas abaixo:<\/span><\/p>\n

1) Criar os objetos que ser\u00e3o utilizados na simula\u00e7\u00e3o<\/span><\/strong>
\n2) Executar uma consulta e verificar o processo de PARSE<\/span><\/strong>
\n3) Executar a mesma consulta com valor diferente na BIND VARIABLE<\/span><\/strong>
\n4) Executar a mesma consulta com um terceiro valor na BIND VARIABLE e verificar o processo de PARSE<\/span><\/strong>
\n5) Executar a mesma consulta com o valor da primeira execu\u00e7\u00e3o na BIND VARIABLE e verificar o processo de PARSE<\/span><\/strong><\/p>\n

1) Criar os objetos para a simula\u00e7\u00e3o<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o e coletar 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
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER                                                                               CON_ID
\n--------------------------------------------------------------------------------<\/span> ----------<\/span>
\nOracle Database 12c Enterprise Edition Release 12.<\/span>2.<\/span>0.<\/span>1.<\/span>0 -<\/span> 64bit Production              0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> drop table dbtw060 purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw060 as<\/span>
\n  2<\/span>  select case<\/span> when level <=<\/span> 99800<\/span> then 0<\/span> else<\/span> level-<\/span>99800<\/span> end<\/span><\/a> cod,<\/span>
\n  3<\/span>         'DBTimeWizard - Oracle Performance and Tuning'<\/span> as<\/span> prod
\n  4<\/span>            from dual
\n  5<\/span>         connect by level <=<\/span> 100000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw060_idx on dbtw060(<\/span>cod)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>user,<\/span> 'DBTW060'<\/span>,<\/span> method_opt =><\/span> 'for columns cod size 254'<\/span>,<\/span> cascade =><\/span> true<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> O histograma mostra como o Oracle fez esta distribui\u00e7\u00e3o de dados:<\/span>
\nSQL ><\/span>
\nSQL ><\/span> col COLUMN_NAME for<\/span> a12
\nSQL ><\/span> col ENDPOINT_ACTUAL_VALUE for<\/span> a12
\nSQL ><\/span>
\nSQL ><\/span> select column_name,<\/span>
\n  2<\/span>         endpoint_number,<\/span>
\n  3<\/span>         endpoint_value,<\/span>
\n  4<\/span>         endpoint_number -<\/span> nvl(<\/span>endpoint_number_prev,<\/span>0<\/span>)<\/span> row_count
\n  5<\/span>    from (<\/span>select column_name,<\/span> endpoint_number,<\/span> endpoint_value,<\/span> lag(<\/span>endpoint_number,<\/span>1<\/span>)<\/span> over (<\/span>order by endpoint_number)<\/span>  endpoint_number_prev
\n  6<\/span>            from user_tab_histograms
\n  7<\/span>           where table_name=<\/span>'DBTW060'<\/span>
\n  8<\/span>             and column_name=<\/span>'COD'<\/span>)<\/span>
\n  9<\/span>   order by endpoint_value;<\/span>
\n
\nCOLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE  ROW_COUNT
\n------------<\/span> ---------------<\/span> --------------<\/span> ----------<\/span>
\nCOD                    99800<\/span>              0<\/span>      99800<\/span>
\nCOD                    99801<\/span>              1<\/span>          1<\/span>
\nCOD                    99802<\/span>              2<\/span>          1<\/span>
\nCOD                    99803<\/span>              3<\/span>          1<\/span>
\nCOD                    99804<\/span>              4<\/span>          1<\/span>
\nCOD                    99805<\/span>              5<\/span>          1<\/span>
\nCOD                    99806<\/span>              6<\/span>          1<\/span>
\nCOD                    99807<\/span>              7<\/span>          1<\/span>
\nCOD                    99808<\/span>              8<\/span>          1<\/span>
\nCOD                    99809<\/span>              9<\/span>          1<\/span>
\nCOD                    99810<\/span>             10<\/span>          1<\/span>
\nCOD                    99811<\/span>             11<\/span>          1<\/span>
\nCOD                    99812<\/span>             12<\/span>          1<\/span>
\nCOD                    99813<\/span>             13<\/span>          1<\/span>
\nCOD                    99814<\/span>             14<\/span>          1<\/span>
\nCOD                    99815<\/span>             15<\/span>          1<\/span>
\nCOD                    99816<\/span>             16<\/span>          1<\/span>
\nCOD                    99817<\/span>             17<\/span>          1<\/span>
\nCOD                    99818<\/span>             18<\/span>          1<\/span>
\nCOD                    99819<\/span>             19<\/span>          1<\/span>
\nCOD                    99820<\/span>             20<\/span>          1<\/span>
\nCOD                    99821<\/span>             21<\/span>          1<\/span>
\nCOD                    99822<\/span>             22<\/span>          1<\/span>
\nCOD                    99823<\/span>             23<\/span>          1<\/span>
\nCOD                    99824<\/span>             24<\/span>          1<\/span>
\nCOD                    99825<\/span>             25<\/span>          1<\/span>
\nCOD                    99826<\/span>             26<\/span>          1<\/span>
\nCOD                    99827<\/span>             27<\/span>          1<\/span>
\nCOD                    99828<\/span>             28<\/span>          1<\/span>
\nCOD                    99829<\/span>             29<\/span>          1<\/span>
\nCOD                    99830<\/span>             30<\/span>          1<\/span>
\nCOD                    99831<\/span>             31<\/span>          1<\/span>
\nCOD                    99832<\/span>             32<\/span>          1<\/span>
\nCOD                    99833<\/span>             33<\/span>          1<\/span>
\nCOD                    99834<\/span>             34<\/span>          1<\/span>
\nCOD                    99835<\/span>             35<\/span>          1<\/span>
\nCOD                    99836<\/span>             36<\/span>          1<\/span>
\nCOD                    99837<\/span>             37<\/span>          1<\/span>
\nCOD                    99838<\/span>             38<\/span>          1<\/span>
\nCOD                    99839<\/span>             39<\/span>          1<\/span>
\nCOD                    99840<\/span>             40<\/span>          1<\/span>
\nCOD                    99841<\/span>             41<\/span>          1<\/span>
\nCOD                    99842<\/span>             42<\/span>          1<\/span>
\nCOD                    99843<\/span>             43<\/span>          1<\/span>
\nCOD                    99844<\/span>             44<\/span>          1<\/span>
\nCOD                    99845<\/span>             45<\/span>          1<\/span>
\nCOD                    99846<\/span>             46<\/span>          1<\/span>
\nCOD                    99847<\/span>             47<\/span>          1<\/span>
\nCOD                    99848<\/span>             48<\/span>          1<\/span>
\nCOD                    99849<\/span>             49<\/span>          1<\/span>
\nCOD                    99850<\/span>             50<\/span>          1<\/span>
\nCOD                    99851<\/span>             51<\/span>          1<\/span>
\nCOD                    99852<\/span>             52<\/span>          1<\/span>
\nCOD                    99853<\/span>             53<\/span>          1<\/span>
\nCOD                    99854<\/span>             54<\/span>          1<\/span>
\nCOD                    99855<\/span>             55<\/span>          1<\/span>
\nCOD                    99856<\/span>             56<\/span>          1<\/span>
\nCOD                    99857<\/span>             57<\/span>          1<\/span>
\nCOD                    99858<\/span>             58<\/span>          1<\/span>
\nCOD                    99859<\/span>             59<\/span>          1<\/span>
\nCOD                    99860<\/span>             60<\/span>          1<\/span>
\nCOD                    99861<\/span>             61<\/span>          1<\/span>
\nCOD                    99862<\/span>             62<\/span>          1<\/span>
\nCOD                    99863<\/span>             63<\/span>          1<\/span>
\nCOD                    99864<\/span>             64<\/span>          1<\/span>
\nCOD                    99865<\/span>             65<\/span>          1<\/span>
\nCOD                    99866<\/span>             66<\/span>          1<\/span>
\nCOD                    99867<\/span>             67<\/span>          1<\/span>
\nCOD                    99868<\/span>             68<\/span>          1<\/span>
\nCOD                    99869<\/span>             69<\/span>          1<\/span>
\nCOD                    99870<\/span>             70<\/span>          1<\/span>
\nCOD                    99871<\/span>             71<\/span>          1<\/span>
\nCOD                    99872<\/span>             72<\/span>          1<\/span>
\nCOD                    99873<\/span>             73<\/span>          1<\/span>
\nCOD                    99874<\/span>             74<\/span>          1<\/span>
\nCOD                    99875<\/span>             75<\/span>          1<\/span>
\nCOD                    99876<\/span>             76<\/span>          1<\/span>
\nCOD                    99877<\/span>             77<\/span>          1<\/span>
\nCOD                    99878<\/span>             78<\/span>          1<\/span>
\nCOD                    99879<\/span>             79<\/span>          1<\/span>
\nCOD                    99880<\/span>             80<\/span>          1<\/span>
\nCOD                    99881<\/span>             81<\/span>          1<\/span>
\nCOD                    99882<\/span>             82<\/span>          1<\/span>
\nCOD                    99883<\/span>             83<\/span>          1<\/span>
\nCOD                    99884<\/span>             84<\/span>          1<\/span>
\nCOD                    99885<\/span>             85<\/span>          1<\/span>
\nCOD                    99886<\/span>             86<\/span>          1<\/span>
\nCOD                    99887<\/span>             87<\/span>          1<\/span>
\nCOD                    99888<\/span>             88<\/span>          1<\/span>
\nCOD                    99889<\/span>             89<\/span>          1<\/span>
\nCOD                    99890<\/span>             90<\/span>          1<\/span>
\nCOD                    99891<\/span>             91<\/span>          1<\/span>
\nCOD                    99892<\/span>             92<\/span>          1<\/span>
\nCOD                    99893<\/span>             93<\/span>          1<\/span>
\nCOD                    99894<\/span>             94<\/span>          1<\/span>
\nCOD                    99895<\/span>             95<\/span>          1<\/span>
\nCOD                    99896<\/span>             96<\/span>          1<\/span>
\n
\nCOLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE  ROW_COUNT
\n------------<\/span> ---------------<\/span> --------------<\/span> ----------<\/span>
\nCOD                    99897<\/span>             97<\/span>          1<\/span>
\nCOD                    99898<\/span>             98<\/span>          1<\/span>
\nCOD                    99899<\/span>             99<\/span>          1<\/span>
\nCOD                    99900<\/span>            100<\/span>          1<\/span>
\nCOD                    99901<\/span>            101<\/span>          1<\/span>
\nCOD                    99902<\/span>            102<\/span>          1<\/span>
\nCOD                    99903<\/span>            103<\/span>          1<\/span>
\nCOD                    99904<\/span>            104<\/span>          1<\/span>
\nCOD                    99905<\/span>            105<\/span>          1<\/span>
\nCOD                    99906<\/span>            106<\/span>          1<\/span>
\nCOD                    99907<\/span>            107<\/span>          1<\/span>
\nCOD                    99908<\/span>            108<\/span>          1<\/span>
\nCOD                    99909<\/span>            109<\/span>          1<\/span>
\nCOD                    99910<\/span>            110<\/span>          1<\/span>
\nCOD                    99911<\/span>            111<\/span>          1<\/span>
\nCOD                    99912<\/span>            112<\/span>          1<\/span>
\nCOD                    99913<\/span>            113<\/span>          1<\/span>
\nCOD                    99914<\/span>            114<\/span>          1<\/span>
\nCOD                    99915<\/span>            115<\/span>          1<\/span>
\nCOD                    99916<\/span>            116<\/span>          1<\/span>
\nCOD                    99917<\/span>            117<\/span>          1<\/span>
\nCOD                    99918<\/span>            118<\/span>          1<\/span>
\nCOD                    99919<\/span>            119<\/span>          1<\/span>
\nCOD                    99920<\/span>            120<\/span>          1<\/span>
\nCOD                    99921<\/span>            121<\/span>          1<\/span>
\nCOD                    99922<\/span>            122<\/span>          1<\/span>
\nCOD                    99923<\/span>            123<\/span>          1<\/span>
\nCOD                    99924<\/span>            124<\/span>          1<\/span>
\nCOD                    99925<\/span>            125<\/span>          1<\/span>
\nCOD                    99926<\/span>            126<\/span>          1<\/span>
\nCOD                    99927<\/span>            127<\/span>          1<\/span>
\nCOD                    99928<\/span>            128<\/span>          1<\/span>
\nCOD                    99929<\/span>            129<\/span>          1<\/span>
\nCOD                    99930<\/span>            130<\/span>          1<\/span>
\nCOD                    99931<\/span>            131<\/span>          1<\/span>
\nCOD                    99932<\/span>            132<\/span>          1<\/span>
\nCOD                    99933<\/span>            133<\/span>          1<\/span>
\nCOD                    99934<\/span>            134<\/span>          1<\/span>
\nCOD                    99935<\/span>            135<\/span>          1<\/span>
\nCOD                    99936<\/span>            136<\/span>          1<\/span>
\nCOD                    99937<\/span>            137<\/span>          1<\/span>
\nCOD                    99938<\/span>            138<\/span>          1<\/span>
\nCOD                    99939<\/span>            139<\/span>          1<\/span>
\nCOD                    99940<\/span>            140<\/span>          1<\/span>
\nCOD                    99941<\/span>            141<\/span>          1<\/span>
\nCOD                    99942<\/span>            142<\/span>          1<\/span>
\nCOD                    99943<\/span>            143<\/span>          1<\/span>
\nCOD                    99944<\/span>            144<\/span>          1<\/span>
\nCOD                    99945<\/span>            145<\/span>          1<\/span>
\nCOD                    99946<\/span>            146<\/span>          1<\/span>
\nCOD                    99947<\/span>            147<\/span>          1<\/span>
\nCOD                    99948<\/span>            148<\/span>          1<\/span>
\nCOD                    99949<\/span>            149<\/span>          1<\/span>
\nCOD                    99950<\/span>            150<\/span>          1<\/span>
\nCOD                    99951<\/span>            151<\/span>          1<\/span>
\nCOD                    99952<\/span>            152<\/span>          1<\/span>
\nCOD                    99953<\/span>            153<\/span>          1<\/span>
\nCOD                    99954<\/span>            154<\/span>          1<\/span>
\nCOD                    99955<\/span>            155<\/span>          1<\/span>
\nCOD                    99956<\/span>            156<\/span>          1<\/span>
\nCOD                    99957<\/span>            157<\/span>          1<\/span>
\nCOD                    99958<\/span>            158<\/span>          1<\/span>
\nCOD                    99959<\/span>            159<\/span>          1<\/span>
\nCOD                    99960<\/span>            160<\/span>          1<\/span>
\nCOD                    99961<\/span>            161<\/span>          1<\/span>
\nCOD                    99962<\/span>            162<\/span>          1<\/span>
\nCOD                    99963<\/span>            163<\/span>          1<\/span>
\nCOD                    99964<\/span>            164<\/span>          1<\/span>
\nCOD                    99965<\/span>            165<\/span>          1<\/span>
\nCOD                    99966<\/span>            166<\/span>          1<\/span>
\nCOD                    99967<\/span>            167<\/span>          1<\/span>
\nCOD                    99968<\/span>            168<\/span>          1<\/span>
\nCOD                    99969<\/span>            169<\/span>          1<\/span>
\nCOD                    99970<\/span>            170<\/span>          1<\/span>
\nCOD                    99971<\/span>            171<\/span>          1<\/span>
\nCOD                    99972<\/span>            172<\/span>          1<\/span>
\nCOD                    99973<\/span>            173<\/span>          1<\/span>
\nCOD                    99974<\/span>            174<\/span>          1<\/span>
\nCOD                    99975<\/span>            175<\/span>          1<\/span>
\nCOD                    99976<\/span>            176<\/span>          1<\/span>
\nCOD                    99977<\/span>            177<\/span>          1<\/span>
\nCOD                    99978<\/span>            178<\/span>          1<\/span>
\nCOD                    99979<\/span>            179<\/span>          1<\/span>
\nCOD                    99980<\/span>            180<\/span>          1<\/span>
\nCOD                    99981<\/span>            181<\/span>          1<\/span>
\nCOD                    99982<\/span>            182<\/span>          1<\/span>
\nCOD                    99983<\/span>            183<\/span>          1<\/span>
\nCOD                    99984<\/span>            184<\/span>          1<\/span>
\nCOD                    99985<\/span>            185<\/span>          1<\/span>
\nCOD                    99986<\/span>            186<\/span>          1<\/span>
\nCOD                    99987<\/span>            187<\/span>          1<\/span>
\nCOD                    99988<\/span>            188<\/span>          1<\/span>
\nCOD                    99989<\/span>            189<\/span>          1<\/span>
\nCOD                    99990<\/span>            190<\/span>          1<\/span>
\nCOD                    99991<\/span>            191<\/span>          1<\/span>
\nCOD                    99992<\/span>            192<\/span>          1<\/span>
\nCOD                    99993<\/span>            193<\/span>          1<\/span>
\n
\nCOLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE  ROW_COUNT
\n------------<\/span> ---------------<\/span> --------------<\/span> ----------<\/span>
\nCOD                    99994<\/span>            194<\/span>          1<\/span>
\nCOD                    99995<\/span>            195<\/span>          1<\/span>
\nCOD                    99996<\/span>            196<\/span>          1<\/span>
\nCOD                    99997<\/span>            197<\/span>          1<\/span>
\nCOD                    99998<\/span>            198<\/span>          1<\/span>
\nCOD                    99999<\/span>            199<\/span>          1<\/span>
\nCOD                   100000<\/span>            200<\/span>          1<\/span>
\n
\n201<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na consulta acima na vis\u00e3o USER_TAB_HISTOGRAMS podemos verificar que foi gerado um histograma na coluna COD da tabela DBTW060 e o valor “0” tem 99800 repeti\u00e7\u00f5es, enquanto os demais valores aparecem somente uma vez.<\/span><\/p>\n

2) Executar uma consulta e verificar o processo de PARSE<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable B1 number;<\/span>
\nSQL ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 0<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>
\n  2<\/span>    FROM dbtw060
\n  3<\/span>   WHERE cod=:<\/span>B1;<\/span>
\n
\nCOUNT<\/span><\/a>(<\/span>PROD)<\/span>
\n-----------<\/span>
\n      99800<\/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 '%dbtw-060%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2k4sn0mffnf17            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>'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>'2k4sn0mffnf17'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2k4sn0mffnf17,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 2118012488<\/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>     725<\/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>     725<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW060 |<\/span>      1<\/span> |<\/span>  99800<\/span> |<\/span>  99800<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     725<\/span> |<\/span>
\n----------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"COD"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col SQL_TEXT for<\/span> a70
\nSQL ><\/span> col BIND_SENS for<\/span> a10
\nSQL ><\/span> col BIND_AWARE for<\/span> a10
\nSQL ><\/span> col SHARABLE for<\/span> a10
\nSQL ><\/span> SELECT sql_text,<\/span>
\n  2<\/span>         child_number AS<\/span> child#,
\n<\/span>  3<\/span>         executions AS<\/span> exec<\/span><\/a>,<\/span>
\n  4<\/span>         buffer_gets AS<\/span> buff_gets,<\/span>
\n  5<\/span>         is_bind_sensitive AS<\/span> bind_sens,<\/span>
\n  6<\/span>         is_bind_aware AS<\/span> bind_aware,<\/span>
\n  7<\/span>         is_shareable AS<\/span> sharable
\n  8<\/span>    FROM v$sql<\/span>
\n  9<\/span>   WHERE sql_id=<\/span>'&m_sql_id'<\/span>;<\/span>
\nantigo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'&m_sql_id'<\/span>
\nnovo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'2k4sn0mffnf17'<\/span>
\n
\nSQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
\n<\/span>----------------------------------------------------------------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 0<\/span>          1<\/span>        829<\/span> Y          N          Y
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na primeira execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL o Oracle realiza um HARD PARSE e durante este processo ele executa as seguintes tarefas:<\/span>
\n1. Gera um plano de execu\u00e7\u00e3o considerando a cardinalidade do valor informado na BIND VARIABLE (No nosso exemplo o plano de execu\u00e7\u00e3o faz acesso a tabela usando a opera\u00e7\u00e3o TABLE ACCESS FULL pois a cardinalidade do valor “0” \u00e9 99800 linhas)<\/span>
\n2. Marca o curso como BIND-SENSITIVE, um cursor BIND-SENSITIVE \u00e9 aquele em que o plano de execu\u00e7\u00e3o ideal depende do valor da BIND VARIABLE<\/span>
\n3. Armazena metadados sobre o predicado incluindo a cardinalidade dos valores vinculados<\/span><\/p>\n

3) Executar a mesma consulta com valor diferente na BIND VARIABLE<\/span><\/h2>\n

 <\/p>\n

Vamos repetir a execu\u00e7\u00e3o da consulta utilizada anteriormente, s\u00f3 que desta vez vamos passar o valor da BIND VARIABLE como “2”.<\/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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable B1 number;<\/span>
\nSQL ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 2<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>
\n  2<\/span>    FROM dbtw060
\n  3<\/span>   WHERE cod=:<\/span>B1;<\/span>
\n
\nCOUNT<\/span><\/a>(<\/span>PROD)<\/span>
\n-----------<\/span>
\n          1<\/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 '%dbtw-060%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2k4sn0mffnf17            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>'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>'2k4sn0mffnf17'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2k4sn0mffnf17,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 2118012488<\/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>     725<\/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>     725<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW060 |<\/span>      1<\/span> |<\/span>  99800<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     725<\/span> |<\/span>
\n----------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"COD"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col SQL_TEXT for<\/span> a70
\nSQL ><\/span> col BIND_SENS for<\/span> a10
\nSQL ><\/span> col BIND_AWARE for<\/span> a10
\nSQL ><\/span> col SHARABLE for<\/span> a10
\nSQL ><\/span> SELECT sql_text,<\/span>
\n  2<\/span>         child_number AS<\/span> child#,
\n<\/span>  3<\/span>         executions AS<\/span> exec<\/span><\/a>,<\/span>
\n  4<\/span>         buffer_gets AS<\/span> buff_gets,<\/span>
\n  5<\/span>         is_bind_sensitive AS<\/span> bind_sens,<\/span>
\n  6<\/span>         is_bind_aware AS<\/span> bind_aware,<\/span>
\n  7<\/span>         is_shareable AS<\/span> sharable
\n  8<\/span>    FROM v$sql<\/span>
\n  9<\/span>   WHERE sql_id=<\/span>'&m_sql_id'<\/span>;<\/span>
\nantigo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'&m_sql_id'<\/span>
\nnovo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'2k4sn0mffnf17'<\/span>
\n
\nSQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
\n<\/span>----------------------------------------------------------------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 0<\/span>          2<\/span>       1554<\/span> Y          N          Y
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na segunda execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL com outro valor na BIND VARIABLE o Oracle realiza um SOFT PARSE e utiliza o plano de execu\u00e7\u00e3o gerado na etapa anterior e que ficou armazenado em um cursor na LIBRARY CACHE.<\/span>
\nAp\u00f3s a execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL podemos verificar que o cursor BIND-SENSITIVE acumulou as estat\u00edsticas das duas execu\u00e7\u00f5es.<\/span><\/p>\n

4) Executar a mesma consulta com um terceiro valor na BIND VARIABLE<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable B1 number;<\/span>
\nSQL ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 6<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>
\n  2<\/span>    FROM dbtw060
\n  3<\/span>   WHERE cod=:<\/span>B1;<\/span>
\n
\nCOUNT<\/span><\/a>(<\/span>PROD)<\/span>
\n-----------<\/span>
\n          1<\/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 '%dbtw-060%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2k4sn0mffnf17            0<\/span>
\n2k4sn0mffnf17            1<\/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>'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>'2k4sn0mffnf17'<\/span>,<\/span>         1<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2k4sn0mffnf17,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 3248072391<\/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> Reads  |<\/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>       3<\/span> |<\/span>      2<\/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>       3<\/span> |<\/span>      2<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW060     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>      2<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW060_IDX |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>      2<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"COD"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col SQL_TEXT for<\/span> a70
\nSQL ><\/span> col BIND_SENS for<\/span> a10
\nSQL ><\/span> col BIND_AWARE for<\/span> a10
\nSQL ><\/span> col SHARABLE for<\/span> a10
\nSQL ><\/span> SELECT sql_text,<\/span>
\n  2<\/span>         child_number AS<\/span> child#,
\n<\/span>  3<\/span>         executions AS<\/span> exec<\/span><\/a>,<\/span>
\n  4<\/span>         buffer_gets AS<\/span> buff_gets,<\/span>
\n  5<\/span>         is_bind_sensitive AS<\/span> bind_sens,<\/span>
\n  6<\/span>         is_bind_aware AS<\/span> bind_aware,<\/span>
\n  7<\/span>         is_shareable AS<\/span> sharable
\n  8<\/span>    FROM v$sql<\/span>
\n  9<\/span>   WHERE sql_id=<\/span>'&m_sql_id'<\/span>;<\/span>
\nantigo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'&m_sql_id'<\/span>
\nnovo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'2k4sn0mffnf17'<\/span>
\n
\nSQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
\n<\/span>----------------------------------------------------------------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 0<\/span>          2<\/span>       1554<\/span> Y          N          N
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 1<\/span>          1<\/span>          3<\/span> Y          Y          Y
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na terceira execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL o banco compara as estat\u00edsticas da segunda execu\u00e7\u00e3o com as estat\u00edsticas da primeira execu\u00e7\u00e3o e verifica que a cardinalidade para os valores das BIND VARIABLES informadas foram diferentes e ao verificar que o valor da BIND VARIABLE desta execu\u00e7\u00e3o tem uma cardinalidade semelhante ao da segunda execu\u00e7\u00e3o ele decide criar um novo plano de execu\u00e7\u00e3o que tenha uma desempenho melhor para este valor da BIND VARIABLE.<\/span>
\nQuando consultamos a vis\u00e3o din\u00e2mica V$SQL podemos constatar que agora existem dois cursores para a nossa consulta e o campo BIND-AWARE do segundo cursor esta ativo, j\u00e1 o primeiro cursor esta com o campo SHARABLE=N indicando que este cursor n\u00e3o ser\u00e1 mais utilizado nas pr\u00f3ximas execu\u00e7\u00f5es.<\/span><\/p>\n

5) Executar a mesma consulta com o valor da primeira execu\u00e7\u00e3o na BIND VARIABLE<\/span><\/h2>\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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> variable B1 number;<\/span>
\nSQL ><\/span> exec<\/span><\/a> :<\/span>B1 :=<\/span> 0<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>
\n  2<\/span>    FROM dbtw060
\n  3<\/span>   WHERE cod=:<\/span>B1;<\/span>
\n
\nCOUNT<\/span><\/a>(<\/span>PROD)<\/span>
\n-----------<\/span>
\n      99800<\/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 '%dbtw-060%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n2k4sn0mffnf17            0<\/span>
\n2k4sn0mffnf17            1<\/span>
\n2k4sn0mffnf17            2<\/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>'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>'2k4sn0mffnf17'<\/span>,<\/span>         2<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  2k4sn0mffnf17,<\/span> child number 2<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1
\n
\nPlan hash<\/span><\/a> value:<\/span> 2118012488<\/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>     725<\/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>     725<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> DBTW060 |<\/span>      1<\/span> |<\/span>  99800<\/span> |<\/span>  99800<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     725<\/span> |<\/span>
\n----------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"COD"<\/span>=:<\/span>B1)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col SQL_TEXT for<\/span> a70
\nSQL ><\/span> col BIND_SENS for<\/span> a10
\nSQL ><\/span> col BIND_AWARE for<\/span> a10
\nSQL ><\/span> col SHARABLE for<\/span> a10
\nSQL ><\/span> SELECT sql_text,<\/span>
\n  2<\/span>         child_number AS<\/span> child#,
\n<\/span>  3<\/span>         executions AS<\/span> exec<\/span><\/a>,<\/span>
\n  4<\/span>         buffer_gets AS<\/span> buff_gets,<\/span>
\n  5<\/span>         is_bind_sensitive AS<\/span> bind_sens,<\/span>
\n  6<\/span>         is_bind_aware AS<\/span> bind_aware,<\/span>
\n  7<\/span>         is_shareable AS<\/span> sharable
\n  8<\/span>    FROM v$sql<\/span>
\n  9<\/span>   WHERE sql_id=<\/span>'&m_sql_id'<\/span>;<\/span>
\nantigo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'&m_sql_id'<\/span>
\nnovo   9<\/span>:<\/span>  WHERE sql_id=<\/span>'2k4sn0mffnf17'<\/span>
\n
\nSQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
\n<\/span>----------------------------------------------------------------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> ----------<\/span>
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 0<\/span>          2<\/span>       1554<\/span> Y          N          N
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 1<\/span>          1<\/span>          3<\/span> Y          Y          Y
\nSELECT \/* dbtw-060 *\/<\/span> count<\/span><\/a>(<\/span>prod)<\/span>   FROM dbtw060  WHERE cod=:<\/span>B1                 2<\/span>          1<\/span>        725<\/span> Y          Y          Y
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na quarta execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL vamos utilizar novamente o valor “0” na BIND VARIABLE, a cardinalidade para este valor n\u00e3o \u00e9 adequada ao cursor utilizado na execu\u00e7\u00e3o anterior e como o primeiro cursor foi marcado como n\u00e3o utiliz\u00e1vel o banco vai criar um novo plano de execu\u00e7\u00e3o e um novo cursor para este valor de BIND VARIABLE.<\/span>
\nQuando consultamos a vis\u00e3o din\u00e2mica V$SQL podemos constatar que agora existem tr\u00eas cursores para a nossa consulta e o campo BIND-AWARE do segundo e terceiro cursor est\u00e3o ativos.<\/span><\/p>\n

CONCLUS\u00c3O<\/span><\/h2>\n

O recurso ADAPTIVE CURSOR SHARING permite que o Oracle utilize um grupo de planos de execu\u00e7\u00e3o diferentes para garantir o desempenho da instru\u00e7\u00e3o SQL dependendo do valor da BIND VARIABLE e suas estat\u00edsticas de HISTOGRAMS.<\/span><\/p>\n

REFER\u00caNCIAS<\/span><\/h2>\n

https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/12.2\/<\/a><\/span><\/strong><\/p>\n

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

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

A utiliza\u00e7\u00e3o de BIND VARIABLE \u00e9 muito recomendada quando enviamos instru\u00e7\u00f5es SQL para execu\u00e7\u00e3o no banco de dados Oracle pois esta pr\u00e1tica evita que o Otimizador tenha que realizar um quantidade excessiva de HARD PARSE o que evita o consumo desnecess\u00e1rio de CPU. No entanto quando utilizamos BIND VARIABLE num filtro da cl\u00e1usula WHERE cuja coluna da tabela possui um<\/p>\n","protected":false},"author":2,"featured_media":2006,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[122],"tags":[216,217,220,219,39,162,161,82,10,218],"class_list":["post-2005","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-parse","tag-adaptive-cursor-sharing","tag-bind-variable","tag-bind-aware","tag-bind-sensitive","tag-estatisticas","tag-histogramas","tag-histograms","tag-optimizer","tag-otimizador","tag-parse"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2005","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=2005"}],"version-history":[{"count":15,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2005\/revisions"}],"predecessor-version":[{"id":2068,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2005\/revisions\/2068"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/2006"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2005"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2005"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}