Histogramas: Quando devemos evitá-los?

não use histogramas

O uso de histogramas ajuda o Otimizador a ser mais preciso no cálculo da cardinalidade das operações no plano de execução, mas existem situações em que não devemos utilizá-los, pois eles vão induzir o Otimizador a criar planos de execução de baixa qualidade. Neste artigo vamos mostrar uma destas situações onde o Otimizador realiza um cálculo de cardinalidade muito ruim, devido a existência do histograma em uma coluna da tabela e como consequência ele vai gerar um plano de execução de baixo desempenho.

 

Para demonstrar esta situação, vamos realizar uma simulação prática com as seguintes etapas:

1) Criar uma tabela com um índice e coletar as estatísticas
2) Executar uma consulta na tabela criada e verificar o plano de execução
3) Eliminar o histograma da coluna incluída na cláusula WHERE da consulta
4) Executar a mesma consulta novamente e verificar o plano de execução

 

1) Criar a tabela, índice e estatísticas

Vamos criar os objetos necessários para realizar a nossa simulação.
 

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
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL >
SQL > create table dbtw054 as select * from dba_objects where rownum < 1;

Tabela criada.

SQL >
SQL > ALTER TABLE dbtw054 MODIFY (object_type varchar2(60));

Tabela alterada.

SQL >
SQL >
SQL > insert into dbtw054   select
  2                            OWNER,
  3                            OBJECT_NAME,
  4                            SUBOBJECT_NAME,
  5                            OBJECT_ID,
  6                            DATA_OBJECT_ID,
  7                            'x123456789y123456789z123456789w123'||OBJECT_TYPE,
  8                            CREATED,
  9                            LAST_DDL_TIME,
 10                            TIMESTAMP,
 11                            STATUS,
 12                            TEMPORARY,
 13                            GENERATED,
 14                            SECONDARY,
 15                            NAMESPACE,
 16                            EDITION_NAME
 17                         from dba_objects
 18                        where object_type in ('QUEUE',
 19                                              'TABLE SUBPARTITION',
 20                                              'RULE SET',
 21                                              'CONSUMER GROUP',
 22                                              'MATERIALIZED VIEW',
 23                                              'PROGRAM',
 24                                              'DATABASE LINK',
 25                                              'JOB CLASS',
 26                                              'EVALUATION CONTEXT',
 27                                              'UNDEFINED',
 28                                              'RESOURCE PLAN',
 29                                              'CLUSTER',
 30                                              'JAVA SOURCE',
 31                                              'WINDOW',
 32                                              'INDEXTYPE',
 33                                              'RULE',
 34                                              'CONTEXT',
 35                                              'DIMENSION',
 36                                              'SCHEDULER GROUP',
 37                                              'SCHEDULE',
 38                                              'DESTINATION',
 39                                              'EDITION',
 40                                              'REWRITE EQUIVALENCE',
 41                                              'LOB PARTITION');

362 linhas criadas.

SQL >
SQL > commit;

Commit concluído.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW054', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > create index dbtw054_IDX on dbtw054(object_type);

Índice criado.

SQL >

 

2) Executar uma consulta usando a tabela criada

 

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
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-054.1 */ object_name,
  2         owner
  3    FROM DBTW054
  4   WHERE object_type='x123456789y123456789z123456789w123RULE';

OBJECT_NAME                    OWNER
------------------------------ -------------------
SYS$SERVICE_METRICS$61         SYS
ALERT_QUE$1                    SYS
SCHEDULER$_EVENT_QUEUE$1       SYS
SCHEDULER$_EVENT_QUEUE$3       SYS

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-054.1%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
3h619x7nhkxq9            0

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3h619x7nhkxq9, child number 0
-------------------------------------
SELECT /* DBTW-054.1 */ object_name,        owner   FROM DBTW054  WHERE
object_type=:"SYS_B_0"

Plan hash value: 1846229803

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |       |     5 (100)|          |      4 |00:00:00.01 |      16 |
|*  1 |  TABLE ACCESS FULL| DBTW054 |      1 |    362 | 26788 |     5   (0)| 00:00:01 |      4 |00:00:00.01 |      16 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE"=:SYS_B_0)


19 linhas selecionadas.

SQL >
SQL > SELECT column_name,
  2         num_distinct,
  3         density,
  4         num_nulls,
  5         histogram,
  6         num_buckets
  7    FROM user_tab_col_statistics
  8   WHERE table_name = 'DBTW054'
  9     AND column_name='OBJECT_TYPE';

COLUMN_NAME                    NUM_DISTINCT      DENSITY  NUM_NULLS HISTOGRAM       NUM_BUCKETS
------------------------------ ------------ ------------ ---------- --------------- -----------
OBJECT_TYPE                              22   .001381215          0 FREQUENCY                 1

1 linha selecionada.

SQL >
SQL > SELECT COUNT(1)
  2    FROM dbtw054;

  COUNT(1)
----------
       362

1 linha selecionada.

SQL >

 

Observe no plano de execução que o Otimizador estimou que a operação 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álculo da cardinalidade e quando geramos as estatísticas no inicio da simulação utilizando o pacote DBMS_STATS a criação do histograma na coluna OBJECT_TYPE considerou somente os 32 caracteres iniciais desta coluna, desprezando o resto (limite da versão 11g).

Consultando a visão de estatísticas das colunas (USER_TAB_COL_STATISTICS) 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 é o resultado da combinação de duas situações, 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ão 11g para a criação de histogramas.

Obs: Na versão 12c este limite é de 64 caracteres.

 

3) Eliminar o histograma da coluna OBJECT_TYPE

Para ajudar o Otimizador na estimativa da cardinalidade das linhas que serão selecionadas na tabela, podemos eliminar o histograma criado na coluna OBJECT_TYPE e alterar as preferências da coleta de estatísticas de forma que este histograma não seja mais gerado.

 

1
2
3
4
5
6
7
8
9
10
11
SQL > exec dbms_stats.delete_column_stats(ownname=> USER, tabname=>'DBTW054', colname=>'OBJECT_TYPE', col_stat_type=>'HISTOGRAM');

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > exec dbms_stats.set_table_prefs(USER, 'DBTW054', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 OBJECT_TYPE');

Procedimento PL/SQL concluído com sucesso.

SQL >

 

4) Executar a mesma consulta novamente

Vamos executar uma consulta na tabela e verificar a eficiência do plano de execução:
 

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
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-054.2 */ object_name,
  2         owner
  3    FROM DBTW054
  4   WHERE object_type='x123456789y123456789z123456789w123RULE';

OBJECT_NAME                    OWNER
------------------------------ ----------------------------
SYS$SERVICE_METRICS$61         SYS
ALERT_QUE$1                    SYS
SCHEDULER$_EVENT_QUEUE$1       SYS
SCHEDULER$_EVENT_QUEUE$3       SYS

4 linhas selecionadas.

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-054.2%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
fm9ww6785g3dm            0

1 linha selecionada.

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fm9ww6785g3dm, child number 0
-------------------------------------
SELECT /* DBTW-054.2 */ object_name,        owner   FROM DBTW054  WHERE
object_type=:"SYS_B_0"

Plan hash value: 87264188

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |       |     4 (100)|          |      4 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DBTW054     |      1 |     16 |  1184 |     4   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | DBTW054_IDX |      1 |     16 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"=:SYS_B_0)


20 linhas selecionadas.

SQL >
SQL > SELECT column_name,
  2         num_distinct,
  3         density,
  4         num_nulls,
  5         histogram,
  6         num_buckets
  7    FROM user_tab_col_statistics
  8   WHERE table_name = 'DBTW054'
  9     AND column_name='OBJECT_TYPE';

COLUMN_NAME                    NUM_DISTINCT      DENSITY  NUM_NULLS HISTOGRAM       NUM_BUCKETS
------------------------------ ------------ ------------ ---------- --------------- -----------
OBJECT_TYPE                              22   .045454545          0 NONE                      1

1 linha selecionada.

SQL >

 

Após a exclusão do histograma podemos verificar que a estimativa de cardinalidade melhorou (E-ROWS=16) e ficou mais próxima da quantidade de linhas selecionas (A-ROWS=4), com isso o Otimizador considerou que a leitura das linhas utilizando um índice seria mais eficiente.

Referências

https://docs.oracle.com/cd/E11882_01/server.112
 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

2 comments

  • JOSÉ GABRIEL DE NOVAIS XAVIER

    Muito bom, onde trabalhei como DBA nós não usávamos histogramas, apenas indices baseado nas estatisticas dos select’s.

    Aprendi mais um pouco sobre o assunto. Vlw!

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *