{"id":311,"date":"2016-02-21T23:14:50","date_gmt":"2016-02-22T02:14:50","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=311"},"modified":"2019-11-11T19:01:21","modified_gmt":"2019-11-11T22:01:21","slug":"qual-a-importancia-das-estatisticas-de-colunas-para-o-otimizador","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/qual-a-importancia-das-estatisticas-de-colunas-para-o-otimizador\/","title":{"rendered":"Qual a import\u00e2ncia das estat\u00edsticas de colunas para o Otimizador?"},"content":{"rendered":"

O Otimizador baseado em custo (CBO) utiliza estat\u00edsticas para determinar o custo dos diversos planos de execu\u00e7\u00e3o que ele produz para uma determinada instru\u00e7\u00e3o SQL e escolhe o plano com o menor custo para utilizar, portanto as informa\u00e7\u00f5es estat\u00edsticas s\u00e3o cruciais na escolha do plano de execu\u00e7\u00e3o e devem fornecer informa\u00e7\u00f5es precisas e atualizadas para que o Otimizador consiga realizar um bom trabalho.<\/span><\/p>\n

Num artigo anterior abordamos a import\u00e2ncia das estat\u00edsticas de tabelas<\/a><\/strong> para Otimizador, neste artigo vamos abordar como as estat\u00edsticas de colunas s\u00e3o utilizadas pelo Otimizador na de cria\u00e7\u00e3o de um plano de execu\u00e7\u00e3o para uma determinada instru\u00e7\u00e3o SQL.<\/span><\/p>\n

COLUMN STATISTICS<\/span><\/h2>\n

As informa\u00e7\u00f5es estat\u00edsticas de colunas podem ser consultadas na vis\u00e3o USER_TAB_COLUMNS, existem muitos informa\u00e7\u00f5es dispon\u00edveis nessa vis\u00e3o mas os campos mais importantes para o Otimizador s\u00e3o:<\/span><\/p>\n

NUM_DISTINCT<\/strong> – N\u00famero de valores distintos em uma determinada coluna<\/span>
\nDENSITY<\/strong>\u00a0 \u00a0 \u00a0 – Algoritmo para estimar a seletividade de uma coluna, formula: 1\/num_distinct para colunas sem histogramas<\/span>
\nNUM_NULLS<\/strong>\u00a0 \u00a0 \u00a0– N\u00famero de linhas na tabela que uma determinada coluna tem o valor nulo<\/span>
\nHISTOGRAM<\/strong>\u00a0 \u00a0 \u00a0– Indica a exist\u00eancia ou tipo de histograma para uma determinada coluna<\/span><\/p>\n

Neste artigo vamos considerar a coluna HISTOGRAM=NONE, ou seja n\u00e3o existe histogramas para essa coluna, vamos abordar a utiliza\u00e7\u00e3o de histogramas pelo Otimizador num futuro post. Para ilustrar na pr\u00e1tica a utiliza\u00e7\u00e3o das estat\u00edsticas de colunas pelo Otimizador, vamos executar uma consulta simples e verificar que formula o Otimizador utiliza para calcular a cardinalidade da opera\u00e7\u00e3o apresentada no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

Cria\u00e7\u00e3o da tabela para a consulta<\/span><\/h2>\n

Para execu\u00e7\u00e3o da consulta vamos criar uma tabela TAB1, alterar um campo para nulo em algumas linhas da tabela e gerar as estat\u00edsticas dessa 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
<\/div><\/td>
appuser@<\/span>LAB11 ><\/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
\nappuser@<\/span>LAB11 ><\/span> create table tab1
\n  2<\/span>  as<\/span>
\n  3<\/span>  select *<\/span>
\n  4<\/span>    from all_objects
\n  5<\/span>   where rownum <=<\/span> 10000<\/span>;<\/span>
\n
\nTable created.<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> update tab1 set object_type =<\/span> null<\/span>
\n  2<\/span>   where object_type =<\/span> 'TABLE'<\/span>;<\/span>
\n
\n1022<\/span> rows updated.<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> commit;<\/span>
\n
\nCommit complete.<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> begin
\n  2<\/span>      dbms_stats.<\/span>gather_table_stats(<\/span>
\n  3<\/span>          ownname      =><\/span> user,<\/span>
\n  4<\/span>          tabname      =><\/span>'TAB1'<\/span>,<\/span>
\n  5<\/span>          method_opt   =><\/span> 'for all columns size 1'<\/span>
\n  6<\/span>      )<\/span>;<\/span>
\n  7<\/span>  end<\/span><\/a>;<\/span>
\n  8<\/span>  \/<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> select sample_size,<\/span> num_nulls,<\/span> num_distinct,<\/span> histogram
\n  2<\/span>    from user_tab_columns
\n  3<\/span>   where table_name =<\/span> 'TAB1'<\/span>
\n  4<\/span>     and column_name =<\/span> 'OBJECT_TYPE'<\/span>;<\/span>
\n
\nSAMPLE_SIZE  NUM_NULLS NUM_DISTINCT HISTOGRAM
\n-----------<\/span> ----------<\/span> ------------<\/span> ---------------<\/span>
\n       8978<\/span>       1022<\/span>           18<\/span> NONE
\n
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Plano de execu\u00e7\u00e3o da consulta<\/span><\/h2>\n

Agora vamos executar a consulta e verificar como o Otimizador calcular\u00e1 a cardinalidade da opera\u00e7\u00e3o de acesso as linhas 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
<\/div><\/td>
appuser@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> select \/* tst101 *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n  2<\/span>    from TAB1
\n  3<\/span>   where object_type =<\/span> 'INDEX'<\/span>;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n----------<\/span>
\n      1142<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nappuser@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/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>
\n6j1gtqn30y14t            0<\/span>
\n
\nappuser@<\/span>LAB11 ><\/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>
\nold   2<\/span>:<\/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>
\nnew<\/span>   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'6j1gtqn30y14t'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  6j1gtqn30y14t,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* tst101 *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span>   from TAB1  where object_type =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1117438016<\/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>    40<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     133<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>    |<\/span>      |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     7<\/span> |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     133<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS FULL|<\/span> TAB1 |<\/span>      1<\/span> |<\/span>    499<\/span> |<\/span>  3493<\/span> |<\/span>    40<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>   1142<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     133<\/span> |<\/span>
\n---------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> select NUM_ROWS from user_tab_statistics where table_name =<\/span> 'TAB1'<\/span>;<\/span>
\n
\n  NUM_ROWS
\n----------<\/span>
\n     10000<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> select DENSITY from user_tab_columns where TABLE_NAME =<\/span> 'TAB1'<\/span> and COLUMN_NAME =<\/span> 'OBJECT_TYPE'<\/span>;<\/span>
\n
\n   DENSITY
\n----------<\/span>
\n.055555556<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> select NUM_NULLS from user_tab_columns where TABLE_NAME =<\/span> 'TAB1'<\/span> and COLUMN_NAME =<\/span> 'OBJECT_TYPE'<\/span>;<\/span>
\n
\n NUM_NULLS
\n----------<\/span>
\n      1022<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span> --><\/span> CARDINALITY<\/span> =<\/span> (<\/span> density *<\/span> (<\/span>num_rows -<\/span> num_nulls)<\/span> \/<\/span> num_rows )<\/span> *<\/span> num_rows
\nappuser@<\/span>LAB11 ><\/span>
\nappuser@<\/span>LAB11 ><\/span> select (<\/span>0.055555556<\/span>*<\/span>(<\/span>10000<\/span>-<\/span>1022<\/span>)<\/span>\/<\/span>10000<\/span>)<\/span>*<\/span>10000<\/span> CARDINALITY from dual;<\/span>
\n
\nCARDINALITY
\n-----------<\/span>
\n 498.777782<\/span>
\n
\nappuser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o da consulta, constatamos que o Otimizador estimou que seriam acessadas 499 linhas (E-ROWS) na opera\u00e7\u00e3o “TABLE ACCESS FULL” da tabela TAB1, quando na verdade foram acessadas 1142 linhas (A-ROWS), esta estimativa n\u00e3o foi muito boa pois os valores repetidos dessa coluna n\u00e3o est\u00e3o distribu\u00eddos de forma uniforme, neste tipo de situa\u00e7\u00e3o para o Otimizador fazer um estimativa melhor ele precisa acessar estat\u00edsticas de histogramas.<\/span><\/p>\n

A formula utilizada pelo Otimizador para calcular essa cardinalidade sem o auxilio de histogramas foi CARDINALITY = ( density * (num_rows – num_nulls) \/ num_rows ) * num_rows<\/strong> . Na log de execu\u00e7\u00e3o acima foram inclu\u00eddas algumas consultas para obter os valores de cada uma das vari\u00e1veis da formula e o resultado final dessa equa\u00e7\u00e3o foi 498.777782 que arredondando resulta nas 499 linhas estimadas no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

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

Neste artigo verificamos como o Otimizador utiliza as estat\u00edsticas de tabela e colunas para estimar a cardinalidade de uma opera\u00e7\u00e3o no plano de execu\u00e7\u00e3o sem a utiliza\u00e7\u00e3o de estat\u00edsticas de histogramas, com a presen\u00e7a de histogramas o Otimizador utiliza outra formula que ser\u00e1 tratada num futuro post. No nosso exemplo a estimativa realizada pelo Otimizador n\u00e3o foi muito boa pois para esse tipo de coluna o ideal seria que ele tivesse acesso as informa\u00e7\u00f5es estat\u00edsticas de histograma da coluna indicada no filtro da cl\u00e1usula WHERE (OBJECT_TYPE), com isso podemos constatar que aus\u00eancia de informa\u00e7\u00f5es estat\u00edsticas dos objetos envolvidos numa consulta podem prejudicar a qualidade das estimativas realizadas pelo Otimizador e lava-lo a montar planos de execu\u00e7\u00e3o com desempenho ruim.<\/span><\/p>\n

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

http:\/\/docs.oracle.com\/cd\/E25054_01\/server.1111\/e16638\/stats.htm<\/a><\/strong><\/p>\n

Livro: Cost-Based Oracle Fundamentals – Autor: Jonathan Lewis<\/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":"

O Otimizador baseado em custo (CBO) utiliza estat\u00edsticas para determinar o custo dos diversos planos de execu\u00e7\u00e3o que ele produz para uma determinada instru\u00e7\u00e3o SQL e escolhe o plano com o menor custo para utilizar, portanto as informa\u00e7\u00f5es estat\u00edsticas s\u00e3o cruciais na escolha do plano de execu\u00e7\u00e3o e devem fornecer informa\u00e7\u00f5es precisas e atualizadas para que o Otimizador consiga realizar<\/p>\n","protected":false},"author":2,"featured_media":324,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[6,90,42,84,91,93,94,10,92],"class_list":["post-311","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-cbo","tag-column-statistics","tag-cost-base-optimizer","tag-density","tag-estatisticas-de-colunas","tag-num_distinct","tag-num_nulls","tag-otimizador","tag-user_tab_columns"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t