Plano de execução ruim? Turbine o Otimizador.

Estatísticas dinâmicas

A “Feature Dynamic Sampling” foi disponibilizada a partir da versão Oracle 9iR2. O equívoco mais comum é que ela pode ser utilizada para substituir as estatísticas coletadas pelo pacote DBMS_STATS. O objetivo dela é dar mais opções estatísticas ao otimizador, ela é usada quando as estatísticas regulares não são suficientes para obter estimativas de cardinalidade de boa qualidade.

 

Durante o processo “Hard Parse” de uma instrução SQL, o otimizador decide se usa ou não a “Feature Dynamic Sampling”, verificando se as estatísticas disponíveis são suficientes para gerar um bom plano de execução. Se as estatísticas disponíveis não são suficientes, serão geradas estatísticas dinâmicas (DS). Elas são geralmente usadas para compensar as estatísticas ausentes ou insuficientes que podem levar a um plano de execução ruim. Caso uma ou mais tabelas da consulta não possuam estatísticas, o otimizador vai coletar estatísticas dinâmicas dessas tabelas antes de gerar o plano de execução. As estatísticas coletadas neste caso não são de alta qualidade ou tão completas quanto as estatísticas coletadas com o pacote DBMS_STATS. Esta limitação visa minimizar o impacto sobre o tempo de “Hard Parse” da instrução SQL.

 

O Otimizador utiliza uma variedade de informações para montar o plano de execução. Ele utiliza as “Constraints” definida para a tabela, estatísticas de sistema (Informações sobre velocidade I/O e CPU), estatísticas coletadas previamente dos segmentos envolvidos na instrução SQL.

 

O Otimizador utiliza estatísticas para estimar a cardinalidade (Numero de linhas estimadas para cada operação do plano de execução) e essas cardinalidades são as variáveis mais importantes para determinar o custo da instrução SQL. Quando a cardinalidade das operações esta incorreta a estimativa de custo do plano é imprecisa e o plano de execução escolhido é ineficiente.

 

A principal razão pela qual o Otimizador (CBO) gera um plano de execução ineficiente é devido a estimativa da cardinalidade ser de baixa qualidade. Assim podemos dizer que a “Feature Dynamic Sampling” veio para ajudar o Otimizador (CBO) a gerar estimativas de cardinalidade mais assertivas. Alimentando o otimizador com informações estatísticas adicionais ela contribui para melhorar a qualidade do plano de execução.

Como habilitar a “Feature Dynamic Sampling”?

Podemos definir a utilização da “Feature Dynamic Sampling” de três maneiras:

  1. Configurando o parâmetro OPTIMIZER_DYNAMIC_SAMPLING a nível de instância;
  2. Configurando o parâmetro OPTIMIZER_DYNAMIC_SAMPLING a nível de sessão com o comando “ALTER SESSION”, esta opção sobrepõe a definição a nível de instância;
  3. Adicionando um HINT DYNAMIC_SAMPLING numa consulta específica;

Quando usar a “Feature Dynamic Sampling”?

Esta é uma pergunta não tem uma resposta simples. Tal como acontece com outras “FEATURES”, Em alguns momentos devemos usá-la e em outros devemos evitá-la. Até agora só falamos dos benefícios da “Feature Dynamic Sampling”, e baseado nisso, parece que deveriamos definir o nível de 3 ou 4 que o Otimizador passaria a fazer um trabalho melhor.

 

Isso faz sentido em um ambiente em que o tempo de execução das instruções SQL é relativamente longo comparado ao tempo gasto para realizar o “Hard Parse”, tal como num ambiente “DATA WAREHOUSING”. Nessa ambiente a definição de um nível de “Dynamic Sampling” alto faz todo sentido. Você pode dar ao otimizador um pouco mais de tempo durante “Hard Parse” (quando a “Dynamic Sampling” entra em ação) para obter um plano de execução melhor para instruções SQL complexas.

 

Quando pensamos num outro tipo de ambiente como o OLTP a situação é diferente, nesse ambiente você está executando instruções SQL milhares de vezes por segundo e gasta pouco tempo executando essas instruções, elas são tipicamente pequenas e rápidas. Aumentar o tempo “Hard Parse” num sistema OLTP pode fazer o banco levar mais tempo para gerar o plano de execução do que para executar a instruções SQL. Nesse ambiente não queremos aumentar o tempo “Hard Parse”, portanto, não é aconselhável a utilização da “Feature Dynamic Sampling” nesse ambiente.

 

Alem de utilizar a “Feature Dynamic Sampling” de forma massiva configurando o parâmetro OPTIMIZER_DYNAMIC_SAMPLING a nível de instância, podemos utiliza-la de forma especifica num processo de tuning de uma instrução SQL quando percebemos que o plano de execução gerado apresenta erros de estimativas de cardinalidade elevados e a “Feature Dynamic Sampling” não foi utilizada, neste caso podemos forçar a sua utilização através do HINT DYNAMIC_SAMPLING para gerar um plano de melhor qualidade.

Qual é o significado dos diferentes níveis que podem ser definidos?

O parâmetro OPTIMIZER_DYNAMIC_SAMPLING aceita até 11 níveis na sua definição a partir da versão 11gR2, a tabela abaixo descreve como esse recurso trabalha em cada um desses níveis:

Níveis de estatísticas dinâmicas

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Simulação quando tabela não tem estatísticas

A seguir vamos fazer uma simulação da execução de uma consulta cuja tabela referênciada não possui estatísticas e o parâmetro OPTIMIZER_DYNAMIC_SAMPLING esta definido no nível 2 que é  padrão para instância 11gR2.

 

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
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL > create table teste1
  2         as select decode( mod(rownum,2), 0, 'D', 'R' ) col1,
  3                   decode( mod(rownum,2), 0, 'R', 'D' ) col2, t.*
  4              from all_objects t;

Table created.

SQL >
SQL > create index teste1_objtype on teste1(object_type);

Index created.

SQL > show parameters dynamic

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_dynamic_sampling           integer                          2
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL > select /* dbtw001  */ object_name, object_type
  2    from teste1
  3   where object_type='CLUSTER';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
C_COBJ#                        CLUSTER
C_TS#                          CLUSTER
C_FILE#_BLOCK#                 CLUSTER
C_USER#                        CLUSTER
C_OBJ#                         CLUSTER
SMON_SCN_TO_TIME_AUX           CLUSTER
C_OBJ#_INTCOL#                 CLUSTER
C_TOID_VERSION#                CLUSTER
C_MLOG#                        CLUSTER
C_RG#                          CLUSTER

10 rows selected.

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 '%dbtw001%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
51a8mdf13jvk7            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('51a8mdf13jvk7',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw001  */ object_name, object_type   from teste1  where
object_type=:"SYS_B_0"

Plan hash value: 1401039129

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |     10 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTE1         |      1 |     10 |     10 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | TESTE1_OBJTYPE |      1 |     10 |     10 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"=:SYS_B_0)

Note
-----
   - dynamic sampling used for this statement (level=2)


24 rows selected.

SQL >
SQL > --------------------------------------------------------------------------------------------------------
SQL > --------------------------------------------------------------------------------------------------------
SQL > --------------------------------------------------------------------------------------------------------
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'TESTE1',method_opt=>'for all columns size 254',cascade=>true);

PL/SQL procedure successfully completed.

SQL > select /* dbtw002  */ object_name, object_type
  2    from teste1
  3   where object_type='CLUSTER';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
C_COBJ#                        CLUSTER
C_TS#                          CLUSTER
C_FILE#_BLOCK#                 CLUSTER
C_USER#                        CLUSTER
C_OBJ#                         CLUSTER
SMON_SCN_TO_TIME_AUX           CLUSTER
C_OBJ#_INTCOL#                 CLUSTER
C_TOID_VERSION#                CLUSTER
C_MLOG#                        CLUSTER
C_RG#                          CLUSTER

10 rows selected.

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 '%dbtw002%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
9pry7kw7avpff            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));
old   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'))
new   2:   FROM TABLE (dbms_xplan.display_cursor ('9pry7kw7avpff',         0,'basic iostats last'))

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw002  */ object_name, object_type   from teste1  where
object_type=:"SYS_B_0"

Plan hash value: 1401039129

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |     10 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTE1         |      1 |     18 |     10 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | TESTE1_OBJTYPE |      1 |     18 |     10 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"=:SYS_B_0)


20 rows selected.

SQL >

 

Na primeira execução da consulta não coletamos as estatísticas da tabela, o parâmetro OPTIMIZER_DYNAMIC_SAMPLING esta definido como 2 e nesse caso conforme a descrição desse nível o Otimizador vai utilizar as estatísticas dinâmicas para determinar a cardinalidade da operação que vai acessar as linhas da tabela. No plano de execução podemos verificar que as estatísticas dinâmicas tem ótima qualidade pois a cardinalidade estimada e exatamente igual a quantidade de linhas recuperadas da tabela. Na seção NOTE podemos observar a mensagem “dynamic sampling used for this statement (level=2)”.

 

Na segunda execução da consulta coletamos as estatísticas da tabela e o parâmetro OPTIMIZER_DYNAMIC_SAMPLING foi mantido no mesmo nível, porem neste caso o Otimizador não utilizou as estatísticas dinâmincas, ele utilizou as estatísticas coletadas pelo pacote DBMS_STATS pois no nível 2 as estatísticas dinânicas são utilizadas somente se não houver estatísticas para a tabela utilizada na consulta. No plano de execução podemos observar que a precisão da estimativa de linhas não foi tão boa quanto a da primeira consulta, nesse plano o Otimizador estimou 18 linhas (E-Rows) e a execução recuperou 10 linhas (A-Rows).

Simulação quando consulta usa expressão na cláusula WHERE

 

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
SQL > alter session set OPTIMIZER_DYNAMIC_SAMPLING = 2;

Session altered.

SQL > select /* dbtw101  */ object_name, object_type
  2    from teste1
  3   where SUBSTR(object_type,1,3) = 'CLU';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
C_COBJ#                        CLUSTER
C_TS#                          CLUSTER
C_FILE#_BLOCK#                 CLUSTER
C_USER#                        CLUSTER
C_OBJ#                         CLUSTER
SMON_SCN_TO_TIME_AUX           CLUSTER
C_OBJ#_INTCOL#                 CLUSTER
C_TOID_VERSION#                CLUSTER
C_MLOG#                        CLUSTER
C_RG#                          CLUSTER

10 rows selected.

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 '%dbtw101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
8ymzy3yvpvask            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw101  */ object_name, object_type   from teste1  where
SUBSTR(object_type,:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2"

Plan hash value: 110554063

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |     10 |00:00:00.02 |    1471 |
|*  1 |  TABLE ACCESS FULL| TESTE1 |      1 |    987 |     10 |00:00:00.02 |    1471 |
--------------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_TYPE",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

SQL >
SQL > --------------------------------------------------------------------------------------------------------------------
SQL > --------------------------------------------------------------------------------------------------------------------
SQL > --------------------------------------------------------------------------------------------------------------------
SQL >
SQL > alter session set OPTIMIZER_DYNAMIC_SAMPLING = 3;

Session altered.

SQL > select /* dbtw102  */ object_name, object_type
  2    from teste1
  3   where SUBSTR(object_type,1,3) = 'CLU';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
C_COBJ#                        CLUSTER
C_TS#                          CLUSTER
C_FILE#_BLOCK#                 CLUSTER
C_USER#                        CLUSTER
C_OBJ#                         CLUSTER
SMON_SCN_TO_TIME_AUX           CLUSTER
C_OBJ#_INTCOL#                 CLUSTER
C_TOID_VERSION#                CLUSTER
C_MLOG#                        CLUSTER
C_RG#                          CLUSTER

10 rows selected.

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 '%dbtw102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
11xnnj3yssf65            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw102  */ object_name, object_type   from teste1  where
SUBSTR(object_type,:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2"

Plan hash value: 110554063

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |     10 |00:00:00.02 |    1471 |
|*  1 |  TABLE ACCESS FULL| TESTE1 |      1 |     38 |     10 |00:00:00.02 |    1471 |
--------------------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OBJECT_TYPE",:SYS_B_0,:SYS_B_1)=:SYS_B_2)

Note
-----
   - dynamic sampling used for this statement (level=3)


23 rows selected.

SQL >

 

Quando a consulta utiliza uma expressão na cláusula WHERE as estatísticas dinâmicas são geradas somente se o parâmetro OPTIMIZER_DYNAMIC_SAMPLING for definido como 3 ou maior que 3. Na simulação acima temos a execução da mesma consulta duas vezes, na primeira o parâmetro OPTIMIZER_DYNAMIC_SAMPLING foi definido como 2 e podemos observar no plano de execução que o Otimizador não utilizou as estatísticas dinâmicas, já na segunda execução o parâmetro foi alterado para 3 e nesse caso o Otimizador utiliza as estatísticas dinâmicas. Observando a estimativa da cardinalidade das operações nas duas execuções podemos constatar que a estimativa utilizando a estatísticas dinâmicas foi muito melhor, a estimativa do otimizador utilizando estatísticas dinâmicas foi 4 vezes maior que o numero de linhas recuperadas, já a estimativa utilizando as estatísticas regulares da tabela foi 100 vezes maior que o numero de linhas recuperadas.

Simulação quando consulta usa predicado complexo

 

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
SQL > alter session set OPTIMIZER_DYNAMIC_SAMPLING = 3;

Session altered.

SQL > create index idx_teste1_col1_col2 on teste1(col1,col2) ;

Index created.

SQL > select /* dbtw201  */ object_name, object_type
  2    from teste1
  3   where col1='D' and col2='D';

no rows selected

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 '%dbtw201%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
c069870q93pbg            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw201  */ object_name, object_type   from teste1  where
col1=:"SYS_B_0" and col2=:"SYS_B_1"

Plan hash value: 110554063

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |      0 |00:00:00.01 |    1470 |
|*  1 |  TABLE ACCESS FULL| TESTE1 |      1 |  24680 |      0 |00:00:00.01 |    1470 |
--------------------------------------------------------------------------------------

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

   1 - filter(("COL1"=:SYS_B_0 AND "COL2"=:SYS_B_1))


19 rows selected.

SQL >
SQL > -------------------------------------------------------------------------------------------------------------------------------------------------
SQL > -------------------------------------------------------------------------------------------------------------------------------------------------
SQL > -------------------------------------------------------------------------------------------------------------------------------------------------
SQL >
SQL > alter session set optimizer_dynamic_sampling = 4;

Session altered.

SQL > select /* dbtw202  */ object_name, object_type
  2    from teste1
  3   where col1='D' and col2='D';

no rows selected

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 '%dbtw202%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
8zby3uhd6cjmc            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* dbtw202  */ object_name, object_type   from teste1  where
col1=:"SYS_B_0" and col2=:"SYS_B_1"

Plan hash value: 1718104728

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |      1 |        |      0 |00:00:00.01 |       2 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTE1               |      1 |     38 |      0 |00:00:00.01 |       2 |      1 |
|*  2 |   INDEX RANGE SCAN          | IDX_TESTE1_COL1_COL2 |      1 |     38 |      0 |00:00:00.01 |       2 |      1 |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=:SYS_B_0 AND "COL2"=:SYS_B_1)

Note
-----
   - dynamic sampling used for this statement (level=4)


24 rows selected.

SQL >

 

Quando uma instrução SQL utiliza predicado complexo o Otimizador tem mais dificuldades para estimar a cardinalidade das operações, para que o Otimizador utilize as estatísticas dinâmicas na estimativa de cardinalidade dessas instruções SQL e preciso definir o nível do parâmetro OPTIMIZER_DYNAMIC_SAMPLING igual a 4 ou maior que 4. Na simulação acima executamos uma consulta duas vezes com predicado complexo, na primeira com o parâmetro OPTIMIZER_DYNAMIC_SAMPLING definido no nível 3 e na segunda no nível 4, a primeira execução não utilizou as estatísticas dinâmicas, o Otimizador fez uma péssima estimativa da cardinalidade e o resultado foi um plano de execução muito ruim, na segunda execução o otimizador utilizou as estatísticas dinâmicas e a estimativa de cardinalidade foi muito melhor culminando com um plano de execução de melhor qualidade.

Conclusão

As estatísticas dinâmicas aumentam a capacidade do Otimizador de produzir planos de execução de melhor qualidade, quando estivermos analisando instruções SQL que apresentam desempenho ruim e verificarmos que a cardinalidade estimada pelo Otimizador não é de boa qualidade podemos recorrer a este recurso, seja de forma global definindo o parâmetro OPTIMIZER_DYNAMIC_SAMPLING a nível de instância ou de forma pontual definindo este parâmetro a nível de HINT.

Referências

http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101

http://www.oracle.com/technetwork/testcontent/o19asktom-086775.html

https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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