Estatísticas: a matéria prima do otimizador

fevereiro 5, 2017 V. J.A. Estatísticas

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 Otimizador.

Ausência de estatísticas

Quando o Otimizador esta gerando o plano de execução de uma instrução SQL e a tabela referenciada nesta instrução não possui estatísticas, o Otimizador utiliza o recurso “Dynamic Sampling” que foi disponibilizado a partir da versão 9iR2 do Oracle Database. Este recurso foi criado para melhorar a qualidade do plano de execução neste tipo de situação, pois até então o Otimizador utilizava algumas pressuposições de cardinalidade que tinha como resultado planos de execução de baixa qualidade.

A qualidade das estatísticas geradas pelo “Dynamic Sampling” são inferiores as estatísticas geradas pelo pacote DBMS_STATS, portanto quando estiver analisando um plano de execução cuja cardinalidade estimada for muito diferente da cardinalidade real e for constatada a ausência de estatísticas para a tabela da operação em questão, a melhor solução neste caso é gerar as estatísticas dessa tabela utilizando o pacote DBMS_STATS.

Para ver na prática essa situação vamos realizar as seguintes operações:

1) Criar uma tabela e não coletar suas estatísticas
2) Executar a consulta que utilizará essa tabela como fonte de dados e verificar a cardinalidade estimada pelo Otimizador
3) Coletar as estatísticas da tabela
4) Executar a mesma consulta novamente e verificar se houve mudança na cardinalidade estimada pelo Otimizador

Criar a tabela

Primeiro vamos criar a tabela que sera utilizada na consulta sem 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

SQL > SELECT * FROM V$VERSION where rownum < 2;

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

SQL > alter session set workarea_size_policy=MANUAL;

Sessão alterada.

SQL > alter session set sort_area_size=2000000000;

Sessão alterada.

SQL > create table dbtw01 as
2 select 'DBTimeWizard - Performance and Tuning' as produto,
3 mod(rownum,5) as codigo,
4 mod(rownum,1000) as cliente_id ,
5 mod(rownum,2000000)as postal_id,
6 5000 as total_vendas,
7 trunc(sysdate - 9999 + mod(rownum,10000)) as data_venda
8 from dual connect by level<=2e7;

Tabela criada.

SQL >
SQL > select round(bytes/1024/1024/1024,1) as "Tamanho GB"
2 from user_segments
3 where segment_name = 'DBTW01';

Tamanho GB
----------
1,4

SQL >

Executar a consulta

Agora vamos executar uma consulta na tabela criada e listar o plano de execução gerado pelo Otimizador:

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:

https://dbtimewizard.com.br/mentoria

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

SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > select /* dbtw001 */ cliente_id, produto, postal_id
2 from DBTW01
3 where postal_id=1869326;

CLIENTE_ID PRODUTO POSTAL_ID
---------- ------------------------------------- ----------
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326

10 linhas selecionadas.

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
------------- ------------
44xjytpsyj617 0

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44xjytpsyj617, child number 0
-------------------------------------
select /* dbtw001 */ cliente_id, produto, postal_id from DBTW01
where postal_id=:"SYS_B_0"

Plan hash value: 658621498

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:04.96 | 183K| 183K|
|* 1 | TABLE ACCESS FULL| DBTW01 | 1 | 2031 | 10 |00:00:04.96 | 183K| 183K|
-----------------------------------------------------------------------------------------------

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

1 - filter("POSTAL_ID"=:SYS_B_0)

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

23 linhas selecionadas.

SQL >

Nesta etapa podemos observar dois pontos importantes:

1) No plano de execução verificamos que o Otimizador estimou que a cardinalidade da tabela “DBTW01” seriam 2031 registros (E-ROWS), quando na verdade foram 10 registros (A-ROWS).

2) Na seção “Note” do plano de execução observamos a seguinte frase “dynamic sampling used for this statement (level=2)”, isso significa que o recurso “Dynamic Sampling” foi utilizado.

Coletar as estatísticas da tabela

Na primeira execução da consulta verificamos que o recurso “Dynamic Sampling” estimou a cardinalidade com pouca precisão, isto ocorre porque este recurso lê somente alguns blocos da tabela e baseado na ocorrência dos registros nesses blocos lidos, ele estima a cardinalidade final. Para melhorar a qualidade da cardinalidade estimada pelo Otimizador vamos coletar as estatísticas da tabela utilizando o pacote DBMS_STATS:

1
2
3
4
5

SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW01', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16);

Procedimento PL/SQL concluído com sucesso.

SQL >

Executar a consulta novamente

Finalmente vamos executar a consulta na tabela com as estatísticas geradas pelo pacote DBMS_STATS e verificar a qualidade da cardinalidade estimada pelo Otimizador:

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

SQL > select /* dbtw002 */ cliente_id, produto, postal_id
2 from DBTW01
3 where postal_id=1869326;

CLIENTE_ID PRODUTO POSTAL_ID
---------- ------------------------------------- ----------
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326
326 DBTimeWizard - Performance and Tuning 1869326

10 linhas selecionadas.

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
------------- ------------
c6kdqz1rm8mx6 0

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c6kdqz1rm8mx6, child number 0
-------------------------------------
select /* dbtw002 */ cliente_id, produto, postal_id from DBTW01
where postal_id=:"SYS_B_0"

Plan hash value: 658621498

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:05.10 | 183K| 183K|
|* 1 | TABLE ACCESS FULL| DBTW01 | 1 | 10 | 10 |00:00:05.10 | 183K| 183K|
-----------------------------------------------------------------------------------------------

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

1 - filter("POSTAL_ID"=:SYS_B_0)

19 linhas selecionadas.

SQL >

Analisando o plano de execução acima verificamos que a cardinalidade estimada pelo Otimizador foi perfeita (10 registros), igual a quantidade de registros lidos (E-ROWS = A-ROWS).

Alem disso, não aparece mais a seção “Note” o que indica que o Otimizador deixou de utilizar o recurso “Dynamic Sampling” pois haviam estatísticas da tabela disponíveis.

Falta de atualização das estatísticas

O processo de atualização das estatísticas é executado no período noturno por padrão para evitar o impacto na performance das aplicações no horário comercial e nesse processo algumas tabelas podem ficar dias sem que suas estatísticas sejam atualizadas pois a quantidade de registros atualizados não atingiu 10% do total de registros da tabela, esse mecanismo existe para que tabelas que não tenham atualizações ou cujas as atualizações sejam feitas com frequência reduzida não tenham suas estatísticas coletadas todos os dias, gerando uma carga de trabalho desnecessária no banco de dados.

Este período que a tabela fica sem atualização das estatísticas, pode afetar as estimativas de cardinalidade do Otimizador no momento da geração do plano de execução de uma instrução SQL que utilize esta tabela como fonte de dados.

Para facilitar o entendimento desse mecanismo, vamos realizar a seguir algumas operações que demonstram uma situação onde este tipo de problema pode ocorrer:

1) Criar uma tabela, eliminar os registros do ultimo dia, coletar suas estatísticas e inserir os registros do ultimo dia para simular uma situação de estatísticas desatualizadas
2) Executar uma consulta que utilizará essa tabela como fonte de dados e verificar a cardinalidade estimada pelo Otimizador
3) Coletar as estatísticas da tabela
4) Executar a mesma consulta novamente e verificar a cardinalidade estimada pelo Otimizador

Criar a tabela

Para simular a situação de estatísticas desatualizadas, vamos criar a tabela, eliminar os registros do dia da criação da tabela, coletar as estatísticas e inserir registros com a data do dia da criação da tabela:

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

SQL > SELECT * FROM V$VERSION where rownum < 2;

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

1 linha selecionada.

SQL >
SQL > create table dbtw02 as
2 select 'DBTimeWizard - Performance and Tuning' as produto,
3 mod(rownum,5) as codigo,
4 mod(rownum,1000) as cliente_id ,
5 mod(rownum,2000000)as postal_id,
6 5000 as total_vendas,
7 trunc(sysdate - 9999 + mod(rownum,10000)) as data_venda
8 from dual connect by level<=100000;

Tabela criada.

SQL >
SQL > delete from dbtw02 where data_venda = trunc(sysdate);

10 linhas deletadas.

SQL >
SQL > create index idx_dbtw02_dt_venda on dbtw02(data_venda) parallel 16;

Índice criado.

SQL >
SQL > alter index idx_dbtw02_dt_venda noparallel;

Índice alterado.

SQL >
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW02', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > insert /*+ append */ into dbtw02
2 select 'DBTimeWizard - Performance and Tuning' as produto,
3 mod(rownum,5) as codigo,
4 mod(rownum,1000) as cliente_id ,
5 mod(rownum,2000000)as postal_id,
6 5000 as total_vendas,
7 trunc(sysdate) as data_venda
8 from dual connect by level<= 10000;

10000 linhas criadas.

SQL >
SQL > commit;

Commit concluído.

SQL >
SQL > EXEC dbms_stats.flush_database_monitoring_info ;

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > select TABLE_NAME, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_NAME = 'DBTW02';

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
DBTW02 10000 0 0

1 linha selecionada.

SQL >
SQL > col stale_stats for a20;
SQL > SELECT table_name, stale_stats FROM user_tab_statistics where table_name = 'DBTW02';

TABLE_NAME STALE_STATS
------------------------------ --------------------
DBTW02 YES

1 linha selecionada.

SQL >

Executar a consulta

Agora vamos executar uma consulta na tabela criada e listar o plano de execução gerado pelo Otimizador:

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

SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > SELECT /*+ dbtw001 */ codigo, produto, data_venda
2 FROM dbtw02
3 WHERE data_venda = trunc(sysdate);

CODIGO PRODUTO DATA_VEN
---------- ------------------------------------- --------
1 DBTimeWizard - Performance and Tuning 24/01/17
..............
4 DBTimeWizard - Performance and Tuning 24/01/17
0 DBTimeWizard - Performance and Tuning 24/01/17

10000 linhas selecionadas.

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
------------- ------------
9gp82gcn5k01b 0

1 linha selecionada.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9gp82gcn5k01b, child number 0
-------------------------------------
SELECT /*+ dbtw001 */ codigo, produto, data_venda FROM dbtw02 WHERE
data_venda = trunc(sysdate)

Plan hash value: 3866670392

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:00.13 | 317 | 90 |
| 1 | TABLE ACCESS BY INDEX ROWID| DBTW02 | 1 | 10 | 10000 |00:00:00.13 | 317 | 90 |
|* 2 | INDEX RANGE SCAN | IDX_DBTW02_DT_VENDA | 1 | 10 | 10000 |00:00:00.01 | 126 | 0 |
----------------------------------------------------------------------------------------------------------------------

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

2 - access("DATA_VENDA"=TRUNC(SYSDATE@!))

20 linhas selecionadas.

SQL >

Ao examinarmos o plano de execução verificamos que o Otimizador errou feio na estimativa de cardinalidade da tabela “DBTW02”, estimou que seriam 10 registros (E-ROWS), quando na verdade foram 10000 registros (A-ROWS).

Coletar as estatísticas da tabela

Na consulta acima verificamos uma das inúmeras situações que podem ocorrer numa base de dados e que levam o Otimizador a falhar na estimativa de cardinalidade do plano de execução, o pacote DBMS_STATS possui inúmeras funções e procedimentos que podem ser utilizados para gerenciar a coleta de estatísticas e melhorar sua qualidade. Para demonstrar o efeito das estatísticas atualizadas na estimativa de cardinalidade vamos coletar a estatísticas novamente:

1
2
3
4
5

SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW02', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16);

Procedimento PL/SQL concluído com sucesso.

SQL >

Executar a consulta novamente

Vamos executar a consulta na tabela com as estatísticas atualizadas e verificar a qualidade da cardinalidade estimada pelo Otimizador.

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

SQL > SELECT /*+ dbtw002 */ codigo, produto, data_venda
2 FROM dbtw02
3 WHERE data_venda = trunc(sysdate);

CODIGO PRODUTO DATA_VEN
---------- ------------------------------------- --------
1 DBTimeWizard - Performance and Tuning 24/01/17
.......................
4 DBTimeWizard - Performance and Tuning 24/01/17
0 DBTimeWizard - Performance and Tuning 24/01/17

10000 linhas selecionadas.

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
------------- ------------
16b548kz49wnh 0

1 linha selecionada.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 16b548kz49wnh, child number 0
-------------------------------------
SELECT /*+ dbtw002 */ codigo, produto, data_venda FROM dbtw02 WHERE
data_venda = trunc(sysdate)

Plan hash value: 202550308

--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:00.01 | 1107 |
|* 1 | TABLE ACCESS FULL| DBTW02 | 1 | 10176 | 10000 |00:00:00.01 | 1107 |
--------------------------------------------------------------------------------------

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

1 - filter("DATA_VENDA"=TRUNC(SYSDATE@!))

19 linhas selecionadas.

SQL >

Verificando o plano de execução acima constatamos que a cardinalidade estimada pelo Otimizador foi muito próxima do real. (E-ROWS=10176 A-ROWS=10000)

Conclusão

Nesse artigo verificamos através de exemplos a importância das estatísticas para que o Otimizador consiga realizar estimativas de cardinalidade com precisão, o gerenciamento da coleta de estatísticas é crucial para manter a qualidade das mesmas. O pacote DBMS_STATS pode e deve ser utilizado para fazer os ajustes necessários no processo de coleta de estatísticas de acordo com as características dos dados e como eles são atualizados.