{"id":399,"date":"2016-04-03T22:04:11","date_gmt":"2016-04-04T01:04:11","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=399"},"modified":"2021-01-21T18:28:23","modified_gmt":"2021-01-21T21:28:23","slug":"como-pode-um-plano-de-execucao-mudar-quando-nao-ha-alteracoes-no-banco","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/como-pode-um-plano-de-execucao-mudar-quando-nao-ha-alteracoes-no-banco\/","title":{"rendered":"Como pode um plano de execu\u00e7\u00e3o mudar quando n\u00e3o h\u00e1 altera\u00e7\u00f5es no banco?"},"content":{"rendered":"

Como pode um plano de execu\u00e7\u00e3o mudar de repente, quando ningu\u00e9m fez qualquer altera\u00e7\u00e3o no banco de dados?<\/span><\/p>\n

Por nenhuma mudan\u00e7a, queremos dizer que n\u00e3o houve altera\u00e7\u00f5es na estrutura das tabelas, n\u00e3o foram adicionados ou alterados \u00edndices, nenhuma mudan\u00e7a relativa a “Bind Peeking”, sem mudan\u00e7as de par\u00e2metros do banco, n\u00e3o houve aplica\u00e7\u00e3o novos patches ou upgrades, n\u00e3o houve cria\u00e7\u00e3o de Outlines ou Profiles, n\u00e3o houve coleta de estat\u00edsticas do sistema e n\u00e3o houve quaisquer altera\u00e7\u00f5es nas estat\u00edsticas CBO.<\/span><\/p>\n

O DBA n\u00e3o fez qualquer altera\u00e7\u00e3o e de repente, sem raz\u00e3o aparente, o plano de execu\u00e7\u00e3o foi\u00a0modificado e (por exemplo) um \u00edndice inadequado passa a ser usado e causa degrada\u00e7\u00e3o no desempenho da instru\u00e7\u00e3o SQL.<\/span><\/p>\n

Como isso pode ser poss\u00edvel?<\/span><\/p>\n

Conceitos distorcidos<\/span><\/h2>\n

Exitem dois pontos que precisamos esclarecer para conseguir compreender a resposta para a quest\u00e3o acima proposta:<\/span><\/p>\n

1. h\u00e1 um equ\u00edvoco comum que se n\u00e3o houver coleta de estat\u00edsticas (e assumindo que nada mais \u00e9 alterado no banco de dados), o plano de execu\u00e7\u00e3o vai permanecer sempre o mesmo e pode-se garantir que o banco de dados manter\u00e1 a mesma performance.<\/span><\/p>\n

Esta afirma\u00e7\u00e3o n\u00e3o \u00e9 verdadeira. Na verdade, \u00e9 exatamente o oposto. \u00c9 necess\u00e1rio coletar novas estat\u00edsticas para garantir que os planos de execu\u00e7\u00e3o n\u00e3o mudem. S\u00e3o as estat\u00edsticas desatualizadas que podem causar mudan\u00e7a nos planos de execu\u00e7\u00e3o.<\/span><\/p>\n

2. Quando algu\u00e9m analisa todas as coisas que poderiam ter mudado no banco de dados, dois aspectos importantes normalmente s\u00e3o ignorados:<\/span><\/p>\n

O primeiro aspecto \u00e9 que os dados das aplica\u00e7\u00f5es normalmente est\u00e3o em constante mudan\u00e7a na maioria das bases de dados. A mudan\u00e7a nos dados das aplica\u00e7\u00f5es podem influenciar diretamente o comportamento do Otimizador.<\/span><\/p>\n

O segundo aspecto que esta em constante modifica\u00e7\u00e3o \u00e9 o tempo. Quando a performance das transa\u00e7\u00f5es do banco estavam funcionando bem, o banco estava em um ponto diferente no tempo do que esta agora quando as transa\u00e7\u00f5es est\u00e3o apresentando problema de performance.<\/span><\/p>\n

Portanto, nem todas as mudan\u00e7as que ocorrem no banco est\u00e3o sob o controle do DBA.<\/span><\/p>\n

Demonstra\u00e7\u00e3o pr\u00e1tica<\/span><\/h2>\n

A seguir vamos ver um exemplo pr\u00e1tico de como um plano de execu\u00e7\u00e3o pode ser modificado pelo Otimizador numa base em que o DBA n\u00e3o far\u00e1 qualquer\u00a0altera\u00e7\u00e3o, haver\u00e1 mudan\u00e7a somente nos dois aspectos que est\u00e3o fora do controle do DBA, os dados da aplica\u00e7\u00e3o e o passar do tempo.<\/span><\/p>\n

Para execu\u00e7\u00e3o da consulta\u00a0vamos criar um tabela e incluir nela uns 5\u00a0anos de registros, criar um \u00edndice e coletar as estat\u00edsticas.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw01 (<\/span>id number,<\/span> reg_date date<\/span><\/a>,<\/span> name varchar2(<\/span>20<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTable created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> declare<\/span>
\n  2<\/span>  v_count  number;<\/span>
\n  3<\/span>  begin
\n  4<\/span>  v_count:=<\/span>0<\/span>;<\/span>
\n  5<\/span>  for<\/span> i in 1<\/span>..<\/span>1830<\/span> loop
\n  6<\/span>     for<\/span> j in 1<\/span>..<\/span>1000<\/span> loop
\n  7<\/span>     v_count:=<\/span> v_count+<\/span>1<\/span>;<\/span>
\n  8<\/span>     insert into dbtw01 values (<\/span>v_count,<\/span> sysdate-<\/span>i,<\/span> 'DBTimeWizard'<\/span>)<\/span>;<\/span>
\n  9<\/span>     end<\/span><\/a> loop;<\/span>
\n 10<\/span>  end<\/span><\/a> loop;<\/span>
\n 11<\/span>  commit;<\/span>
\n 12<\/span>  end<\/span><\/a>;<\/span>
\n 13<\/span>  \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw01_idx on dbtw01(<\/span>reg_date)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>USER,<\/span> tabname=><\/span>'DBTW01'<\/span>,<\/span> cascade=><\/span>true<\/span>,<\/span> estimate_percent=><\/span>null<\/span>,<\/span> method_opt=><\/span>'FOR ALL COLUMNS SIZE 254'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consulta normal com as estat\u00edsticas atualizadas<\/span><\/h2>\n

Vamos executar uma consulta t\u00edpica de uma aplica\u00e7\u00e3o onde estamos interessados nos valores de dados dos \u00faltimos doze meses. Esta consulta \u00e9 executada diariamente e trabalha com uma “janela m\u00f3vel” de dados e os registros relevantes s\u00e3o aqueles que foram gerados nos \u00faltimos 365 dias.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span> select \/* tst101 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where reg_date ><\/span> sysdate -<\/span> 365<\/span>;<\/span>
\n
\n
\n        ID REG_DATE  NAME
\n----------<\/span> ---------<\/span> ------------------------------<\/span>
\n    ......<\/span>
\n   
\n    358582<\/span> 08<\/span>-<\/span>APR-<\/span>15<\/span> DBTimeWizard
\n    358583<\/span> 08<\/span>-<\/span>APR-<\/span>15<\/span> DBTimeWizard
\n    358584<\/span> 08<\/span>-<\/span>APR-<\/span>15<\/span> DBTimeWizard
\n    358585<\/span> 08<\/span>-<\/span>APR-<\/span>15<\/span> DBTimeWizard
\n    358586<\/span> 08<\/span>-<\/span>APR-<\/span>15<\/span> DBTimeWizard
\n    358587<\/span> 08<\/span>-<\/span>APR-<\/span>15<\/span> DBTimeWizard
\n
\n364000<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst101%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nga3uqz5gpxufn            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  ga3uqz5gpxufn,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* tst101 *\/<\/span> *<\/span>   from dbtw01  where reg_date ><\/span> sysdate -<\/span>
\n:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 658621498<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n----------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>        |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2196<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    364K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.29<\/span> |<\/span>   32199<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>    364K|<\/span>  9251K|<\/span>  2196<\/span>   (<\/span>3<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>27<\/span> |<\/span>    364K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.29<\/span> |<\/span>   32199<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"REG_DATE"<\/span>><\/span>SYSDATE@!-:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Podemos observar na log acima que o Otimizador (CBO) escolheu uma plano de execu\u00e7\u00e3o que faz uma opera\u00e7\u00e3o 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\u00e9m um \u00edndice seria ineficiente. Observe tamb\u00e9m como o Otimizador foi preciso\u00a0com rela\u00e7\u00e3o a cardinalidade e previu corretamente o n\u00famero de registros a ser retornado. Quando o Otimizador consegue estimar a cardinalidade com alta precis\u00e3o a efici\u00eancia do plano de execu\u00e7\u00e3o que ele gera \u00e9 muito boa. Nesse caso como os usu\u00e1rios est\u00e3o satisfeitos com o tempo de resposta da consulta vamos imaginar que o DBA decide n\u00e3o coletar mais estat\u00edsticas dessa tabela para evitar que o Otimizador mude o plano de execu\u00e7\u00e3o numa execu\u00e7\u00e3o futura.<\/span><\/p>\n

Entretanto todos os dias temos milhares de registros sendo inseridos nesta tabela, e para simular esta situa\u00e7\u00e3o vamos executar uma “procedure” que vai simular a inser\u00e7\u00e3o de registros nos pr\u00f3ximos 365 dias.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<\/div><\/td>
SQL ><\/span> declare<\/span>
\n  2<\/span>     v_count  number;<\/span>
\n  3<\/span>  begin
\n  4<\/span>     v_count:=<\/span>1830000<\/span>;<\/span>
\n  5<\/span>     for<\/span> i in 1<\/span>..<\/span>365<\/span> loop
\n  6<\/span>        for<\/span> j in 1<\/span>..<\/span>1000<\/span> loop
\n  7<\/span>        v_count:=<\/span> v_count+<\/span>1<\/span>;<\/span>
\n  8<\/span>        insert into dbtw01 values (<\/span>v_count,<\/span> sysdate+<\/span>i,<\/span> 'DBTimeWizard'<\/span>)<\/span>;<\/span>
\n  9<\/span>        end<\/span><\/a> loop;<\/span>
\n 10<\/span>     end<\/span><\/a> loop;<\/span>
\n 11<\/span>  commit;<\/span>
\n 12<\/span>  end<\/span><\/a>;<\/span>
\n 13<\/span>  \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

<\/h2>\n

Consulta ap\u00f3s um ano sem atualiza\u00e7\u00e3o das estat\u00edsticas<\/span><\/h2>\n

Como na execu\u00e7\u00e3o da “procedure” simulamos que 365 dias se passaram, vamos agora executar aquela consulta que tem periodicidade di\u00e1ria e simular esse avan\u00e7o no tempo, para isso vamos\u00a0somar a data atual (sysdate) mais 365 dias, ou seja nossa consulta vai viajar no tempo para o futuro. Um detalhe muito importante como foi\u00a0dito anteriormente o DBA decidiu suspender a coleta de estat\u00edsticas para evitar que o plano fosse alterado pelo Otimizador, portanto n\u00e3o vamos coletar as estat\u00edsticas da tabela.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> select \/* tst102 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where reg_date ><\/span> (<\/span>sysdate +<\/span> 365<\/span>)<\/span> -<\/span> 365<\/span>;<\/span>
\n
\n
\n        ID REG_DATE  NAME
\n----------<\/span> ---------<\/span> ------------------------------<\/span>
\n    ......<\/span>
\n   
\n   2194905<\/span> 01<\/span>-<\/span>APR-<\/span>17<\/span> DBTimeWizard
\n   2194906<\/span> 01<\/span>-<\/span>APR-<\/span>17<\/span> DBTimeWizard
\n   2194907<\/span> 01<\/span>-<\/span>APR-<\/span>17<\/span> DBTimeWizard
\n   2194908<\/span> 01<\/span>-<\/span>APR-<\/span>17<\/span> DBTimeWizard
\n   2194909<\/span> 01<\/span>-<\/span>APR-<\/span>17<\/span> DBTimeWizard
\n
\n365000<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst102%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n51g8b9my5ya4s            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  51g8b9my5ya4s,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* tst102 *\/<\/span> *<\/span>   from dbtw01  where reg_date ><\/span> (<\/span>sysdate +<\/span>
\n:<\/span>"SYS_B_0"<\/span>)<\/span> -<\/span> :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 954539352<\/span>
\n
\n------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name       |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT            |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>    11<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    365K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.40<\/span> |<\/span>   51611<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      1<\/span> |<\/span>    961<\/span> |<\/span> 24986<\/span> |<\/span>    11<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    365K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.40<\/span> |<\/span>   51611<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      1<\/span> |<\/span>    961<\/span> |<\/span>       |<\/span>     5<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    365K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.22<\/span> |<\/span>   25577<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"REG_DATE"<\/span>><\/span>SYSDATE@!+:<\/span>SYS_B_0-:<\/span>SYS_B_1)<\/span>
\n
\n
\n20<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o resultado da nossa consulta acima, podemos observar que o plano de execu\u00e7\u00e3o mudou. O acesso a tabela agora utiliza o \u00edndice DBTW01_IDX, observe tamb\u00e9m que a estimativa de linhas a ser acessada esta muito ruim\u00a0(963 linhas), quando na realidade a consulta acessou 365 mil linhas.<\/span><\/p>\n

A consulta \u00e9 a mesma, o DBA n\u00e3o fez altera\u00e7\u00f5es no banco, no entanto o plano de execu\u00e7\u00e3o mudou e o desempenho da consulta ficou pior pois na primeira execu\u00e7\u00e3o o banco acessou 32199 Buffers e nesta ultima 51611 Buffers.<\/span><\/p>\n

Por que o plano de execu\u00e7\u00e3o mudou quando n\u00e3o houve altera\u00e7\u00f5es na estat\u00edsticas?<\/span><\/p>\n

Para o Otimizador o registro mais atual na tabela \u00e9 de 1 ano atr\u00e1s em rela\u00e7\u00e3o a data da execu\u00e7\u00e3o da consulta (sysdate+365), pois essa \u00e9 a informa\u00e7\u00e3o que ele consegue obter consultando as estat\u00edsticas, portanto para calcular a estimativa de linhas que vai ser recuperada da tabela dos \u00faltimos 365 dias, ele considera que s\u00f3 existem registros de 1 dia.<\/span><\/p>\n

Como vamos corrigir esse plano de execu\u00e7\u00e3o ineficiente?<\/span><\/p>\n

Consulta ap\u00f3s um ano com atualiza\u00e7\u00e3o das estat\u00edsticas<\/span><\/h2>\n

Para que o Otimizador volte a escolher o melhor plano de execu\u00e7\u00e3o, precisamos deixar que ele obtenha informa\u00e7\u00f5es mais precisas sobre os dados existentes na tabela e isso s\u00f3 \u00e9 poss\u00edvel se atualizarmos as estat\u00edsticas.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>USER,<\/span> tabname=><\/span>'DBTW01'<\/span>,<\/span> cascade=><\/span>true<\/span>,<\/span> estimate_percent=><\/span>null<\/span>,<\/span> method_opt=><\/span>'FOR ALL COLUMNS SIZE 254'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* tst103 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where reg_date ><\/span> (<\/span>sysdate +<\/span> 365<\/span>)<\/span> -<\/span> 365<\/span>;<\/span>
\n
\n
\n        ID REG_DATE  NAME
\n----------<\/span> ---------<\/span> ------------------------------<\/span>
\n    ......<\/span>
\n   
\n   2125897<\/span> 22<\/span>-<\/span>JAN-<\/span>17<\/span> DBTimeWizard
\n   2125898<\/span> 22<\/span>-<\/span>JAN-<\/span>17<\/span> DBTimeWizard
\n   2125899<\/span> 22<\/span>-<\/span>JAN-<\/span>17<\/span> DBTimeWizard
\n   2125900<\/span> 22<\/span>-<\/span>JAN-<\/span>17<\/span> DBTimeWizard
\n   2125901<\/span> 22<\/span>-<\/span>JAN-<\/span>17<\/span> DBTimeWizard
\n
\n365000<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst103%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n18mprzh16nz46            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n-------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  18mprzh16nz46,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* tst103 *\/<\/span> *<\/span>   from dbtw01  where reg_date ><\/span> (<\/span>sysdate +<\/span>
\n:<\/span>"SYS_B_0"<\/span>)<\/span> -<\/span> :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 658621498<\/span>
\n
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>        |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>  2820<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>    365K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.72<\/span> |<\/span>   33825<\/span> |<\/span>   9589<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>    365K|<\/span>  9637K|<\/span>  2820<\/span>   (<\/span>3<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>34<\/span> |<\/span>    365K|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>02.72<\/span> |<\/span>   33825<\/span> |<\/span>   9589<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"REG_DATE"<\/span>><\/span>SYSDATE@!+:<\/span>SYS_B_0-:<\/span>SYS_B_1)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Quando atualizamos as estat\u00edsticas e executamos novamente a consulta que avan\u00e7a no tempo 1 ano, podemos verificar que a estimativa de cardinalidade volta a ser precisa (E-Rows = A-Rows) e o plano de execu\u00e7\u00e3o volta a ser o mesmo da primeira execu\u00e7\u00e3o com acesso a tabela atrav\u00e9s de uma opera\u00e7\u00e3o de FULL TABLE SCAN.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Portanto respondendo a quest\u00e3o apresentada no inicio desse artigo: sim, um plano de execu\u00e7\u00e3o pode mudar, mesmo que n\u00e3o ocorram quaisquer altera\u00e7\u00f5es no banco de dados, incluindo a coleta de estat\u00edsticas de dados. Se voc\u00ea acha que n\u00e3o coletando estat\u00edsticas de dados, as coisas v\u00e3o simplesmente permanecer inalteradas, um dia, quando voc\u00ea menos espera, voc\u00ea vai se deparar com planos de execu\u00e7\u00e3o diferentes e com desempenho indesejado.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

https:\/\/richardfoote.wordpress.com\/2010\/02\/16\/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo\/<\/a><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Como pode um plano de execu\u00e7\u00e3o mudar de repente, quando ningu\u00e9m fez qualquer altera\u00e7\u00e3o no banco de dados? Por nenhuma mudan\u00e7a, queremos dizer que n\u00e3o houve altera\u00e7\u00f5es na estrutura das tabelas, n\u00e3o foram adicionados ou alterados \u00edndices, nenhuma mudan\u00e7a relativa a “Bind Peeking”, sem mudan\u00e7as de par\u00e2metros do banco, n\u00e3o houve aplica\u00e7\u00e3o novos patches ou upgrades, n\u00e3o houve cria\u00e7\u00e3o de<\/p>\n","protected":false},"author":2,"featured_media":400,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[6,42,39,82,10,119],"class_list":["post-399","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-cbo","tag-cost-base-optimizer","tag-estatisticas","tag-optimizer","tag-otimizador","tag-statistics"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/399","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=399"}],"version-history":[{"count":15,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/399\/revisions"}],"predecessor-version":[{"id":2264,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/399\/revisions\/2264"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/400"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=399"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}