Como pode um plano de execução mudar quando não há alterações no banco?

abril 3, 2016 V. J.A. Estatísticas

Como pode um plano de execução mudar de repente, quando ninguém fez qualquer alteração no banco de dados?

Por nenhuma mudança, queremos dizer que não houve alterações na estrutura das tabelas, não foram adicionados ou alterados índices, nenhuma mudança relativa a “Bind Peeking”, sem mudanças de parâmetros do banco, não houve aplicação novos patches ou upgrades, não houve criação de Outlines ou Profiles, não houve coleta de estatísticas do sistema e não houve quaisquer alterações nas estatísticas CBO.

O DBA não fez qualquer alteração e de repente, sem razão aparente, o plano de execução foi modificado e (por exemplo) um índice inadequado passa a ser usado e causa degradação no desempenho da instrução SQL.

Como isso pode ser possível?

Conceitos distorcidos

Exitem dois pontos que precisamos esclarecer para conseguir compreender a resposta para a questão acima proposta:

1. há um equívoco comum que se não houver coleta de estatísticas (e assumindo que nada mais é alterado no banco de dados), o plano de execução vai permanecer sempre o mesmo e pode-se garantir que o banco de dados manterá a mesma performance.

Esta afirmação não é verdadeira. Na verdade, é exatamente o oposto. É necessário coletar novas estatísticas para garantir que os planos de execução não mudem. São as estatísticas desatualizadas que podem causar mudança nos planos de execução.

2. Quando alguém analisa todas as coisas que poderiam ter mudado no banco de dados, dois aspectos importantes normalmente são ignorados:

O primeiro aspecto é que os dados das aplicações normalmente estão em constante mudança na maioria das bases de dados. A mudança nos dados das aplicações podem influenciar diretamente o comportamento do 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

O segundo aspecto que esta em constante modificação é o tempo. Quando a performance das transações do banco estavam funcionando bem, o banco estava em um ponto diferente no tempo do que esta agora quando as transações estão apresentando problema de performance.

Portanto, nem todas as mudanças que ocorrem no banco estão sob o controle do DBA.

Demonstração prática

A seguir vamos ver um exemplo prático de como um plano de execução pode ser modificado pelo Otimizador numa base em que o DBA não fará qualquer alteração, haverá mudança somente nos dois aspectos que estão fora do controle do DBA, os dados da aplicação e o passar do tempo.

Para execução da consulta vamos criar um tabela e incluir nela uns 5 anos de registros, criar um índice 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

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

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

1 row selected.

SQL >
SQL > create table dbtw01 (id number, reg_date date, name varchar2(20));

Table created.

SQL >
SQL >
SQL >
SQL > declare
2 v_count number;
3 begin
4 v_count:=0;
5 for i in 1..1830 loop
6 for j in 1..1000 loop
7 v_count:= v_count+1;
8 insert into dbtw01 values (v_count, sysdate-i, 'DBTimeWizard');
9 end loop;
10 end loop;
11 commit;
12 end;
13 /

PL/SQL procedure successfully completed.

SQL >
SQL >
SQL >
SQL > create index dbtw01_idx on dbtw01(reg_date);

Index created.

SQL >
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'DBTW01', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL >

Consulta normal com as estatísticas atualizadas

Vamos executar uma consulta típica de uma aplicação onde estamos interessados nos valores de dados dos últimos doze meses. Esta consulta é executada diariamente e trabalha com uma “janela móvel” de dados e os registros relevantes são aqueles que foram gerados nos últimos 365 dias.

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

SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL > select /* tst101 */ *
2 from dbtw01
3 where reg_date > sysdate - 365;

ID REG_DATE NAME
---------- --------- ------------------------------
......

358582 08-APR-15 DBTimeWizard
358583 08-APR-15 DBTimeWizard
358584 08-APR-15 DBTimeWizard
358585 08-APR-15 DBTimeWizard
358586 08-APR-15 DBTimeWizard
358587 08-APR-15 DBTimeWizard

364000 rows selected.

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

SQL_ID CHILD_NUMBER
------------- ------------
ga3uqz5gpxufn 0

1 row selected.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ga3uqz5gpxufn, child number 0
-------------------------------------
select /* tst101 */ * from dbtw01 where reg_date > sysdate -
:"SYS_B_0"

Plan hash value: 658621498

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2196 (100)| | 364K|00:00:00.29 | 32199 |
|* 1 | TABLE ACCESS FULL| DBTW01 | 1 | 364K| 9251K| 2196 (3)| 00:00:27 | 364K|00:00:00.29 | 32199 |
----------------------------------------------------------------------------------------------------------------------

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

1 - filter("REG_DATE">SYSDATE@!-:SYS_B_0)

19 rows selected.

SQL >

Podemos observar na log acima que o Otimizador (CBO) escolheu uma plano de execução que faz uma operação de “Full Table Scan” (FTS), pois a quantidade de registros a ser acessada corresponde a um percentual alto da tabela e o acesso desses registros consultando também um índice seria ineficiente. Observe também como o Otimizador foi preciso com relação a cardinalidade e previu corretamente o número de registros a ser retornado. Quando o Otimizador consegue estimar a cardinalidade com alta precisão a eficiência do plano de execução que ele gera é muito boa. Nesse caso como os usuários estão satisfeitos com o tempo de resposta da consulta vamos imaginar que o DBA decide não coletar mais estatísticas dessa tabela para evitar que o Otimizador mude o plano de execução numa execução futura.

Entretanto todos os dias temos milhares de registros sendo inseridos nesta tabela, e para simular esta situação vamos executar uma “procedure” que vai simular a inserção de registros nos próximos 365 dias.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SQL > declare
2 v_count number;
3 begin
4 v_count:=1830000;
5 for i in 1..365 loop
6 for j in 1..1000 loop
7 v_count:= v_count+1;
8 insert into dbtw01 values (v_count, sysdate+i, 'DBTimeWizard');
9 end loop;
10 end loop;
11 commit;
12 end;
13 /

PL/SQL procedure successfully completed.

SQL >

Consulta após um ano sem atualização das estatísticas

Como na execução da “procedure” simulamos que 365 dias se passaram, vamos agora executar aquela consulta que tem periodicidade diária e simular esse avanço no tempo, para isso vamos somar a data atual (sysdate) mais 365 dias, ou seja nossa consulta vai viajar no tempo para o futuro. Um detalhe muito importante como foi dito anteriormente o DBA decidiu suspender a coleta de estatísticas para evitar que o plano fosse alterado pelo Otimizador, portanto não vamos coletar as estatísticas 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

SQL > select /* tst102 */ *
2 from dbtw01
3 where reg_date > (sysdate + 365) - 365;

ID REG_DATE NAME
---------- --------- ------------------------------
......

2194905 01-APR-17 DBTimeWizard
2194906 01-APR-17 DBTimeWizard
2194907 01-APR-17 DBTimeWizard
2194908 01-APR-17 DBTimeWizard
2194909 01-APR-17 DBTimeWizard

365000 rows selected.

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

SQL_ID CHILD_NUMBER
------------- ------------
51g8b9my5ya4s 0

1 row selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 51g8b9my5ya4s, child number 0
-------------------------------------
select /* tst102 */ * from dbtw01 where reg_date > (sysdate +
:"SYS_B_0") - :"SYS_B_1"

Plan hash value: 954539352

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 365K|00:00:00.40 | 51611 |
| 1 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 1 | 961 | 24986 | 11 (0)| 00:00:01 | 365K|00:00:00.40 | 51611 |
|* 2 | INDEX RANGE SCAN | DBTW01_IDX | 1 | 961 | | 5 (0)| 00:00:01 | 365K|00:00:00.22 | 25577 |
------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("REG_DATE">SYSDATE@!+:SYS_B_0-:SYS_B_1)

20 rows selected.

SQL >

Verificando o resultado da nossa consulta acima, podemos observar que o plano de execução mudou. O acesso a tabela agora utiliza o índice DBTW01_IDX, observe também que a estimativa de linhas a ser acessada esta muito ruim (963 linhas), quando na realidade a consulta acessou 365 mil linhas.

A consulta é a mesma, o DBA não fez alterações no banco, no entanto o plano de execução mudou e o desempenho da consulta ficou pior pois na primeira execução o banco acessou 32199 Buffers e nesta ultima 51611 Buffers.

Por que o plano de execução mudou quando não houve alterações na estatísticas?

Para o Otimizador o registro mais atual na tabela é de 1 ano atrás em relação a data da execução da consulta (sysdate+365), pois essa é a informação que ele consegue obter consultando as estatísticas, portanto para calcular a estimativa de linhas que vai ser recuperada da tabela dos últimos 365 dias, ele considera que só existem registros de 1 dia.

Como vamos corrigir esse plano de execução ineficiente?

Consulta após um ano com atualização das estatísticas

Para que o Otimizador volte a escolher o melhor plano de execução, precisamos deixar que ele obtenha informações mais precisas sobre os dados existentes na tabela e isso só é possível se atualizarmos 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

SQL > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'DBTW01', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL >
SQL > select /* tst103 */ *
2 from dbtw01
3 where reg_date > (sysdate + 365) - 365;

ID REG_DATE NAME
---------- --------- ------------------------------
......

2125897 22-JAN-17 DBTimeWizard
2125898 22-JAN-17 DBTimeWizard
2125899 22-JAN-17 DBTimeWizard
2125900 22-JAN-17 DBTimeWizard
2125901 22-JAN-17 DBTimeWizard

365000 rows selected.

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

SQL_ID CHILD_NUMBER
------------- ------------
18mprzh16nz46 0

1 row selected.

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 18mprzh16nz46, child number 0
-------------------------------------
select /* tst103 */ * from dbtw01 where reg_date > (sysdate +
:"SYS_B_0") - :"SYS_B_1"

Plan hash value: 658621498

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2820 (100)| | 365K|00:00:02.72 | 33825 | 9589 |
|* 1 | TABLE ACCESS FULL| DBTW01 | 1 | 365K| 9637K| 2820 (3)| 00:00:34 | 365K|00:00:02.72 | 33825 | 9589 |
-------------------------------------------------------------------------------------------------------------------------------

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

1 - filter("REG_DATE">SYSDATE@!+:SYS_B_0-:SYS_B_1)

19 rows selected.

SQL >

Quando atualizamos as estatísticas e executamos novamente a consulta que avança no tempo 1 ano, podemos verificar que a estimativa de cardinalidade volta a ser precisa (E-Rows = A-Rows) e o plano de execução volta a ser o mesmo da primeira execução com acesso a tabela através de uma operação de FULL TABLE SCAN.

Conclusão

Portanto respondendo a questão apresentada no inicio desse artigo: sim, um plano de execução pode mudar, mesmo que não ocorram quaisquer alterações no banco de dados, incluindo a coleta de estatísticas de dados. Se você acha que não coletando estatísticas de dados, as coisas vão simplesmente permanecer inalteradas, um dia, quando você menos espera, você vai se deparar com planos de execução diferentes e com desempenho indesejado.