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

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
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'

 

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 >
Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

 

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
 
 
 
 
 
 
 
 
 
 
 
 

3 comments

  • Jorge Luiz Vaz

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

    • 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

    • 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

Deixe um comentário para V. J.A. Cancelar resposta

O seu endereço de e-mail não será publicado.