{"id":991,"date":"2017-03-06T22:39:56","date_gmt":"2017-03-07T01:39:56","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=991"},"modified":"2019-10-10T23:05:23","modified_gmt":"2019-10-11T02:05:23","slug":"histogramas-qual-importancia-deles","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/histogramas-qual-importancia-deles\/","title":{"rendered":"Histogramas: Qual import\u00e2ncia deles?"},"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 abordar com mais detalhes um desses t\u00f3picos, como a repeti\u00e7\u00e3o de dados n\u00e3o uniformes em uma coluna da tabela podem influenciar de maneira negativa o Otimizador.<\/span><\/p>\n

 
\n 
\n <\/p>\n

Repeti\u00e7\u00e3o de dados n\u00e3o uniformes<\/span><\/h2>\n

Quando o Otimizador gera\u00a0o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL e na cl\u00e1usula WHERE dessa instru\u00e7\u00e3o existe um filtro numa coluna da tabela que possui um n\u00famero de valores distintos muito inferior ao numero total de registros da tabela, o Otimizador utiliza uma regra simples para calcular a cardinalidade, ele divide o numero total de registros da tabela pelo numero de valores distintos. Essa formula pode ser eficaz se para cada valor distinto houver uma quantidade de registros semelhante, porem ela se torna ineficaz quando um ou mais valores tem uma quantidade de registros muito superior ao outros.<\/span><\/p>\n

Para que o Otimizador consiga calcular a cardinalidade de forma mais precisa na condi\u00e7\u00e3o apresentada acima \u00e9 necess\u00e1rio a utiliza\u00e7\u00e3o de um histograma na coluna cuja repeti\u00e7\u00e3o de dados n\u00e3o \u00e9 uniforme. Para gera\u00e7\u00e3o desse histograma devemos utilizar 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 coletar as estat\u00edsticas sem a gera\u00e7\u00e3o do histograma<\/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 estast\u00edsticas da tabela gerando o histograma <\/span>
\n4) Executar a mesma consulta novamente e verificar a cardinalidade estimada pelo Otimizador<\/span><\/p>\n

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

Primeiro vamos clonar a tabela EMPLOYEES do SCHEMA HR, coletar as estat\u00edsticas sem gerar o histograma na coluna JOB_ID que ser\u00e1 utilizada na cl\u00e1usula WHERE da consulta.<\/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
<\/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> create table employees as<\/span> select *<\/span> from hr.<\/span>employees;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span> CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES (<\/span>EMPLOYEE_ID)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'EMPLOYEES'<\/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<\/span><\/h3>\n

Agora vamos executar uma consulta na tabela criada e listar o plano de execu\u00e7\u00e3o utilizado 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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
<\/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> EMPLOYEE_ID,<\/span> LAST_NAME,<\/span> DEPARTMENT_ID,<\/span> JOB_ID
\n  2<\/span>    from employees
\n  3<\/span>   where JOB_ID =<\/span> 'AD_ASST'<\/span>;<\/span>
\n
\nEMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID JOB_ID
\n-----------<\/span> -------------------------<\/span> -------------<\/span> ----------<\/span>
\n        200<\/span> Whalen                               10<\/span> AD_ASST
\n
\n1<\/span> linha selecionada.<\/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>
\n4aukqvu2t0yvh            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  4aukqvu2t0yvh,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw001  *\/<\/span> EMPLOYEE_ID,<\/span> LAST_NAME,<\/span> DEPARTMENT_ID,<\/span> JOB_ID
\nfrom employees  where JOB_ID =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1445457117<\/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>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> EMPLOYEES |<\/span>      1<\/span> |<\/span>      6<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"JOB_ID"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span> column "Num. linhas na tabela"<\/span> new_value num_lin_tot
\nSQL ><\/span> column "Num. linhas distintas"<\/span> new_value num_lin_dist
\nSQL ><\/span>
\nSQL ><\/span> select count<\/span><\/a>(<\/span>1<\/span>)<\/span> "Num. linhas na tabela"<\/span> from employees;<\/span>
\n
\nNum.<\/span> linhas na tabela
\n---------------------<\/span>
\n                  107<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span> select count<\/span><\/a>(<\/span>distinct(<\/span>JOB_ID)<\/span>)<\/span> "Num. linhas distintas"<\/span> from employees;<\/span>
\n
\nNum.<\/span> linhas distintas
\n---------------------<\/span>
\n                   19<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span> select round<\/span><\/a>(<\/span>&<\/span>num_lin_tot\/&<\/span>num_lin_dist)<\/span> CARDINALIDADE from dual;<\/span>
\n
\nCARDINALIDADE
\n-------------<\/span>
\n            6<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

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

O Otimizador calculou a cardinalidade como 6 pois ele dividiu a quantidade total de registros (107) pelo n\u00famero de valores distintos na coluna JOB_ID (19).<\/span><\/p>\n

Coletar as estat\u00edsticas com histograma<\/span><\/h3>\n

Para gerar histogramas em todas as colunas que possuem repeti\u00e7\u00e3o de dados n\u00e3o uniforme na tabela \u00e9 necess\u00e1rio utilizar a cl\u00e1usula “method_opt=> ‘FOR ALL COLUMNS SIZE SKEWONLY'”.<\/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
<\/div><\/td>
SQL ><\/span> SELECT column_name,<\/span> num_distinct,<\/span> histogram
\n  2<\/span>    FROM user_tab_col_statistics
\n  3<\/span>   WHERE table_name =<\/span> 'EMPLOYEES'<\/span>
\n  4<\/span>     AND column_name =<\/span> 'JOB_ID'<\/span>;<\/span>
\n
\nColuna               NUM_DISTINCT HISTOGRAM
\n--------------------<\/span> ------------<\/span> ---------------<\/span>
\nJOB_ID                         19<\/span> NONE
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'EMPLOYEES'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE SKEWONLY'<\/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> SELECT column_name,<\/span> num_distinct,<\/span> histogram
\n  2<\/span>    FROM user_tab_col_statistics
\n  3<\/span>   WHERE table_name =<\/span> 'EMPLOYEES'<\/span>
\n  4<\/span>     AND column_name =<\/span> 'JOB_ID'<\/span>;<\/span>
\n
\nColuna               NUM_DISTINCT HISTOGRAM
\n--------------------<\/span> ------------<\/span> ---------------<\/span>
\nJOB_ID                         19<\/span> FREQUENCY
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

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

Finalmente vamos executar a consulta na tabela com os histogramas gerados 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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* dbtw002  *\/<\/span> EMPLOYEE_ID,<\/span> LAST_NAME,<\/span> DEPARTMENT_ID,<\/span> JOB_ID
\n  2<\/span>    from employees
\n  3<\/span>   where JOB_ID =<\/span> 'AD_ASST'<\/span>;<\/span>
\n
\nEMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID JOB_ID
\n-----------<\/span> -------------------------<\/span> -------------<\/span> ----------<\/span>
\n        200<\/span> Whalen                               10<\/span> AD_ASST
\n
\n1<\/span> linha selecionada.<\/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>
\ndv574duqu8q1w            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  dv574duqu8q1w,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* dbtw002  *\/<\/span> EMPLOYEE_ID,<\/span> LAST_NAME,<\/span> DEPARTMENT_ID,<\/span> JOB_ID
\nfrom employees  where JOB_ID =<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1445457117<\/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>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> EMPLOYEES |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"JOB_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 (1 registro), igual a quantidade de registros lidos (E-ROWS = A-ROWS).<\/span><\/p>\n

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

Nesse artigo verificamos na pr\u00e1tica a import\u00e2ncia do histograma para que o Otimizador consiga realizar estimativas de cardinalidade com precis\u00e3o, o gerenciamento da coleta de estat\u00edsticas \u00e9 fundamental para garantir a cria\u00e7\u00e3o de histogramas em colunas de tabelas que possuem repeti\u00e7\u00e3o de dados n\u00e3o uniforme e s\u00e3o mencionadas na cl\u00e1usula WHERE das instru\u00e7\u00f5es SQL. 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

http:\/\/gavinsoorma.com\/wp-content\/uploads\/2011\/03\/top_tips_for_optimal_sql_execution.pdf<\/a><\/span><\/strong><\/p>\n

https:\/\/docs.oracle.com\/database\/121\/ARPLS\/d_stats.htm#ARPLS68491<\/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 abordar com mais detalhes um desses t\u00f3picos, como a repeti\u00e7\u00e3o de dados n\u00e3o uniformes em uma coluna da tabela podem influenciar de maneira negativa o Otimizador.       Repeti\u00e7\u00e3o de<\/p>\n","protected":false},"author":2,"featured_media":992,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[129,128,6,42,163,162,161,10],"class_list":["post-991","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-histogramas","tag-histograms","tag-otimizador"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/991","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=991"}],"version-history":[{"count":16,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/991\/revisions"}],"predecessor-version":[{"id":2059,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/991\/revisions\/2059"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/992"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=991"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=991"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=991"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}