{"id":955,"date":"2017-02-05T22:45:14","date_gmt":"2017-02-06T00:45:14","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=955"},"modified":"2019-11-11T18:52:52","modified_gmt":"2019-11-11T21:52:52","slug":"estatisticas-a-materia-prima-do-otimizador","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/estatisticas-a-materia-prima-do-otimizador\/","title":{"rendered":"Estat\u00edsticas: a mat\u00e9ria prima do otimizador"},"content":{"rendered":"

No artigo “Por que o Otimizador criou um plano de execu\u00e7\u00e3o ineficiente?<\/a><\/strong>” elencamos algumas condi\u00e7\u00f5es que levam o Otimizador a gerar um plano de execu\u00e7\u00e3o ineficiente, neste artigo vamos mostrar com mais detalhes como a aus\u00eancia de estat\u00edsticas ou a falta de sua atualiza\u00e7\u00e3o podem influenciar de maneira negativa o Otimizador.<\/span><\/p>\n

 
\n 
\n <\/p>\n

Aus\u00eancia de estat\u00edsticas<\/span><\/h2>\n

Quando o Otimizador esta gerando o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL e a tabela referenciada nesta instru\u00e7\u00e3o n\u00e3o possui estat\u00edsticas, o Otimizador utiliza o recurso “Dynamic Sampling” que foi disponibilizado a partir da vers\u00e3o 9iR2 do Oracle Database. Este recurso foi criado para melhorar a qualidade do plano de execu\u00e7\u00e3o neste tipo de situa\u00e7\u00e3o, pois at\u00e9 ent\u00e3o o Otimizador utilizava algumas pressuposi\u00e7\u00f5es de cardinalidade que tinha como resultado planos de execu\u00e7\u00e3o de baixa qualidade.<\/span><\/p>\n

A qualidade das estat\u00edsticas geradas pelo “Dynamic Sampling” s\u00e3o inferiores as estat\u00edsticas geradas pelo pacote DBMS_STATS, portanto quando estiver analisando um plano de execu\u00e7\u00e3o cuja cardinalidade estimada for muito diferente da cardinalidade real e for constatada a aus\u00eancia de estat\u00edsticas para a tabela da opera\u00e7\u00e3o em quest\u00e3o, a melhor solu\u00e7\u00e3o neste caso \u00e9 gerar as estat\u00edsticas dessa tabela utilizando o pacote DBMS_STATS.<\/span><\/p>\n

Para ver na pr\u00e1tica essa situa\u00e7\u00e3o vamos realizar as seguintes opera\u00e7\u00f5es:<\/span><\/p>\n

1) Criar uma tabela e n\u00e3o coletar suas estat\u00edsticas<\/span>
\n2) Executar a consulta que utilizar\u00e1 essa tabela como fonte de dados e verificar a cardinalidade estimada pelo Otimizador <\/span>
\n3) Coletar as estat\u00edsticas da tabela <\/span>
\n4) Executar a mesma consulta novamente e verificar se houve mudan\u00e7a na cardinalidade estimada pelo Otimizador<\/span><\/p>\n

Criar a tabela<\/span><\/h3>\n

Primeiro vamos criar a tabela que sera utilizada na consulta sem coletar as estat\u00edsticas:<\/span>
\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
<\/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
\nSQL ><\/span> alter session set workarea_size_policy=<\/span>MANUAL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> alter session set sort_area_size=<\/span>2000000000<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> create table dbtw01 as<\/span>
\n  2<\/span>     select 'DBTimeWizard - Performance and Tuning'<\/span> as<\/span> produto,<\/span>
\n  3<\/span>            mod(<\/span>rownum,<\/span>5<\/span>)<\/span>    as<\/span> codigo,<\/span>
\n  4<\/span>            mod(<\/span>rownum,<\/span>1000<\/span>)<\/span> as<\/span> cliente_id ,<\/span>
\n  5<\/span>            mod(<\/span>rownum,<\/span>2000000<\/span>)<\/span>as<\/span> postal_id,<\/span>
\n  6<\/span>            5000<\/span> as<\/span> total_vendas,<\/span>
\n  7<\/span>            trunc(<\/span>sysdate -<\/span> 9999<\/span> +<\/span> mod(<\/span>rownum,<\/span>10000<\/span>)<\/span>)<\/span> as<\/span> data_venda
\n  8<\/span>       from dual connect by level<=<\/span>2e7<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select round<\/span><\/a>(<\/span>bytes\/<\/span>1024<\/span>\/<\/span>1024<\/span>\/<\/span>1024<\/span>,<\/span>1<\/span>)<\/span> as<\/span> "Tamanho GB"<\/span>
\n  2<\/span>    from user_segments
\n  3<\/span>   where segment_name =<\/span> 'DBTW01'<\/span>;<\/span>
\n
\nTamanho GB
\n----------<\/span>
\n       1<\/span>,<\/span>4<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Executar a consulta<\/span><\/h3>\n

Agora vamos executar uma consulta na tabela criada e listar o plano de execu\u00e7\u00e3o gerado pelo Otimizador:<\/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
69
70
71
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* dbtw001  *\/<\/span> cliente_id,<\/span> produto,<\/span> postal_id
\n  2<\/span>    from DBTW01
\n  3<\/span>   where postal_id=<\/span>1869326<\/span>;<\/span>
\n
\nCLIENTE_ID PRODUTO                                POSTAL_ID
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n
\n10<\/span> linhas selecionadas.<\/span>
\n
\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 '%dbtw001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n44xjytpsyj617            0<\/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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  44xjytpsyj617,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw001  *\/<\/span> cliente_id,<\/span> produto,<\/span> postal_id   from DBTW01
\nwhere postal_id=:<\/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> 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>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.96<\/span> |<\/span>     183K|<\/span>    183K|<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>   2031<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.96<\/span> |<\/span>     183K|<\/span>    183K|<\/span>
\n-----------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"POSTAL_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> dynamic sampling used for<\/span> this statement (<\/span>level=<\/span>2<\/span>)<\/span>
\n
\n
\n23<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Nesta etapa podemos observar dois pontos importantes:<\/span><\/p>\n

1) No plano de execu\u00e7\u00e3o verificamos que o Otimizador estimou que a cardinalidade da tabela “DBTW01” seriam 2031 registros (E-ROWS), quando na verdade foram 10 registros (A-ROWS).<\/span><\/p>\n

2) Na se\u00e7\u00e3o “Note” do plano de execu\u00e7\u00e3o observamos a seguinte frase “dynamic sampling used for this statement (level=2)”, isso significa que o recurso “Dynamic Sampling” foi utilizado.<\/span><\/p>\n

Coletar as estat\u00edsticas da tabela<\/span><\/h3>\n

Na primeira execu\u00e7\u00e3o da consulta verificamos que o recurso “Dynamic Sampling” estimou a cardinalidade com pouca precis\u00e3o, isto ocorre porque este recurso l\u00ea somente alguns blocos da tabela e baseado na ocorr\u00eancia dos registros nesses blocos lidos, ele estima a cardinalidade final. Para melhorar a qualidade da cardinalidade estimada pelo Otimizador vamos coletar as estat\u00edsticas da tabela utilizando o pacote DBMS_STATS:<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
<\/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> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Executar a consulta novamente<\/span><\/h3>\n

Finalmente vamos executar a consulta na tabela com as estat\u00edsticas geradas pelo pacote DBMS_STATS e verificar a qualidade da cardinalidade estimada pelo Otimizador:<\/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
<\/div><\/td>
SQL ><\/span> select \/* dbtw002  *\/<\/span> cliente_id,<\/span> produto,<\/span> postal_id
\n  2<\/span>    from DBTW01
\n  3<\/span>   where postal_id=<\/span>1869326<\/span>;<\/span>
\n
\nCLIENTE_ID PRODUTO                                POSTAL_ID
\n----------<\/span> -------------------------------------<\/span> ----------<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n       326<\/span> DBTimeWizard -<\/span> Performance and Tuning    1869326<\/span>
\n
\n10<\/span> linhas selecionadas.<\/span>
\n
\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 '%dbtw002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nc6kdqz1rm8mx6            0<\/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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  c6kdqz1rm8mx6,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw002  *\/<\/span> cliente_id,<\/span> produto,<\/span> postal_id   from DBTW01
\nwhere postal_id=:<\/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> 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>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>05.10<\/span> |<\/span>     183K|<\/span>    183K|<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>     10<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>05.10<\/span> |<\/span>     183K|<\/span>    183K|<\/span>
\n-----------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"POSTAL_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o acima verificamos que a cardinalidade estimada pelo Otimizador foi perfeita (10 registros), igual a quantidade de registros lidos (E-ROWS = A-ROWS).<\/span><\/p>\n

Alem disso, n\u00e3o aparece mais a se\u00e7\u00e3o “Note” o que indica que o Otimizador deixou de utilizar o recurso “Dynamic Sampling” pois haviam estat\u00edsticas da tabela dispon\u00edveis.<\/span><\/p>\n

Falta de atualiza\u00e7\u00e3o das estat\u00edsticas<\/span><\/h2>\n

O processo de atualiza\u00e7\u00e3o das estat\u00edsticas \u00e9 executado no per\u00edodo noturno por padr\u00e3o para evitar o impacto na performance das aplica\u00e7\u00f5es no hor\u00e1rio comercial e nesse processo algumas tabelas podem ficar dias sem que suas estat\u00edsticas sejam atualizadas pois a quantidade de registros atualizados n\u00e3o atingiu 10% do total de registros da tabela, esse mecanismo existe para que tabelas que n\u00e3o tenham atualiza\u00e7\u00f5es ou cujas as atualiza\u00e7\u00f5es sejam feitas com frequ\u00eancia reduzida n\u00e3o tenham suas estat\u00edsticas coletadas todos os dias, gerando uma carga de trabalho desnecess\u00e1ria no banco de dados.<\/span><\/p>\n

Este per\u00edodo que a tabela fica sem atualiza\u00e7\u00e3o das estat\u00edsticas, pode afetar as estimativas de cardinalidade do Otimizador no momento da gera\u00e7\u00e3o do plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL que utilize esta tabela como fonte de dados.<\/span><\/p>\n

Para facilitar o entendimento desse mecanismo, vamos realizar a seguir algumas opera\u00e7\u00f5es que demonstram uma situa\u00e7\u00e3o onde este tipo de problema pode ocorrer:<\/span><\/p>\n

1) Criar uma tabela, eliminar os registros do ultimo dia, coletar suas estat\u00edsticas e inserir os registros do ultimo dia para simular uma situa\u00e7\u00e3o de estat\u00edsticas desatualizadas<\/span>
\n2) Executar uma consulta que utilizar\u00e1 essa tabela como fonte de dados e verificar a cardinalidade estimada pelo Otimizador <\/span>
\n3) Coletar as estat\u00edsticas da tabela <\/span>
\n4) Executar a mesma consulta novamente e verificar a cardinalidade estimada pelo Otimizador<\/span><\/p>\n

Criar a tabela<\/span><\/h3>\n

Para simular a situa\u00e7\u00e3o de estat\u00edsticas desatualizadas, vamos criar a tabela, eliminar os registros do dia da cria\u00e7\u00e3o da tabela, coletar as estat\u00edsticas e inserir registros com a data do dia da cria\u00e7\u00e3o 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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
<\/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> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw02 as<\/span>
\n  2<\/span>     select 'DBTimeWizard - Performance and Tuning'<\/span> as<\/span> produto,<\/span>
\n  3<\/span>            mod(<\/span>rownum,<\/span>5<\/span>)<\/span>    as<\/span> codigo,<\/span>
\n  4<\/span>            mod(<\/span>rownum,<\/span>1000<\/span>)<\/span> as<\/span> cliente_id ,<\/span>
\n  5<\/span>            mod(<\/span>rownum,<\/span>2000000<\/span>)<\/span>as<\/span> postal_id,<\/span>
\n  6<\/span>            5000<\/span> as<\/span> total_vendas,<\/span>
\n  7<\/span>            trunc(<\/span>sysdate -<\/span> 9999<\/span> +<\/span> mod(<\/span>rownum,<\/span>10000<\/span>)<\/span>)<\/span> as<\/span> data_venda
\n  8<\/span>       from dual connect by level<=<\/span>100000<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> delete from dbtw02 where data_venda =<\/span> trunc(<\/span>sysdate)<\/span>;<\/span>
\n
\n10<\/span> linhas deletadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index idx_dbtw02_dt_venda on dbtw02(<\/span>data_venda)<\/span> parallel 16<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> alter index idx_dbtw02_dt_venda noparallel;<\/span>
\n
\n\u00cdndice alterado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW02'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> insert \/*+ append *\/<\/span> into dbtw02
\n  2<\/span>     select 'DBTimeWizard - Performance and Tuning'<\/span> as<\/span> produto,<\/span>
\n  3<\/span>            mod(<\/span>rownum,<\/span>5<\/span>)<\/span>    as<\/span> codigo,<\/span>
\n  4<\/span>            mod(<\/span>rownum,<\/span>1000<\/span>)<\/span> as<\/span> cliente_id ,<\/span>
\n  5<\/span>            mod(<\/span>rownum,<\/span>2000000<\/span>)<\/span>as<\/span> postal_id,<\/span>
\n  6<\/span>            5000<\/span> as<\/span> total_vendas,<\/span>
\n  7<\/span>            trunc(<\/span>sysdate)<\/span> as<\/span> data_venda
\n  8<\/span>       from dual connect by level<=<\/span> 10000<\/span>;<\/span>
\n
\n10000<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> commit;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>flush_database_monitoring_info ;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select TABLE_NAME,<\/span> INSERTS,<\/span> UPDATES,<\/span> DELETES from dba_tab_modifications where TABLE_NAME =<\/span> 'DBTW02'<\/span>;<\/span>
\n
\nTABLE_NAME                         INSERTS    UPDATES    DELETES
\n------------------------------<\/span>  ----------<\/span> ----------<\/span> ----------<\/span>
\nDBTW02                               10000<\/span>          0<\/span>          0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col stale_stats for<\/span> a20;<\/span>
\nSQL ><\/span> SELECT table_name,<\/span> stale_stats FROM user_tab_statistics where table_name =<\/span> 'DBTW02'<\/span>;<\/span>
\n
\nTABLE_NAME                     STALE_STATS
\n------------------------------<\/span> --------------------<\/span>
\nDBTW02                         YES
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Executar a consulta<\/span><\/h3>\n

Agora vamos executar uma consulta na tabela criada e listar o plano de execu\u00e7\u00e3o gerado pelo Otimizador:<\/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> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/*+ dbtw001 *\/<\/span> codigo,<\/span> produto,<\/span> data_venda
\n  2<\/span>    FROM dbtw02
\n  3<\/span>   WHERE data_venda =<\/span> trunc(<\/span>sysdate)<\/span>;<\/span>
\n
\n    CODIGO PRODUTO                               DATA_VEN                                                                                                                                              
\n----------<\/span> -------------------------------------<\/span> --------<\/span>                                                                                                                                              
\n         1<\/span> DBTimeWizard -<\/span> Performance and Tuning 24<\/span>\/<\/span>01<\/span>\/<\/span>17<\/span>
\n         ..............<\/span>
\n         4<\/span> DBTimeWizard -<\/span> Performance and Tuning 24<\/span>\/<\/span>01<\/span>\/<\/span>17<\/span>                                                                                                                                              
\n         0<\/span> DBTimeWizard -<\/span> Performance and Tuning 24<\/span>\/<\/span>01<\/span>\/<\/span>17<\/span>                                                                                                                                              
\n
\n10000<\/span> linhas selecionadas.<\/span>
\n
\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 '%dbtw001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER                                                                                                                                                                              
\n-------------<\/span> ------------<\/span>                                                                                                                                                                              
\n9gp82gcn5k01b            0<\/span>                                                                                                                                                                              
\n
\n1<\/span> linha selecionada.<\/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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT                                                                                                                                                                                      
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  9gp82gcn5k01b,<\/span> child number 0<\/span>                                                                                                                                                                  
\n-------------------------------------<\/span>                                                                                                                                                                  
\nSELECT \/*+ dbtw001 *\/<\/span> codigo,<\/span> produto,<\/span> data_venda   FROM dbtw02  WHERE                                                                                                                                  
\ndata_venda =<\/span> trunc(<\/span>sysdate)<\/span>                                                                                                                                                                            
\n                                                                                                                                                                                                       
\nPlan hash<\/span><\/a> value:<\/span> 3866670392<\/span>                                                                                                                                                                            
\n                                                                                                                                                                                                       
\n----------------------------------------------------------------------------------------------------------------------<\/span>                                                                                  
\n|<\/span> Id  |<\/span> Operation                   |<\/span> Name                |<\/span> Starts |<\/span> E-<\/span>Rows |<\/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>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.13<\/span> |<\/span>     317<\/span> |<\/span>     90<\/span> |<\/span>                                                                                  
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW02              |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.13<\/span> |<\/span>     317<\/span> |<\/span>     90<\/span> |<\/span>                                                                                  
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> IDX_DBTW02_DT_VENDA |<\/span>      1<\/span> |<\/span>     10<\/span> |<\/span>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     126<\/span> |<\/span>      0<\/span> |<\/span>                                                                                  
\n----------------------------------------------------------------------------------------------------------------------<\/span>                                                                                  
\n                                                                                                                                                                                                       
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>                                                                                                                                                    
\n---------------------------------------------------<\/span>                                                                                                                                                    
\n                                                                                                                                                                                                       
\n   2<\/span> -<\/span> access(<\/span>"DATA_VENDA"<\/span>=<\/span>TRUNC(<\/span>SYSDATE@!<\/span>)<\/span>)<\/span>                                                                                                                                                            
\n                                                                                                                                                                                                       
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ao examinarmos o plano de execu\u00e7\u00e3o 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).<\/p>\n

Coletar as estat\u00edsticas da tabela<\/span><\/h3>\n

Na consulta acima verificamos uma das in\u00fameras situa\u00e7\u00f5es que podem ocorrer numa base de dados e que levam o Otimizador a falhar na estimativa de cardinalidade do plano de execu\u00e7\u00e3o, o pacote DBMS_STATS possui in\u00fameras fun\u00e7\u00f5es e procedimentos que podem ser utilizados para gerenciar a coleta de estat\u00edsticas e melhorar sua qualidade. Para demonstrar o efeito das estat\u00edsticas atualizadas na estimativa de cardinalidade vamos coletar a estat\u00edsticas novamente:<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW02'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Executar a consulta novamente<\/span><\/h3>\n

Vamos executar a consulta na tabela com as estat\u00edsticas atualizadas e verificar a qualidade da cardinalidade estimada pelo Otimizador.<\/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
<\/div><\/td>
SQL ><\/span> SELECT \/*+ dbtw002 *\/<\/span> codigo,<\/span> produto,<\/span> data_venda
\n  2<\/span>    FROM dbtw02
\n  3<\/span>   WHERE data_venda =<\/span> trunc(<\/span>sysdate)<\/span>;<\/span>
\n
\n    CODIGO PRODUTO                               DATA_VEN                                                                                                                                              
\n----------<\/span> -------------------------------------<\/span> --------<\/span>                                                                                                                                              
\n         1<\/span> DBTimeWizard -<\/span> Performance and Tuning 24<\/span>\/<\/span>01<\/span>\/<\/span>17<\/span>
\n         .......................<\/span>
\n         4<\/span> DBTimeWizard -<\/span> Performance and Tuning 24<\/span>\/<\/span>01<\/span>\/<\/span>17<\/span>                                                                                                                                              
\n         0<\/span> DBTimeWizard -<\/span> Performance and Tuning 24<\/span>\/<\/span>01<\/span>\/<\/span>17<\/span>                                                                                                                                              
\n
\n10000<\/span> linhas selecionadas.<\/span>
\n
\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 '%dbtw002%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER                                                                                                                                                                              
\n-------------<\/span> ------------<\/span>                                                                                                                                                                              
\n16b548kz49wnh            0<\/span>                                                                                                                                                                              
\n
\n1<\/span> linha selecionada.<\/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>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT                                                                                                                                                                                      
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  16b548kz49wnh,<\/span> child number 0<\/span>                                                                                                                                                                  
\n-------------------------------------<\/span>                                                                                                                                                                  
\nSELECT \/*+ dbtw002 *\/<\/span> codigo,<\/span> produto,<\/span> data_venda   FROM dbtw02  WHERE                                                                                                                                  
\ndata_venda =<\/span> trunc(<\/span>sysdate)<\/span>                                                                                                                                                                            
\n                                                                                                                                                                                                       
\nPlan hash<\/span><\/a> value:<\/span> 202550308<\/span>                                                                                                                                                                              
\n                                                                                                                                                                                                       
\n--------------------------------------------------------------------------------------<\/span>                                                                                                                  
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/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>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1107<\/span> |<\/span>                                                                                                                  
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW02 |<\/span>      1<\/span> |<\/span>  10176<\/span> |<\/span>  10000<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1107<\/span> |<\/span>                                                                                                                  
\n--------------------------------------------------------------------------------------<\/span>                                                                                                                  
\n                                                                                                                                                                                                       
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>                                                                                                                                                    
\n---------------------------------------------------<\/span>                                                                                                                                                    
\n                                                                                                                                                                                                       
\n   1<\/span> -<\/span> filter(<\/span>"DATA_VENDA"<\/span>=<\/span>TRUNC(<\/span>SYSDATE@!<\/span>)<\/span>)<\/span>                                                                                                                                                            
\n                                                                                                                                                                                                       
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o acima constatamos que a cardinalidade estimada pelo Otimizador foi muito pr\u00f3xima do real. (E-ROWS=10176 A-ROWS=10000)<\/span><\/p>\n

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

Nesse artigo verificamos atrav\u00e9s de exemplos a import\u00e2ncia das estat\u00edsticas para que o Otimizador consiga realizar estimativas de cardinalidade com precis\u00e3o, o gerenciamento da coleta de estat\u00edsticas \u00e9 crucial para manter a qualidade das mesmas. O pacote DBMS_STATS pode e deve ser utilizado para fazer os ajustes necess\u00e1rios no processo de coleta de estat\u00edsticas de acordo com as caracter\u00edsticas dos dados e como eles s\u00e3o atualizados.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/database\/121\/ARPLS\/d_stats.htm#ARPLS68491<\/a><\/span><\/strong><\/p>\n

http:\/\/gavinsoorma.com\/wp-content\/uploads\/2011\/03\/top_tips_for_optimal_sql_execution.pdf<\/a><\/span><\/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":"

No artigo “Por que o Otimizador criou um plano de execu\u00e7\u00e3o ineficiente?” elencamos algumas condi\u00e7\u00f5es que levam o Otimizador a gerar um plano de execu\u00e7\u00e3o ineficiente, neste artigo vamos mostrar com mais detalhes como a aus\u00eancia de estat\u00edsticas ou a falta de sua atualiza\u00e7\u00e3o podem influenciar de maneira negativa o Otimizador.       Aus\u00eancia de estat\u00edsticas Quando o Otimizador<\/p>\n","protected":false},"author":2,"featured_media":956,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[129,128,6,42,163,10],"class_list":["post-955","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-cardinalidade","tag-cardinality","tag-cbo","tag-cost-base-optimizer","tag-dbms_stats","tag-otimizador"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/955","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=955"}],"version-history":[{"count":30,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/955\/revisions"}],"predecessor-version":[{"id":2087,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/955\/revisions\/2087"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/956"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=955"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}