{"id":1743,"date":"2018-08-09T10:18:18","date_gmt":"2018-08-09T13:18:18","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1743"},"modified":"2019-11-11T18:43:49","modified_gmt":"2019-11-11T21:43:49","slug":"histogramas-quando-devemos-evita-los","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/histogramas-quando-devemos-evita-los\/","title":{"rendered":"Histogramas: Quando devemos evit\u00e1-los?"},"content":{"rendered":"

O uso de histogramas<\/a><\/strong> ajuda o Otimizador a ser mais preciso no c\u00e1lculo da cardinalidade das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o, mas existem situa\u00e7\u00f5es em que n\u00e3o devemos utiliz\u00e1-los, pois eles v\u00e3o induzir o Otimizador a criar planos de execu\u00e7\u00e3o de baixa qualidade. Neste artigo vamos mostrar uma destas situa\u00e7\u00f5es onde o Otimizador realiza um c\u00e1lculo de cardinalidade muito ruim, devido a exist\u00eancia do histograma em uma coluna da tabela e como consequ\u00eancia ele vai gerar um plano de execu\u00e7\u00e3o de baixo desempenho.<\/span><\/p>\n

 <\/p>\n

Para demonstrar esta situa\u00e7\u00e3o, vamos realizar uma simula\u00e7\u00e3o pr\u00e1tica com as seguintes etapas:<\/span><\/p>\n

1) Criar uma tabela com um \u00edndice e coletar as estat\u00edsticas<\/span>
\n2) Executar uma consulta na tabela criada e verificar o plano de execu\u00e7\u00e3o<\/span>
\n3) Eliminar o histograma da coluna inclu\u00edda na cl\u00e1usula WHERE da consulta<\/span>
\n4) Executar a mesma consulta novamente e verificar o plano de execu\u00e7\u00e3o<\/span><\/p>\n

 <\/p>\n

1) Criar a tabela, \u00edndice e estat\u00edsticas<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o.<\/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
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
<\/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>
\nSQL ><\/span> create table dbtw054 as<\/span> select *<\/span> from dba_objects where rownum <<\/span> 1<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER TABLE dbtw054 MODIFY (<\/span>object_type varchar2(<\/span>60<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> insert into dbtw054   select
\n  2<\/span>                            OWNER,<\/span>
\n  3<\/span>                            OBJECT_NAME,<\/span>
\n  4<\/span>                            SUBOBJECT_NAME,<\/span>
\n  5<\/span>                            OBJECT_ID,<\/span>
\n  6<\/span>                            DATA_OBJECT_ID,<\/span>
\n  7<\/span>                            'x123456789y123456789z123456789w123'<\/span>||<\/span>OBJECT_TYPE,<\/span>
\n  8<\/span>                            CREATED,<\/span>
\n  9<\/span>                            LAST_DDL_TIME,<\/span>
\n 10<\/span>                            TIMESTAMP,<\/span>
\n 11<\/span>                            STATUS,<\/span>
\n 12<\/span>                            TEMPORARY,<\/span>
\n 13<\/span>                            GENERATED,<\/span>
\n 14<\/span>                            SECONDARY,<\/span>
\n 15<\/span>                            NAMESPACE<\/span>,<\/span>
\n 16<\/span>                            EDITION_NAME
\n 17<\/span>                         from dba_objects
\n 18<\/span>                        where object_type in (<\/span>'QUEUE'<\/span>,<\/span>
\n 19<\/span>                                              'TABLE SUBPARTITION'<\/span>,<\/span>
\n 20<\/span>                                              'RULE SET'<\/span>,<\/span>
\n 21<\/span>                                              'CONSUMER GROUP'<\/span>,<\/span>
\n 22<\/span>                                              'MATERIALIZED VIEW'<\/span>,<\/span>
\n 23<\/span>                                              'PROGRAM'<\/span>,<\/span>
\n 24<\/span>                                              'DATABASE LINK'<\/span>,<\/span>
\n 25<\/span>                                              'JOB CLASS'<\/span>,<\/span>
\n 26<\/span>                                              'EVALUATION CONTEXT'<\/span>,<\/span>
\n 27<\/span>                                              'UNDEFINED'<\/span>,<\/span>
\n 28<\/span>                                              'RESOURCE PLAN'<\/span>,<\/span>
\n 29<\/span>                                              'CLUSTER'<\/span>,<\/span>
\n 30<\/span>                                              'JAVA SOURCE'<\/span>,<\/span>
\n 31<\/span>                                              'WINDOW'<\/span>,<\/span>
\n 32<\/span>                                              'INDEXTYPE'<\/span>,<\/span>
\n 33<\/span>                                              'RULE'<\/span>,<\/span>
\n 34<\/span>                                              'CONTEXT'<\/span>,<\/span>
\n 35<\/span>                                              'DIMENSION'<\/span>,<\/span>
\n 36<\/span>                                              'SCHEDULER GROUP'<\/span>,<\/span>
\n 37<\/span>                                              'SCHEDULE'<\/span>,<\/span>
\n 38<\/span>                                              'DESTINATION'<\/span>,<\/span>
\n 39<\/span>                                              'EDITION'<\/span>,<\/span>
\n 40<\/span>                                              'REWRITE EQUIVALENCE'<\/span>,<\/span>
\n 41<\/span>                                              'LOB PARTITION'<\/span>)<\/span>;<\/span>
\n
\n362<\/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>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW054'<\/span>,<\/span> method_opt=><\/span>'FOR ALL COLUMNS SIZE SKEWONLY'<\/span>,<\/span>cascade=><\/span>true<\/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> create index dbtw054_IDX on dbtw054(<\/span>object_type)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar uma consulta usando a tabela criada<\/span><\/h2>\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
88
89
90
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW-054.1 *\/<\/span> object_name,<\/span>
\n  2<\/span>         owner
\n  3<\/span>    FROM DBTW054
\n  4<\/span>   WHERE object_type=<\/span>'x123456789y123456789z123456789w123RULE'<\/span>;<\/span>
\n
\nOBJECT_NAME                    OWNER
\n------------------------------<\/span> -------------------<\/span>
\nSYS$SERVICE_METRICS<\/span>$61<\/span>         SYS
\nALERT_QUE$1<\/span>                    SYS
\nSCHEDULER$_EVENT_QUEUE<\/span>$1<\/span>       SYS
\nSCHEDULER$_EVENT_QUEUE<\/span>$3<\/span>       SYS
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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 '%DBTW-054.1%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n3h619x7nhkxq9            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/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  3h619x7nhkxq9,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-054.1 *\/<\/span> object_name,<\/span>        owner   FROM DBTW054  WHERE
\nobject_type=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1846229803<\/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>     5<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      16<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW054 |<\/span>      1<\/span> |<\/span>    362<\/span> |<\/span> 26788<\/span> |<\/span>     5<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      16<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         num_distinct,<\/span>
\n  3<\/span>         density,<\/span>
\n  4<\/span>         num_nulls,<\/span>
\n  5<\/span>         histogram,<\/span>
\n  6<\/span>         num_buckets
\n  7<\/span>    FROM user_tab_col_statistics
\n  8<\/span>   WHERE table_name =<\/span> 'DBTW054'<\/span>
\n  9<\/span>     AND column_name=<\/span>'OBJECT_TYPE'<\/span>;<\/span>
\n
\nCOLUMN_NAME                    NUM_DISTINCT      DENSITY  NUM_NULLS HISTOGRAM       NUM_BUCKETS
\n------------------------------<\/span> ------------<\/span> ------------<\/span> ----------<\/span> ---------------<\/span> -----------<\/span>
\nOBJECT_TYPE                              22<\/span>   .001381215<\/span>          0<\/span> FREQUENCY                 1<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n  2<\/span>    FROM dbtw054;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n----------<\/span>
\n       362<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que o Otimizador estimou que a opera\u00e7\u00e3o TABLE ACCESS FULL selecionaria 362 linhas (E-ROWS) quando na verdade foram selecionada somente 4 linhas (A-ROWS), este erro absurdo ocorreu por que o Otimizador utiliza preferencialmente o histograma, quando ele existe, para fazer o c\u00e1lculo da cardinalidade e quando geramos as estat\u00edsticas no inicio da simula\u00e7\u00e3o utilizando\u00a0o\u00a0pacote DBMS_STATS a cria\u00e7\u00e3o do histograma na coluna OBJECT_TYPE considerou somente os 32 caracteres iniciais desta coluna, desprezando o resto (limite da vers\u00e3o 11g).<\/span><\/p>\n

Consultando a vis\u00e3o de estat\u00edsticas das colunas (USER_TAB_COL_STATISTICS)<\/span> <\/strong>verificamos que a coluna OBJECT_TYPE possui um histograma do tipo FREQUENCY com 1 BUCKET, quando deveriam ser 22 BUCKETS pois esta coluna tem 22 valores distintos. Este histograma defeituoso \u00e9 o resultado da combina\u00e7\u00e3o de duas situa\u00e7\u00f5es, os primeiros 32 caracteres dos valores presentes na coluna OBJECT_TYPE serem iguais em todas as linhas da tabela e ao limite de 32 caracteres da vers\u00e3o 11g para a cria\u00e7\u00e3o de histogramas.<\/span><\/p>\n

Obs: Na vers\u00e3o 12c este limite \u00e9 de 64 caracteres.<\/span><\/p>\n

 <\/p>\n

3) Eliminar o histograma da coluna OBJECT_TYPE<\/strong><\/span><\/p>\n

Para ajudar o Otimizador na estimativa da cardinalidade das linhas que ser\u00e3o selecionadas na tabela, podemos eliminar o histograma criado na coluna OBJECT_TYPE e alterar as prefer\u00eancias da coleta de estat\u00edsticas de forma que este histograma n\u00e3o seja mais gerado.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
<\/div><\/td>
SQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>delete_column_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span>'DBTW054'<\/span>,<\/span> colname=><\/span>'OBJECT_TYPE'<\/span>,<\/span> col_stat_type=><\/span>'HISTOGRAM'<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>set_table_prefs(<\/span>USER,<\/span> 'DBTW054'<\/span>,<\/span> 'METHOD_OPT'<\/span>,<\/span> 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 OBJECT_TYPE'<\/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

4) Executar a mesma consulta novamente<\/strong><\/span><\/p>\n

Vamos executar uma consulta na tabela e verificar a efici\u00eancia do plano de execu\u00e7\u00e3o:<\/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
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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW-054.2 *\/<\/span> object_name,<\/span>
\n  2<\/span>         owner
\n  3<\/span>    FROM DBTW054
\n  4<\/span>   WHERE object_type=<\/span>'x123456789y123456789z123456789w123RULE'<\/span>;<\/span>
\n
\nOBJECT_NAME                    OWNER
\n------------------------------<\/span> ----------------------------<\/span>
\nSYS$SERVICE_METRICS<\/span>$61<\/span>         SYS
\nALERT_QUE$1<\/span>                    SYS
\nSCHEDULER$_EVENT_QUEUE<\/span>$1<\/span>       SYS
\nSCHEDULER$_EVENT_QUEUE<\/span>$3<\/span>       SYS
\n
\n4<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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 '%DBTW-054.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nfm9ww6785g3dm            0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relatorio do<\/span> plano de execucao da consulta
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/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  fm9ww6785g3dm,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW-054.2 *\/<\/span> object_name,<\/span>        owner   FROM DBTW054  WHERE
\nobject_type=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 87264188<\/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>     4<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> DBTW054     |<\/span>      1<\/span> |<\/span>     16<\/span> |<\/span>  1184<\/span> |<\/span>     4<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW054_IDX |<\/span>      1<\/span> |<\/span>     16<\/span> |<\/span>       |<\/span>     1<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"OBJECT_TYPE"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         num_distinct,<\/span>
\n  3<\/span>         density,<\/span>
\n  4<\/span>         num_nulls,<\/span>
\n  5<\/span>         histogram,<\/span>
\n  6<\/span>         num_buckets
\n  7<\/span>    FROM user_tab_col_statistics
\n  8<\/span>   WHERE table_name =<\/span> 'DBTW054'<\/span>
\n  9<\/span>     AND column_name=<\/span>'OBJECT_TYPE'<\/span>;<\/span>
\n
\nCOLUMN_NAME                    NUM_DISTINCT      DENSITY  NUM_NULLS HISTOGRAM       NUM_BUCKETS
\n------------------------------<\/span> ------------<\/span> ------------<\/span> ----------<\/span> ---------------<\/span> -----------<\/span>
\nOBJECT_TYPE                              22<\/span>   .045454545<\/span>          0<\/span> NONE                      1<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ap\u00f3s a exclus\u00e3o do histograma podemos verificar que a estimativa de cardinalidade melhorou (E-ROWS=16) e ficou mais pr\u00f3xima da quantidade de linhas selecionas (A-ROWS=4), com isso o Otimizador considerou que a leitura das linhas utilizando um \u00edndice seria mais eficiente.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cd\/E11882_01\/server.112<\/a><\/span><\/strong>
\n 
\n 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

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

O uso de histogramas ajuda o Otimizador a ser mais preciso no c\u00e1lculo da cardinalidade das opera\u00e7\u00f5es no plano de execu\u00e7\u00e3o, mas existem situa\u00e7\u00f5es em que n\u00e3o devemos utiliz\u00e1-los, pois eles v\u00e3o induzir o Otimizador a criar planos de execu\u00e7\u00e3o de baixa qualidade. Neste artigo vamos mostrar uma destas situa\u00e7\u00f5es onde o Otimizador realiza um c\u00e1lculo de cardinalidade muito ruim,<\/p>\n","protected":false},"author":2,"featured_media":1744,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[163,162,161,82,10],"class_list":["post-1743","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-dbms_stats","tag-histogramas","tag-histograms","tag-optimizer","tag-otimizador"],"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