Por que minha consulta esta alternando planos de execução diferentes?

Parse
Bind Histograms

A utilização de BIND VARIABLE é muito recomendada quando enviamos instruções SQL para execução no banco de dados Oracle pois esta prática evita que o Otimizador tenha que realizar um quantidade excessiva de HARD PARSE o que evita o consumo desnecessário de CPU. No entanto quando utilizamos BIND VARIABLE num filtro da cláusula WHERE cuja coluna da tabela possui um HISTOGRAM, esta combinação pode levar o Otimizador a criar planos de execução diferentes para garantir o desempenho da instrução SQL dependendo do valor passado na BIND VARIABLE.

Este recurso do Oracle é conhecido como ADAPTIVE CURSOR SHARING e neste artigo vamos simular este mecanismo para que você entenda como ele funciona.

Para realizar esta simulação vamos seguir as etapas abaixo:

1) Criar os objetos que serão utilizados na simulação
2) Executar uma consulta e verificar o processo de PARSE
3) Executar a mesma consulta com valor diferente na BIND VARIABLE
4) Executar a mesma consulta com um terceiro valor na BIND VARIABLE e verificar o processo de PARSE
5) Executar a mesma consulta com o valor da primeira execução na BIND VARIABLE e verificar o processo de PARSE

1) Criar os objetos para a simulação

Vamos criar os objetos necessários para realizar a nossa simulação e coletar as estatísticas.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SQL >
SQL > drop table dbtw060 purge;

Tabela eliminada.

SQL >
SQL > create table dbtw060 as
  2  select case when level <= 99800 then 0 else level-99800 end cod,
  3         'DBTimeWizard - Oracle Performance and Tuning' as prod
  4            from dual
  5         connect by level <= 100000;

Tabela criada.

SQL >
SQL > create index dbtw060_idx on dbtw060(cod);

Índice criado.

SQL >
SQL > exec dbms_stats.gather_table_stats(user, 'DBTW060', method_opt => 'for columns cod size 254', cascade => true);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > -- O histograma mostra como o Oracle fez esta distribuição de dados:
SQL >
SQL > col COLUMN_NAME for a12
SQL > col ENDPOINT_ACTUAL_VALUE for a12
SQL >
SQL > select column_name,
  2         endpoint_number,
  3         endpoint_value,
  4         endpoint_number - nvl(endpoint_number_prev,0) row_count
  5    from (select column_name, endpoint_number, endpoint_value, lag(endpoint_number,1) over (order by endpoint_number)  endpoint_number_prev
  6            from user_tab_histograms
  7           where table_name='DBTW060'
  8             and column_name='COD')
  9   order by endpoint_value;

COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE  ROW_COUNT
------------ --------------- -------------- ----------
COD                    99800              0      99800
COD                    99801              1          1
COD                    99802              2          1
COD                    99803              3          1
COD                    99804              4          1
COD                    99805              5          1
COD                    99806              6          1
COD                    99807              7          1
COD                    99808              8          1
COD                    99809              9          1
COD                    99810             10          1
COD                    99811             11          1
COD                    99812             12          1
COD                    99813             13          1
COD                    99814             14          1
COD                    99815             15          1
COD                    99816             16          1
COD                    99817             17          1
COD                    99818             18          1
COD                    99819             19          1
COD                    99820             20          1
COD                    99821             21          1
COD                    99822             22          1
COD                    99823             23          1
COD                    99824             24          1
COD                    99825             25          1
COD                    99826             26          1
COD                    99827             27          1
COD                    99828             28          1
COD                    99829             29          1
COD                    99830             30          1
COD                    99831             31          1
COD                    99832             32          1
COD                    99833             33          1
COD                    99834             34          1
COD                    99835             35          1
COD                    99836             36          1
COD                    99837             37          1
COD                    99838             38          1
COD                    99839             39          1
COD                    99840             40          1
COD                    99841             41          1
COD                    99842             42          1
COD                    99843             43          1
COD                    99844             44          1
COD                    99845             45          1
COD                    99846             46          1
COD                    99847             47          1
COD                    99848             48          1
COD                    99849             49          1
COD                    99850             50          1
COD                    99851             51          1
COD                    99852             52          1
COD                    99853             53          1
COD                    99854             54          1
COD                    99855             55          1
COD                    99856             56          1
COD                    99857             57          1
COD                    99858             58          1
COD                    99859             59          1
COD                    99860             60          1
COD                    99861             61          1
COD                    99862             62          1
COD                    99863             63          1
COD                    99864             64          1
COD                    99865             65          1
COD                    99866             66          1
COD                    99867             67          1
COD                    99868             68          1
COD                    99869             69          1
COD                    99870             70          1
COD                    99871             71          1
COD                    99872             72          1
COD                    99873             73          1
COD                    99874             74          1
COD                    99875             75          1
COD                    99876             76          1
COD                    99877             77          1
COD                    99878             78          1
COD                    99879             79          1
COD                    99880             80          1
COD                    99881             81          1
COD                    99882             82          1
COD                    99883             83          1
COD                    99884             84          1
COD                    99885             85          1
COD                    99886             86          1
COD                    99887             87          1
COD                    99888             88          1
COD                    99889             89          1
COD                    99890             90          1
COD                    99891             91          1
COD                    99892             92          1
COD                    99893             93          1
COD                    99894             94          1
COD                    99895             95          1
COD                    99896             96          1

COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE  ROW_COUNT
------------ --------------- -------------- ----------
COD                    99897             97          1
COD                    99898             98          1
COD                    99899             99          1
COD                    99900            100          1
COD                    99901            101          1
COD                    99902            102          1
COD                    99903            103          1
COD                    99904            104          1
COD                    99905            105          1
COD                    99906            106          1
COD                    99907            107          1
COD                    99908            108          1
COD                    99909            109          1
COD                    99910            110          1
COD                    99911            111          1
COD                    99912            112          1
COD                    99913            113          1
COD                    99914            114          1
COD                    99915            115          1
COD                    99916            116          1
COD                    99917            117          1
COD                    99918            118          1
COD                    99919            119          1
COD                    99920            120          1
COD                    99921            121          1
COD                    99922            122          1
COD                    99923            123          1
COD                    99924            124          1
COD                    99925            125          1
COD                    99926            126          1
COD                    99927            127          1
COD                    99928            128          1
COD                    99929            129          1
COD                    99930            130          1
COD                    99931            131          1
COD                    99932            132          1
COD                    99933            133          1
COD                    99934            134          1
COD                    99935            135          1
COD                    99936            136          1
COD                    99937            137          1
COD                    99938            138          1
COD                    99939            139          1
COD                    99940            140          1
COD                    99941            141          1
COD                    99942            142          1
COD                    99943            143          1
COD                    99944            144          1
COD                    99945            145          1
COD                    99946            146          1
COD                    99947            147          1
COD                    99948            148          1
COD                    99949            149          1
COD                    99950            150          1
COD                    99951            151          1
COD                    99952            152          1
COD                    99953            153          1
COD                    99954            154          1
COD                    99955            155          1
COD                    99956            156          1
COD                    99957            157          1
COD                    99958            158          1
COD                    99959            159          1
COD                    99960            160          1
COD                    99961            161          1
COD                    99962            162          1
COD                    99963            163          1
COD                    99964            164          1
COD                    99965            165          1
COD                    99966            166          1
COD                    99967            167          1
COD                    99968            168          1
COD                    99969            169          1
COD                    99970            170          1
COD                    99971            171          1
COD                    99972            172          1
COD                    99973            173          1
COD                    99974            174          1
COD                    99975            175          1
COD                    99976            176          1
COD                    99977            177          1
COD                    99978            178          1
COD                    99979            179          1
COD                    99980            180          1
COD                    99981            181          1
COD                    99982            182          1
COD                    99983            183          1
COD                    99984            184          1
COD                    99985            185          1
COD                    99986            186          1
COD                    99987            187          1
COD                    99988            188          1
COD                    99989            189          1
COD                    99990            190          1
COD                    99991            191          1
COD                    99992            192          1
COD                    99993            193          1

COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE  ROW_COUNT
------------ --------------- -------------- ----------
COD                    99994            194          1
COD                    99995            195          1
COD                    99996            196          1
COD                    99997            197          1
COD                    99998            198          1
COD                    99999            199          1
COD                   100000            200          1

201 linhas selecionadas.

SQL >

 

Na consulta acima na visão USER_TAB_HISTOGRAMS podemos verificar que foi gerado um histograma na coluna COD da tabela DBTW060 e o valor “0” tem 99800 repetições, enquanto os demais valores aparecem somente uma vez.

2) Executar uma consulta e verificar o processo de PARSE

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable B1 number;
SQL > exec :B1 := 0;

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > SELECT /* dbtw-060 */ count(prod)
  2    FROM dbtw060
  3   WHERE cod=:B1;

COUNT(PROD)
-----------
      99800

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw-060%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2k4sn0mffnf17            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('2k4sn0mffnf17',         0,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2k4sn0mffnf17, child number 0
-------------------------------------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1

Plan hash value: 2118012488

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     725 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     725 |
|*  2 |   TABLE ACCESS FULL| DBTW060 |      1 |  99800 |  99800 |00:00:00.01 |     725 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COD"=:B1)


19 linhas selecionadas.

SQL >
SQL > col SQL_TEXT for a70
SQL > col BIND_SENS for a10
SQL > col BIND_AWARE for a10
SQL > col SHARABLE for a10
SQL > SELECT sql_text,
  2         child_number AS child#,
  3         executions AS exec,
  4         buffer_gets AS buff_gets,
  5         is_bind_sensitive AS bind_sens,
  6         is_bind_aware AS bind_aware,
  7         is_shareable AS sharable
  8    FROM v$sql
  9   WHERE sql_id='&m_sql_id';
antigo   9:  WHERE sql_id='&m_sql_id'
novo   9:  WHERE sql_id='2k4sn0mffnf17'

SQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 0          1        829 Y          N          Y

SQL >

 

Na primeira execução da instrução SQL o Oracle realiza um HARD PARSE e durante este processo ele executa as seguintes tarefas:
1. Gera um plano de execução considerando a cardinalidade do valor informado na BIND VARIABLE (No nosso exemplo o plano de execução faz acesso a tabela usando a operação TABLE ACCESS FULL pois a cardinalidade do valor “0” é 99800 linhas)
2. Marca o curso como BIND-SENSITIVE, um cursor BIND-SENSITIVE é aquele em que o plano de execução ideal depende do valor da BIND VARIABLE
3. Armazena metadados sobre o predicado incluindo a cardinalidade dos valores vinculados

3) Executar a mesma consulta com valor diferente na BIND VARIABLE

 

Vamos repetir a execução da consulta utilizada anteriormente, só que desta vez vamos passar o valor da BIND VARIABLE como “2”.

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable B1 number;
SQL > exec :B1 := 2;

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > SELECT /* dbtw-060 */ count(prod)
  2    FROM dbtw060
  3   WHERE cod=:B1;

COUNT(PROD)
-----------
          1

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw-060%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2k4sn0mffnf17            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('2k4sn0mffnf17',         0,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2k4sn0mffnf17, child number 0
-------------------------------------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1

Plan hash value: 2118012488

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     725 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     725 |
|*  2 |   TABLE ACCESS FULL| DBTW060 |      1 |  99800 |      1 |00:00:00.01 |     725 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COD"=:B1)


19 linhas selecionadas.

SQL >
SQL > col SQL_TEXT for a70
SQL > col BIND_SENS for a10
SQL > col BIND_AWARE for a10
SQL > col SHARABLE for a10
SQL > SELECT sql_text,
  2         child_number AS child#,
  3         executions AS exec,
  4         buffer_gets AS buff_gets,
  5         is_bind_sensitive AS bind_sens,
  6         is_bind_aware AS bind_aware,
  7         is_shareable AS sharable
  8    FROM v$sql
  9   WHERE sql_id='&m_sql_id';
antigo   9:  WHERE sql_id='&m_sql_id'
novo   9:  WHERE sql_id='2k4sn0mffnf17'

SQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 0          2       1554 Y          N          Y

SQL >

 

Na segunda execução da instrução SQL com outro valor na BIND VARIABLE o Oracle realiza um SOFT PARSE e utiliza o plano de execução gerado na etapa anterior e que ficou armazenado em um cursor na LIBRARY CACHE.
Após a execução da instrução SQL podemos verificar que o cursor BIND-SENSITIVE acumulou as estatísticas das duas execuções.

4) Executar a mesma consulta com um terceiro valor na BIND VARIABLE

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable B1 number;
SQL > exec :B1 := 6;

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > SELECT /* dbtw-060 */ count(prod)
  2    FROM dbtw060
  3   WHERE cod=:B1;

COUNT(PROD)
-----------
          1

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw-060%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2k4sn0mffnf17            0
2k4sn0mffnf17            1

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('2k4sn0mffnf17',         1,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2k4sn0mffnf17, child number 1
-------------------------------------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1

Plan hash value: 3248072391

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      1 |00:00:00.01 |       3 |      2 |
|   1 |  SORT AGGREGATE                      |             |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DBTW060     |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |
|*  3 |    INDEX RANGE SCAN                  | DBTW060_IDX |      1 |      1 |      1 |00:00:00.01 |       2 |      2 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("COD"=:B1)


20 linhas selecionadas.

SQL >
SQL > col SQL_TEXT for a70
SQL > col BIND_SENS for a10
SQL > col BIND_AWARE for a10
SQL > col SHARABLE for a10
SQL > SELECT sql_text,
  2         child_number AS child#,
  3         executions AS exec,
  4         buffer_gets AS buff_gets,
  5         is_bind_sensitive AS bind_sens,
  6         is_bind_aware AS bind_aware,
  7         is_shareable AS sharable
  8    FROM v$sql
  9   WHERE sql_id='&m_sql_id';
antigo   9:  WHERE sql_id='&m_sql_id'
novo   9:  WHERE sql_id='2k4sn0mffnf17'

SQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 0          2       1554 Y          N          N
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 1          1          3 Y          Y          Y

SQL >

 

Na terceira execução da instrução SQL o banco compara as estatísticas da segunda execução com as estatísticas da primeira execução 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ção tem uma cardinalidade semelhante ao da segunda execução ele decide criar um novo plano de execução que tenha uma desempenho melhor para este valor da BIND VARIABLE.
Quando consultamos a visão dinâmica V$SQL podemos constatar que agora existem dois cursores para a nossa consulta e o campo BIND-AWARE do segundo cursor esta ativo, já o primeiro cursor esta com o campo SHARABLE=N indicando que este cursor não será mais utilizado nas próximas execuções.

5) Executar a mesma consulta com o valor da primeira execução na BIND VARIABLE

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > variable B1 number;
SQL > exec :B1 := 0;

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > SELECT /* dbtw-060 */ count(prod)
  2    FROM dbtw060
  3   WHERE cod=:B1;

COUNT(PROD)
-----------
      99800

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw-060%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2k4sn0mffnf17            0
2k4sn0mffnf17            1
2k4sn0mffnf17            2

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('2k4sn0mffnf17',         2,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2k4sn0mffnf17, child number 2
-------------------------------------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1

Plan hash value: 2118012488

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     725 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     725 |
|*  2 |   TABLE ACCESS FULL| DBTW060 |      1 |  99800 |  99800 |00:00:00.01 |     725 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COD"=:B1)


19 linhas selecionadas.

SQL >
SQL > col SQL_TEXT for a70
SQL > col BIND_SENS for a10
SQL > col BIND_AWARE for a10
SQL > col SHARABLE for a10
SQL > SELECT sql_text,
  2         child_number AS child#,
  3         executions AS exec,
  4         buffer_gets AS buff_gets,
  5         is_bind_sensitive AS bind_sens,
  6         is_bind_aware AS bind_aware,
  7         is_shareable AS sharable
  8    FROM v$sql
  9   WHERE sql_id='&m_sql_id';
antigo   9:  WHERE sql_id='&m_sql_id'
novo   9:  WHERE sql_id='2k4sn0mffnf17'

SQL_TEXT                                                                   CHILD#       EXEC  BUFF_GETS BIND_SENS  BIND_AWARE SHARABLE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 0          2       1554 Y          N          N
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 1          1          3 Y          Y          Y
SELECT /* dbtw-060 */ count(prod)   FROM dbtw060  WHERE cod=:B1                 2          1        725 Y          Y          Y

SQL >

 

Na quarta execução da instrução SQL vamos utilizar novamente o valor “0” na BIND VARIABLE, a cardinalidade para este valor não é adequada ao cursor utilizado na execução anterior e como o primeiro cursor foi marcado como não utilizável o banco vai criar um novo plano de execução e um novo cursor para este valor de BIND VARIABLE.
Quando consultamos a visão dinâmica V$SQL podemos constatar que agora existem três cursores para a nossa consulta e o campo BIND-AWARE do segundo e terceiro cursor estão ativos.

CONCLUSÃO

O recurso ADAPTIVE CURSOR SHARING permite que o Oracle utilize um grupo de planos de execução diferentes para garantir o desempenho da instrução SQL dependendo do valor da BIND VARIABLE e suas estatísticas de HISTOGRAMS.

REFERÊNCIAS

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Artigos semelhantes:

  • histogramas
    Histogramas: Qual importância deles?

    No artigo "Por que o Otimizador criou um plano de execução ineficiente?" elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos abordar com mais detalhes um desses tópicos, como a repetição de dados não uniformes em uma coluna da tabela podem influenciar…

  • não use histogramas
    Histogramas: Quando devemos evitá-los?

    O uso de histogramas ajuda o Otimizador a ser mais preciso no cálculo da cardinalidade das operações no plano de execução, mas existem situações em que não devemos utilizá-los, pois eles vão induzir o Otimizador a criar planos de execução de baixa qualidade. Neste artigo vamos mostrar uma destas situações…

  • Otimizador_Estatisticas
    Estatísticas: a matéria prima do otimizador

    No artigo "Por que o Otimizador criou um plano de execução ineficiente?" elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos mostrar com mais detalhes como a ausência de estatísticas ou a falta de sua atualização podem influenciar de maneira negativa o…

3 comments

3 comments

  • Jorge Luiz Vaz
    fevereiro 27, 2020 at 11:52

    Uma dúvida : O curso do cursor_sharing=force, beneficia uma aplicação que faz pouco uso de BIND ?

    Responder
    • V. J.A.
      fevereiro 28, 2020 at 15:07

      Olá Jorge Luiz,
      se entendi direito a sua pergunta, você que saber se o cursor_sharing=force traz algum beneficio para aplicações que passam as variáveis literalmente nas instruções SQL e não utilizam BIND VARIABLE, neste caso se o parâmetro cursor_sharing estiver definido como EXACT que é o padrão de instalação você vai ter um consumo de recurso do banco desnecessário toda vez que estas instruções forem executadas pois para cada execução será realizado um HARD PARSE, e se o volume de instruções nessa situação for alto você pode ter contenção de CPU no servidor do banco, pois o HARD PARSE consome CPU. Neste caso a utilização do cursor_sharing=force vai transformar as variáveis literais em BIND VARIABLE e com isso você eliminará os HARD PARSE desnecessários e você terá um ganho desempenho nas instruções SQL e diminuirá o load de CPU do servidor de banco.

      Obrigado por compartilhar a sua dúvida, espero te-la esclarecido, se não fique a vontade para me questionar.

      Um forte abraço,
      Valter Aquino

      Responder
    • V. J.A.
      abril 30, 2020 at 22:01

      Olá Jorge,
      O curso do cursor_sharing=force pode beneficiar uma aplicação que não usa BIND e passa as variáveis nas instruções SQL como literais, pois esta opções do cursor_sharing transforma as variáveis literais em BIND antes do processo de parse, evitando assim que sejam realizados Hard parses desnecessários.
      Um forte abraço,
      Valter Aquino

      Responder

Deixe uma resposta

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